MyBatis流式查询

1.应用场景说明

• MyBatis
  preview: JDBC三种读取方式：
  1.一次全部（默认）：一次获取全部。
  2.流式：多次获取，一次一行。
  3.游标：多次获取，一次多行。

在开发中我们经常需要会遇到统计数据，将数据导出到excel表格中。由于生成报表逻辑要从数据库读取大量数据并在内存中加工处理后再生成Excel返回给客户端。如果数据量过大，采用默认的读取方式（一次性获取全部）会导致内存飙升，甚至是内存溢出。而导出数据又需要查询大量的数据，因此采用流式查询就比较合适了。

2.模拟excel导出场景

1.创建海量数据的sql脚本

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

delimiter $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
create function rand_string(n INT)
returns varchar(255) #该函数会返回一个字符串
begin
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 declare return_str varchar(255) default '';
 declare i int default 0; 
 while i < n do
    # concat 函数 : 连接函数mysql函数
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


 #这里我们又自定了一个函数,返回一个随机的部门号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$

 #创建一个存储过程， 可以添加雇员
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 set autocommit = 0; #默认不提交sql语句
 repeat
 set i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
 #commit整体提交所有sql语句，提高效率
   commit;
 end $$

 #添加8000000数据
call insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

2.MyBatis流式查询

1.创建src\main\java\com\llp\llpmybatis\entity\Emp.java

@Data
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private BigDecimal sal;
    private BigDecimal comm;
    private Integer deptno;
}
1
2
3
4
5
6
7
8
9
10
11

2.创建src\main\java\com\llp\llpmybatis\vo\EmpVo.java

@Data
public class EmpVo {
    @ExcelProperty("员工编号")
    private Integer empno;
    @ExcelProperty("员工姓名")
    private String ename;
    @ExcelProperty("员工工种")
    private String job;
    @ExcelProperty("主管编号")
    private Integer mgr;
    @ExcelProperty("入职日期")
    private Date hiredate;
    @ExcelProperty("工资")
    private BigDecimal sal;
    @ExcelProperty("通讯")
    private BigDecimal comm;
    @ExcelProperty("部门编号")
    private Integer deptno;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.创建src\main\java\com\llp\llpmybatis\controller\EmpController.java

@RestController
public class EmpController {

    @Autowired
    private EmpService empService;

    /**
     * 导出员工数据到excel
     */
    @RequestMapping("/export")
    public void exportEmp(){
        StopWatch watch = new StopWatch();
        watch.start();
        List<EmpVo> empList = empService.exportEmp();
        //将数据分sheet进行导出
        EasyExcleUtil.excelExportDivisionBySheet(EmpVo.class, "员工信息_"+System.currentTimeMillis(), empList);
        watch.stop();
        long totalTimeMillis = watch.getTotalTimeMillis();
        System.out.println("共计耗时："+totalTimeMillis+"毫秒");
    }

    /**
     * 导入excel数据到员工表
     * @param file
     */
    @RequestMapping("/import")
    public void importEmp(@RequestParam(name = "file") MultipartFile file){
        //这里我们在导入时传入回调接口的匿名内部类实现，在ExcleDataListener easyExcel读取监听器中对接口进行赋值
        //在监听器中doAfterAllAnalysed，在所有数据解析完之后回调用这个方法，我们在方法中对导出的数据集进行赋值
        EasyExcleUtil.importExcel(file, EmpVo.class, new ExcleFinshCallBack(){
            @Override
            public void doAfterAllAnalysed(List<Object> result) {
                empService.exportEmp();
            }
        });
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

4.创建src\main\java\com\llp\llpmybatis\service\EmpService.java

public interface EmpService {
    List<EmpVo> exportEmp();
}
1
2
3

5.创建src\main\java\com\llp\llpmybatis\service\impl\EmpServiceImpl.java（重点）

@Service
public class EmpServiceImpl implements EmpService {

    @Resource
    private EmpDao empdao;

    /**
     * mybatis流式查询导出员工数据
     * @return
     */
    @Override
    public List<EmpVo> exportEmp() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<EmpVo> empList = new ArrayList<>();
        empdao.getAll(new ResultHandler<EmpVo>() {
            /**
             * mybatis流失查询会回调处理逻辑
             * @param resultContext
             */
            @Override
            public void handleResult(ResultContext<? extends EmpVo> resultContext) {
                empList.add(resultContext.getResultObject());
            }
        });
        stopWatch.stop();
        System.out.println("查询共计耗费"+stopWatch.getTotalTimeMillis()+"毫秒");
        return empList;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

6.创建src\main\java\com\llp\llpmybatis\dao\EmpDao.java（重点）

@Repository
public interface EmpDao {
    void getAll(ResultHandler<EmpVo> handler);
}
1
2
3
4

这里dao层没有返回值，但是在还是需要指定resultMap，因为查询的数据要映射到回调函数的resultContext中，此外还需要设置：resultSetType=“FORWARD_ONLY” 、fetchSize=“-2147483648”

EmpDao.xml

<mapper namespace="com.llp.llpmybatis.dao.EmpDao">

   <resultMap id="empResultMap" type="com.llp.llpmybatis.vo.EmpVo">
      <result column="empno" property="empno"/>
      <result column="ename" property="ename"/>
      <result column="job" property="job"/>
      <result column="mgr" property="mgr"/>
      <result column="hiredate" property="hiredate"/>
      <result column="sal" property="sal"/>
      <result column="comm" property="comm"/>
      <result column="deptno" property="deptno"/>
   resultMap>
   <select id="getAll" resultMap="empResultMap" resultSetType="FORWARD_ONLY" fetchSize="-2147483648">
      select *
      from emp;
   select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

至此mybatis流式查询就完成了

3.Excel通用导出工具类

1.Excel导入导出工具类

public class EasyExcleUtil {

    private static final int MAXROWS = 500000;

    /**
     * excel读取
     *
     * @param file     excel文件
     * @param head     列名
     * @param callBack 回调接口的实现类
     */
    public static void importExcel(MultipartFile file, Class head, ExcleFinshCallBack callBack) {
        try {
            EasyExcel.read(file.getInputStream(), head, new ExcleDataListener(callBack)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出数据
     *
     * @param head      类名
     * @param excelname excel名字
     * @param data      数据
     *                  java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowa
     *                  这是由于Excel的一张sheet允许的最大行数是1048575，由于导出的数据比较大，超出了一张sheet所能容纳的最大行数，导致无法继续创建新的行
     *                  1048575
     *                  1000000
     */
    public static void excelExport(Class head, String excelname, List data) {
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        //response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(excelname, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head).sheet("Sheet1").doWrite(data);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取默认表头内容的样式
     *
     * @return
     */
    private static HorizontalCellStyleStrategy getDefaultHorizontalCellStyleStrategy() {
        /** 表头样式 **/
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景色（浅灰色）
        // 可以参考：https://www.cnblogs.com/vofill/p/11230387.html
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        // 字体大小
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 内容样式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 内容字体样式（名称、大小）
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容垂直居中对齐
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置内容水平居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 头样式与内容样式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }


    /**
     * 将数据分sheet进行导出
     * @param data     查询结果
     * @param fileName 导出文件名称
     * @param clazz    映射实体class类
     * @param       查询结果类型
     * @throws Exception
     */
    public static <T> void excelExportDivisionBySheet(Class clazz, String fileName, List<T> data) {
        OutputStream out = null;
        ExcelWriter excelWriter = null;
        try {
            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = requestAttributes.getResponse();
            // 分割的集合
            List<List<T>> lists = SplitList.splitList(data, MAXROWS);
            out = getOutputStream(fileName, response);
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(out, clazz).excelType(ExcelTypeEnum.XLSX).registerWriteHandler(getDefaultHorizontalCellStyleStrategy());
            excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1; i <= lists.size(); i++) {
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder();
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("sheet" + i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(lists.get(i - 1), writeSheet);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws IOException {
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        //  response.setContentType("application/vnd.ms-excel"); // .xls
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); // .xlsx
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        return response.getOutputStream();
    }


}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144

2.Excel数据读取监听器

/**
 * excel读取监听器
 */
public class ExcleDataListener extends AnalysisEventListener {
   //定义一个保存Excel所有记录的集合
    private List<Object> list = new LinkedList<>();
    //回调接口
    private ExcleFinshCallBack callBack;

    /**
     * 构造注入ExcleFinshCallBack
     * @param callBack
     */
    public ExcleDataListener(ExcleFinshCallBack callBack) {
        this.callBack = callBack;
    }


    /**
     * 这个每一条数据解析都会来调用
     * 我们将每一条数据都保存到list集合中
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        list.add(data);
    }

    /**
     * 所有数据解析完成了 都会来调用这个方法
     * 在
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        this.callBack.doAfterAllAnalysed(this.list);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

4.Excel读取数据完成回调接口

/**
 * excel读取数据完成回调接口
 */
public interface ExcleFinshCallBack {
    void doAfterAllAnalysed(List<Object> result);
}
1
2
3
4
5
6

5.拆分List集合工具类

/**
 * 拆分List集合
 */
public class SplitListUtil {

    /**
     *
     * @param list 待切割集合
     * @param len  集合按照多大size来切割
     * @param 
     * @return
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.size() == 0 || len < 1) {
            return null;
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int size = list.size();
        int count = (size + len - 1) / len;

        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
            result.add(subList);
        }
        return result;
    }


    /**
     * @param source 源集合
     * @param n      分成n个集合
     * @param     集合类型
     * @return
     * @description 集合平均分组
     */
    public static <T> List<List<T>> groupList(List<T> source, int n) {
        if (source == null || source.size() == 0 || n < 1) {
            return null;
        }
        if (source.size() < n) {
            return Arrays.asList(source);
        }
        List<List<T>> result = new ArrayList<List<T>>();
        int number = source.size() / n;
        int remaider = source.size() % n;
        // 偏移量，每有一个余数分配，就要往右偏移一位
        int offset = 0;
        for (int i = 0; i < n; i++) {
            List<T> list1 = null;
            if (remaider > 0) {
                list1 = source.subList(i * number + offset, (i + 1) * number + offset + 1);
                remaider--;
                offset++;
            } else {
                list1 = source.subList(i * number + offset, (i + 1) * number + offset);
            }
            result.add(list1);
        }

        return result;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

4.测试结果

sheet1

sheet2

sheet3

5.遗留问题，待处理

这个问题时由于excelWriter.finish();去关闭连接时，发现连接已经被终止了导致的，对数据导出的完整性并没有影响