使用spring jdbcTemplate 批量查询校验【支持50万数据】使用JDBC不会造成内存溢出

3/8/2017来源:ASP.NET技巧人气:1814

JDBC连接MySQL,查大数据集报:java.lang.OutOfMemoryError: Java heap space http://blog.csdn.net/hwwn2009/article/details/42423039

1.使用sPRing jdbcTemplate做批量校验 (支持50万数据)

public void mxCheck(String tableName,Map<String,String> checkMap) { String sql="SELECT * from "+ConstKey.SCHEMA_RPT.trim()+"." + tableName+" where 1=1"; final Map<String,String> map = checkMap; jdbcTemplate.query(sql, new ResultSetExtractor<List>() { @Override public List extractData(ResultSet rs) throws SQLException, DataaccessException { List result = new ArrayList(); ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据 int columnCount = md.getColumnCount(); //获得列数 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); Date checkStart = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); System.out.println("明细校验开始时间:"+dateFormat.format(checkStart)); logger.info("明细校验开始时间:"+dateFormat.format(checkStart)); while(rs.next()) { Map<String,Object> rowData = new HashMap<String,Object>();//一行记录 for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); if(rs.getRow()%ConstKey.checkNum == 0 || rs.isLast()){ checkData.singleCheckData(list,map); list.clear(); } } Date checkEnd = new Date(); SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); System.out.println("明细校验结束时间:"+dateFormat2.format(checkEnd)); logger.info("明细校验结束时间:"+dateFormat2.format(checkEnd)); return result; }}); }

1.使用JDBC原生做批量校验 (不会有内存溢出)

public void mxCheckJDBC(String tableName,Map<String,String> checkMap) { String sql="SELECT * from "+ConstKey.SCHEMA_RPT.trim()+"." + tableName+" where 1=1"; long checkNum = getCheckNum(tableName); DataSource dataSource = jdbcTemplate.getDataSource(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; long count =0; try{ con = dataSource.getConnection(); ps = (PreparedStatement) con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); ps.setFetchSize(Integer.MIN_VALUE); ps.setFetchDirection(ResultSet.FETCH_REVERSE); rs = ps.executeQuery(); ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据 int columnCount = md.getColumnCount(); //获得列数 List<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); Date checkStart = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); System.out.println("明细校验开始时间:"+dateFormat.format(checkStart)); logger.info("明细校验开始时间:"+dateFormat.format(checkStart)); while(rs.next()) { Map<String,Object> rowData = new HashMap<String,Object>();//一行记录 for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); count++; if(count%ConstKey.checkNum == 0 || count == checkNum){ checkData.singleCheckData(list,checkMap); list.clear(); } } Date checkEnd = new Date(); SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); System.out.println("明细校验结束时间:"+dateFormat2.format(checkEnd)); logger.info("明细校验结束时间:"+dateFormat2.format(checkEnd)); }catch(Exception e){ logger.info("批量校验异常:"+e.getMessage()); } finally { try { if(rs!=null){ rs.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(ps!=null){ ps.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if(con!=null){ con.close(); } } catch (SQLException e) { e.printStackTrace(); } } }