java,mysql - 为什么不能从一个表中获取最后插入的 id 并将其再次插入到另一个表中？

Question

它的重复问题。我再次输入，因为它给了我错误

我正在创建一个插入到 M_GROUPS 上的组。

我需要获取 GROUP_ID 和 GROUP_CREATOR_ID 并将其插入 M_GROUP_MEMBERS 表为 GROUP_ID INT、MEMBER_ID INT。

我的代码是

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

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

        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,Statement.RETURN_GENERATED_KEYS);

    ResultSet rs = stmt.getGeneratedKeys();
    rs.next();  
    int key = rs.getInt(1);
    System.out.println(key);

    sql2 = "INSERT INTO M_GROUP_MEMBERS(GROUP_ID,MEMBER_ID,JOIN_TIME)"
            + " VALUES ('"
            + key
            + "','"
            + creator_id
             + "',NOW())";

    stmt2.executeUpdate(sql2);


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

它给我的输出如下

18

java.sql.SQLException: Column count doesn't match value count at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1811)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725)
at org.uims.service.GroupService.groupCreation(GroupService.java:106)
at org.uims.servlet.GroupServlet.doPost(GroupServlet.java:66)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Answer 1

我是这样解决这个问题的......

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

    DatabaseService oDatabaseService = new DatabaseService();
    Connection connection = oDatabaseService.connect();
    Statement stmt = null;
    Statement stmt2 = null;
    Statement stmt3 = null;
    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.close();



    stmt2 = connection.createStatement();
    String sql2;

    sql2 ="select max(group_id)as group_id from m_groups where group_creator_id='"+ creator_id+ "'";

    //System.out.println("before rs for sql2");
    ResultSet rs = stmt2.executeQuery(sql2);
    //System.out.println("after rs for sql2");

    int groupId = 0 ;

    while (rs.next()) {
    groupId =rs.getInt("group_id");
    }

    System.out.println(groupId);

    stmt3 = connection.createStatement();
    String sql3;

     sql3 = "INSERT INTO M_GROUP_MEMBERS(GROUP_ID,MEMBER_ID,JOIN_TIME)"
            + " VALUES ('"
            + groupId
            + "','"
            + creator_id
             + "',NOW())";

    stmt3.executeUpdate(sql3);


    } catch (SQLException se) {
        se.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try{
             if(stmt!=null)
                stmt.close();
          }catch(SQLException se2){
          }// nothing  can do
        try{
             if(stmt2!=null)
                stmt2.close();
          }catch(SQLException se3){
          }// nothing  can do

        try{
             if(stmt3!=null)
                stmt3.close();
          }catch(SQLException se4){
          }// nothing  can do
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }
}