MySQL ResultSet scrollable/updatable not working a

Question

I have a test JDBC program that tries to alter the Scrollability and Updatability features of a ResultSet. Unfortunately, all the combinations of TYPE_ and CONCUR_ seem to produce the same result (TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY).

Even with the default (TYPE_FORWARD_ONLY) it's possible to scroll through the ResultSet. Can anyone explain why this is?

I am using MySQL 5.6 and JDK7. Here is the code:

public class ResultSetTest3 {

    public static void main(String[] args)
    {
        Connection conn;

        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/bd", "user", "password");

            Statement sta = conn.createStatement();
            sta.execute("DELETE FROM test");
            sta.close();

            PreparedStatement ps = conn.prepareStatement("INSERT INTO test VALUES(?, ?)");
            for(int i=1; i<=100; i++)
            {
                ps.setInt(1, i);
                ps.setString(2, "Teste " + i);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();

            System.out.println("TYPE_FORWARD_ONLY  CONCUR_READ_ONLY");
            result(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            System.out.println("===================================");

            System.out.println("TYPE_SCROLL_INSENSITIVE  CONCUR_READ_ONLY");
            result(conn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            System.out.println("===================================");

            System.out.println("TYPE_SCROLL_SENSITIVE  CONCUR_READ_ONLY");
            result(conn, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            System.out.println("===================================");

            System.out.println("TYPE_FORWARD_ONLY  CONCUR_UPDATABLE");
            result(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
            System.out.println("===================================");

            System.out.println("TYPE_SCROLL_INSENSITIVE  CONCUR_UPDATABLE");
            result(conn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            System.out.println("===================================");

            System.out.println("TYPE_SCROLL_SENSITIVE  CONCUR_UPDATABLE");
            result(conn, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            System.out.println("===================================");

            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void result(Connection conn, int type, int update) throws SQLException
    {
        Statement sta = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = sta.executeQuery("SELECT * FROM test");

        System.out.println(rs.getConcurrency() + " " + update);
        System.out.println(rs.getType() + " " + type);

        try
        {
            rs.absolute(10);
            System.out.println(rs.getInt(1) + " - " + rs.getString(2));

            rs.relative(20);
            System.out.println(rs.getInt(1) + " - " + rs.getString(2));

            rs.previous();
            System.out.println(rs.getInt(1) + " - " + rs.getString(2));

            rs.first();
            System.out.println(rs.getInt(1) + " - " + rs.getString(2));

            try {
                System.out.println("AGORA!!!");
                Thread.sleep(20000);
            } catch (Exception e) {
                System.out.println(e);
            }
            rs.absolute(3);
            System.out.println(rs.getInt(1) + " - " + rs.getString(2));
        }
        catch(SQLException e)
        {
            System.out.println("Not Scrollable");
        }

        try
        {
            rs.next();
            rs.next();
            rs.next();
            rs.next();

            rs.deleteRow();

            rs.next();
            rs.updateString(2, "TesteUpdate");

            rs.insertRow();
        }
        catch(SQLException e)
        {
            System.out.println("Not Updatable");
        }

        rs.close();
        sta.close();
    }
}

Answer 1

Try in this way to create a Scroll-Insensitive, read only ResultSet object statement object.

Statement sta = conn.createStatement(
          ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);

instead of

Statement sta = conn.createStatement();

Do the same for PreparedStatement as well.

---

Through this ResultSet type the cursor can move in any direction. It is insensitive which means result set that does not reflect changes made while it is still open. It is default resultset type for MySql.

Must read Retrieving and Modifying Values from Result Sets

Answer 2

As Mark Rotteveel mentions in a comment to the question, MySQL caches ResultSet data by default (also discussed in a blog article by Ben J. Christensen here). An apparent side-effect of this caching is that MySQL Connector/J will "upgrade" a TYPE_FORWARD_ONLY ResultSet to actually be scrollable:

Statement s = dbConnection.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, 
        ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery("SELECT * FROM testdata");
rs.last();
System.out.println(String.format("Current row number: %d", rs.getRow()));
rs.previous();
System.out.println(String.format("Current row number: %d", rs.getRow()));

displays

Current row number: 3
Current row number: 2

According to the blog article cited above, the way to prevent caching and "stream" the ResultSet data is to use Statement.setFetchSize:

Statement s = dbConnection.createStatement(
        ResultSet.TYPE_FORWARD_ONLY, 
        ResultSet.CONCUR_READ_ONLY);
s.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = s.executeQuery("SELECT * FROM testdata");
rs.next();
System.out.println("Data from first row: " + rs.getString(2));
System.out.println("now let's try rs.last() ...");
try {
    rs.last();
    System.out.println("... Okay, done.");
} catch (Exception e) {
    System.out.println("... Exception: " + e.getMessage());
}

resulting in

Data from first row: Gord
now let's try rs.last() ...
... Exception: Operation not supported for streaming result sets