CachedRowSet and SQLite JDBC driver

Question

I'm trying to use CachedRowSet with SQLite and Xerial driver https://bitbucket.org/xerial/sqlite-jdbc .

If I call execute() method like that:

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

I'm getting the SQLException "not implemented by SQLite JDBC driver":

    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)

on the other hand ResultSet and populate() insteed of excecute() works ok:

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

Does anybody know whats wrong with execute()?

Answer 1

Unfortunately there's a handful of JDBC functions you'll have to implement workarounds for when using SQLite. This happens to be one of them. Probably the best alternate solution is to put the entire result set into a List<> and work with that:

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

An answer in this post contains a comment about simulating a function if it's not supported by your driver.