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 :
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.
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 1The 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.
Comments