Insert row and get generated id

Question

I am trying to use the Spring JdbcTemplate class to insert a row into a MySQL table called transaction and get the generated identifier. Relevant Code:

 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 called by getJdbcTemplate().update

java.sql.SQLException: generated keys are not requested. You must specify Statement.RETURN_GENERATED_KEYS for Statement.executeUpdate () or Connection.prepareStatement ().

Is it possible to insert a string and get the generated identifier without dropping the JdbcTemplate ? I am using Spring 2.5, MySQL 5.5.27 and MySQL Connector 5.1.26.

Answer 1

Just prepare a Statement as follows

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

The main JDBC driver (used indirectly through Spring JdbcTemplate here) requires specifying that you want to get the generated keys. This can be done either by preparing the PreparedStatement as

 connection.prepareStatement(strSQL, Statement.RETURN_GENERATED_KEYS); 

or, at the time of execution of Statement as

 statement.executeUpdate(strSQL, Statement.RETURN_GENERATED_KEYS); 

This is what your java.sql.SQLException points to.

Answer 2

There is an easier way to get this behavior:

 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, that support 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(); 

Answer 3

You can get the following sequence number, as in step 1, then it can be passed in the insert statement, as in 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() });