Using Spring KeyHolder with Software Generated Primary Keys

Question

I am using Spring NamedParameterJdbcTemplate to perform insertion into a table. The table uses NEXTVAL for the sequence to get the primary key. Then I want this generated identifier to be passed back to me. I am using Spring's KeyHolder implementation as follows:

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

However, when I run this statement, I get:

 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

I think you are using the wrong method on JdbcTemplate . The only update method that seems to match your code snippet is

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

If so, you pass params and key as a two-element vargs array, and JdbcTemplate treats key as normal binding parameters and interprets it incorrectly.

The only publicly available update method on JdbcTemplate that accepts a KeyHolder is

 int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) 

So, you need to rephrase your code to use it.

Answer 2

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

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

with key column names containing automatically generated columns, in my case, just ["Id"]. Otherwise, you will receive a ROWID. See Spring doc for details.

Answer 3

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

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

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 4

Do not specify @konstantin's answer: Here is a complete working example: Assuming the database is the Oracle name and the name of the column that stores the generated identifier "GENERATED_ID" (it can be any name). NOTE. I used NamedParameterJdbcTemplate.update (....) In this example, the NOT JdbcTemplate Spring class.

  public Integer insertRecordReturnGeneratedId(final MyObject obj) { final String INSERT_QUERY = "INSERT INTO MY_TABLE VALUES(GENERATED_ID_SEQ.NEXTVAL, :param1, :param2)"; try { MapSqlParameterSource parameters = new MapSqlParameterSource().addValue( "param1", obj.getField1() ).addValue( "param2", obj.getField1() ) ; final KeyHolder holder = new GeneratedKeyHolder(); this.namedParameterJdbcTemplate.update( INSERT_QUERY, parameters, holder, new String[] {"GENERATED_ID" } ); Number generatedId = holder.getKey(); // Note: USING holder.getKey("GENERATED_ID") IS ok TOO. return generatedId.intValue(); } catch( DataAccessException dataAccessException ) { } } 

Answer 5

 CREATE TABLE 'vets' ( 'id' int(4) unsigned NOT NULL AUTO_INCREMENT, 'first_name' varchar(30) DEFAULT NULL, 'last_name' varchar(30) DEFAULT NULL, PRIMARY KEY ('id'), KEY 'last_name' ('last_name') ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; public @Data class Vet { private int id; private String firstname; private String lastname; } @Repository public class VetDaoImpl implements VetDao { /** Logger. */ private static final Logger LOGGER = LoggerFactory.getLogger(VetDaoImpl.class); private static final String INSERT_VET = "INSERT INTO vets (first_name, last_name) VALUES (:first_name, :last_name)"; @Autowired private NamedParameterJdbcTemplate namedParameterJdbcTemplate; @Override public Number insertVet(final Vet vet) { MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("first_name", vet.getFirstname()); paramSource.addValue("last_name", vet.getLastname()); KeyHolder keyHolder = new GeneratedKeyHolder(); int nbRecord = namedParameterJdbcTemplate.update(INSERT_VET, paramSource, keyHolder, new String[] {"id" }); LOGGER.info("insertVet: id ["+keyHolder.getKey()+"]"); return nbRecord; } }