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百万条数据。即可测试
参考链接:直达