Java MYSQL / JDBC查询从缓存的Connection返回过时数据 [英] Java MYSQL/JDBC query is returning stale data from cached Connection

问题描述

我一直在寻找Stackoverflow的答案，但似乎找不到一个不涉及Hibernate或其他数据库包装器。

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.

我是直接通过Tomcat 6 Java EE应用程序中的MYSQL 5.18 JDBC驱动程序使用JDBC。我正在缓存Connection对象，但不缓存Statement对象。查询的ResultSet正确地在第一次运行时返回最新数据。当我通过PHPMyAdmin或其他一些外部工具更改几行时，重新运行查询，我得到陈旧的过时数据。

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.

我正在使用普通语句，不是PreparedStatements。我试过ResultSet.TYPE_SCROLL_SENSITIVE，ResultSet.CONCUR_UPDATABLE。我也在关闭结果集。这些并不能解决问题。我也尝试过ResultSet.refreshRows（），但是这会导致错误，因为查询有一个JOIN子句。

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.

有没有办法重用Connections而不返回陈旧数据？

Is there a way to reuse Connections without returning stale data?

编辑：我目前没有使用交易进行查询。

I'm not using transactions for queries at the moment.

以下是一般代码。

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

提前感谢您的帮助！

推荐答案

原来这是一个未经审查的问题。感谢Brent Worden关于交易的问题让我环顾四周，注意到我已经禁用了自动提交，并且在查询后没有提交。

Turns out it was a matter of uncommited queries. Thanks to Brent Worden for the question about transactions which led me to look around and notice that I had disabled auto commit and was not committing after queries.

所以解决方案有效对我来说：

So the solutions that worked for me:

conn.setAutoCommit(true);

或

statement.executeQuery(query);
conn.commit();

这样可以刷新查询并防止过时数据。

This allows the queries to be flushed out and stale data is prevented.

这篇关于Java MYSQL / JDBC查询从缓存的Connection返回过时数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！