MySQL ResultSet可滚动/可更新无法按预期工作 [英] MySQL ResultSet scrollable/updatable not working as expected
问题描述
我有一个测试JDBC程序，该程序试图更改ResultSet的可滚动性和可更新性功能.不幸的是，TYPE_和CONCUR_的所有组合似乎产生相同的结果(TYPE_SCROLL_INSENSITIVE和CONCUR_READ_ONLY).
I have a test JDBC program that tries to alter the Scrollability and Updatability features of a ResultSet. Unfortunately, all the combinations of TYPE_ and CONCUR_ seem to produce the same result (TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY).
即使使用默认值(TYPE_FORWARD_ONLY)，也可以在ResultSet中滚动.谁能解释这是为什么?
Even with the default (TYPE_FORWARD_ONLY) it's possible to scroll through the ResultSet. Can anyone explain why this is?
我正在使用MySQL 5.6和JDK7.这是代码:
I am using MySQL 5.6 and JDK7. Here is the code:
public class ResultSetTest3 {
public static void main(String[] args)
{
Connection conn;
try {
conn = DriverManager.getConnection("jdbc:mysql://localhost/bd", "user", "password");
Statement sta = conn.createStatement();
sta.execute("DELETE FROM test");
sta.close();
PreparedStatement ps = conn.prepareStatement("INSERT INTO test VALUES(?, ?)");
for(int i=1; i<=100; i++)
{
ps.setInt(1, i);
ps.setString(2, "Teste " + i);
ps.addBatch();
}
ps.executeBatch();
ps.close();
System.out.println("TYPE_FORWARD_ONLY CONCUR_READ_ONLY");
result(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
System.out.println("===================================");
System.out.println("TYPE_SCROLL_INSENSITIVE CONCUR_READ_ONLY");
result(conn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
System.out.println("===================================");
System.out.println("TYPE_SCROLL_SENSITIVE CONCUR_READ_ONLY");
result(conn, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
System.out.println("===================================");
System.out.println("TYPE_FORWARD_ONLY CONCUR_UPDATABLE");
result(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
System.out.println("===================================");
System.out.println("TYPE_SCROLL_INSENSITIVE CONCUR_UPDATABLE");
result(conn, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
System.out.println("===================================");
System.out.println("TYPE_SCROLL_SENSITIVE CONCUR_UPDATABLE");
result(conn, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
System.out.println("===================================");
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void result(Connection conn, int type, int update) throws SQLException
{
Statement sta = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sta.executeQuery("SELECT * FROM test");
System.out.println(rs.getConcurrency() + " " + update);
System.out.println(rs.getType() + " " + type);
try
{
rs.absolute(10);
System.out.println(rs.getInt(1) + " - " + rs.getString(2));
rs.relative(20);
System.out.println(rs.getInt(1) + " - " + rs.getString(2));
rs.previous();
System.out.println(rs.getInt(1) + " - " + rs.getString(2));
rs.first();
System.out.println(rs.getInt(1) + " - " + rs.getString(2));
try {
System.out.println("AGORA!!!");
Thread.sleep(20000);
} catch (Exception e) {
System.out.println(e);
}
rs.absolute(3);
System.out.println(rs.getInt(1) + " - " + rs.getString(2));
}
catch(SQLException e)
{
System.out.println("Not Scrollable");
}
try
{
rs.next();
rs.next();
rs.next();
rs.next();
rs.deleteRow();
rs.next();
rs.updateString(2, "TesteUpdate");
rs.insertRow();
}
catch(SQLException e)
{
System.out.println("Not Updatable");
}
rs.close();
sta.close();
}
}
推荐答案
正如Mark Rotteveel在对该问题的评论中提到的那样，MySQL默认情况下会缓存ResultSet数据(也在本博客文章中进行了讨论) Ben J. Christensen 此处)) .这种缓存的明显副作用是MySQL Connector/J将升级" TYPE_FORWARD_ONLY ResultSet使其实际上是可滚动的:
As Mark Rotteveel mentions in a comment to the question, MySQL caches ResultSet data by default (also discussed in a blog article by Ben J. Christensen here). An apparent side-effect of this caching is that MySQL Connector/J will "upgrade" a TYPE_FORWARD_ONLY ResultSet to actually be scrollable:
Statement s = dbConnection.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = s.executeQuery("SELECT * FROM testdata");
rs.last();
System.out.println(String.format("Current row number: %d", rs.getRow()));
rs.previous();
System.out.println(String.format("Current row number: %d", rs.getRow()));
显示
Current row number: 3
Current row number: 2
根据上面引用的博客文章，防止缓存和流式处理" ResultSet数据的方法是使用Statement.setFetchSize:
According to the blog article cited above, the way to prevent caching and "stream" the ResultSet data is to use Statement.setFetchSize:
Statement s = dbConnection.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
s.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = s.executeQuery("SELECT * FROM testdata");
rs.next();
System.out.println("Data from first row: " + rs.getString(2));
System.out.println("now let's try rs.last() ...");
try {
rs.last();
System.out.println("... Okay, done.");
} catch (Exception e) {
System.out.println("... Exception: " + e.getMessage());
}
导致
Data from first row: Gord
now let's try rs.last() ...
... Exception: Operation not supported for streaming result sets
这篇关于MySQL ResultSet可滚动/可更新无法按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
