JDBC ResultSet Type_Scroll_Sensitive

Question

I have been playing with JDBC and the different types of ResultSet and have a question about making it TYPE_SCROLL_SENSITIVE (this question is to do with the sensitive part, not the type scroll part). The theory says changes in the database are reflected in the ResultSet and vice-versa but this is not happening for me.

Here is something I've tried:

Connection conn = null;
try {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    String url = "jdbc:oracle:thin:@oracle.myCompany.com:1521:xe";
    conn = DriverManager.getConnection(url, "username", "password");
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    String query = "SELECT user_id, first_name, last_name FROM Users";
    ResultSet rs = stmt.executeQuery(query);

    while(rs.next()){
        System.out.println("-----------------------------");
        System.out.println(rs.getInt(1));
        System.out.println(rs.getString("first_name"));
        System.out.println(rs.getString("last_name"));

        int id = rs.getInt(1);
        rs.updateInt(1,100+id);
    }

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

When I update the ID, adding 100 to it, the change isn't shown in the database (if, below this, I add rs.updateRow() then it does update the database. But this also updates the database if I make it TYPE_SCROLL_INSENSITIVE).

Another thing I tried was this:

Connection conn = null;
try {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    String url = "jdbc:oracle:thin:@oracle.myCompany.com:1521:xe";
    conn = DriverManager.getConnection(url, "username", "password");
    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    String query = "SELECT user_id, first_name, last_name FROM Users";
    ResultSet rs = stmt.executeQuery(query);

    String query2 = "INSERT INTO User VALUES (4, 'Joe', 'Bloggs')";
    stmt.executeUpdate(query2);

    while(rs.next()){
        System.out.println("-----------------------------");
        System.out.println(rs.getInt(1));
        System.out.println(rs.getString("first_name"));
        System.out.println(rs.getString("last_name"));

        int id = rs.getInt(1);
        rs.updateInt(1,100+id);
    }

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

But if I run this code, and expect the new row to be in the resultSet because it's sensitive... I get a NullPointerException on the line: while(rs.next()){

So could someone please explain how the sensitive ResultSet works and what I am doing wrong? Possibly with an example?

Answer 1

TYPE_SCROLL_SENSITIVE means that if a change happened to the table while you are iterating over its data, you will see it. To reproduce this behavior make a break on

System.out.println("-----------------------------");

change the first_name using an external tool, then execute this line

System.out.println(rs.getString("first_name"));

the change should be visible.

Note that this feature is optional. To test if it's enabled run this

boolean res = conn.getMetaData().ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println(res);