Sunday, 22 June 2008

MySQL last_insert_id and Spring Framework JdbcTemplate

I'm using Spring Framework JdbcTemplate on a project with MySQL server as database.

And I've noticed that the query "SELECT last_insert_id()" returns 0 most of the time.

Using this syntax improves a bit :

SELECT last_insert_id() from `last_inserted_table` LIMIT 1

The reason is that for each jdbc access, Spring use a connection for it's connection pool.

So it may use one connection from its pool, to make the insert, and another connection for the last_insert_id query.

The problem is that the last_insert_id query is tied to the connection that actually made the insert.

In order to force Spring JdbcTemplate to use the same connection all along a portion of code is to start a Transaction before the insert, and commit it after the last_insert_id query.

See this post for simply managing transaction.

You might say "I always use transaction"... well, in my case, transaction were not needed until this last_insert_id issue that returned 0.