last_inserted_id() and jdbc

[halhaig]

I have a relational table with the following definition:

CREATE TABLE heap (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
object MEDIUMBLOB);

I would like to use SPRING RDBMS objects to insert rows (one at a time) into this table. I would like to use the function last_inserted_id() to get back the value of the id field. For this to work both the insert and the call to last_inserted_id() have to be performed on the same connection. Each RDBMS object establishes it own connection. So using two of these object, one for the insert, and one for the call to last_insered_id() does not work.

What would be the best way to perform both of these db operations through a single connection. I want to rely on SPRING as much as possible. Do I need to subclass jdbcTemplate? Or is there some elegant straightforward way to make this work.

Thanks in advance for any help.

[trisberg]

If you have a jdbc driver that supports the JDBC 3.0 auto generated key feature, then the following code illustrates how to use Spring's generated key feature:

		SqlUpdate su = new SqlUpdate(dataSource, "insert into Show2 (name) values(?)");
		su.declareParameter(new SqlParameter("name", Types.VARCHAR));
		su.setReturnGeneratedKeys(true);
		su.compile();
		Object[] val = {"Xxxx"};
		KeyHolder kh = new GeneratedKeyHolder();
		int updateCnt = su.update(val, kh);
		int myKey = kh.getKey().intValue();
		System.out.println("Key: " + myKey);

[Colin Sampaleanu]

I would like to use SPRING RDBMS objects to insert rows (one at a time) into this table. I would like to use the function last_inserted_id() to get back the value of the id field. For this to work both the insert and the call to last_inserted_id() have to be performed on the same connection. Each RDBMS object establishes it own connection. So using two of these object, one for the insert, and one for the call to last_insered_id() does not work.

In fact, as long as you are using a wrapping transaction with a Spring transaction manager, the first used connection via a Spring jdbc class will be bound to the current thread and used for subsequent Spring jdbc operations. In fact this connection sharing can happen even between Hibernate and JDBC, or JDO and JDBC (with the right JDODialet registered).

So you should just be able to read back the last value.