I am trying to insert a row into a MySQL table and get it's insert ID. I am aware of the MySQL last_insert_id()
function, I just cannot seem to get it to work. Currently, I am trying to use a function annotated as a transaction and I am only getting 0 returned. I am using Spring 3.1.
@Transactional (propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
private long insertTransactionRecord
(
int custID,
int porID,
String date,
short crvID
) {
m_template.update ("INSERT INTO " +
" transaction " +
"( " +
" por_id, " +
" cust_id, " +
" trans_date, " +
" crv_id " +
") " +
"VALUES " +
"( " +
" ?, " +
" ?, " +
" ?, " +
" ? " +
")",
new Object[] {
porID,
custID,
date,
crvID
});
return m_template.queryForLong ("SELECT " +
" last_insert_id() " +
"FROM " +
" transaction " +
"LIMIT 1");
}
Use Spring's built-in support for this rather than doing it yourself.
SqlUpdate insert = new SqlUpdate(ds, "INSERT INTO company (name) VALUES (?)");
insert.declareParameter(new SqlParameter(Types.VARCHAR));
insert.setReturnGeneratedKeys(true);
// assuming auto-generated col is named 'id'
insert.setGeneratedKeysColumnNames(new String[] {"id"});
insert.compile();
....
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
insert.update(new Object[]{"test"}, keyHolder);
System.out.println(keyHolder.getKey().longValue());
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With