Showing posts from 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.