POI百万级大数据量EXCEL导出

一. 简介

          excel导出,如果数据量在百万级,会出现俩点内存溢出的问题:

          1. 查询数据量过大,导致内存溢出。 该问题可以通过分批查询来解决;

          2. 最后下载的时候大EXCEL转换的输出流内存溢出;该方式可以通过新版的SXSSFWorkbook来解决,可通过其构造函数执指定在内存中缓存的行数,剩余的会自动缓存在硬盘的临时目录上,同时,并不会存在页面卡顿的情况;

          3. 为了能够使用不同的mapper并分批写数据, 采用了外观模式和模板方法模式,大体分三步:

              a. 根据总数量生成excel,确定sheet的数量和写标题;

              b. 写数据,在可变的匿名内部类中实现写入逻辑;
              c. 转换输出流进行下载;

          4. 使用案例在3.3 ServiceImpl中,可自行书写。

          5. 最近太忙,写的太仓促,性能我感觉还有一倍的优化空间,比如循环次数,现在存在无意义空循环(这个耗时比较多)的情况,缓冲流,mybatis的浮标等,待优化........   

          6. 优化空间很大  刚试了下 把空循环去掉  4个字段 90W条数据  40s  180W  75s  300W 122s

          7. 转战阿里开源的EasyExcel了, 那个内存控制在kb级别,绝对不会内存溢出。使用说明请参见博主的另一篇文章:

阿里开源(EasyExcel)---导出EXCEL

          8. 导入的也有,请参见另一篇文章: 阿里开源(EasyExcel)---导入EXCEL

二. 工具代码

2.1 配置

2.1.1 pom.xml

      <!-- poi -->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.17</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.17</version>
      </dependency>

注: 如果是springboot2.0,则不需要poi依赖,如果是1.0,则需要poi依赖,并且poi和poi-ooxml的版本要保持一致。

          别的依赖我就不加了。

2.1.2 application.yml

# pagehelper
pagehelper:
    helperDialect: mysql
    reasonable: false # 如果没有数据  返回空 而非最后一页的数据
    supportMethodsArguments: true
    params: count=countSql
    returnPageInfo: check

注:  reasonable一定要为false, 其他的我就不粘了。

2.2 ExcelConstant

package com.yzx.caasscs.constant;

/**
 * @author qjwyss
 * @date 2018/9/19
 * @description EXCEL常量类
 */
public class ExcelConstant {

    /**
     * 每个sheet存储的记录数 100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小) 20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 200000;


    /**
     * 每个sheet的写入次数 5
     */
    public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;


}

注: xlsx模式的excel每个sheet最多存储104W,此处我就每个sheet存储了 100W数据;每次查询20W数据; 自己根据内存来调合适的大小,写入次数待优化。

2.3 写数据委托类

package com.yzx.caasscs.util;

import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description EXCEL写数据委托类
 */
public interface WriteExcelDataDelegated {

    /**
     * EXCEL写数据委托类  针对不同的情况自行实现
     *
     * @param eachSheet     指定SHEET
     * @param startRowCount 开始行
     * @param endRowCount   结束行
     * @param currentPage   分批查询开始页
     * @param pageSize      分批查询数据量
     * @throws Exception
     */
    public abstract void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception;


}

2.4 DateUtil工具类(非必须)

package com.yzx.caasscs.util;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/20
 * @description 日期工具类
 */
public class DateUtil {

    public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";


    /**
     * 将日期转换为字符串
     *
     * @param date   DATE日期
     * @param format 转换格式
     * @return 字符串日期
     */
    public static String formatDate(Date date, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        return simpleDateFormat.format(date);
    }


}

2.5 POI工具类

package com.yzx.caasscs.util;


import com.yzx.caasscs.constant.ExcelConstant;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

/**
 * @author qjwyss
 * @date 2018/9/18
 * @description POI导出工具类
 */
public class PoiUtil {

    private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);

    /**
     * 初始化EXCEL(sheet个数和标题)
     *
     * @param totalRowCount 总记录数
     * @param titles        标题集合
     * @return XSSFWorkbook对象
     */
    public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {

        // 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        Integer sheetCount = ((totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0) ?
                (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT) : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1));

        // 根据总记录数创建sheet并分配标题
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
            SXSSFRow headRow = sheet.createRow(0);

            for (int j = 0; j < titles.length; j++) {
                SXSSFCell headRowCell = headRow.createCell(j);
                headRowCell.setCellValue(titles[j]);
            }
        }

        return wb;
    }


    /**
     * 下载EXCEL到本地指定的文件夹
     *
     * @param wb         EXCEL对象SXSSFWorkbook
     * @param exportPath 导出路径
     */
    public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {
        FileOutputStream fops = null;
        try {
            fops = new FileOutputStream(exportPath);
            wb.write(fops);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != fops) {
                try {
                    fops.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 下载EXCEL到浏览器
     *
     * @param wb       EXCEL对象XSSFWorkbook
     * @param response
     * @param fileName 文件名称
     * @throws IOException
     */
    public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {

        response.setHeader("Content-disposition", "attachment; filename="
                + new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名

        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            wb.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != wb) {
                try {
                    wb.dispose();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (null != outputStream) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 导出Excel到本地指定路径
     *
     * @param totalRowCount           总记录数
     * @param titles                  标题
     * @param exportPath              导出路径
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);

        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;


                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToLocalPath(wb, exportPath);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


    /**
     * 导出Excel到浏览器
     *
     * @param response
     * @param totalRowCount           总记录数
     * @param fileName                文件名称
     * @param titles                  标题
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {

        logger.info("开始导出:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        // 初始化EXCEL
        SXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);


        // 调用委托类分批写数据
        int sheetCount = wb.getNumberOfSheets();
        for (int i = 0; i < sheetCount; i++) {
            SXSSFSheet eachSheet = wb.getSheetAt(i);

            for (int j = 1; j <= ExcelConstant.PER_SHEET_WRITE_COUNT; j++) {

                int currentPage = i * ExcelConstant.PER_SHEET_WRITE_COUNT + j;
                int pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
                int startRowCount = (j - 1) * ExcelConstant.PER_WRITE_ROW_COUNT + 1;
                int endRowCount = startRowCount + pageSize - 1;

                writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);

            }
        }


        // 下载EXCEL
        PoiUtil.downLoadExcelToWebsite(wb, response, fileName);

        logger.info("导出完成:" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));
    }


}

三. 使用DEMO

3.1 Controller

package com.yzx.caasscs.controller.organiza;

import com.yzx.caasscs.controller.BaseController;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import springfox.documentation.annotations.ApiIgnore;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户控制类
 */
@Api(tags = {"UserController"}, description = "用户Controller")
@RestController
@RequestMapping(value = "/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;


    @ApiOperation("导出用户EXCEL")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "loginUid", value = "登录用户UID", required = true),
            @ApiImplicitParam(paramType = "query", dataType = "Long", name = "uid", value = "用户UID", required = true)
    })
    @GetMapping("/export")
    public ResultVO<Void> exportUser(@ApiIgnore UserVO userVO, HttpServletResponse response) throws Exception {
        return this.userService.export(userVO, response);
    }

}

3.2 Service

package com.yzx.caasscs.service.organiza;

import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserVO;

import javax.servlet.http.HttpServletResponse;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICE
 */
public interface UserService {


    /**
     * 导出用户EXCEL
     *
     * @param userVO
     * @return VOID
     * @throws Exception
     */
    ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception;


}

3.3 ServiceImpl

package com.yzx.caasscs.service.impl.organiza;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.yzx.caasscs.constant.middleware.RedisConstant;
import com.yzx.caasscs.constant.organiza.UserApartmentConstant;
import com.yzx.caasscs.constant.organiza.UserConstant;
import com.yzx.caasscs.constant.organiza.UserRoleConstant;
import com.yzx.caasscs.constant.sys.SysLogConstant;
import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserApartment;
import com.yzx.caasscs.entity.dscaasscs.organiza.UserRole;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserApartmentMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper;
import com.yzx.caasscs.mapper.dscaasscs.organiza.UserRoleMapper;
import com.yzx.caasscs.mapper.dscaasscs.sys.RoleMenuMapper;
import com.yzx.caasscs.service.organiza.UserService;
import com.yzx.caasscs.service.sys.SysLogService;
import com.yzx.caasscs.util.CommonUtil;
import com.yzx.caasscs.util.DateUtil;
import com.yzx.caasscs.util.PoiUtil;
import com.yzx.caasscs.util.WriteExcelDataDelegated;
import com.yzx.caasscs.vo.PageVO;
import com.yzx.caasscs.vo.ResultVO;
import com.yzx.caasscs.vo.organiza.UserApartmentVO;
import com.yzx.caasscs.vo.organiza.UserRoleVO;
import com.yzx.caasscs.vo.organiza.UserVO;
import com.yzx.caasscs.vo.sys.MenuVO;
import com.yzx.caasscs.vo.sys.RoleMenuVO;
import com.yzx.caasscs.vo.sys.SysLogVO;
import net.sf.json.JSONObject;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/30
 * @description 用户SERVICEIMPL
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private HttpServletRequest request;


    @Override
    public ResultVO<Void> export(UserVO userVO, HttpServletResponse response) throws Exception {

        // 总记录数
        Integer totalRowCount = this.userMapper.selectUserVOCount(userVO);

        // 导出EXCEL文件名称
        String filaName = "用户EXCEL";

        // 标题
        String[] titles = {"账号", "密码", "状态", "昵称", "职位", "手机号", "邮箱", "创建人ID", "创建时间", "修改人ID", "修改时间"};

        // 开始导入
        PoiUtil.exportExcelToWebsite(response, totalRowCount, filaName, titles, new WriteExcelDataDelegated() {
            @Override
            public void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount, Integer currentPage, Integer pageSize) throws Exception {

                PageHelper.startPage(currentPage, pageSize);
                List<UserVO> userVOList = userMapper.selectUserVOList(userVO);

                if (!CollectionUtils.isEmpty(userVOList)) {

                    // --------------   这一块变量照着抄就行  强迫症 后期也封装起来     ----------------------
                    for (int i = startRowCount; i <= endRowCount; i++) {
                        SXSSFRow eachDataRow = eachSheet.createRow(i);
                        if ((i - startRowCount) < userVOList.size()) {

                            UserVO eachUserVO = userVOList.get(i - startRowCount);
                            // ---------   这一块变量照着抄就行  强迫症 后期也封装起来     -----------------------

                            eachDataRow.createCell(0).setCellValue(eachUserVO.getUsername() == null ? "" : eachUserVO.getUsername());
                            eachDataRow.createCell(1).setCellValue(eachUserVO.getPassword() == null ? "" : eachUserVO.getPassword());
                            eachDataRow.createCell(2).setCellValue(eachUserVO.getUserState() == null ? "" : (eachUserVO.getUserState() == 1 ? "启用" : "停用"));
                            eachDataRow.createCell(3).setCellValue(eachUserVO.getNickname() == null ? "" : eachUserVO.getNickname());
                            eachDataRow.createCell(4).setCellValue(eachUserVO.getPosition() == null ? "" : eachUserVO.getPosition());
                            eachDataRow.createCell(5).setCellValue(eachUserVO.getMobile() == null ? "" : eachUserVO.getMobile());
                            eachDataRow.createCell(6).setCellValue(eachUserVO.getEmail() == null ? "" : eachUserVO.getEmail());
                            if (null != eachUserVO.getCreateUid()) {
                                eachDataRow.createCell(7).setCellValue(eachUserVO.getCreateUid());
                            }
                            if (null != eachUserVO.getCreateTime()) {
                                eachDataRow.createCell(8).setCellValue(DateUtil.formatDate(eachUserVO.getCreateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                            if (null != eachUserVO.getUpdateUid()) {
                                eachDataRow.createCell(9).setCellValue(eachUserVO.getUpdateUid());
                            }
                            if (null != eachUserVO.getUpdateTime()) {
                                eachDataRow.createCell(10).setCellValue(DateUtil.formatDate(eachUserVO.getUpdateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                            }
                        }
                    }
                }

            }
        });

        return ResultVO.getSuccess("导出用户EXCEL成功");
    }


}

3.4 mapper

package com.yzx.caasscs.mapper.dscaasscs.organiza;

import com.yzx.caasscs.entity.dscaasscs.organiza.User;
import com.yzx.caasscs.vo.organiza.UserVO;

import java.util.List;

/**
 * @author qjwyss
 * @date 2018/8/29
 * @description 用户MAPPER
 */
public interface UserMapper {


    /**
     * 查询用户列表
     *
     * @param userVO
     * @return UserVO集合
     * @throws Exception
     */
    List<UserVO> selectUserVOList(UserVO userVO) throws Exception;


    /**
     * 查询用户数量
     *
     * @param userVO
     * @return 用户数量
     * @throws Exception
     */
    Integer selectUserVOCount(UserVO userVO) throws Exception;

}

3.5 mapper.xml

<?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.yzx.caasscs.mapper.dscaasscs.organiza.UserMapper" >

  <resultMap id="BaseResultMap" type="com.yzx.caasscs.entity.dscaasscs.organiza.User" >
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="state" property="state" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>


  <!-- 查询用户分页列表返回MAP -->
  <resultMap id="SelectUserVOListMap" type="com.yzx.caasscs.vo.organiza.UserVO">
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="user_state" property="userState" jdbcType="INTEGER" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="position" property="position" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="email" property="email" jdbcType="VARCHAR" />
    <result column="create_uid" property="createUid" jdbcType="BIGINT" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_uid" property="updateUid" jdbcType="BIGINT" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="apartment_name" property="apartmentName" jdbcType="VARCHAR" />
    <result column="role_names" property="roleNames" jdbcType="VARCHAR" />
  </resultMap>


  <sql id="Base_Column_List" >
    uid, username, password, state, nickname, position, mobile, email, create_uid, create_time,
    update_uid, update_time
  </sql>


  <!-- 查询用户列表 -->
  <select id="selectUserVOList" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultMap="SelectUserVOListMap">
    SELECT
		U.uid, U.username, U.state AS user_state, U.nickname, U.position, U.mobile, U.email, U.create_uid, U.create_time,
		U.update_uid, U.update_time,
		A.apartment_name,
		(
			SELECT
				GROUP_CONCAT( R.role_name ) AS role_name_list
			FROM
				user_role AS UR
				LEFT JOIN role AS R ON R.rid = UR.rid
			WHERE
				UR.state > 0
				AND UR.uid = U.uid
			GROUP BY UR.uid
		) role_names
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
    ORDER BY U.create_time DESC

  </select>


  <!-- 查询用户列表数量 -->
  <select id="selectUserVOCount" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultType="java.lang.Integer" >
    SELECT
        COUNT(1)
    FROM
        `user` AS U
        LEFT JOIN user_apartment AS UA ON UA.uid = U.uid
        LEFT JOIN apartment AS A ON A.aid = UA.aid
    WHERE
        U.state > 0
        <if test="userState != null">
          AND U.state = #{userState,jdbcType=INTEGER}
        </if>
        <if test="nickname != null and nickname != ''">
          AND U.nickname LIKE CONCAT("%", #{nickname,jdbcType=VARCHAR}, "%")
        </if>
        <if test="apartmentName != null and apartmentName != ''">
          AND A.apartment_name LIKE CONCAT("%", #{apartmentName,jdbcType=VARCHAR}, "%")
        </if>
  </select>
  

</mapper>

4. 测试

每个sheet存100W,每次查询写20W,每条记录10个字段,时间如下:

数据量 时间
100W 3.5分钟
150W 7分钟

 

 

发布了123 篇原创文章 · 获赞 60 · 访问量 16万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览