Java Code Examples for java.sql.Statement#getGeneratedKeys()

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

@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 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());
}
 

@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 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);
    }
}
 

@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 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];
	}
}
 

@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());
}
 

@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]));
  }
}
 

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();
   }
}
 

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

@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);
    }
}
 

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();
    }
}
 

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 loadByJDBC(DatabaseObject forObject, int atStage, Statement stmt) throws SQLException
{
  try (ResultSet rs = stmt.getGeneratedKeys()) {
    if (rs.next()) {
      forObject.setPkey(rs.getInt(1));
    }
  }
}
 

/**
 * 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 long getGeneratedKey(final Statement statement) throws SQLException {
    ResultSet resultSet = statement.getGeneratedKeys();
    return resultSet.next() ? resultSet.getLong(1) : 0L;
}
 

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();
  }
}