MyBatis流式查詢的使用詳解

2022-08-17 14:02:11

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 ;

2.MyBatis流式查詢

1.建立srcmainjavacomllpllpmybatisentityEmp.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.建立srcmainjavacomllpllpmybatisvoEmpVo.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.建立srcmainjavacomllpllpmybatiscontrollerEmpController.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();
            }
        });
    }
}

4.建立srcmainjavacomllpllpmybatisserviceEmpService.java

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

5.建立srcmainjavacomllpllpmybatisserviceimplEmpServiceImpl.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;
    }
}

6.建立srcmainjavacomllpllpmybatisdaoEmpDao.java（重點）

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

這裡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>

至此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 <T>      查詢結果型別
     * @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資料讀取監聽器

/**
 * 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);
    }
}

3.Excel讀取資料完成回撥介面

/**
 * excel讀取資料完成回撥介面
 */
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;
        // 偏移量，每有一個餘數分配，就要往右偏移一位
        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;
    }
}