Obtaning next MySQL auto-increment ID for J2EE CMP ejbCreate method

I have recently been working a lot with J2EE. Well working or studying (however you want to call it :-D). Anyways, I ran into a serious problem and for the first time, even Google wasn't much help.

Application Server: Glassfish / Sun App Server 9
Database Server: MySQL
EJB Version: 2.0 / 2.1

I assume that you all know how EJB CMPs (Container-Managed Persistence) work. The developer basically provides a set of cmp-fields which are the database attributes, provices a create/ejbCreate(parameters_here) method which is used by the container to generate SQL queries that insert data into the database: OK, before you scream at me, it is not this simple but I just want to get to the point.

This usually works without a problem if you can provide the primary key to the ejbCreate method. Let's assume your ejbCreate method looks like this:

public Long ejbCreate(Long id, String firstname, String lastname) { ... }.

To this assumption, let's add the fact that the primary key here (Long id) is auto-generated by your database management system, in this case MySQL.

The problem arises that there is no way you can provide an ID to your create method. So you would tend to do the following:

customerHome.create(null, "Mickey", "Mouse");

The problem with the code above is that your EJB container will scream at you and throw some aweful exceptions.

I also thought about trying something like this:

customerHome.create(new Long(-1), "Mickey", "Mouse");

My assumption was that the since ID was the primary key field with auto-increment in the MySQL table design, the RDBMS would intelligently decide that "-1" was not right and would assign the right value. I unfortunately found out that MySQL was not that smart.

I searched Google and asked a few developers friends to see whether MySQL had a way to determine the next primary key sequence like Oracle does (sequence.nextValue() ?) and I could not get much help either from Google or other developers.

After some serious digging in the functionning of MySQL and Schemas, I ran into the fact that one could find about the status of all tables in a database using the following query:

SHOW TABLE STATUS.

Such query will show the current values of the properties of all tables in the currently selected database. Interestingly enough, there is a property called "Auto_increment". Some search in the manuals told me that the "Auto_increment" property contains an integer value showing the next "auto-increment" value a inserted record would have for primary key. That's all that was needed.

Next, there was a need to narrow the results: instead of showing the status of all the tables in the database, I need to get the status of only one table: the one I was interested into. In this example, that would be the CUSTOMERBEAN table. The following query sorts it out:

SHOW TABLE STATUS LIKE "TABLE_NAME"

In our case, we would write is as
SHOW TABLE STATUS LIKE "CUSTOMERBEAN". For some reason, I think the MySQL guys need to redesign this. I've been reading a lot about API design and the above sentence does not convey much about what the query does (this was just a side note).

Once this can be done, the rest is just about obtaining a ResultSet object from the query above, get the value of the Auto_increment field and pass it to your cutomerHome.create(...) method call. In my particular case, I wrote a utility class to do that for me. I just get the result and up and running I am!

I hope this helps any of you developers out there dealing with the same problem. It took me about 2 hours to sort it out and that was quite a waste of time.

Also, if you think there are other ways (I know that there is a different way of handling this with the WebLogic server), please post a comment and let me know.

-- JPGeek