在使用Oracle数据库进行分页查询时,经常会用到如下SQL:
select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?
使用的java代码如下:
int startIdx = 0;
int endIdx = 10000;
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
ps.setInt(1, startIdx);
ps.setInt(2, endIdx);
try (ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
String id = rs.getString(2);
System.out.println("id="+id);
}
}
}
当使用以上代码时,会发现当取完最后一条记录后,再执行rs.next()时,本来希望返回false后跳出循环,但rs.next()会执行非常长的时间。解决的方法是不让rs.next()来判断下一条记录不存在,而在代码通过计数来实现:
int startIdx = 0;
int endIdx = 10000;
int i = 0;
int count = endIdx - startIdx;
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
ps.setInt(1, startIdx);
ps.setInt(2, endIdx);
try (ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
i++;
String id = rs.getString(2);
System.out.println("id="+id);
if(i == count) {
break;
}
}
}
}
如果代码中设置了fetchSize,并且fetchSize不能被count整除时,在取最后一片数据时,rs.next()也会执行很长时间:
int startIdx = 0;
int endIdx = 10000;
String sql = "select tm.* from (select rownum rm, t.* xmlrecord from testtable t) tm where tm.rm > ? and tm.rm <= ?";
try (Connection conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
ps.setFetchSize(300);
ps.setInt(1, startIdx);
ps.setInt(2, endIdx);
try (ResultSet rs = ps.executeQuery();) {
while (rs.next()) {
String id = rs.getString(2);
System.out.println("id="+id);
}
}
}
以上代码中,当取得9900条数据后,再取下一个300条时,rs.next()就会执行很长时间,可能是由于取不到一个完整的300条记录造成的。解决方法是将fetchSize设置成能被count整除的数字,比如:
ps.setFetchSize(500);
在以上两种状况下,为什么rs.next()会执行很长时间,还不是很清楚,但可以通过上述方式解决。至于为什么会有这个问题,有知道原因的朋友,请不吝赐教。