大数量据导出设计大数量据导出设计1 导出方法设计思路有以下几点导出方法设计思路有以下几点1、 导出数据的格式采用 CSV 或者可转换 xls 的 htm 格式2、 控制一次最大的导出数量，如果超过上限分多次导出。3、 从数据库中读取数据后不放入集合类的容器，而是直接使用 IO 写入本地文件，读取一条写入一条或者合并到一定数量批量写入。4、 导出完成后提示下载链接，让用户自行下载，而不是采用原先直接放入response 弹出下载，以此减少数据流量加快导出速度。2 示例示例2.1 小数据量的导出小数据量的导出商务领航原先采用的皆是此方法，先将数据从数据库中取出然后放入集合容器中，最后再通过 response 导出数据流，此方法优点是适合小量数据的导出，而且可以做的比较通用。不足之处是资源占用较大，大数据量时 jvm 内存占用高。public static void testExport1() DBConnEX db = null; BufferedWriter buf = null; String sql = “select * from biz_moon_log t order by t.id desc“; File f = new File(“d:/log2.csv“); Connection conn = null; StringBuffer stb = null; List list = null; long star = System.currentTimeMillis(); System.out.println(star); try list = new ArrayList(); buf = new BufferedWriter(new FileWriter(f); db = new DBConnEX();conn = db.getConnection(); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next() stb = new StringBuffer(); stb.append(rs.getString(1)+“,“); stb.append(rs.getString(2)+“,“); stb.append(rs.getString(3)+“,“); stb.append(rs.getString(4)+“,“); stb.append(rs.getString(5)+“,“); stb.append(rs.getString(6); list.add(stb.toString(); /buf.write(stb.toString();stb = null; for(int i = 0;i“ + “1“ + “ mmsw 金华市锦华园度假村有限公司 系统管理员“ + “ 731“ + “ 101 )(.*)“; Pattern patter = Pattern.compile(regex); Matcher match = patter.matcher(originalText); String head = null; / String body = null; String tail = null; while (match.find() head = match.group(1); / body = match.group(2); tail = match.group(3); String t1 = “ + “999“; String t2 = “ “;String t3 = “ “; String t4 = “ “; StringBuffer sbHead = new StringBuffer(); StringBuffer sbBody = new StringBuffer(); StringBuffer sbTail = new StringBuffer(); sbHead.append(head); pw.write(sbHead.toString(); pw.flush();String cusid = null; String compname = null; String compcode = null; String username = null; String areacode = null; String cityid = null;int totalNum = 0; int index = 0;StringBuffer sql = new StringBuffer(); sql.append(“SELECT a.COMPCODE, a.COMPNAME, c.CITYID, c.AREACODE, a.CUSID,a.USERNAME “); sql.append(“FROM BIZ_COMP_INFO a “); sql.append(“LEFT OUTER join BIZ_CUST_BASE b ON a.CUSID=b.ID “); sql.append(“LEFT OUTER join BIZ_CUST_EXPANDINFO c ON a.CUSID=c.CUSTID “); sql.append(“WHERE a.ENABLED = 1 “);Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql.toString();while (rs.next() index+; totalNum+;cusid = String.valueOf(rs.getLong(“CUSID“); compcode = rs.getString(“COMPCODE“); compname = rs.getString(“COMPNAME“); username = rs.getString(“USERNAME“); areacode = rs.getString(“AREACODE“); cityid = “101“;sbBody.append(t1 + cusid); sbBody.append(t2 + compcode);sbBody.append(t2 + compname); sbBody.append(t2 + username); sbBody.append(t3 + areacode); sbBody.append(t3 + cityid); sbBody.append(t4);compcode = compname = cusid = username = areacode = cityid = null;if (index = 20000) pw.write(sbBody.toString(); pw.flush();sbBody = null; sbBody = new StringBuffer();index = 0; if (totalNum % 60000 = 0) pw.print(sbTail.toString(); pw.flush(); pw.close();pw = new PrintWriter(new FileWriter(“E:/new“ + totalNum / 60000 + “.xls“); pw.write(sbHead.toString(); pw.flush(); if (index % 20000 != 0) pw.write(sbBody.toString(); pw.flush(); System.out.println(“totalNum= “ + totalNum); sbTail.append(tail); pw.print(sbTail.toString(); pw.flush(); pw.close(); reader.close(); sbHead = sbBody = sbTail = sql = null; rs.close(); conn.close(); long endtime = System.currentTimeMillis(); System.out.println(“耗时 “ + (endtime - starttime) / 1000 + “秒“);/ System.gc(); catch (Exception e) e.printStackTrace();