I've been searching Stackoverflow for an answer but can't seem to find one that doesn't involve Hibernate or some other database wrapper.
I'm using JDBC directly via the MYSQL 5.18 JDBC driver in a Tomcat 6 Java EE app. I am caching Connection objects, but not caching Statement objects. The ResultSets for the query are correctly returning up to date data on the first run. When I change a few rows via PHPMyAdmin or some other external tool, rerun the query, I get stale out-of-date data.
I'm using normal Statements, not PreparedStatements. I've tried ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE. I'm also closing out the result set. These do not solve the problem. I've also tried ResultSet.refreshRows(), but that results in an error because the query has a JOIN clause.
The only thing that clearly solves the problem is closing the Connection and reconnecting to the database, which results in a heavy cost for each query attempt.
Is there a way to reuse Connections without returning stale data?
EDIT: I'm not using transactions for queries at the moment.
Here's the general code.
Connection conn; //created elsewhere and reused
...
String query = "SELECT p.ID as oid,rid,handle,summary,city,state,zip,t.name AS category
FROM profiles AS p
JOIN (terms AS t) ON (p.tid = t.ID)
WHERE p.ID = 1";
ResultSet resultSet;
Statement s;
synchronized (conn)
{
s = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
resultSet = s.executeQuery(query);
}
//Iterate over the results using .next() and copy data to another data structure
List retval = getResults(resultSet);
s.close();
Thanks for the help in advance!
Copyright Notice：Content Author:「ricosrealm」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/9322673/java-mysql-jdbc-query-is-returning-stale-data-from-cached-connection