insert row and get generated ID

Question

I'm trying to use Spring's JdbcTemplate class to insert a row into a 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 the following exception is thrown by the call to getJdbcTemplate().update

java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate() or Connection.prepareStatement().

Can I insert the row and get the generated ID, without abandoning JdbcTemplate? I'm using Spring 2.5, MySQL 5.5.27 and MySQL Connector 5.1.26.

With MySQL I think you will need to call `last_insert_id()`. See [here](http://stackoverflow.com/questions/7501464/how-to-get-the-id-of-inserted-row-in-mysql). — Sotirios Delimanolis, Sep 30 '13 at 13:57

GerritCap · Answer 1 · 2015-01-14T10:08:04.233

10

There is an easier way to get that behaviour:

protected JdbcTemplate            jdbcTemplate;
private SimpleJdbcInsert          insert;

    this.jdbcTemplate = new JdbcTemplate(this.databaseSetup.getDataSource());
    this.insert = new SimpleJdbcInsert(this.jdbcTemplate).withTableName(this.tableName).usingGeneratedKeyColumns(this.pkColumn);

Then you create a Map called parameters which conmtains the values for each column name in your table and insert a record like this:

    final Map<String, Object> parameters = new HashMap<>();
    parameters.put("empName", employee.getName()); // store the String name of employee in the column empName
    parameters.put("dept", employee.getDepartment()); // store the int (as Integer) of the employee in the column dept
    final Number key = this.insert.executeAndReturnKey(parameters);
    final long pk = key.longValue();

edited Jan 14 '15 at 10:08

answered Sep 30 '13 at 14:05

GerritCap

1,606
10
9

Would be great if you provided an example of the mapped values in your answer. From the current answer, it's not obvious to use a Map without looking further into external documentation and what is represented by Key and Value. This would deserve an upvote if that information were available. – Hazok Jan 13 '15 at 21:57
I thought it was quite obvious that if I mention a Map parameters containing values for each column name, as I mentioned column name it should have been obvious that the keys are Strings and values as undetermined can be any Object mapping to an sql type. BTW: it never hurts to check the documentation, never trust any particular answer and not double check it. – GerritCap Jan 14 '15 at 10:11
That is obvious to check documentation and not to trust a particular answer. It's also obvious that a good question on Stackoverflow provides all the necessary details and doesn't leave anything up to ambiguity. It's also obvious that someone using the Java JDBC stack for the first won't see these types of things as obvious ;) – Hazok Jan 14 '15 at 16:38

score 8 · Accepted Answer · answered Sep 30 '13 at 14:05

Just prepare your Statement as follows

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

The underlying JDBC driver (used indirectly through the Spring's JdbcTemplate here) requires a hint that you would like to retrieve the generated keys. This can be done either while preparing a PreparedStatement as

connection.prepareStatement(strSQL, Statement.RETURN_GENERATED_KEYS);

or, at the time of executing a Statement as

statement.executeUpdate(strSQL, Statement.RETURN_GENERATED_KEYS);

This is what your java.sql.SQLException is pointing at as well.

Hi, just a question, if the query succeeds, it returns the generated ID, but what does it return if the query fails to execute, so that I could handle the error gracefully in my code? — Sajib Acharya, Mar 24 '16 at 08:46

score 0 · Answer 3 · edited Sep 03 '14 at 05:40

You can retrieve the next sequence number like in step 1, then it can passed in the insert statement as in the step 2:

1-

Integer nextSeq = (Integer) getJdbcTemplate().queryForObject(
        "select SEQ_CUSTOMER_ID.nextVal from dual", new Object[] {}, Integer.class);

2-

getJdbcTemplate().update(
        "INSERT INTO customer "
        + "(CUST_ID, NAME, UPDATED) VALUES (?, ?, ?)",
        new Object[] { nextSeq ,customer.getName(),
                customer.getUpdated() });

insert row and get generated ID

3 Answers3

Linked