mysql，java - 从一个表中获取最后插入的自动增量 id 并将其再次插入到另一个表中 [英] mysql, java - Get last inserted auto increment id from one table and insert it again on another table

问题描述

与在mysql中获取最后插入的自动增量id的重复问题

我正在创建一个它在 M_GROUPS 上插入的组.

I'm creating a group it insert on M_GROUPS.

M_GROUPS 表:

GROUP_ID INT AUTO_INCREMENT, 
GROUP_CREATOR_ID INT

来自会话.

我需要将 GROUP_ID 和 GROUP_CREATOR_ID 插入到
M_GROUP_MEMBERS 表为

I need to take GROUP_ID and GROUP_CREATOR_ID and insert it on
M_GROUP_MEMBERS table as

GROUP_ID INT,
MEMBER_ID INT.

我的问题是我不能从 M_GROUPS 获取自动增量值 GROUP_ID

My problem is I can't take auto increment value GROUP_ID from M_GROUPS

public void groupCreation(String groupname, int grouptype, int creator_id) {

    DatabaseService oDatabaseService = new DatabaseService();
    Connection connection = oDatabaseService.connect();
    try {
        Statement stmt = null;
        stmt = connection.createStatement();
        String sql;

        sql = "INSERT INTO M_GROUPS( GROUP_NAME, GROUP_CREATOR_ID,
                                     GROUP_TYPE, CREATION_TIME)"
            + " VALUES ('"
            + groupname
            + "','"
            + creator_id
            + "','"
            + grouptype + "',NOW())";

        //stmt.executeUpdate(sql);
        stmt.executeUpdate(sql);
    } catch (SQLException se) {
        se.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
}

推荐答案

使用 getGeneratedKeys() 方法从您的 Statement 对象识别新的自动生成的值.迭代返回的ResultSet对象，以批处理语句的顺序获取新生成的键值.

Use getGeneratedKeys() method from your Statement object to identify the new auto generated values. Iterate the returned ResultSet object to get the newly generated key values in the order of batch statements.

变化:

stmt.executeUpdate(sql);

致:

int rowsAffected = 
  stmt.executeUpdate( sql, Statement.RETURN_GENERATED_KEYS );  
ResultSet rs = stmt.getGeneratedKeys();  

//******************************************************  
// in case of batch insert, you can check how many are inserted
rs.last();  
int rows = rs.getRow();  
System.out.println( "Generated keys count: " + rows );  
//******************************************************/  

int currentRow = 1;  
rs.beforeFirst();  
while( rs.next() ) {  
    System.out.println( /**/( currentRow++ ) + " = " + /**/rs.getInt( 1 ) );  
} // while rs  

一旦您在变量中拥有此自动生成的键值，您就可以将其与其他 SQL 语句一起使用，以存储在其他表中.

Once you have this auto generated key value in a variable, you can use it with other SQL statements, to store in other tables.

注意:调用 getGeneratedKeys() 可能会抛出 java.sql.SQLFeatureNotSupportedException，如果您使用的 JDBC 驱动程序不支持这种方法.

Note: Call to getGeneratedKeys() may throw java.sql.SQLFeatureNotSupportedException, if the JDBC driver, that you are using, does not support this method.

这篇关于mysql，java - 从一个表中获取最后插入的自动增量 id 并将其再次插入到另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！