CachedRowSet和SQLite JDBC驱动程序

Question

我正在尝试将CachedRowSet与SQLite和Xerial https://bitbucket.org/xerial/sqlite-jdbc一起使用。

如果我像这样调用execute()方法：

  Connection connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
  CachedRowSet crs = new CachedRowSetImpl();
  crs.setCommand("select * from person");
  crs.execute(connection);

我得到了“不是由SQLite JDBC驱动程序实现的”SQLException：

    at com.sun.rowset.internal.CachedRowSetReader.readData(Unknown Source)
    at com.sun.rowset.CachedRowSetImpl.execute(Unknown Source)
    at com.sun.rowset.CachedRowSetImpl.execute(Unknown Source)
    at com.oracle.tutorial.jdbc.CachedRowSetSample.testPaging(CachedRowSetSample.java:100)
    at com.oracle.tutorial.jdbc.CachedRowSetSample.main(CachedRowSetSample.java:273)

另一方面，ResultSet和populate()代替excecute()工作正常：

  Connection connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
  statement = connection.createStatement();
  ResultSet rs = statement.executeQuery("select * from person");
  CachedRowSet crs = new CachedRowSetImpl();
  crs.populate(rs);

有人知道execute()有什么问题吗？

Answer 1

不幸的是，在使用SQLite时，有一些JDBC函数必须实现变通方法。这恰好就是其中之一。也许最好的替代解决方案是将整个结果集放到一个List<>中并使用它：

// Variables. 
final int TIMEOUT_DEFAULT=30;
String query = "select * from person";
ResultSet rs;
Statement statement;
List<String[]> people;

...

// Create query and execute. (Connection established, database open.)
try {                
    statement = connection.createStatement();
    statement.setQueryTimeout(TIMEOUT_DEFAULT);
    connection.setAutoCommit(true);
    rs = statement.executeQuery(query);  
} catch (SQLException e) {
    // If error, close connection & ignore close errors.
    try { connection.close(); } 
        catch (SQLException e2) { /* Ignore */ }
    // Throw new error.
    throw new SQLException("Query failed",e);
}

// Retrieve results.
try {
    people = new ArrayList<>();
    while (rs.next()) {
        people.add(new String[]{
            rs.getString(1), rs.getString(2), rs.getString(3)
        });
    }
} catch (SQLException e) {
    // If error, close connection & ignore close errors.
    try { connection.close(); } 
        catch (SQLException e2) { /* Ignore */ }
    // Throw new error.
    throw new SQLException("Error retrieving data",e);
}

// Close connection, ignore error.
try { 
    connection.close(); 
} catch (SQLException e) { /* Ignore */ }

// Print output.
for (String[] p : people) {
    System.out.println(Arrays.deepToString(p));
}

this post中的答案包含关于模拟函数的注释，如果您的驱动程序不支持该函数。