Switching from H2 to MySQL with iBatis

Consider your prototype technologies very carefully...

Well, always be careful when purchasing development code that uses any beta or 'prototype' tools to help the developer. You may just get stuck with them and it could hurt you during a customer launch.

My original launch of the software was plagued by my database going down a lot. This would happen when my virtual server provider would bounce my machine. No matter what script I came up with, H2 liked to either not start or start in a protected mode and not share its connections.

I will not have this problem with MySql because you get it hard-baked-in-as-a-service from any control panel you buy with a virtual server these days. It will also save me $15 per month because I can reduce my java processes I have to buy.

Hey, all these things add up when you're starting up.

My situation I'd like to share is this, the original developer used the H2 database. While a very cool database, there are a couple of problems with it:

Problems with H2:

1) I'm not as familiar with it so it soaks up my time. Can't stress this enough - what entrepreneur has lots of time? If you love H2, don't flame me - I'm simply more skilled with mysql - you can even buy lots of books on it.

I know it's a cool database and there is an active group, and it's all java. But I've got to share my perspective from a CTO / entrepreneur that only has so many dollars to get a product off the ground.

2) Every person that tried to setup the H2 environment to help code wasted lots of hours struggling with it - many never made it, many had a different version that was incompatible.

- Remember, I said be careful of beta software - it has a high cost when the you or especially another outsource group uses beta releases. What tends to happen is that the code jar version evolves fast, and there is a cost to upgrading to the latest jar version. Usually, the developers cut ties with any backwards compatibility and you have to retool some API calls. This is costly when you have only a startup budget and annoying and time consuming at best.

In my case, sometimes my H2 database data wouldn't be compatible with previous H2 releases. Or my developer (the new ones trying to help) would run the H2 console with one version but checkout of svn the old jar, etc. It was a real drag keeping it all in sync.

3) My production environment was never stable with it.

As mentioned above, this was very painful for my first two customers. It caused a lot of problems and probably cost me a faster pace of growing my business. I intend to go back to those customers, but I can't do it in good conscience until I get my environment 110% stable.

4) The main developer decided not to support this app once past the fun stage

Don't underestimate the value of going with technology that everybody understands. Asking someone to setup MySql and run a script is an easy thing to find.

The MySQL conversion:

So, I've been chipping away for months trying to change our code over to MySQL. Here's what I had to do:

1) Use Squirrel plugin to read my old H2 database and create the MySql schema. Wish I would have taken better notes, but at least I've got you off to the right path.

2) Change my AbatorConfig.xml to point to the new JDBC connection.

3) Regen my artifacts with Abator Plugin for Eclipse (believe me, if starting a new project in 2010, you'd probably want to use Spring Roo or even a more modern version of iBatis).

4) Fixed by hand the Campaign class to include all methods that CampaignWithBlobs had (don't worry, this note is just for me to remember what the hell I did).  Needed to hunt down numerous call in DAO and XML and DAOImpl to remove calls to WithBlobs - very extensive change.

There is always risk with these fancy auto-gen type arrangements that many of us rely on. I must stress that if your developer uses a stack to make his life easy, make sure you have it all explained to you and documented. It's a real drag trying to go read about Abator plugin (which is the oldest one he could have used and isn't developed on anymore).

5) Fixed all the DAOImpl.java classes and SQLMap.xml to use lowercase.

Let's talk about #5 above. I kept getting some obtuse UnresolvedDependency errors when starting tomcat. Took me forever to realize that some of the DAO and XML sql files had extra methods not generated by Abator.

By the way SR32.exe from http://www.funduc.com/srshareware.htm rocks! I purchased it for $25 fifteen years ago and still use it today.

Here's the regex expression I used to search for: "+[A-Z_].

And replaced it with: "%1<.

This replaced all my uppercase stuff with lowercase.

These DAO's had UPPERCASE calls, but MySql likes to make lowercase table names. Compare:

return (User)getSqlMapClientTemplate().queryForObject("USER.searchByEmailAddress", emailAddress);

return (User)getSqlMapClientTemplate().queryForObject("user.searchByEmailAddress", emailAddress);

And my SQL.XML files had the same problem:

resultMap="COMPANY.abatorgenerated

resultMap="company.abatorgenerated

This resulted in obtuse errors like this: org.springframework.beans.factory.UnsatisfiedDependencyException: There is no result map named COMPANY.abatorgenerated_CompanyResult in this SqlMap

Just note, MySql is case-sensitive (for search engines myaql case sensitive mysql casesensitive).

6) Change the sequence nextval to max(id)+1

Unfortunately, MySql doesn't support seq.nextval from dual. In other words, I had to ask for the max value of my id columns, like so:

select max(campaign_data_id)+1 from campaign_data

I put these statements into my AbatorConfig.xml file to make sure the generatedKey statements would work. So, my class_SqlMap.xml files for iBatis have calls like:

select max(campaign_data_id)+1 from campaign_data

7) Turns out, I also have to look out for nulls if we're not using sequences. That is, my final generatedKeys will also need to include the mysql IFNULL() command.

So, we have something like:

select IFNULL(max(company_id)+1, 1) from company;

This will replace the NULL with the value 1 to start my first inserts off in the right direction.

8) H2 uses sysdate and MySql uses sysdate()
Had to alter that in one hand created sql call.

Also, MySql uses BIT and H2 uses BOOLEAN.  This change was done by Abator correctly generating the right flavor.  Didn't know about BIT type.