MyBatis流式查詢的使用詳解

更新時間：2022年08月17日 10:36:52 作者：llp1110

流式查詢指的是查詢成功后不是返回一個集合而是返回一個迭代器，應(yīng)用每次從迭代器取一條查詢結(jié)果。流式查詢的好處是能夠降低內(nèi)存使用

1.應(yīng)用場景說明

MyBatis

preview: JDBC三種讀取方式：

1.一次全部（默認(rèn)）：一次獲取全部。

2.流式：多次獲取，一次一行。

3.游標(biāo)：多次獲取，一次多行。

在開發(fā)中我們經(jīng)常需要會遇到統(tǒng)計數(shù)據(jù)，將數(shù)據(jù)導(dǎo)出到excel表格中。由于生成報表邏輯要從數(shù)據(jù)庫讀取大量數(shù)據(jù)并在內(nèi)存中加工處理后再生成Excel返回給客戶端。如果數(shù)據(jù)量過大，采用默認(rèn)的讀取方式（一次性獲取全部）會導(dǎo)致內(nèi)存飆升，甚至是內(nèi)存溢出。而導(dǎo)出數(shù)據(jù)又需要查詢大量的數(shù)據(jù)，因此采用流式查詢就比較合適了。

2.模擬excel導(dǎo)出場景

1.創(chuàng)建海量數(shù)據(jù)的sql腳本

CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#創(chuàng)建表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
);
#測試數(shù)據(jù)
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 $$
#創(chuàng)建一個函數(shù)，名字 rand_string，可以隨機返回我指定的個數(shù)字符串
create function rand_string(n INT)
returns varchar(255) #該函數(shù)會返回一個字符串
begin
#定義了一個變量 chars_str， 類型  varchar(100)
#默認(rèn)給 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 函數(shù) : 連接函數(shù)mysql函數(shù)
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$
 #這里我們又自定了一個函數(shù),返回一個隨機的部門號
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
 #創(chuàng)建一個存儲過程， 可以添加雇員
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit設(shè)置成0
 #autocommit = 0 含義: 不要自動提交
 set autocommit = 0; #默認(rèn)不提交sql語句
 repeat
 set i = i + 1;
 #通過前面寫的函數(shù)隨機產(chǎn)生字符串和部門編號，然后加入到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數(shù)據(jù)
call insert_emp(100001,8000000)$$
#命令結(jié)束符，再重新設(shè)置為;
delimiter ;

2.MyBatis流式查詢

1.創(chuàng)建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;
}

2.創(chuàng)建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;
}

3.創(chuàng)建src\main\java\com\llp\llpmybatis\controller\EmpController.java

@RestController
public class EmpController {
    @Autowired
    private EmpService empService;
    /**
     * 導(dǎo)出員工數(shù)據(jù)到excel
     */
    @RequestMapping("/export")
    public void exportEmp(){
        StopWatch watch = new StopWatch();
        watch.start();
        List<EmpVo> empList = empService.exportEmp();
        //將數(shù)據(jù)分sheet進(jìn)行導(dǎo)出
        EasyExcleUtil.excelExportDivisionBySheet(EmpVo.class, "員工信息_"+System.currentTimeMillis(), empList);
        watch.stop();
        long totalTimeMillis = watch.getTotalTimeMillis();
        System.out.println("共計耗時："+totalTimeMillis+"毫秒");
    }
    /**
     * 導(dǎo)入excel數(shù)據(jù)到員工表
     * @param file
     */
    @RequestMapping("/import")
    public void importEmp(@RequestParam(name = "file") MultipartFile file){
        //這里我們在導(dǎo)入時傳入回調(diào)接口的匿名內(nèi)部類實現(xiàn)，在ExcleDataListener easyExcel讀取監(jiān)聽器中對接口進(jìn)行賦值
        //在監(jiān)聽器中doAfterAllAnalysed，在所有數(shù)據(jù)解析完之后回調(diào)用這個方法，我們在方法中對導(dǎo)出的數(shù)據(jù)集進(jìn)行賦值
        EasyExcleUtil.importExcel(file, EmpVo.class, new ExcleFinshCallBack(){
            @Override
            public void doAfterAllAnalysed(List<Object> result) {
                empService.exportEmp();
            }
        });
    }
}

4.創(chuàng)建src\main\java\com\llp\llpmybatis\service\EmpService.java

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

5.創(chuàng)建src\main\java\com\llp\llpmybatis\service\impl\EmpServiceImpl.java（重點）

@Service
public class EmpServiceImpl implements EmpService {
    @Resource
    private EmpDao empdao;
    /**
     * mybatis流式查詢導(dǎo)出員工數(shù)據(jù)
     * @return
     */
    @Override
    public List<EmpVo> exportEmp() {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        List<EmpVo> empList = new ArrayList<>();
        empdao.getAll(new ResultHandler<EmpVo>() {
            /**
             * mybatis流失查詢會回調(diào)處理邏輯
             * @param resultContext
             */
            @Override
            public void handleResult(ResultContext<? extends EmpVo> resultContext) {
                empList.add(resultContext.getResultObject());
            }
        });
        stopWatch.stop();
        System.out.println("查詢共計耗費"+stopWatch.getTotalTimeMillis()+"毫秒");
        return empList;
    }
}

6.創(chuàng)建src\main\java\com\llp\llpmybatis\dao\EmpDao.java（重點）

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

這里dao層沒有返回值，但是在還是需要指定resultMap，因為查詢的數(shù)據(jù)要映射到回調(diào)函數(shù)的resultContext中，此外還需要設(shè)置：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>

至此mybatis流式查詢就完成了

3.Excel通用導(dǎo)出工具類

1.Excel導(dǎo)入導(dǎo)出工具類

public class EasyExcleUtil {
    private static final int MAXROWS = 500000;
    /**
     * excel讀取
     *
     * @param file     excel文件
     * @param head     列名
     * @param callBack 回調(diào)接口的實現(xiàn)類
     */
    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();
        }
    }
    /**
     * 導(dǎo)出數(shù)據(jù)
     *
     * @param head      類名
     * @param excelname excel名字
     * @param data      數(shù)據(jù)
     *                  java.lang.IllegalArgumentException: Invalid row number (1048576) outside allowa
     *                  這是由于Excel的一張sheet允許的最大行數(shù)是1048575，由于導(dǎo)出的數(shù)據(jù)比較大，超出了一張sheet所能容納的最大行數(shù)，導(dǎo)致無法繼續(xù)創(chuàng)建新的行
     *                  1048575
     *                  1000000
     */
    public static void excelExport(Class head, String excelname, List data) {
        ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = requestAttributes.getResponse();
        // 這里注意 有同學(xué)反應(yīng)使用swagger 會導(dǎo)致各種問題，請直接用瀏覽器或者用postman
        //response.setContentType("application/vnd.ms-excel");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try {
            // 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒有關(guān)系
            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();
        }
    }
    /**
     * 獲取默認(rèn)表頭內(nèi)容的樣式
     *
     * @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);
        //設(shè)置表頭居中對齊
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        /** 內(nèi)容樣式 **/
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 內(nèi)容字體樣式（名稱、大?。?
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋體");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //設(shè)置內(nèi)容垂直居中對齊
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //設(shè)置內(nèi)容水平居中對齊
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //設(shè)置邊框樣式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 頭樣式與內(nèi)容樣式合并
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
    /**
     * 將數(shù)據(jù)分sheet進(jìn)行導(dǎo)出
     * @param data     查詢結(jié)果
     * @param fileName 導(dǎo)出文件名稱
     * @param clazz    映射實體class類
     * @param <T>      查詢結(jié)果類型
     * @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();
    }
}

2.Excel數(shù)據(jù)讀取監(jiān)聽器

/**
 * excel讀取監(jiān)聽器
 */
public class ExcleDataListener extends AnalysisEventListener {
   //定義一個保存Excel所有記錄的集合
    private List<Object> list = new LinkedList<>();
    //回調(diào)接口
    private ExcleFinshCallBack callBack;
    /**
     * 構(gòu)造注入ExcleFinshCallBack
     * @param callBack
     */
    public ExcleDataListener(ExcleFinshCallBack callBack) {
        this.callBack = callBack;
    }
    /**
     * 這個每一條數(shù)據(jù)解析都會來調(diào)用
     * 我們將每一條數(shù)據(jù)都保存到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);
    }
    /**
     * 所有數(shù)據(jù)解析完成了 都會來調(diào)用這個方法
     * 在
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        this.callBack.doAfterAllAnalysed(this.list);
    }
}

3.Excel讀取數(shù)據(jù)完成回調(diào)接口

/**
 * excel讀取數(shù)據(jù)完成回調(diào)接口
 */
public interface ExcleFinshCallBack {
    void doAfterAllAnalysed(List<Object> result);
}

4.拆分List集合工具類

/**
 * 拆分List集合
 */
public class SplitListUtil {
    /**
     *
     * @param list 待切割集合
     * @param len  集合按照多大size來切割
     * @param <T>
     * @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 <T>    集合類型
     * @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;
        // 偏移量，每有一個余數(shù)分配，就要往右偏移一位
        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;
    }
}