大数据量查询方法

直接使用数据库教程的分页sql获得子集.而不在内存中操作. 这样的好处是将子集放到一个中间结果里,每次再将中间结果合并到目标结果集.避免内存中同时出现二个大的结果集

1. 一种全部查询到内存,然后使用subList, subList的场景比如in参数的限制.

private List getQualityWithGroupidAndKey(
List cpcList, Long groupid, Long accountid) {
if (cpcList == null || cpcList.size() return null;
List res = new ArrayList();

int group = cpcList.size() % IDS_PER_BATCH == 0 ? cpcList.size()
/ IDS_PER_BATCH : (cpcList.size() / IDS_PER_BATCH + 1);
for (int i = 0; i String sql = "SELECT * FROM QUALITYHISTORY A WHERE A.GROUPID="
+ groupid
+ " AND A.ACCOUNTID="
+ accountid
+ " AND A.KEY IN ("
+ DtoBaseUtility
.getOtherStringFromEntitySet(
cpcList
.subList(
i * IDS_PER_BATCH,
((i + 1) * IDS_PER_BATCH) > cpcList
.size() ? cpcList
.size()
: ((i + 1) * IDS_PER_BATCH)),
CpcKeyDTO.class, "getKey") + ")";
;
List ls = DtoBaseUtility
.queryWithSpecifiedClassType(sql, QualityHistory.class,
jdbcTemplateCpc);
if (ls != null) {
res.addAll(ls);
}
}
return res;
}

.

......
for (int pageNo = 1; pageNo doPage(sql, pageNo, set);
}

......

private void doPage(String sql, int pageNo, Set set) {
long t1 = System.currentTimeMillis();
final int startIndex = PageUtil.getStartOfPage(pageNo, pageSize);
String sqlLimit = PageUtil.getLimitString(sql, true);
Object[] obj = new Object[] { startIndex, startIndex + pageSize };
List list = jdbcTemplateCpc.query(sqlLimit, obj,
new RowMapper() {

@Override
public Object mapRow(ResultSet rs, int arg1)
throws SQLException {
return "" + rs.getString(2) + rs.getString(1);
}

});
set.addAll(list);
long t2 = System.currentTimeMillis();
logger.info("第" + pageNo + "次查询数据量" + list.size() + ",set中现有"
+ set.size() + "条记录，耗时" + ((t2 - t1) / 1000) + "秒");
}