六、ibatis1.2.8查询性能优化，实现百万数据zip导出

    经测试发现将查询的结果100万数据(池子中共有大概14亿的数据)写入Excle文件并进行压缩导出zip文件最耗时的地方竟然在查询，因此本篇文章主要是针对如何在spring+ibatis1.2.8中优化查询
1）对查询的SQL进行优化，表数据量特别大(上亿、上十亿)的时候可以按照查询条件中的某个字段如：finish_time进行分区存储或者建立复合索引或者分区复合索引
2）有博友说在ibatis映射器<select>元素中增加fetchSize属性，可惜ibatis1.2.8不支持该属性。其在2.0版本中才增加了该属性(未确认是否属实)。让人豁然开朗的是
在Statement和ResultSet接口中都有提供有setFetchSize方法，因此优化的出发点就有了。使用spring的JdbcTemplate获取数据源信息后再使原始的jdbc方法进行查询优化
<select  id="getPersonCount" resultClass="PoersonResult" parameterClass="PoersonBean" fetchSize="1000">

1、什么是fetchSize
    对Oracle中的fetchsize的理解，当我们执行一个SQL查询语句的时候，需要在客户端和服务器端都打开一个游标，并且分别申请一块内存空间，作为存放查询的数据的一个缓冲区。这块内存区，存放多少条数据就由fetchsize来决定，同时每次网络包会传送fetchsize条记录到客户端。应该很容易理解，如果fetchsize设置为20，当我们从服务器端查询数据往客户端传送时，每次可以传送20条数据，但是两端分别需要20条数据的内存空闲来保存这些数据。fetchsize决定了每批次可以传输的记录条数，但同时，也决定了内存的大小。这块内存，在oracle服务器端是动态分配的（大家可以想想为什么）。而在客户端（JBOSS），PS对象会存在一个缓冲中（LRU链表），也就是说，这块内存是事先配好的，
应用端内存的分配在conn.prepareStatement(sql)或都conn.CreateStatement(sql)的时候完成。
    setFetchSize 最主要是为了减少网络交互次数设计的。访问ResultSet时，如果它每次只从服务器上取一行数据，则会产生大量的开销。setFetchSize的意 思是当调用rs.next时，ResultSet会一次性从服务器上取得多少行数据回来，这样在下次rs.next时，它可以直接从内存中获取出数据而不 需要网络交互，提高了效率。 这个设置可能会被某些JDBC驱动忽略的，而且设置过大也会造成内存的上升。
参看博文：
https://blog.csdn.net/bincavin/article/details/8727612
https://blog.csdn.net/hx756262429/article/details/8196845

2、Statement接口和ResultSet接口中setFetchSize(int rows)理解
1）Statement接口中解释如下：
为JDBC 驱动程序提供一个提示，它提示此Statement 生成的ResultSet 对象需要更多行时应该从数据库获取的行数。指定的行数仅影响使
用此语句创建的结果集合。如果指定的值为 0，则忽略该提示。默认值为 0。
2）ResultSet接口中解释如下：
为 JDBC 驱动程序设置此ResultSet 对象需要更多行时应该从数据库获取的行数。如果指定的获取大小为零，则 JDBC 驱动程序忽略该值，
随意对获取大小作出它自己的最佳猜测。默认值由创建结果集的Statement 对象设置。获取大小可以在任何时间更改。

3、优化查询思路：使用spring的JdbcTemplate获取数据源后再使原始setFetchSize方法
原spring集成ibatis后使用getList()进行查询方法如下：
List org.biframework.dao.ibatis.BaseDao.getList(String statementName, Object parameterObject) throws DaoException

3.1）配置jdbcTemplate对象
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource">
        <ref local="dataSource"/>
    </property>
</bean>
3.2）获取JdbcTemplate对象

方法一：实现类(service)中利用构造器去获取JdbcTemplate对象
<bean id="stPolicyService"
    class="org.bussiness.product.detailquery.service.StPolicyService">
    <property name="stPolicyDao">
        <ref local="stPolicyDao" />
    </property>
    <property name="jdbcTemplate">
        <ref bean="jdbcTemplate" />
    </property>
</bean>
同时在StPolicyService类中提供jdbcTemplate对象的set和get方法

方法二：Spring也我们提供了JdbcDaoSupport支持类，所有DAO继承这个类，就会自动获得JdbcTemplate（前提是注入DataSource）
<bean id="userDao" class="com.curd.spring.impl.UserDAOImpl">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>

spring集成ibatis进行项目中dao层基类封装可以参看我的博文：https://www.cnblogs.com/jiarui-zjb/p/9534810.html

3.3）点击导出按钮，生成Excle或者zip功能实现：
支持每个Excle文件最多15万条数据，每个sheet页最多5万条数据，导出数据量小于等于15万条则生成excle文件，大于15万条将生成zip文件

Action中的方法

if(count == 0){
    response.setCharacterEncoding("GBK"); 
    PrintWriter out = response.getWriter();
    out.println("<script>");
    out.println("    alert('您查询的数据数量为零!');");
    out.println("    window.close();");
    out.println("</script>");
    return null;
}else if(count >= 1000000){
    response.setCharacterEncoding("GBK"); 
    PrintWriter out = response.getWriter();
    out.println("<script>");
    out.println("    alert('查询结果数据量偏大，请缩小查询范围！');");
    out.println("    window.close();");
    out.println("</script>");
    return null;
}else{
    List list = new ArrayList();
    System.out.println("数据提取开始...");
    if("1".endsWith(Kind)){
        list = this.stPolicyService.getStPerm(nstPolicyBean);
    }
    String filename = "明细数据查询结果";
    //response.setHeader("Connection", "close");
    List head = new ArrayList();
    head.add("机构ID");
    head.add("机构名称");
    head.add("渠道");
    head.add("姓名");
    head.add("年龄");
    //工具类
    ExcelTools excel = new ExcelTools();
    long t1 = System.currentTimeMillis();
    int exclNumber = excel.makeExcelNumber(list);
    //1、将查询的结果集list中的数据拆分后放入ArrayList中
    ArrayList spileList = excel.getSpileList(list,exclNumber);
    long nowTime = System.nanoTime();
    String tempExclePath=request.getRealPath("/") + File.separator +"WEB-INF"+File.separator+"temp"+File.separator+"excle"+File.separator+File.separator+nowTime;
    //存放excle的文件夹
    File excleFile=new File(tempExclePath);
    //2.1生成exlce文件
    if(spileList.size()==1){
        excel.makeTempExcel(tempExclePath,filename,spileList,excel,head,excleFile);
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(filename.getBytes("GBK"),"ISO8859-1") + ".xls\"");
        FileInputStream excleInput =new FileInputStream(new File(tempExclePath+File.separator+filename+".xls"));
        OutputStream os = response.getOutputStream();
        int temp = 0;
        byte[] buffer = new byte[1024 * 8];//缓冲区
        while((temp = excleInput.read(buffer)) != -1){
            os.write(buffer, 0,temp);
        }
        excleInput.close();
        os.flush();
        long end = System.currentTimeMillis();
        System.out.println("共计耗时--"+(end-begin)/(1000)+"--秒");
        //下载完之后清空zip目录下生成的临时文件
        if(excel.deleteDir(new File(tempExclePath))){
            System.out.println("删除临时生成的xls成功");
        }
        os.close();
    }else {
    //2.2生成zip文件
        excel.makeTempExcel(tempExclePath,filename,spileList,excel,head,excleFile);
        long makeExcleend = System.currentTimeMillis();
        InputStream input = null;
        //1)打包生成的zip文件目录
        String tempZipPath=request.getRealPath("/") + File.separator+"WEB-INF"+File.separator+"temp"+File.separator+"zip"+File.separator+nowTime;
        File tempZipFile=new File(tempZipPath);
        if(!tempZipFile.exists()||!tempZipFile.isDirectory()){
            tempZipFile.mkdirs();
        }
     
        File zipFile = new File(tempZipPath+File.separator+filename+".zip");//要zip文件名
        //2)zip输出流
        //正确输出流
        FileOutputStream zipFileFos = new FileOutputStream(zipFile);
        ZipOutputStream zipOut = new ZipOutputStream(zipFileFos);
        // 创建缓冲输出流
        BufferedOutputStream bos = new BufferedOutputStream(zipOut,1024);
        if(excleFile.isDirectory()){
            File[] files = excleFile.listFiles();
            for(int i = 0; i < files.length; ++i){
                input = new FileInputStream(files[i]);
                //3)逐一对需要打包的文件夹目录中的文件进行压缩，生成后的压缩文件目录名称为：短期险保单明细数据查询结果（存放files[i]）
                zipOut.putNextEntry(new ZipEntry(filename+ File.separator + files[i].getName()));
                BufferedInputStream bis = new BufferedInputStream(input);
                int temp = 0;
                byte[] buffer = new byte[1024 * 8];//缓冲区
                while((temp = bis.read(buffer)) != -1){
                    bos.write(buffer, 0,temp);
                }
                input.close();
            }
            zipOut.setEncoding("gbk");
            
        }
        zipOut.close();
        zipFileFos.close();
        //清空excle目录下生成的临时文件
        if(excel.deleteDir(excleFile)){
            System.out.println("删除临时生成的exlce成功");
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=\"" + new String(filename.getBytes("GBK"),"ISO8859-1") + ".zip\"");
        FileInputStream zipInput =new FileInputStream(zipFile);
        OutputStream os = response.getOutputStream();
        int temp = 0;
        byte[] buffer = new byte[1024 * 8];//缓冲区
        while((temp = zipInput.read(buffer)) != -1){
            os.write(buffer, 0,temp);
        }
        long end = System.currentTimeMillis();
        System.out.println("共计耗时--"+(end-begin)/(1000)+"--秒");
        zipInput.close();
        os.flush();
        //下载完之后清空zip目录下生成的临时文件
        if(excel.deleteDir(new File(tempZipPath))){
            System.out.println("删除临时生成的zip成功");
        }
        os.close();
    }
    //excel.makeZipExcel(os, list, head, filename+".xls");
}

工具类中方法

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;

public class ExcelTools {

    private int _SHEETSIZE = 50000;//每个sheet页多少条数据
    private int xlsSheetNum = 3;//每个excle文件存在多少个sheet页
    
    public int getXlsSheetNum() {
        return xlsSheetNum;
    }

    public void setXlsSheetNum(int xlsSheetNum) {
        this.xlsSheetNum = xlsSheetNum;
    }

    public int get_SHEETSIZE() {
        return _SHEETSIZE;
    }

    public void set_SHEETSIZE(int sheetsize) {
        _SHEETSIZE = sheetsize;
    }

    private static WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
            WritableFont.BOLD, false);
    private static WritableCellFormat wcfF = new WritableCellFormat(wf);
    // 设置内容字体、字号等
    private static WritableFont wft = new WritableFont(WritableFont.ARIAL, 10,
            WritableFont.NO_BOLD, false);
    private static WritableCellFormat wcfFt = new WritableCellFormat(wft);
    // 设置合计字段字体、字号等
    private static WritableCellFormat wcfFtotal = new WritableCellFormat(wf);
    // 设置保费数值类型
    private static NumberFormat nf = new NumberFormat("#,##0.00");
    private static WritableCellFormat wcfN = new WritableCellFormat(nf);
    // 设置其他费用数值类型
    private static NumberFormat nft = new NumberFormat("#,##0");
    private static WritableCellFormat wcfNt = new WritableCellFormat(nft);
    
    
    static {
        try {
            wcfF.setBorder(Border.ALL, BorderLineStyle.THIN);
            wcfF.setAlignment(Alignment.CENTRE);
            wcfFt.setBorder(Border.ALL, BorderLineStyle.THIN);
            wcfFtotal.setAlignment(Alignment.CENTRE);
            wcfFtotal.setBorder(Border.ALL, BorderLineStyle.THIN);
            wcfN.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            wcfNt.setBorder(Border.ALL, BorderLineStyle.THIN);
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    //服务器中不存在临时文件目录则进行创建
    @SuppressWarnings("unchecked")
    public void makeFile(File file, List datas, List head, boolean zip, String excelname)
            throws FileNotFoundException, IOException, WriteException {
        FileOutputStream out = new FileOutputStream(file);
        if(zip){
            makeZipExcel(out, datas, head,excelname);
        }else{
            makeExcel(out, datas, head);
        }
        out.flush();
        out.close();
    }
    
    //删除服务器WEB-INF/temp/excle/9725645661448目录及其下面的文件
    @SuppressWarnings("unchecked")
    public boolean deleteDir(File dir) {
        if (dir.isDirectory()) {
            String[] children = dir.list();
            //递归删除目录中的子目录下
            for (int i=0; i<children.length; i++) {
                boolean success = deleteDir(new File(dir, children[i]));
                if (!success) {
                    System.out.println("删除失败");
                    return false;
                }
            }
        }
        // 目录此时为空，可以删除
        return dir.delete();
    }
    
    //生成多少个xls文件,每个xls文件为3个sheet页，每个sheet页_SHEETSIZE=50000条记录
    public int makeExcelNumber(List datas)
            throws IOException, WriteException {
        int cycle =datas.size()%(_SHEETSIZE*xlsSheetNum)>=1?(datas.size()/(_SHEETSIZE*xlsSheetNum)+1):datas.size()/(_SHEETSIZE*xlsSheetNum);
        return cycle;
    }
    
    //仅仅生成xls文件
    @SuppressWarnings("unchecked")
    public void simpleMakeExcel(OutputStream out,List datas, List head,String xlsName)
            throws IOException, WriteException {
        WritableWorkbook wwb=Workbook.createWorkbook(out);
        //创建多少个sheet页
        int cycle = (datas.size() + (_SHEETSIZE - 1)) / _SHEETSIZE;
        for (int i = 0; i < cycle; i++) {
            makeSheet(wwb, datas, head, i);
        }
        wwb.write();
        wwb.close();
    }
    //对所有数据按照每个exlce文件存放数量进行拆分放入list中
    public ArrayList getSpileList(List list,int exclNumber){
        ArrayList spileList=new ArrayList();
        int baseRow;
        if (exclNumber > 1) {
            for (int i = 0; i < exclNumber; i++) {
                //i=0为第一个sheet页没问题
                ArrayList innerList = new ArrayList();
                //index：第几个sheet页；    _SHEETSIZE：每sheet页面数量大小10个
                baseRow = (i+1)*xlsSheetNum*_SHEETSIZE;
                //不足下一个xls文件时
                for (int j=i*xlsSheetNum*_SHEETSIZE; j< baseRow; j++) {// 每个spileList元素存放多少条记录
                    if(j<list.size()){
                        innerList.add(list.get(j));
                    }
                }
                spileList.add(innerList);
            }
        } else {
            spileList.add(0, list);
        }
        return spileList;
    }
    //生成.xls文件临时存放目录
    public void makeTempExcel(String tempExclePath,String filename,ArrayList spileList,ExcelTools excel,List head,File excleFile){
        if(!excleFile.exists()||!excleFile.isDirectory()){
            excleFile.mkdirs();
        }
        for(int i=0;i<spileList.size();i++){
            FileOutputStream fos=null;
            try {
                if(i==0){
                    fos = new FileOutputStream(new File(tempExclePath+File.separator+filename+".xls"));
                }else {
                    fos = new FileOutputStream(new File(tempExclePath+File.separator+filename+i+".xls"));
                }
                try {
                    excel.simpleMakeExcel(fos,(List)spileList.get(i), head, "filename"+i+".xls");
                } catch (WriteException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally{
                // 压缩
                try {
                    fos.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
    
    @SuppressWarnings("unchecked")
    public void makeZipExcel(OutputStream out, List datas, List head,String xlsName)
            throws IOException, WriteException {
        ZipOutputStream zipout = new ZipOutputStream(out);
        zipout.setEncoding("GBK");  
        ZipEntry entry = new ZipEntry(xlsName);
        zipout.putNextEntry(entry);

        makeExcel(new BufferedOutputStream(zipout), datas, head);

        zipout.closeEntry();
        zipout.flush();
        zipout.close();
    }

    @SuppressWarnings("unchecked")
    public void makeExcel(OutputStream out, List datas, List head)
            throws IOException, WriteException {
        WritableWorkbook wwb = Workbook.createWorkbook(out);
        int cycle = (datas.size() + (_SHEETSIZE - 1)) / _SHEETSIZE;
        for (int i = 0; i < cycle; i++) {
            makeSheet(wwb, datas, head, i);
        }
        wwb.write();
        wwb.close();
    }

    @SuppressWarnings("unchecked")
    public void makeSheet(WritableWorkbook wwb, List datas, List head, int index)
            throws WriteException {
        int baseRow = index * _SHEETSIZE;
        int endRow = (baseRow + _SHEETSIZE > datas.size()) ? datas.size() : (baseRow + _SHEETSIZE);
        WritableSheet ws = wwb.createSheet((baseRow+1) + "-" + endRow, index);

        for (int i = 0; i < head.size(); i++) {
            ws.addCell(new Label(i, 0, head.get(i).toString(), wcfF));
        }

        for (int i = 0; i + baseRow < datas.size() && i < _SHEETSIZE; i++) {
            Getable obj = (Getable) datas.get(i + baseRow);
            for (int j = 0; j < head.size(); j++) {
                Object o = obj.get(j);
                if (o != null) {
                    if (o instanceof String) {
                        ws.addCell(new Label(j, i + 1, o.toString(), wcfFt));
                    } else if (o instanceof Double) {
                        ws.addCell(new Number(j, i + 1, ((Double) o)
                                .doubleValue(), wcfN));
                    } else if (o instanceof Integer) {
                        ws.addCell(new Number(j, i + 1, ((Integer) o)
                                .doubleValue(), wcfFt));
                    }
                } else {
                    ws.addCell(new Label(j, i + 1, "", wcfFt));
                }
            }
        }

    }

}

service中的查询方法

public List getStPerm(StPolicyBean param) throws DaoException {
        String sql = "SELECT T1.ORGAN_ID,T2.ORGNAME, FROM   DM_COMM_USR T1 INNER  JOIN DMUSER.D_ORG T2 ON (T1.ORGAN_ID = T2.ORGCODE)WHERE 1=1";
        StringBuilder sb=new StringBuilder();
        sb.append(sql);
        if(param.getORGAN_ID()!=null&&!"".equals(param.getORGAN_ID())){
            sb.append("AND T1.ORGAN_ID in (SELECT ORGAN_ID FROM   ODSUSER.T_COMPANY_ORGAN START  WITH ORGAN_ID = "+param.getORGAN_ID()+" "+"CONNECT BY PARENT_ID = PRIOR ORGAN_ID)");
        }
        if(param.getFINISH_TIME1()!=null&&!"".equals(param.getFINISH_TIME1())){
            sb.append("AND T1.FINISH_TIME >= to_date("+"'"+param.getFINISH_TIME1().replace(" ", "")+"'"+",'yyyy-mm-dd')");
        }
        if(param.getFINISH_TIME2()!=null&&!"".equals(param.getFINISH_TIME2())){
            sb.append("AND T1.FINISH_TIME <= to_date("+"'"+param.getFINISH_TIME2().replace(" ", "")+"'"+",'yyyy-mm-dd')");
        }
        sb.append("order by T1.ORGAN_ID");
        
        System.out.println("sb.toString-->"+sb.toString());
        DataSource dataSource = jdbcTemplate.getDataSource();
        Connection conn = null;
        PreparedStatement pst = null;
        List<StPolicyBean> policyList = new ArrayList<StPolicyBean>();
        ResultSet rs=null;
        StPolicyBean stPolicyBean=null;
        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            pst = conn.prepareStatement(sb.toString());
            pst.setFetchSize(5000);
            rs = pst.executeQuery();
            rs.setFetchSize(5000);
            while (rs.next()) {
                stPolicyBean = new StPolicyBean();
                stPolicyBean.setORGAN_ID(rs.getString("ORGAN_ID"));
                stPolicyBean.setORGNAME(rs.getString("ORGNAME"));
                stPolicyBean.setDISTRCHNNNAME(rs.getString("DISTRCHNNNAME"));
                policyList.add(stPolicyBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (rs != null) { // 关闭记录集
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pst != null) { // 关闭声明
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) { // 关闭连接对象
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return policyList;
    }

上述代码复制粘贴后调试后即可运行，备注：上述将查询的代码放在service层了，最好是放到dao层进行实现。

参看博文：https://www.cnblogs.com/lichenwei/p/3902294.html