CODEWITHSUNDEEP
javamysqljdbcdatabase-connectionresultset
Vinit89
Vinit89

Reputation: 583

What is Usage of TYPE_SCROLL_INSENSITIVE while creating Statement object

JavaDoc says: "The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet" .

I am clear about the scrollable part but have doubts regarding the latter part of statement. I am using following code snippet for validate my understanding.

        conn = getConnection();
        Statement stmt = conn
                .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);

        String query = "select * from vehicle";
        ResultSet rs = stmt.executeQuery(query);

        rs.absolute(2);
        System.out.print(rs.getString(2));
        System.out.println("Waiting........");
        Thread.sleep(20000); //1 manually changed database entry
        rs.refreshRow();
        System.out.println(rs.getString(2));//2 Surprisingly changes is reflected

At Comment 1, I did manual changes in the database then I called rs.refreshRow() method. After this At Comment 2, when when I accessed the value of second column then surprisingly change in value of second column is reflected. As per my understanding this change should not be reflected, as 'it is insensitive to changes done by other'(as per JavaDoc). Can anybody explain me what is its actual usage?

Upvotes: 3

Views: 2006

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

I investigated this a while ago, specifically with regard to MySQL Connector/J. As far as I could tell, the settings ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.TYPE_SCROLL_INSENSITIVE did not actually affect the behaviour when retrieving data from MySQL.

Several similar questions and blog posts I found referred to the MySQL Connector/J documentation, where in the section on JDBC API Implementation Notes it says that

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL network protocol, is easier to implement.

It goes on to talk about using ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, and stmt.setFetchSize(Integer.MIN_VALUE); as "a signal to the driver to stream result sets row-by-row", but even in that case my testing showed that the entire ResultSet was still being retrieved as soon as I did stmt.executeQuery(...). (Although perhaps I missed some other connection setting that wasn't explicitly mentioned in that section of the MySQL Connector/J documentation.)

Finally I came to the conclusion that the ResultSet.TYPE_SCROLL_[IN]SENSITIVE setting really doesn't make any difference under MySQL Connector/J. While simply scrolling around the ResultSet it always seems to act like it is INSENSITIVE (ignoring any changes to existing rows that were made by other processes), but rs.refreshRow(); always returns the latest data (including changes made by other processes) as though it is SENSITIVE even if the ResultSet is supposed to be INSENSITIVE.

Upvotes: 1

Related Questions