SpringBoot+MyBatis实现百万级数据的导出

MyBatis导出多量数据时，需要设置成stream模式，一条一条导出。

先定义一个工具类DownloadProcessor，它内部封装一个HttpServletResponse对象，用来将对象写入到csv。

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/7 16:51
 **/
public class DownloadProcessor {

    private final HttpServletResponse response;

    public DownloadProcessor(HttpServletResponse response) {
        this.response = response;
        String fileName = System.currentTimeMillis() + ".csv";
        this.response.addHeader("Content-Type", "application/csv");
        this.response.addHeader("Content-Disposition", "attachment; filename="+fileName);
        this.response.setCharacterEncoding("UTF-8");
    }

    public <E> void processData(E record) {
        try {
            response.getWriter().write(record.toString()); //如果是要写入csv,需要重写toString,属性通过","分割
            response.getWriter().write("\n");
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

然后通过实现org.apache.ibatis.session.ResultHandler里面的handleResult方法，自定义我们的ResultHandler，它用于获取java对象，然后传递给上面的DownloadProcessor处理类进行写文件操作：

import com.fang.demo.entity.DO.TestExport;
import com.fang.demo.utils.DownloadProcessor;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/7 16:53
 **/
public class CustomResultHandler implements ResultHandler {

    private final DownloadProcessor downloadProcessor;

    public CustomResultHandler(DownloadProcessor downloadProcessor) {
        super();
        this.downloadProcessor = downloadProcessor;
    }

    @Override
    public void handleResult(ResultContext resultContext) {
        TestExport authors = (TestExport) resultContext.getResultObject();
        downloadProcessor.processData(authors);
    }
}

测试实体类

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/7 14:17
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TestExport {

    @TableId(value = "table_id")
    private String tableId;
    private String name;
    private String age;
    private String id;

}

Mapper接口

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fang.demo.entity.DO.TestExport;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/7 14:41
 **/
@Mapper
@Repository
public interface TestExportMapper extends BaseMapper<TestExport> {	       
    //example对象没有使用，放这里是告诉你们如果需要按条件查找需要怎么做
    List<TestExport> streamByExample(TestExport example); //以stream形式从mysql获取数据，
}

Mapper xml文件核心片段
在stream获取数据的方式中多了一条属性： fetchSize=“-2147483648”
查了一下官方文档，应该是设置成数据无限吧

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fang.demo.mapper.TestExportMapper">
    <select id="streamByExample" fetchSize="-2147483648" resultType="com.fang.demo.entity.DO.TestExport">
        SELECT table_id,name,age,id FROM test_export ORDER BY table_id DESC
    </select>
</mapper>

接下里就是常写的controlle和service了

import com.fang.demo.handle.CustomResultHandler;
import com.fang.demo.mapper.TestExportMapper;
import com.fang.demo.utils.DownloadProcessor;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/7 17:05
 **/
@Service
public class DownloadServiceImpl {

    @Autowired
    private  SqlSessionTemplate sqlSessionTemplate;
    @Autowired
    private  TestExportMapper testExportMapper;

    /**
     * stream读数据写文件方式
     * @param httpServletResponse
     * @throws IOException
     */
    public void streamDownload(HttpServletResponse httpServletResponse) throws IOException {
        CustomResultHandler customResultHandler = new CustomResultHandler(new DownloadProcessor(httpServletResponse));
        sqlSessionTemplate.select(
                "com.fang.demo.mapper.TestExportMapper.streamByExample", null, customResultHandler);//具体到哪个mapper的哪个方法，参数和handler处理方法
        httpServletResponse.getWriter().flush();
        httpServletResponse.getWriter().close();
    }

}

import com.fang.demo.service.DownloadServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @Description
 * @Author Bernie
 * @Date 2023/4/6 15:49
 **/
@RestController
public class TestController {
    @Autowired
    private DownloadServiceImpl downloadService;

   @GetMapping("streamDownload")
    public void streamDownload(HttpServletResponse response) throws IOException {
        downloadService.streamDownload(response);
    }
}

程序就此完成，测试

保证数据库有数据，如果没有，先写入200百万条数据

先执行以下函数：

DELIMITER $$
DROP FUNCTION IF EXISTS `mock_data`$$
CREATE FUNCTION mock_data ()
BEGIN
 DECLARE num INT DEFAULT 2000000;
 DECLARE i INT DEFAULT 0;
 WHILE i<num DO
  INSERT INTO `test_export`(`name`,`age`,`id`)VALUES(CONCAT('zhangsan',i),RAND() * (99 - 10) + 10,i);
  SET i=i+1;
 END WHILE;
 RETURN i;
END
DELIMITER ;

再执行

select mock_data();

跑个1分多钟，写入2百万条数据。即可测试

参考链接：直达