I am confused about the behaviour of a ResultSet
that is of type TYPE_SCROLL_SENSITIVE
.
My understanding of this is:
- I execute a select query that returns me a result set. I print out the value of a particular column in the first row.
- I then execute
Thread.sleep(10000)
, which halts the program for 10 seconds. - While the program is sleeping, I manually do an update to the same column in the DB (through the SQL prompt).
- After 10 seconds, I again print the value of the same column in the first row of the result set.
In step 4, I expect the printed column value to be different from the value printed in step 1. But I always get the same value (even if my ResultSet
is of type SCROLL_TYPE_SENSITIVE
).
Am I misunderstanding something here ?
Below is the code I use.
private void doStuff() throws Exception
{
final String query = "select * from suppliers where sup_id=420";
Statement stmt = this.con.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(query);
rs.next();
System.out.println("City : " + rs.getString("city"));
Thread.sleep(10000); // While this executes, I do a manual update !
System.out.println("City : " + rs.getString("city"));
}
Yes. You must fetch again to get the latest state of the table, either by firing up a
SELECT
yourself, or callingResultSet.refreshRow()
. Moreover, read the docs ofResultSet.refreshRow()
before using it, otherwise you might get unexpected results.The doc states regarding TYPE_SCROLL_SENSITIVE,
Which merely means that it would be sensitive to the changes made by others in the same ResultSet object. To understand the concept, I would advise to look at this official JDBC Tutorial: Updating Tables.
Okay, editing my post to include the specific line from the original tutorial,
I Think you are using mysql as your db,and this is a known bug.
Let me elaborate fully-
As per Oracle documentation on the java site TYPE_SCROLL_SENSITIVE is used for 2 purposes-
1.Mysql driver can now move the jdbc result set's pointer to and fro (which otherwise just goes in the forward direction),so basically scrolling is enabled {so now you can do resultset.previous() and the pointer will go back}
2.To show updated values(the internal changes),made to the database.
You are stuck at the 2nd point...
See your program is not working,because you never used the concept of fetchSize();
whenever using jdbc,the driver fetches a default number of rows into the cache that is displayed(for ex:oracle loads 10 rows by default)
so TYPE_SCROLL_SENSITIVE will display the updated value of the next cache reload only. it is like you have 100 rows in the DB,you updated all,but till then only 10 rows were fetched,so you will get the other 90 rows updated printed subsequently ,as the driver will load these tables in 9 rounds of cache management.
for explicitly defining the number of rows to be fetched(for example changing the no. of rows from 10 to 1 for oracle) you can explicitly define the fetchSize() while creating statement.(but using cache ineffectively,at the end slows the speed )
so while initializing statement as:
add a line as:
create resultSet as:
to verify the data: print setFetchSize from resultSet,if it passes from statement to resultSet while Sysout than the fetching configuration has been saved,as:
if the sysout gives '1' as fetch size,you will see your dynamic updates from the program as it is, but if it gives '0' that means your DB doesnot support dynamic initialization of fetchSize();
Here is the problem with mysql,mysql by default fetches all the number of rows into the ResultSet,and thus the dynamic internal update,does not fetch the dynamic values. (internal update is the update done by some other thread of the same program).
Here is the bug supporting my point on sql bugs:
sql bugs fetchSize Bug
orcale docs TYPE_SCROLL_SENSITIVE example ResultSet5.java