大数据量查询方法

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

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

private List getQualityWithGroupidAndKey(
            List cpcList, Long groupid, Long accountid) {
        if (cpcList == null || cpcList.size() <= 0)
            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 < group; 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 <= totalPageCount; 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) + "秒");
}