Error with “TYPE_FORWARD_ONLY” in sqlite

Question

I am working with SQLite and JDBC and getting this error about the result_set being TYPE_FORWARD_ONLY.

    PreparedStatement get_mileage = conn.prepareStatement("SELECT * FROM workout_log");
    ResultSet mileage_count = get_mileage.executeQuery();

    mileage_count.absolute(week_start);

    for (int i=week_start;i<=week_finish;i++){
        total_mileage+=mileage_count.getInt(1);
        mileage_count.next();
    }

The error is on the call to absolute() even though I know it is not moving backwards at all. I have tried adding flags to prepareStatement but it says my version of SQLite does not support ResultSet that is not FORWARD_ONLY.

My question is why is this occuring, even though I am not moving backward?

Answer 1

A TYPE_FORWARD_ONLY ResultSet only supports next() for navigation, and not methods like first(), last(), absolute(int), relative(int). The JDBC specification explicitly defines those to throw a SQLException if called on a TYPE_FORWARD_ONLY:

Javadoc of ResultSet.absolute(int):

Throws:
SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY

Using those methods does not make a lot of sense with a TYPE_FORWARD_ONLY: that type of result set is not intended for 'random access' of the rows, like the scrollable result sets are.

For example with a TYPE_FORWARD_ONLY:

• Calling first() would only work when you are positioned before the first row (and on the first row itself), so why not just use next()
• Calling absolute(int) would only work if you pass a row higher than the current row, and you could never go back to earlier rows
• Calling relative(int) would only work if you pass a positive value and you can never go back to earlier rows
• Calling last() would make you skip the rest of the result set and you can never go back to earlier rows

Admittedly: it might have its uses, but it would needlessly complicate the driver with the additional constraints of being TYPE_FORWARD_ONLY.

If you want random access, you need to declare that you want random access by specifying one of the scrollability types TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE. If your driver doesn't support these types, then you may need to simulate it with for example CachedRowSet (specifically com.sun.rowset.CachedRowSetImpl), or by first loading the entire ResultSet (eg into a List<? extends List<Object>>).