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

The following examples show how to use java.sql.Statement#getGeneratedKeys() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: AutoGenJDBC30Test.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * 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();
}
 
Example 2
Source File: InsertParser.java    From dts with Apache License 2.0 6 votes vote down vote up
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;
}
 
Example 3
Source File: DumpOpenFacts2SQL.java    From ambiverse-nlu with Apache License 2.0 6 votes vote down vote up
/**
 * 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;
}
 
Example 4
Source File: AllowMultiQueriesTest.java    From mariadb-connector-j with GNU Lesser General Public License v2.1 6 votes vote down vote up
@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));
  }
}
 
Example 5
Source File: CheckDataTest.java    From mariadb-connector-j with GNU Lesser General Public License v2.1 6 votes vote down vote up
@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());
}
 
Example 6
Source File: AbstractDialect.java    From ymate-platform-v2 with Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example 7
Source File: StatementAdapterTest.java    From sharding-jdbc-1.5.1 with Apache License 2.0 5 votes vote down vote up
@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);
    }
}
 
Example 8
Source File: TestManagedDataSourceInTx.java    From commons-dbcp with Apache License 2.0 5 votes vote down vote up
@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();
}
 
Example 9
Source File: MySQLControl.java    From ezScrum with GNU General Public License v2.0 5 votes vote down vote up
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];
	}
}
 
Example 10
Source File: CheckDataTest.java    From mariadb-connector-j with GNU Lesser General Public License v2.1 5 votes vote down vote up
@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());
}
 
Example 11
Source File: GeneratedTest.java    From mariadb-connector-j with GNU Lesser General Public License v2.1 5 votes vote down vote up
@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]));
  }
}
 
Example 12
Source File: TestBase.java    From jTDS with GNU Lesser General Public License v2.1 5 votes vote down vote up
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();
   }
}
 
Example 13
Source File: BuiltinIdentityIdGenerator.java    From doma with Apache License 2.0 5 votes vote down vote up
/**
 * 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);
  }
}
 
Example 14
Source File: StatementAdapterTest.java    From shardingsphere with Apache License 2.0 5 votes vote down vote up
@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);
    }
}
 
Example 15
Source File: ShardReplaceTest.java    From Zebra with Apache License 2.0 5 votes vote down vote up
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();
    }
}
 
Example 16
Source File: StatementJdbc20Test.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
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 );
}
 
Example 17
Source File: PkeyVariableSetter.java    From ApprovalTests.Java with Apache License 2.0 5 votes vote down vote up
private void loadByJDBC(DatabaseObject forObject, int atStage, Statement stmt) throws SQLException
{
  try (ResultSet rs = stmt.getGeneratedKeys()) {
    if (rs.next()) {
      forObject.setPkey(rs.getInt(1));
    }
  }
}
 
Example 18
Source File: DAOBase.java    From redesocial with MIT License 5 votes vote down vote up
/**
 * 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;
    }
}
 
Example 19
Source File: ProxySQLExecutorCallback.java    From shardingsphere with Apache License 2.0 4 votes vote down vote up
private long getGeneratedKey(final Statement statement) throws SQLException {
    ResultSet resultSet = statement.getGeneratedKeys();
    return resultSet.next() ? resultSet.getLong(1) : 0L;
}
 
Example 20
Source File: Profiles.java    From freeacs with MIT License 4 votes vote down vote up
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();
  }
}