Java一次性查询几十万 几百万数据解决办法

在做大数据量同步的时候，需要注意的内存使用问题，程序稍微控制不足，可能就会导致内存溢出等问题...在网上找了一些资料，发现大家都使用的如下方式：

1、先批量查询出所有数据，例子中是一万条一次
2、在查出数据后把每次的数据按一定的规则存入本地文件
3、读取文件数据时，可以采取单例模式，批量提交等方式操作数据库

代码如下：
Java代码 
public boolean createUploadFileByAll() { 
        boolean flag = false; 
        int onerun = 10000;  // 每次读取记录数 
        int lastrow = 0; // 每次读取后的最后一条记录 
        ResultSet rs = null; 
        Statement stat = null; 
        try { 
            String hql = "SELECT count(*) FROM T_jgdm"; 
            int datanum = ((Long) this.getSession().createQuery(hql).uniqueResult()).intValue(); 
            int runnum = datanum % onerun == 0 ? (datanum / onerun) : (datanum / onerun) + 1; 
            // 分批读取数据 
            conn = getConn(); 
            stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); 
            StringBuffer text = new StringBuffer(); 
            text.append("机构代码（JGDM）-").append("机构名称（JGMC）-").append("首次办证日期（SCBZRQ）-"); 
            text.append("外经委填表人(WJWTBR)-").append("可否公开(GK)-").append("发卡标志(FKBZ)-"); 
            text.append("发卡数量(FKSL)-").append("打印标志(DYBZ)-").append("电邮地址(EMAIL)-"); 
            text.append("网址(URL)-").append("最后修改日期(LASTDATE)-").append("操作标志(CZFLAG)-"); 
            text.append("是否邮寄(YJFLAG)-").append("数据状态(SJZT)-").append("检验结果(JYJG)-"); 
            text.append("分值(FZ)-").append("作废日期（ZFRQ）-").append("办证机构区划代码（BZJGDM）-"); 
            text.append("变更日期（BGRQ）-").append("年检日期(NJRQ)-").append("年检期限(NJQX)-"); 
            text.append("迁址日期（QZRQ)-").append("开户银行(KHYH)-").append("开户账号(KHZH)\r\n");  // 文件列注解  \r\n为换行符 
 
            for (int r = 0; r < runnum; r++) { 
                System.out.println("createUploadFileByAll--" + datanum + " 开始查询第" + (r + 1) + "批数据"); 
                String sql = "SELECT *\n" + 
                        "  FROM (SELECT rownum rn, " + QUERY_FIELD + " FROM t_jgdm ORDER BY rownum ASC)\n" + 
                        " WHERE rn > " + lastrow; 
                stat.setMaxRows(onerun); 
                stat.setFetchSize(1000); // 当调用rs.next时，ResultSet会一次性从服务器上取1000行数据回来，在下次rs.next时，它可以直接从内存中获取出数据而不需要网络交互，从而提高效率 
                rs = stat.executeQuery(sql); 
                // 生成文件 
                int i = 1; 
                while (rs.next()) { 
                    String jgdm = rs.getString("JGDM"); 
                    String jgmc = rs.getString("JGMC"); 
                    String scbzrq = rs.getString("SCBZRQ"); 
                    String wjwtbr = rs.getString("WJWTBR"); 
                    String gk = rs.getString("GK"); 
                    String fkbz = rs.getString("FKBZ"); 
                    String fksl = rs.getString("FKSL"); 
                    String dybz = rs.getString("DYBZ"); 
                    String email = rs.getString("EMAIL"); 
                    String url = rs.getString("URL"); 
                    String lastdate = rs.getString("LASTDATE"); 
                    String czflag = rs.getString("CZFLAG"); 
                    String yjflag = rs.getString("YJFLAG"); 
                    String sjzt = rs.getString("SJZT"); 
                    String jyjg = rs.getString("JYJG"); 
                    String fz = rs.getString("FZ"); 
                    String zfrq = rs.getString("ZFRQ"); 
                    String bzjgdm = rs.getString("BZJGDM"); 
                    String bgrq = rs.getString("BGRQ"); 
                    String njrq = rs.getString("NJRQ"); 
                    String njqx = rs.getString("NJQX"); 
                    String qzrq = rs.getString("QZRQ"); 
                    String khyh = rs.getString("KHYH"); 
                    String khzh = rs.getString("KHZH"); 
 
                    text.append(StringUtil.isNotEmpty(jgdm) ? jgdm : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(jgmc) ? jgmc : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(scbzrq) ? scbzrq : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(wjwtbr) ? wjwtbr : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(gk) ? gk : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(fkbz) ? fkbz : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(fksl) ? fksl : "").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(dybz) ? dybz : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(email) ? email : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(url) ? url : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(lastdate) ? lastdate : "").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(czflag) ? czflag : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(yjflag) ? yjflag : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(sjzt) ? sjzt : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(jyjg) ? jyjg : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(fz) ? fz : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(zfrq) ? zfrq : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(bzjgdm) ? bzjgdm : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(bgrq) ? bgrq : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(njrq) ? njrq : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(njqx) ? njqx : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(qzrq) ? qzrq : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(khyh) ? khyh : " ").append("^^^^"); 
                    text.append(StringUtil.isNotEmpty(khzh) ? khzh : " ").append("^^^^\r\n"); 
 
                    if (i % 1000 == 0) { 
                        // 创建文件，并追加内容 
                        FileOperate fileOperate = FileOperate.getFileOperate(); 
                        fileOperate.createFileByAddTo(UPLOAD_TEMP_FILE_NAME_BY_ALL, text.toString()); 
                        text = new StringBuffer(""); 
                    } 
                    i++; 
                } 
                // 向文件中追加不能被1000求余的内容 
                if (text.length() > 10) { 
                    FileOperate fileOperate = FileOperate.getFileOperate(); 
                    fileOperate.createFileByAddTo(UPLOAD_TEMP_FILE_NAME_BY_ALL, text.toString()); 
                } 
                lastrow += onerun; 
            } 
            rs.close(); 
            stat.close(); 
            conn.close(); 
            flag = true; 
        } catch (Exception e) { 
            flag = false; 
            e.printStackTrace(); 
        } 
        return flag; 
    } 
    文件操作方法如下：
Java代码 
/**
     * 新建文件（在原有文件内容上进行追加）
     * 默认编码UTF-8
     * @param filePathAndName
     * @param fileContent
     */ 
    public void createFileByAddTo(String filePathAndName, String fileContent) { 
        try { 
            File file = new File(filePathAndName); 
            BufferedWriter bw = null; 
            try { 
                bw = new BufferedWriter(new FileWriter(file, true)); // 参数true表示向文件中追加内容 
                bw.write(fileContent); 
                bw.close(); 
            } catch (IOException e) { 
                e.printStackTrace(); 
            } 
        } catch (Exception e) { 
            System.out.println("新建文件（在原有文件内容上进行追加）出错："); 
            e.printStackTrace(); 
        } 
    }