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

来源：互联网发布：卡乐光电 led 软件编辑：程序博客网时间：2024/06/12 00:52

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();                  }              }      }

0 0