import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
public class TestRetrieveAutoGeneratedKeys {
private static final String DRIVER_CLASSNAME = "oracle.jdbc.driver.OracleDriver";
private static final String IP = "192.168.123.72";
private static final int PORT = 1521;
private static final String SID = "wmdw";
private static final String USERNAME = "dss";
private static final String PASSWORD = "dss";
private static final String INSERT_SQL = "insert into testid(id,name) values(test_SEQ.Nextval,?)";
/**
* <pre>
* @param args
* </pre>
*/
public static void main(String[] args) {
int count = 10000;
TestRetrieveAutoGeneratedKeys test = new TestRetrieveAutoGeneratedKeys();
DataSource dataSource = createOracleDataSource(IP, PORT, SID, USERNAME,
PASSWORD);
JdbcTemplate jdbcTemplate = createJdbcTemplate(dataSource);
for (int i = 0; i < count; i++) {
Map<String, Object> map = test.getPKs(jdbcTemplate);
System.out.println("id:"
+ Integer.valueOf(map.get("id").toString()).intValue());
}
}
/**
* <pre>
* 得到数据库自动生成的PK的id
* 这里使用 oracle 数据库做的例子,其他的数据库同样适用,例如:MySQL,MS SQLServer
* DB: Oracle database
* -- Create table
* create table testid(
* id number(11),
* name varchar2(100),
* primary key(id)
* )
*
* -- Create sequence
* create sequence test_SEQ
* minvalue 1
* maxvalue 9999999999999999999999999999
* start with 1
* increment by 1
* nocache;
* @return
* </pre>
*/
private Map<String, Object> getPKs(JdbcTemplate jdbcTemplate) {
KeyHolder keyHolder = createKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(
Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(INSERT_SQL,
new int[] { 1 });
ps.setString(1, "name-1");
return ps;
}
}, keyHolder);
return keyHolder.getKeys();
}
private static DataSource createOracleDataSource(String ip, int port,
String sid, String username, String password) {
String url = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + sid;
BasicDataSource dataSource = createBasicDataSource();
dataSource.setDriverClassName(DRIVER_CLASSNAME);
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
return dataSource;
}
private static BasicDataSource createBasicDataSource() {
return new BasicDataSource();
}
private static KeyHolder createKeyHolder() {
return new GeneratedKeyHolder();
}
private static JdbcTemplate createJdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
另外可以参考一下org.springframework.jdbc.core.simple.AbstractJdbcInsert.java的源代码,看见spring是如何写的。
部分内容如下:
/**
* Method to execute the insert generating any number of keys
*/
private KeyHolder executeInsertAndReturnKeyHolderInternal(final List<Object> values) {
if (logger.isDebugEnabled()) {
logger.debug("The following parameters are used for call " + getInsertString() + " with: " + values);
}
final KeyHolder keyHolder = new GeneratedKeyHolder();
if (this.tableMetaDataContext.isGetGeneratedKeysSupported()) {
getJdbcTemplate().update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = prepareStatementForGeneratedKeys(con);
setParameterValues(ps, values, getInsertTypes());
return ps;
}
},
keyHolder);
}
else {
if (!this.tableMetaDataContext.isGetGeneratedKeysSimulated()) {
throw new InvalidDataAccessResourceUsageException(
"The getGeneratedKeys feature is not supported by this database");
}
if (getGeneratedKeyNames().length < 1) {
throw new InvalidDataAccessApiUsageException("Generated Key Name(s) not specified. " +
"Using the generated keys features requires specifying the name(s) of the generated column(s)");
}
if (getGeneratedKeyNames().length > 1) {
throw new InvalidDataAccessApiUsageException(
"Current database only supports retrieving the key for a single column. There are " +
getGeneratedKeyNames().length + " columns specified: " + Arrays.asList(getGeneratedKeyNames()));
}
// This is a hack to be able to get the generated key from a database that doesn't support
// get generated keys feature. HSQL is one, PostgreSQL is another. Postgres uses a RETURNING
// clause while HSQL uses a second query that has to be executed with the same connection.
final String keyQuery = this.tableMetaDataContext.getSimulationQueryForGetGeneratedKey(
this.tableMetaDataContext.getTableName(), getGeneratedKeyNames()[0]);
Assert.notNull(keyQuery, "Query for simulating get generated keys can't be null");
if (keyQuery.toUpperCase().startsWith("RETURNING")) {
Long key = getJdbcTemplate().queryForObject(getInsertString() + " " + keyQuery,
values.toArray(new Object[values.size()]), Long.class);
Map<String, Object> keys = new HashMap<String, Object>(1);
keys.put(getGeneratedKeyNames()[0], key);
keyHolder.getKeyList().add(keys);
}
else {
getJdbcTemplate().execute(new ConnectionCallback<Object>() {
@Override
public Object doInConnection(Connection con) throws SQLException, DataAccessException {
// Do the insert
PreparedStatement ps = null;
try {
ps = con.prepareStatement(getInsertString());
setParameterValues(ps, values, getInsertTypes());
ps.executeUpdate();
}
finally {
JdbcUtils.closeStatement(ps);
}
//Get the key
Statement keyStmt = null;
ResultSet rs = null;
Map<String, Object> keys = new HashMap<String, Object>(1);
try {
keyStmt = con.createStatement();
rs = keyStmt.executeQuery(keyQuery);
if (rs.next()) {
long key = rs.getLong(1);
keys.put(getGeneratedKeyNames()[0], key);
keyHolder.getKeyList().add(keys);
}
}
finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(keyStmt);
}
return null;
}
});
}
return keyHolder;
}
return keyHolder;
}