I'm getting the SQLNonTransientException error when trying to update one of my rows in a H2 database.

public static void setNewServiceInformationsToShown() {
    try (Connection conn = DriverManager.getConnection("jdbc:h2:"   Main.config_db_location,
            Main.config_db_username, Main.config_db_password)) {
        //read data from database
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM BCSTASKS_SERVICE");
        
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            if(rs.getString("Status").equals("Neu") && rs.getBoolean("wasShown") == false) {
                rs.updateBoolean("WASSHOWN", true);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The error message already suggests that I should use conn.createStatement and set the ResultSet to CONCUR_UPDATABLE. The error occurs at the line with rs.updateBoolean(...);

Error Message:

The result set is readonly. You may need to use conn.createStatement(.., ResultSet.CONCUR_UPDATABLE). [90140-210]

The problem is I don't know where and how I should use this method. In the same function or at the start of the program?

CodePudding user response：

You have to put update query for update data in database but you are going with select query that is the problem.

• Select query is used if you have to fetch data from database.
• Update query is used for update data in database where data already stored in database but you just overwrite data.

Here down is modified code:

public static void setNewServiceInformationsToShown() {
    try (Connection conn = DriverManager.getConnection("jdbc:h2:"   Main.config_db_location,
            Main.config_db_username, Main.config_db_password)) {
        PreparedStatement stmt = conn.prepareStatement("UPDATE BCSTASKS_SERVICE SET wasShown = ? WHERE status = ? AND wasShown = ?");     
        stmt.setBoolean(1, true);
        stmt.setString(2, "Neu");
        stmt.setBoolean(3, false);
        stmt.executeUpdate();
        stmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

CodePudding user response：

You need to create a separate query/prepareStatement for an update. In your case as far as I can see you need only one update query:

conn.prepareStatement("UPDATE BCSTASKS_SERVICE SET WASSHOWN=true where 
                       Status = 'Neu' and wasShown = false "

CodePudding user response：

Most DB code I see doesn't attempt to use the fact that resultsets are updatable, and will instead fire off an additional UPDATE query, which works fine.

However, sure, H2 supports updateable resultsets too. However, some of the features that ResultSets have actually have quite a cost; the DB engine needs to do a boatload of additional bookkeeping to enable such features which have a performance cost. Lots of database queries are extremely performance sensitive, so by default you do not get the bookkeeping and therefore these features do not work. You need to enable them explicitly, that's what the error is telling you.

You're currently calling the 'wrong' preparedStatement method. You want the more extended one, where you pick and choose which additional bookkeeping you want H2 to do for you, in order to enable these things. You want this one.

conn.prepareStatement(
  "SELECT * FROM BCSTASKS_SERVICE",
  0,
  ResultSet.CONCUR_UPDATABLE);

That CONCUR_UPDATABLE thing is just a flag you pass to say: Please do the bookkeeping so that I can call .update.