mysql查询（普通查询、游标查询、流式查询、分页查询、mybatis游标查询）导出百万数据

原创

已于 2025-09-07 13:59:31 修改 · 185 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mybatis #数据库

于 2025-09-07 13:58:21 首次发布


package com.linging.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.linging.easyexcel.mapper.UserMapper;
import com.linging.easyexcel.pojo.User;
import com.linging.easyexcel.service.UserService;
import com.linging.easyexcel.utils.JDBCUtils;
import com.linging.easyexcel.vo.UserExcelVO;
import org.apache.ibatis.cursor.Cursor;
import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author Linging
 * @since 2025-09-06 09:53:46
 */
@RestController
@RequestMapping("/user")
public class UserController {
   
   


    @Resource
    private UserService userService;

    @Resource
    private UserMapper userMapper;

    /**
     * 全量数据获取到内存导出
     * @param response
     */
    @GetMapping("/exportCommon")
    public void exportCommon(HttpServletResponse response) {
   
   
        long start = System.currentTimeMillis();
        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=data.xlsx");

        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        ExcelWriter excelWriter = null;
        WriteSheet writeSheet = null;
        try{
   
   
            // 创建ExcelWriter对象
            excelWriter = EasyExcel.write(response.getOutputStream(), UserExcelVO.class).autoCloseStream(true).build();
            // 写入当前页数据
            writeSheet = EasyExcel.writerSheet("Sheet1")
                    .build();
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery("select * from user");

            List<UserExcelVO> dataList = new ArrayList<>();
            while (rs.next()){
   
   
                long id = rs.getLong("id");
                String username = rs.getString("username");
                int age = rs.getInt("age");
                String password = rs.getString("password");
                String phone = rs.getString("phone");
                String address = rs.getString("address");
                String status = rs.getString("status");
                Date createTime = new