在Java中使用结果集的有效方法 [英] Efficient way to go over result set in Java

问题描述

我正在运行一个select命令，它返回100万行迭代ResultSet。下面的代码需要5分钟才能执行。

I am running a select command which returns 1,000,000 rows iterating over the ResultSet. The below code takes 5 minutes to execute.

是否有更快的方法迭代ResultSet？

Is there a faster way of iterating over a ResultSet?

conn = getDbConnection();
Statement createStatement = conn.createStatement();
ResultSet rs = createStatement.executeQuery("Select * from myTable");
while (rs.next())
{
    //do nothing
}

java中有没有办法让它在查看结果集中的所有记录时更有效率。

Is there a way in java to make it more efficient while go over all the records in the result set.

谢谢

推荐答案

您可以使用 setFetchSize（rows） 以优化获取大小，从DB中一次获取指定的行数。

You may use setFetchSize(rows) to optimize fetch size, which fetches specified number of rows in one go from DB.

conn = getDbConnection();
Statement createStatement = conn.createStatement();
createStatement.setFetchSize(1000);
ResultSet rs = createStatement.executeQuery("Select * from myTable");
while (rs.next())
{
//do nothing        
}

请注意，fetchSize只是对 DB 的提示，它可能会忽略该值。只有测试才能揭示它是否是最优的。

Note that fetchSize is just a hint to DB and it may ignore the value. Only testing will reveal if its optimal.

另外，在你的情况下，最好改变 Scrollable 属性声明，因为您可能不会立即处理所有记录。选择哪个可滚动选项取决于您是否要在迭代时查看其他更改。

Also, in your case its may be better to alter Scrollable attribute of Statement, as you may not process all records at once. Which scrollable option to choose depends on whether you want to see other's changes while you are iterating or not.

//TYPE_FORWARD_ONLY 
//      The constant indicating the type for a ResultSet object 
//    whose cursor may move only forward.
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                           ResultSet.CONCUR_READ_ONLY); 

或

//TYPE_SCROLL_INSENSITIVE
//      The constant indicating the type for a ResultSet object that is 
//    scrollable but generally not sensitive to changes made by others.    
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                           ResultSet.CONCUR_READ_ONLY); 

或

//TYPE_SCROLL_SENSITIVE
//      The constant indicating the type for a ResultSet object that is 
//    scrollable and generally sensitive to changes made by others.
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                           ResultSet.CONCUR_READ_ONLY); 

参见JDBC API指南了解更多详情

这篇关于在Java中使用结果集的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！