2

I'm using SELECT LAST_INSERT_ID() to get the last auto-incremented value. It works fine from the SQLyog and command prompt but when I use the same query using Spring JdbcTemplate like this:

String SQl1="SELECT LAST_INSERT_ID()";
int sales_id=jdbcTemplateObject.queryForInt(SQl1);
System.out.println("returned sales_id is"+sales_id);

it returns 0 rather than the expected last auto-incremented value. How can I get the expected value ?

TLama
  • 75,147
  • 17
  • 214
  • 392
irfan shafi
  • 524
  • 1
  • 6
  • 21
  • this is a mysql issue, not a java one. Did you do any inserts at all? what "Select Max(id)" gives you? have you checked that? http://stackoverflow.com/questions/12125385/last-inserted-id-from-specific-table – Kostas Kryptos Dec 20 '14 at 11:51
  • off course i am inserting value before retrieving the row , max(id) is working fine – irfan shafi Dec 22 '14 at 06:25

1 Answers1

0

You probably use a connection pool in Spring JdbcTemplate.

If yes, your driver may use one connection to make the insert, but another different one for the last_insert_id query.

To force using the same connection, start a Transaction before inserting, and commit it after the last_insert_id query.

Kostas Kryptos
  • 4,081
  • 2
  • 23
  • 24
  • i am doing both the things in the same method consecutive statements how the connection object will be different and i am not using the connection pool anyways. – irfan shafi Dec 22 '14 at 08:46
  • why don't you try a transaction to be sure? even if consecutive, this doesn't mean they will be served from the same connection if you used a pool. Are you sure you are not utilizing the default pool? – Kostas Kryptos Dec 22 '14 at 08:55