MySQL ResultSet可滚动/可更新无法按预期工作

我有一个测试JDBC程序,该程序试图更改ResultSet的可滚动性和可更新性功能。不幸的是,所有组合TYPE_,并CONCUR_似乎产生相同的结果(TYPE_SCROLL_INSENSITIVECONCUR_READ_ONLY)。

即使使用默认值(TYPE_FORWARD_ONLY),也可以在ResultSet中滚动。谁能解释为什么?

我正在使用MySQL 5.6和JDK7。这是代码:

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

}

}

回答:

正如马克Rotteveel到问题的评论中提到,MySQL的高速缓存默认的ResultSet数据(也由Ben J.

Christensen的一篇博客文章中讨论这里)。这种缓存的明显副作用是MySQL Connector / J将“升级”

TYPE_FORWARD_ONLY ResultSet使其实际上是可滚动的:

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

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可滚动/可更新无法按预期工作 的全部内容, 来源链接: utcz.com/qa/403691.html

回到顶部