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

	/**
	 * <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;
	}