mysql resultset size_java – MySQL ResultSet可滚动/可更新不按预期工作

我有一个测试JDBC程序,试图改变ResultSet的Scrollability和Updatability功能.不幸的是,TYPE_和CONCUR_的所有组合似乎产生相同的结果(TYPE_SCROLL_INSENSITIVE和CONCUR_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();

}

}

解决方法:

正如Mark Rotteveel在对该问题的评论中提到的那样,默认情况下MySQL会缓存ResultSet数据(也在博客文章中讨论过)

Ben J. Christensen here).这种缓存的一个明显的副作用是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

标签：java,mysql,jdbc,scroll,resultset

来源： https://codeday.me/bug/20191006/1863328.html