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.


Popular posts from this blog

Setting up UPS link with an ubuntu server

Open ssh connection through proxy with NTML authentication

Limit the upload bandwidth of your apache webserver with mod_bw