六、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

巴特西

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

最新文章

热门文章