SQL table column value won't increment anymore after 2?

Question

The following code should increment the SQL table column "num" value and set the text of a text field to the value of "num" after incrementing. But when it reaches 2, the value won't increment anymore?

int num = 1;
try {
    Statement stm = Database.myCon().createStatement();
    String sql = ("Select * from rent");
    ResultSet rs = stm.executeQuery(sql);

    if (rs.next()) {
        int rentNum = rs.getInt("num");
        rentNum++;
        String rentNumToString = String.valueOf(rentNum);
        stm.executeUpdate("INSERT INTO rent(num)" + "VALUES ('" + rentNum + "')");
        transactionsNum.setText(rentNumToString);
    } else {
        stm.executeUpdate("INSERT INTO rent(num)" + "VALUES ('" + num + "')");
        String numToString = String.valueOf(num);
        transactionsNum.setText(numToString);
    }
} catch (SQLException e) {
    JOptionPane.showMessageDialog(this, e.getMessage());
}

The code should get the current value of the "num" column, increment it, and set the text of the text field to that updated value. Please help

Answer 1

firstly, what you are doing is taking all the data because of "Select * from rent" then you used if (rs.next()) you should be using while (rs.next()), you can imagine rs being an array of data so if you use an if statement you will only check the first line of data.

secondly, "INSERT INTO rent(num)" + "VALUES ('" + rentNum + "')" should be "UPDATE rent SET num = '" + rentNum + "' WHERE rent.<pk> = <primarykey>", insert your table's primary key where <pk>, and find the <primarykey> by using rs.getString("<pk>");, if you do not want to repeat data then I suggest doing this.

thirdly, make sure that the SQL queries are all executing normally on the SQL workbench before writing them in Java, and that all data types are correct and correspond to the Java data types.