MyBatis流式查詢的項目實踐

更新時間：2022年08月16日 15:22:20 作者：llp1110

本文主要介紹了MyBatis流式查詢的項目實踐，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

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

preview: JDBC三種讀取方式：
1.一次全部（默認）：一次獲取全部。
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ù)量過大，采用默認的讀取方式（一次性獲取全部）會導(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)
#默認給 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; #默認不提交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進行導(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)聽器中對接口進行賦值
        //在監(jiān)聽器中doAfterAllAnalysed，在所有數(shù)據(jù)解析完之后回調(diào)用這個方法，我們在方法中對導(dǎo)出的數(shù)據(jù)集進行賦值
        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)出工具類

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

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

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

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

5.拆分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;
    }
}