HSQLDB delete row(s) from JDBC resultset

问题:

I have run into a couple of problems while trying to convert an existing JDBC application to use HSQLDB version 2.2.9 (Currently the codebase runs successfully on MySQL, ORACLE and SQLServer, but an embedded database seemed like a good option too).

I will ask the questions one at a time and separately (although they all relate to the JDBC ResultSet.deleteRow() method, supported since HSQLDB 2.0)

Why does rs.next() return false after calling rs.deleteRow()?

Here is a complete self contained code sample (including simple table creation, sample inserts, and deleting the table at the end):

    int deletedRows=0;
    try{
        Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mytestdb",
                 "SA", "");
        String createSQL =
                 "create table test (num INTEGER PRIMARY KEY, str VARCHAR(25))";
        Statement createStmt = c.createStatement();
        createStmt.execute(createSQL);
        createStmt.close();
        String ins = "insert into test (num,str) values (?,?)";
        PreparedStatement pStmt = c.prepareStatement(ins);
        for(int i=0; i<100; i++){
            pStmt.setInt(1, i);
            pStmt.setString(2, "String"+i);
            pStmt.execute();
        }
        // there should now be 100 rows in the table
        String select = "SELECT * FROM test";
        PreparedStatement stmt = c.prepareStatement(select, 
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery();    
        rs.beforeFirst();
        while(rs.next()){
            int num = rs.getInt("num");
            if((num%7)==0){
                System.out.println("Deleting row:"+num);
                rs.deleteRow();
                deletedRows++;
            }
        }
        Statement dropStmt = c.createStatement();
        dropStmt.execute("drop table test;");
        dropStmt.close();
    } catch (SQLException sqle) {
        System.out.println("Deleted "+deletedRows+
                    " rows before exception: "+sqle.getMessage());
        sqle.printStackTrace();
    }

When running the same code on MySQL database, the output shows that every 7th row is deleted:

Deleting row:0

Deleting row:7

...

Deleting row: 98

On HSQLDB, the output is:

Deleting row:0

The rs.next() returns false after the first call to rs.deleteRow().

I cannot find any information in the HSQLDB javadoc. Does anyone have any ideas?

回答1:

As I also commented before, this sounds like a bug in the HSQLDB JDBC implementation. The JDBC 4.1 spec (section 15.2.4.2) says:

After the method deleteRow has been called, the cursor will be positioned before the next valid row. If the deleted row is the last row, the cursor will be positioned after the last row.

This implies that the call to next() should have returned true (if the ResultSet contained more rows).

回答2:

Specify the cursor holdability in the code:

c.prepareStatement(select,
     ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_UPDATABLE,
     ResultSet.HOLD_CURSORS_OVER_COMMIT);

The problem seems to be that HSQLDB JDBC driver uses ResultSet.CLOSE_CURSORS_AT_COMMIT as the default value.

回答3:

Most likely, you have a misconfiguation. For example you may have an older version of the HSQLDB jar in your classpath.

I get this output with the current SVN code. The JDBC code has not changed since 2.2.9.

Deleting row:0
Deleting row:7
Deleting row:14
Deleting row:21
Deleting row:28
Deleting row:35
Deleting row:42
Deleting row:49
Deleting row:56
Deleting row:63
Deleting row:70
Deleting row:77
Deleting row:84
Deleting row:91
Deleting row:98