I'm trying to get the primary auto incremented key from one table and store this in another using MySQL connector and JDBC. Although its giving me this error:

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

I think its something to do with the storing of the integer variable but not too sure.

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

I've tried using statement for this, but I'm thinking it may have to be prepared statement for it to work. Expecting to store the auto incremented primary key of one table (workouts) into a field within another table (workoutlogs).

CodePudding user response：

It's because you are passing wrong query. Statement.RETURN_GENERATED_KEYS works with Insert queries not with Select queries. When you insert a row in database, an auto increment value gets generated and is returned but you are passing a Select statement

As Syed Asad Manzoor said, it will work for you but then you need to remove Statement.RETURN_GENERATED_KEYS and statement.executeQuery() has return type of ResultSet so you need to store the result in ResultSet only.

CodePudding user response：

    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");** // Line of Concern 1
    
            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 {
// Line of Concern  2
            **workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);**  

In line (marked as Line of Concern 1 .. int workoutID = insertQueryGetId("SELECT workoutID FROM workout"); you are passing query as "SELECT...." and at point marked as Line of Concern 2 workoutID = statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); you are using executeUpdate.. thats why exception is thrown.

Change statement.executeUpdate(query) to statement.executeQuery(query)..

CodePudding user response：

The INSERT statement needs to have flag RETURN_GENERATED_KEYS. Then getting the ResultSet would deliver for every insert record the generated key(s).

Also use a PreparedStatement for escaping of strings and against SQL injection.

Use try-with-resources to automatically close the several objects, even with exception or early return.

Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/workout", "root", "")) {
    String insertSql = "INSERT INTO `workout`.`workoutlogs`"
              " (`SetNumber`, `RepNumber` , `WeightAmount`)"
              " VALUES(?, ?, ?)";
    try (PreparedStatement statement = connection.prepareStatement(insertSql,
            Statement.RETURN_GENERATED_KEYS)) {
        statement.setString(field_setNumber);
        statement.setString(field_repNumber);
        statement.setBigDecimal(field_weightAmount);
        statement.executeUpdate();
        try (ResultSet rs = statement.getGeneratedKey()) {
            if (rs.next()) {
                int workoutID = rs.getInt(0);
                //... second insert here
            }
        }
    }
}