Sometime you need to get the auto generated key of last record inserted into a table. It is possible to use Interface JdbcTemplate() and KeyHolder interface; this sample has been tested with mySql DB and Spring 3.2:
final String sqlCommand = new String("INSERT INTO Template_request(title, descriptio) VALUES (?, ?)");
KeyHolder holder = new GeneratedKeyHolder();
getJdbcTemplate().update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sqlCommand, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, templateRequest.getTitle());
ps.setString(2, templateRequest.getDecription());
return ps;
}
}, holder);
Long request_id = holder.getKey().longValue(); |
final String sqlCommand = new String("INSERT INTO Template_request(title, descriptio) VALUES (?, ?)");
KeyHolder holder = new GeneratedKeyHolder();
getJdbcTemplate().update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sqlCommand, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, templateRequest.getTitle());
ps.setString(2, templateRequest.getDecription());
return ps;
}
}, holder);
Long request_id = holder.getKey().longValue();
This is the definition of Template_request table:
DROP TABLE IF EXISTS `Template_request`;
CREATE TABLE Template_request
(
id_templaterequest INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(256) NOT NULL,
description VARCHAR(2048),
date_uprec TIMESTAMP DEFAULT now() ON UPDATE now(),
CONSTRAINT PK_Template_request PRIMARY KEY (id_templaterequest)
); |
DROP TABLE IF EXISTS `Template_request`;
CREATE TABLE Template_request
(
id_templaterequest integer UNSIGNED NOT NULL AUTO_INCREMENT,
title varchar(256) NOT NULL,
description varchar(2048),
date_uprec timestamp default now() on update now(),
CONSTRAINT PK_Template_request PRIMARY KEY (id_templaterequest)
);
interface KeyHolder
Interface for retrieving keys, typically used for auto-generated keys as potentially returned by JDBC insert statements.
Implementations of this interface can hold any number of keys. In the general case, the keys are returned as a List containing one Map for each row of keys.
Most applications only use on key per row and process only one row at a time in an insert statement. In these cases, just call getKey to retrieve the key. The returned value is a Number here, which is the usual type for auto-generated keys.
public class JdbcTemplate extends JdbcAccessor implements JdbcOperations
This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
Code using this class need only implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection, providing SQL and any necessary parameters. The ResultSetExtractor interface extracts values from a ResultSet. See also PreparedStatementSetter and RowMapper for two popular alternative callback interfaces.
Can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as bean reference. Note: The DataSource should always be configured as a bean in the application context, in the first case given to the service directly, in the second case to the prepared template.
Because this class is parameterizable by the callback interfaces and the SQLExceptionTranslator interface, there should be no need to subclass it.
All SQL operations performed by this class are logged at debug level, using “org.springframework.jdbc.core.JdbcTemplate” as log category.
