converting J2EE App from Sql to Oracle - suggestions with effecient approach

Question

We have a J2EE app built on Struts2+spring+iBatis; not all DAO's use iBatis...some code still uses the old JDBC approach of interacting with Database. All our DAO's call Stored Procedures, we do not have any inline SQL. Since Oracle Stored Procedures return cursors, we have to drastically change our code.

It is fairly easy for us to convert current iBatis mappings (in sql) to oracle (used a groovy script to do this) also it is easy to convert Java code that was calling old mappings that were in sql.

Our problem is to convert the old DAO's that still use JDBC approach. Since we will have to modify them anyways (because we are now using oracle) we are thinking about converting them to iBatis mappings. is this a good approach? This will be a huge effort from our side...

what do you think will be the best approach to tackle this huge effort?

• should we just get to work and start converting each method in every DAO
• should we try to make some small script that looks at each method, parses out relevant information and makes iBatis mappings from that.
• for maintenance and seperation purpose should we have 1 iBatis mapping for each DAO

I appologize if the question is vague but am just looking for someone who has gone through this type of thing before and has some pointers or 'lessons learned'.

Answer 1

The first thing you should do is cover your DAO layer in tests. This way you'll know if you broke something during the conversion. If you are moving a stored procedure from one DBMS to Oracle, you should also write tests for that using a framework like DbUnit.

You should have a TEST DB instance populated with sample data that doesn't change. You should be able to refresh this DB with the same set of sample data after your are done running your tests. This will ensure your TEST DB is in a known state. You will then have your input parameters paired with some expected (correct) result. Your test will read in these pairs and execute them against the test DB instance and confirm the expected result is returned. Assuming your tests mutate the DB, you'll want to refresh the DB between runs of your test suite.

Second, if you're already going in and changing some data access implementations for Oracle, why not use this as an opportunity to move some of that business logic out of the DB and into Java? There are many well-documented problems with maintaining large codebases in a DBMS.

should we try to make some small script that looks at each method, parses out relevant information and makes iBatis mappings from that.

I don't recommend this. The time you'd spend tweaking the script for each special case, plus hunting down all the bugs it would introduce would be better spent doing the conversion by a thinking human.

for maintenance and seperation purpose should we have 1 iBatis mapping for each DAO

That's a fine idea. You can then combine them in your sqlMapConfig with

<sqlMap resource="sqlMaps/XXX.xml" />

This will keep your mappings more manageable. Just make sure to specify the namespace attribute in each sqlMap like:

<sqlMap namespace="User">

So that you can reuse mappings between the sqlMaps for instantiating object graphs (example: when loading a User and his Permissions, the User.xml sqlMap calls the Permission.xml mapping).

Answer 2

All our DAO's call Stored Procedures

I don't see what iBatis is buying you here.

It's also not clear what the migration is. Are you saying that you've decided to move all the code into stored procedures, so there's no more in-line SQL? If that's the case, I'd say don't use iBatis. If you're already using Spring, let it call into Oracle using its StoredProcedure object and map the cursors into objects.

The recommendation to create JUnit or, better yet, TestNG tests is spot on. Do that before changing anything.