Using Spring's KeyHolder with programmatically-generated primary keys

Question

Hello, 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

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.

Answer 2

I think you're using the wrong method on JdbcTemplate. The only one of the update methods that would seem to match your code fragment is

int update(String sql, Object... args)

If so, you're passing params and key as a two-element vargs array, and JdbcTemplate is treating key as a normal bind parameters, and mis-interpreting it.

The only public update method on JdbcTemplate that takes a KeyHolder is

int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder)

So you'll need to rephrase your code to use that.