如何以原子方式运行2 SQL“SELECT”？或任何其他更好的方式获取行数，我处理他们之前

问题描述

我试图原子运行

  ResultSet resSet; 
 resSet = statement.executeQuery（SELECT COUNT（*）FROM table）; 
 resSet.next（）
 long rowCount = resSet.getLong（1）; 
 resSet = statement.executeQuery（SELECT * FROM table）; 
 //读取已知行数的数据... 
  

我的问题是什么是最好的方式？

目前我发现我可以做：

  connection.setAutoCommit（false）; 
 connection.setTransactionIsolation（Connection.TRANSACTION_SERIALIZABLE）
 //调用2以上的SQL查询
 connection.commit（）; 
  

这种方式似乎工作。我测试另一个线程被阻塞执行 SELECT 和 commit（）之间的 INSERT 。
这是正确和最佳的方式吗？我可以确定这样我的COUNT将始终与从下一次选择返回的行计数相同？

我也希望，而不是连接.TRANSACTION_SERIALIZABLE Connection.TRANSACTION_REPEATABLE_READ 就足够了。
但是它不能在Derby 10.11.1.1中工作。这是一个错误？我是新的数据库业务，但它的工作原理H2数据库，因此我希望它可能是一个德比错误...

请注意，我已经知道解决方案您可以执行以下操作：

  statement = conn.createStatement（ResultSet.TYPE_SCROLL_INSENSITIVE，
 ResultSet.CONCUR_READ_ONLY）; 
 resultSet = statement.executeQuery（SELECT * FROM table）; 
 if（ResultSet.last（））{
 int rowCount = ResultSet.getRow（）; 
 ResultSet.beforeFirst（）; 
} 
 while（ResultSet.next（））{...} 
  

但是这个解决方案不是最优的。对于德比我测得它是〜7倍慢。

<$>

$ h p $ p> SELECT（SELECT COUNT（*）FROM table）c，* FROM table


I'm trying to run atomically

ResultSet resSet;
resSet = statement.executeQuery("SELECT COUNT(*) FROM table");
resSet.next()
long rowCount = resSet.getLong(1);
resSet = statement.executeQuery("SELECT * FROM table");
// read data of known row count...

My question is what is the best way?

Currently I found out I can do:

connection.setAutoCommit(false);
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)
// call 2 SQL queries above
connection.commit();

This way seems to work. I tested that another thread is blocked to perform INSERT in between first SELECT and commit().Is that correct and optimal way? Can I be sure that this way my COUNT will always be same as rows count returned from next select?

Also I would expect that instead of Connection.TRANSACTION_SERIALIZABLE the Connection.TRANSACTION_REPEATABLE_READ shall be sufficient.But it does not not work in Derby 10.11.1.1. Is it a bug? I'm new to database business but it works as expected for H2 database - therefore I expect it might be a derby bug...

Note that I already know about solution where you can do:

statement = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                 ResultSet.CONCUR_READ_ONLY);
resultSet = statement.executeQuery("SELECT * FROM table");
if (ResultSet.last()) {
    int rowCount = ResultSet.getRow();
    ResultSet.beforeFirst(); 
}
while(ResultSet.next()){...}

But this solution is not optimal. For derby I measured it is ~7 times slower. For H2 it is ~2 times slower.

解决方案

how about

SELECT (SELECT COUNT(*) FROM table) c, * FROM table

这篇关于如何以原子方式运行2 SQL“SELECT”？或任何其他更好的方式获取行数，我处理他们之前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！