JDBC操作MySQL报错：executeUpdate无法执行生成结果集的语句，需实现自增主键跨表存储

我使用MySQL Connector和JDBC尝试从workouts表获取自增主键并存入workoutlogs表，遇到错误：

statement.executeupdate() cannot issue statements that produce result sets

怀疑和整数变量存储有关，但不确定，以下是我的代码：

public void  insertIntoWorkoutLogs(String field_setNumber, String field_repNumber, String field_weightAmount) {
    try{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","";
        Statement statement =connection.createStatement();

        String insert ="INSERT INTO `workout`.`workoutlogs`" + " (`SetNumber`, `RepNumber` , `WeightAmount`)" 
                  + "VALUES('" +field_setNumber+"','"+field_repNumber+"','"+field_weightAmount+"')";
        statement.executeUpdate(insert);

        int workoutID = insertQueryGetId("SELECT workoutID FROM workout");

        String insert2 ="INSERT INTO `workout`.`workoutlogs`" + " (`WorkoutID`)" 
                  + "VALUES('" +workoutID+"')";
        statement.executeUpdate(insert2);

        connection.close();
    }catch(Exception e) {
        System.out.println(e);
    }
}

public int insertQueryGetId(String query) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/workout","root","";
    Statement statement =connection.createStatement();
    
    int workoutID=0;
    int result=-1;
    
    try {
        workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
        
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()){
            result=rs.getInt(1);
        }
        rs.close();
        
        statement.close();
    
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;
}

我尝试用Statement实现，考虑可能需要PreparedStatement才能生效，预期实现将workouts表的自增主键存入workoutlogs表的对应字段。

错误原因分析 #

1. 核心错误：insertQueryGetId方法中用executeUpdate执行了SELECT查询语句，executeUpdate仅适用于执行INSERT/UPDATE/DELETE这类不返回结果集的DML操作，执行SELECT必须使用executeQuery，这是报错的直接原因。
2. 逻辑缺陷：SELECT workoutID FROM workout会返回所有workout的ID，无法精准获取目标主键；同时分两次插入workoutlogs会导致数据不完整，应该一次性插入所有字段。
3. 其他问题：重复创建数据库连接、存在SQL注入风险、资源未正确关闭。

修正方案（使用PreparedStatement） #

以下是优化后的代码，解决报错问题同时规范JDBC操作：

public void insertIntoWorkoutLogs(String field_setNumber, String field_repNumber, String field_weightAmount) {
    Connection connection = null;
    PreparedStatement pstmtLog = null;
    PreparedStatement pstmtWorkout = null;
    ResultSet generatedKeys = null;

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/workout", "root", "");
        connection.setAutoCommit(false); // 开启事务，保证操作原子性

        // 步骤1：插入workouts表并获取自增主键（如果是关联已有workout，跳过此步骤直接查询对应ID）
        String insertWorkoutSql = "INSERT INTO workouts (xxx) VALUES (?)"; // 替换为workouts表实际的插入语句
        pstmtWorkout = connection.prepareStatement(insertWorkoutSql, Statement.RETURN_GENERATED_KEYS);
        // 设置workouts表的参数，例如 pstmtWorkout.setString(1, "workout_name");
        pstmtWorkout.executeUpdate();

        // 获取刚插入的workoutID
        generatedKeys = pstmtWorkout.getGeneratedKeys();
        int workoutID = -1;
        if (generatedKeys.next()) {
            workoutID = generatedKeys.getInt(1);
        }

        // 步骤2：一次性插入workoutlogs表所有字段
        String insertLogSql = "INSERT INTO workoutlogs (SetNumber, RepNumber, WeightAmount, WorkoutID) VALUES (?, ?, ?, ?)";
        pstmtLog = connection.prepareStatement(insertLogSql);
        pstmtLog.setString(1, field_setNumber);
        pstmtLog.setString(2, field_repNumber);
        pstmtLog.setString(3, field_weightAmount);
        pstmtLog.setInt(4, workoutID);
        pstmtLog.executeUpdate();

        connection.commit(); // 提交事务
    } catch (Exception e) {
        // 出错时回滚事务
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        e.printStackTrace();
    } finally {
        // 按顺序关闭数据库资源，避免泄漏
        try {
            if (generatedKeys != null) generatedKeys.close();
            if (pstmtLog != null) pstmtLog.close();
            if (pstmtWorkout != null) pstmtWorkout.close();
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// 如果需要获取已有workout的最新ID，使用此方法
public int getLatestWorkoutId() throws ClassNotFoundException, SQLException {
    int workoutID = -1;
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/workout", "root", "");
        // 查询最新的workoutID（假设workoutID是自增主键）
        String querySql = "SELECT workoutID FROM workouts ORDER BY workoutID DESC LIMIT 1";
        pstmt = connection.prepareStatement(querySql);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            workoutID = rs.getInt(1);
        }
    } finally {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        if (connection != null) connection.close();
    }
    return workoutID;
}

关键优化点 #

• 使用executeQuery执行SELECT语句，executeUpdate仅用于无结果集的DML操作；
• 采用PreparedStatement避免SQL注入，同时提升代码安全性和可读性；
• 开启事务保证多步数据库操作的原子性，避免数据不一致；
• 在finally块中统一关闭数据库资源，防止资源泄漏；
• 合并workoutlogs的插入操作，一次性写入所有字段，保证数据完整性。

内容的提问来源于stack exchange，提问作者mrjbcam