下面列出了java.sql.Statement#getGeneratedKeys ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* Inserts one row into a table with an auto-generated column, then
* examines the metadata for the generatedKeys ResultSet.
* Old master Test 14.
* @throws SQLException
*/
public void testGetKeyMetadataAfterInsert() throws SQLException
{
Statement s = createStatement();
s.execute("insert into t31_AutoGen(c31) values (99)",
Statement.RETURN_GENERATED_KEYS);
ResultSet rs = s.getGeneratedKeys();
ResultSetMetaData rsmd = rs.getMetaData();
assertEquals("ResultSet column count", 1, rsmd.getColumnCount());
assertEquals("Column type", "DECIMAL", rsmd.getColumnTypeName(1));
assertEquals("Column precision", Limits.DB2_MAX_DECIMAL_PRECISION_SCALE, rsmd.getPrecision(1));
assertEquals("Column scale", 0, rsmd.getScale(1));
int keyval = getKeyValue (rs);
assertEquals("Key value", 1, keyval);
rs.close();
s.close();
}
private Object getAutoIncrementPrimaryKeyValue(Statement sourceStatement) throws SQLException {
ResultSet rs = null;
try {
try {
rs = sourceStatement.getGeneratedKeys();
} catch (SQLException e) {
if (e.getSQLState().equalsIgnoreCase("S1009")) {
rs = sourceStatement.executeQuery("SELECT LAST_INSERT_ID()");
}
}
if (rs != null && rs.next()) {
Object obj = rs.getObject(1);
return obj;
}
} finally {
if (rs != null)
rs.close();
}
return null;
}
/**
* Inserts a row according to a string statement and returns the last inserted row id;
* @param statement
* @return last inserted id
* @throws SQLException
*/
private long addRow(String statement) throws SQLException {
Connection con = ds.getConnection();
Statement st = con.createStatement();
long lastId = -1;
synchronized (DumpOpenFacts2SQL.class) {
String generatedColumns[] = {"id"};
st.executeUpdate(statement, generatedColumns);
ResultSet lastInsertedRow = st.getGeneratedKeys();
if(lastInsertedRow.next()) {
lastId = lastInsertedRow.getLong(1);
}
//}
}
st.close();
con.close();
return lastId;
}
@Override
public Map<String, Object> getGeneratedKey(Statement statement, List<String> autoincrementKeys) throws SQLException {
// 检索由于执行此 Statement 对象而创建的所有自动生成的键
Map<String, Object> _ids = new HashMap<String, Object>();
ResultSet _keyRSet = statement.getGeneratedKeys();
try {
for (String _autoKey : autoincrementKeys) {
while (_keyRSet.next()) {
Object _keyValue;
try {
_keyValue = _keyRSet.getObject(_autoKey);
} catch (SQLException e) {
_keyValue = _keyRSet.getObject(1);
}
_ids.put(_autoKey, _keyValue);
}
}
} finally {
_keyRSet.close();
}
return _ids;
}
@Test
public void testStatementExecuteAutoincrement() throws SQLException {
createTable("CheckDataTest1", "id int not null primary key auto_increment, test varchar(10)");
Statement stmt = sharedConnection.createStatement();
int insert =
stmt.executeUpdate(
"INSERT INTO CheckDataTest1 (test) VALUES ('test1')", Statement.RETURN_GENERATED_KEYS);
assertEquals(1, insert);
int[] autoInc = setAutoInc();
ResultSet rs = stmt.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(autoInc[0] + autoInc[1], rs.getInt(1));
assertFalse(rs.next());
rs = stmt.executeQuery("SELECT * FROM CheckDataTest1");
assertTrue(rs.next());
assertEquals(autoInc[1] + autoInc[0], rs.getInt(1));
assertEquals("test1", rs.getString(2));
assertFalse(rs.next());
}
@Test
public void checkMultiGeneratedKeys() throws SQLException {
try (Connection connection = setConnection("&allowMultiQueries=true")) {
Statement stmt = connection.createStatement();
stmt.execute("SELECT 1; SET @TOTO=3; SELECT 2", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getResultSet();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(stmt.getMoreResults());
stmt.getGeneratedKeys();
assertTrue(stmt.getMoreResults());
rs = stmt.getResultSet();
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
}
}
@Test
public void assertGetGeneratedKeysForSingleRoutedStatement() throws SQLException {
for (Statement each : statements.values()) {
each.execute("INSERT INTO t_order_item (user_id, order_id, status) VALUES (1, 1, 'init')", Statement.RETURN_GENERATED_KEYS);
ResultSet generatedKeysResult = each.getGeneratedKeys();
assertTrue(generatedKeysResult.next());
assertTrue(generatedKeysResult.getInt(1) > 0);
}
}
/**
* Retorna o ID que foi gerado no banco de dados
* @param stmt comando enviado
* @return ID gerado pelo banco de dados
* @throws SQLException
*/
protected Integer getId(Statement stmt) throws SQLException {
ResultSet chavesGeradas = stmt.getGeneratedKeys();
if (chavesGeradas.next()){
return chavesGeradas.getInt(1);
} else {
return null;
}
}
private void loadByJDBC(DatabaseObject forObject, int atStage, Statement stmt) throws SQLException
{
try (ResultSet rs = stmt.getGeneratedKeys()) {
if (rs.next()) {
forObject.setPkey(rs.getInt(1));
}
}
}
private void vetClosedAutoGen( boolean closeOnCompletion, boolean delayClosureCall ) throws Exception
{
Statement stmt = createStatement();
ResultSet rs;
println( "Verifying AUTOGENERATED KEYS wrapper on " + stmt.getClass().getName() +
" with closeOnCompletion = " + closeOnCompletion +
" and delayClosureCall = " + delayClosureCall );
Wrapper41Statement wrapper = new Wrapper41Statement( stmt );
if ( !delayClosureCall )
{ setCloseOnCompletion( wrapper, closeOnCompletion ); }
stmt.executeUpdate( "insert into t_autogen( b ) values ( 1 )", Statement.RETURN_GENERATED_KEYS );
rs = stmt.getGeneratedKeys();
if ( delayClosureCall )
{ setCloseOnCompletion( wrapper, closeOnCompletion ); }
assertFalse( wrapper.isClosed() );
rs.close();
assertEquals( closeOnCompletion, wrapper.isClosed() );
vetSuccessfulClosure( wrapper, closeOnCompletion );
}
private void getGenerateKeys(Statement st) throws SQLException {
ResultSet rs = st.getGeneratedKeys();
if(rs != null) {
while (rs.next()) {
System.out.println("####################--GK: "+rs.getInt(1));
}
}
if(rs != null) {
rs.close();
}
}
@Test
public void assertGetGeneratedKeysForSingleRoutedStatement() throws SQLException {
for (Statement each : statements.values()) {
each.execute("INSERT INTO t_order_item (user_id, order_id, status) VALUES (1, 1, 'init')", Statement.RETURN_GENERATED_KEYS);
ResultSet generatedKeysResult = each.getGeneratedKeys();
assertTrue(generatedKeysResult.next());
assertTrue(generatedKeysResult.getInt(1) > 0);
}
}
/**
* Retrieves the generated value by using {@link Statement#getGeneratedKeys()}.
*
* @param config the configuration
* @param statement the SQL INSERT statement
* @return the generated value
* @throws JdbcException if the generation is failed
*/
protected long getGeneratedValue(IdGenerationConfig config, Statement statement) {
try {
final ResultSet resultSet = statement.getGeneratedKeys();
return getGeneratedValue(config, resultSet);
} catch (final SQLException e) {
throw new JdbcException(Message.DOMA2018, e, config.getEntityType().getName(), e);
}
}
public void dumpKeys( Statement statement )
throws SQLException
{
ResultSet gen = statement.getGeneratedKeys();
// specs require empty resultset instead
assertNotNull( gen );
ResultSetMetaData meta = gen.getMetaData();
boolean empty = true;
while( gen.next() )
{
System.out.print( empty ? "generated keys: " : ", " );
empty = false;
for( int i = 1; i <= meta.getColumnCount(); i ++ )
{
System.out.print( ( i > 1 ? ", " : "" ) + meta.getColumnName( i ) + "=" + String.valueOf( gen.getObject( i ) ) );
}
}
if( ! empty )
{
System.out.println();
}
}
@Test
public void generatedKeys() throws Exception {
Statement st = sharedConnection.createStatement();
int[] autoInc = setAutoInc();
st.executeUpdate("insert into genkeys(dataField) values('a')", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = st.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(autoInc[0] + autoInc[1], rs.getInt(1));
assertEquals(autoInc[0] + autoInc[1], rs.getInt("priKey"));
assertEquals(autoInc[0] + autoInc[1], rs.getInt("foo"));
int[] indexes = {1, 2, 3};
st.executeUpdate("insert into genkeys(dataField) values('b')", indexes);
rs = st.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(autoInc[1] + 2 * autoInc[0], rs.getInt(1));
try {
rs.getInt(2);
fail("should never get here");
} catch (SQLException e) {
// eat
}
String[] columnNames = {"priKey", "Alice", "Bob"};
st.executeUpdate("insert into genkeys(dataField) values('c')", columnNames);
rs = st.getGeneratedKeys();
assertTrue(rs.next());
for (int i = 0; i < 3; i++) {
assertEquals(autoInc[1] + 3 * autoInc[0], rs.getInt(columnNames[i]));
}
}
@Test
public void testStatementBatch() throws SQLException {
Assume.assumeFalse(isGalera());
createTable("CheckDataTest2", "id int not null primary key auto_increment, test varchar(10)");
Statement stmt = sharedConnection.createStatement();
stmt.addBatch("INSERT INTO CheckDataTest2 (id, test) VALUES (2, 'test1')");
stmt.addBatch("INSERT INTO CheckDataTest2 (test) VALUES ('test2')");
stmt.addBatch("UPDATE CheckDataTest2 set test = CONCAT(test, 'tt')");
stmt.addBatch("INSERT INTO CheckDataTest2 (id, test) VALUES (9, 'test3')");
int[] res = stmt.executeBatch();
assertEquals(4, res.length);
assertEquals(1, res[0]);
assertEquals(1, res[1]);
assertEquals(2, res[2]);
assertEquals(1, res[3]);
final int[] autoInc = setAutoInc();
ResultSet rs = stmt.getGeneratedKeys();
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
assertTrue(rs.next());
assertEquals(2 + autoInc[0] + autoInc[1], rs.getInt(1));
assertTrue(rs.next());
assertEquals(9, rs.getInt(1));
assertFalse(rs.next());
rs = stmt.executeQuery("SELECT * FROM CheckDataTest2");
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
assertEquals("test1tt", rs.getString(2));
assertTrue(rs.next());
assertEquals(2 + autoInc[0] + autoInc[1], rs.getInt(1));
assertEquals("test2tt", rs.getString(2));
assertTrue(rs.next());
assertEquals(9, rs.getInt(1));
assertEquals("test3", rs.getString(2));
assertFalse(rs.next());
}
private void setKeys(Statement statement, String query) throws SQLException {
statement.execute(query, Statement.RETURN_GENERATED_KEYS);
ResultSet keys = statement.getGeneratedKeys();
if (keys.next()) {
ResultSetMetaData _metadata = keys.getMetaData();
int columnCount = _metadata.getColumnCount();
mKeys = new String[columnCount];
for (int i = 1; i <= columnCount; i++) {
mKeys[i - 1] = keys.getString(i);
}
} else {
mKeys = new String[0];
}
}
@Override
protected void assertBackPointers(final Connection conn, final Statement statement) throws SQLException {
assertFalse(conn.isClosed());
assertFalse(isClosed(statement));
assertSame(conn, statement.getConnection(),
"statement.getConnection() should return the exact same connection instance that was used to create the statement");
final ResultSet resultSet = statement.getResultSet();
assertFalse(isClosed(resultSet));
assertSame(statement, resultSet.getStatement(),
"resultSet.getStatement() should return the exact same statement instance that was used to create the result set");
final ResultSet executeResultSet = statement.executeQuery("select * from dual");
assertFalse(isClosed(executeResultSet));
assertSame(statement, executeResultSet.getStatement(),
"resultSet.getStatement() should return the exact same statement instance that was used to create the result set");
final ResultSet keysResultSet = statement.getGeneratedKeys();
assertFalse(isClosed(keysResultSet));
assertSame(statement, keysResultSet.getStatement(),
"resultSet.getStatement() should return the exact same statement instance that was used to create the result set");
ResultSet preparedResultSet = null;
if (statement instanceof PreparedStatement) {
final PreparedStatement preparedStatement = (PreparedStatement) statement;
preparedResultSet = preparedStatement.executeQuery();
assertFalse(isClosed(preparedResultSet));
assertSame(statement, preparedResultSet.getStatement(),
"resultSet.getStatement() should return the exact same statement instance that was used to create the result set");
}
resultSet.getStatement().getConnection().close();
}
private void addOrChangeProfileImpl(Profile profile, ACS acs) throws SQLException {
Statement s = null;
String sql;
Connection c = acs.getDataSource().getConnection();
try {
s = c.createStatement();
if (profile.getId() == null) {
sql = "INSERT INTO profile (unit_type_id, profile_name) VALUES (";
sql += profile.getUnittype().getId() + ", ";
sql += "'" + profile.getName() + "')";
s.setQueryTimeout(60);
s.executeUpdate(sql, new String[] {"profile_id"});
ResultSet gk = s.getGeneratedKeys();
if (gk.next()) {
profile.setId(gk.getInt(1));
}
logger.info("Inserted profile " + profile.getName());
if (acs.getDbi() != null) {
acs.getDbi().publishAdd(profile, profile.getUnittype());
}
} else {
sql = "UPDATE profile SET ";
sql += "unit_type_id = " + profile.getUnittype().getId() + ", ";
sql += "profile_name = '" + profile.getName() + "' ";
sql += "WHERE profile_id = " + profile.getId();
s.setQueryTimeout(60);
s.executeUpdate(sql);
logger.info("Updated profile " + profile.getName());
if (acs.getDbi() != null) {
acs.getDbi().publishChange(profile, profile.getUnittype());
}
}
} finally {
if (s != null) {
s.close();
}
c.close();
}
}
private long getGeneratedKey(final Statement statement) throws SQLException {
ResultSet resultSet = statement.getGeneratedKeys();
return resultSet.next() ? resultSet.getLong(1) : 0L;
}