Java – insert the row and get the generated ID

I am trying to use spring's jdbctemplate class to insert a row into the MySQL table named transaction and get the generated ID. the relevant code is:

public Transaction insertTransaction(final Transaction tran) {

    // Will hold the ID of the row created by the insert
    KeyHolder keyHolder = new GeneratedKeyHolder();

    getJdbcTemplate().update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws sqlException {

            PreparedStatement ps = connection.prepareStatement(INSERT_TRAN_sql);
            ps.setString(1,tran.getTransactionType().toString());

            Date sqlDate = new Date(tran.getDate().getTime());
            ps.setDate(2,sqlDate);
            ps.setString(3,tran.getDescription());

            return ps;
        }
    },keyHolder);

    tran.setId(keyHolder.getKey().longValue());
    return tran;
}

But call getjdbctemplate() Update throws the following exception

Can I insert a row and get the generated ID without abandoning the jdbctemplate? I use spring 2.5 and MySQL 5.5 27 and MySQL connector 5.1 twenty-six

Solution

Please prepare your statement as shown below

PreparedStatement ps = connection.prepareStatement(
                           INSERT_TRAN_sql,Statement.RETURN_GENERATED_KEYS);

The underlying jdbc driver (used indirectly through spring's jdbctemplate here) needs to prompt you to retrieve the generated key This can be done when preparing the Preparedstatement

connection.prepareStatement(strsql,Statement.RETURN_GENERATED_KEYS);

Or, when executing the declaration

statement.executeUpdate(strsql,Statement.RETURN_GENERATED_KEYS);

This is also Java sql. The content pointed to by sqlexception