HSQLDB从JDBC结果集中删除行 [英] HSQLDB delete row(s) from JDBC resultset

问题描述

在尝试将现有的JDBC应用程序转换为使用HSQLDB 2.2.9版时遇到了两个问题(当前代码库已在MySQL，ORACLE和SQLServer上成功运行，但是嵌入式数据库似乎也是一个不错的选择)

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).

我将一次并单独地问一个问题(尽管它们都与JDBC ResultSet.deleteRow()方法相关，自HSQLDB 2.0开始受支持)

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)

为什么 rs.next()调用 rs.deleteRow()后返回false?

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

在MySQL数据库上运行相同的代码时，输​​出显示第7行被删除:

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

删除第0行

删除行:7

...

删除行:98

在HSQLDB上，输出为:

On HSQLDB, the output is:

删除第0行

第一次调用 rs.deleteRow()后， rs.next()返回false.

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

我在HSQLDB javadoc中找不到任何信息.有人有什么想法吗?

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

推荐答案

在代码中指定游标的可保留性:

Specify the cursor holdability in the code:

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

问题似乎是HSQLDB JDBC驱动程序使用 ResultSet.CLOSE_CURSORS_AT_COMMIT 作为默认值.

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

这篇关于HSQLDB从JDBC结果集中删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！