Using Spring's KeyHolder with programmatically-generated primary keys

Question

I am using Spring's NamedParameterJdbcTemplate to perform an insert into a table. The table uses a NEXTVAL on a sequence to obtain the primary key. I then want this generated ID to be passed back to me. I am using Spring's KeyHolder implementation like this:

KeyHolder key = new GeneratedKeyHolder();
jdbcTemplate.update(Constants.INSERT_ORDER_STATEMENT, params, key);

However, when I run this statement, I am getting:

org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]
    at org.springframework.jdbc.support.GeneratedKeyHolder.getKey(GeneratedKeyHolder.java:73)

Any ideas what I am missing?

Answer 1

Just solved a similar issue - with Oracle you need to use another method (from NamedParameterJdbcOperations) -

int update(String sql,
           SqlParameterSource paramSource,
           KeyHolder generatedKeyHolder,
           String[] keyColumnNames)
           throws DataAccessException

with keyColumnNames containing auto-generated columns, in my case just ["Id"]. Otherwise all you get is ROWID. See Spring doc for details.

Answer 2

You have to execute the JdbcTemplate.update(PreparedStatementCreator p, KeyHolder k).

The key returned from the database will be injected into the KeyHolder parameter object.

An example:

final String INSERT_ORDER_STATEMENT 
       = "insert into order (product_id, quantity) values(?, ?)";

KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(
            Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(
                    INSERT_ORDER_STATEMENT, new String[] { "id" });
                ps.setInt(1, order.getProductId());
                ps.setInt(2, order.getQuantity());
                return ps;
            }
        }, keyHolder);

More information can be found here in the reference documentation.