jdbc map获取keys_spring jdbctemple 获得自动生成的主键ID

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,?)";

/**

*

* @param args

*

*/

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 map = test.getPKs(jdbcTemplate);

System.out.println("id:"

+ Integer.valueOf(map.get("id").toString()).intValue());

}

}

/**

*

* 得到数据库自动生成的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

*

*/

private Map 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 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 keys = new HashMap(1);

keys.put(getGeneratedKeyNames()[0], key);

keyHolder.getKeyList().add(keys);

}

else {

getJdbcTemplate().execute(new ConnectionCallback() {

@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 keys = new HashMap(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;

}