CODEWITHSUNDEEP
javajdbcresultset
Chris
Chris

Reputation: 899

JDBC ResultSet Type_Scroll_Sensitive

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?

Upvotes: 3

Views: 7598

Answers (3)

user2696092
user2696092

Reputation: 9

while iterating the data from resultset object to see the changes first you need to stop the program execution for sometime for that you can use System.in.read(),after that u can go to database change table data and come to program to see that updated row in resultset you use refreshrow() on resultset object . You can use refreshrow() method to get the updated row data from database to resultset.

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); // you use stmt here

    while(rs.next()){
     System.out.println("-----------------------------");
      System.in.read();
    System.out.println(rs.getInt(1));

   rs.refreshRow();
    System.out.println(rs.getString("first_name"));
    System.out.println(rs.getString("last_name"));
      }

Upvotes: 0

Holger
Holger

Reputation: 298233

All updates to a field like you did with updateInt require an invocation of updateRow to become effective. This has nothing to do with the scroll type. Depending on the particular database you may even update ResultSets which are TYPE_FORWARD_ONLY (if supported, it’s the fastest alternative). The only constraint is, they have to be CONCUR_UPDATABLE.

The mistake of your second code is to use the same statement for the update than you used to get the ResultSet. So the ResultSet becomes invalid when you perform the update. You have to use two statements for this.

Change

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); // you use stmt here

String query2 = "INSERT INTO User VALUES (4, 'Joe', 'Bloggs')";
stmt.executeUpdate(query2); // here you use stmt again

to

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

Statement stmt2 = conn.createStatement();// new statement for the insertion
String query2 = "INSERT INTO User VALUES (4, 'Joe', 'Bloggs')";
stmt2.executeUpdate(query2);

Upvotes: 1

Evgeniy Dorofeev
Evgeniy Dorofeev

Reputation: 136042

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

Upvotes: 3

Related Questions