reset cursor position in hsqlDB, java

Question

I'm using hsqlDB version 2.3.2 that I'm embedding into a small program. Everything worked fine so far, I've made a simple printer method that allows mi to print a resultset in the console:

public static void printResultSet(ResultSet rs){
    try {
        if(rs==null || rs.wasNull()) {
            System.out.println("####  empty result set ####");
            return;
        }
        ResultSetMetaData rsmd = rs.getMetaData();
        int cols = rsmd.getColumnCount();
        System.out.println("############## Printers.resultset ################");
        while(rs.next()){
            System.out.println("");
            for(int i=1; i<cols+1; i++){
                try{
                    System.out.print(rs.getString(i) + ", ");
                }catch(SQLException e){
                    /*drop silently - while it's a bad programming practice to rely on 
                     * exceptions, it's easiest to handle when unknown data types may appear
                     */
                }
            }
        }
        //rs.absolute(0); //reset rs cursor
        rs.beforeFirst();
        System.out.println("###  DONE  ### Printers.resultset ################");
    } catch (SQLException e) {
        System.err.println("SQL exceptin in Printers.printResultSet" + e.getMessage());
    }
}

As I understand the rs.next() causes the resultset cursor to move by one until something else changes it (can be the next call of next()). As such, the call of the printer causes the cursor to end up at the end of the resultset, thus making it impossible for other parts of my code to use the resultset. I am trying to reset the cursor position so that the printer method can be used without any changes to the external codes, but for some reason I can't reset - neither the

        rs.absolute(0); 

nor

        rs.beforeFirst();

works. I am getting a exception with criptic message "feature not supported"

I've found some references in the javadoc saying this happens when JDBC driver does not support this operation. I'm a bit shocked that a very important feature like moving the cursor is not supported and I find it hard to believe, so there must be a different way to reset the cursor position, or there may be an alternative to the .next() method that allows to reset the cursor position when it's done iterating through everything.

Notice: .previous() doesn't work either, so anything else than next fails.

Answer 1

In order for this to work the statement object must be declared as ResultSet.TYPE_SCROLL_INSENSITIVE:

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);

Example here

If you use a PreparedStetement use it like this:

PreparedStatement prepStmt =
        dbCon.prepareStatement(sqlStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);