投稿
  1. 极客之音首页
  2. 后端开发

POI 将按日期分表的数据汇总到一个excel中 大数据量

小半 • • 后端开发 • 阅读 152

导读：本篇文章讲解 POI 将按日期分表的数据汇总到一个excel中 大数据量，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

一. 简介

           现在有按时间分的用户表，要在每月一号将这些表的数据汇总到一个excel中。每张表的数据量很大。

           昨天通宵搞得，只为纪念，方便以后遇见同样的需求做参考。

           之前是想着每天导出一个excel, 然后在月初一起汇总。但是合并excel的时候有问题，这样跨表查也挺快的。

二. 代码

2.1 Ecxel常量类

package com.yzx.caasscs.constant;

/**
 * @author duxuebo
 * @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;

}

2.2 EXCEL写数据委托类

package com.yzx.caasscs.util;

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

/**
 * @author duxuebo
 * @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.3 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.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;

/**
 * @author duxuebo
 * @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 导出路径
     * @param fileName   文件名称
     * @return 文件存储路径
     */
    public static String downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath, String fileName) {
        FileOutputStream fops = null;
        try {

            File dir = new File(exportPath);
            if (!dir.exists()) {
                dir.mkdirs();
            }

            File excelFile = new File(exportPath + "\\" + fileName + ".xlsx");
            if (excelFile.exists()) {
                excelFile.delete();
                excelFile.createNewFile();
            }

            fops = new FileOutputStream(excelFile);
            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();
                }
            }
        }

        return exportPath + "\\" + fileName + ".xlsx";
    }


    /**
     * 下载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 fileName                文件名称
     * @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现
     * @throws Exception
     */
    public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, String fileName, 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, fileName);

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


    /**
     * 创建一个excel
     *
     * @param titles 标题列表
     * @return SXSSFWorkbook
     */
    public static SXSSFWorkbook createExcel(String[] titles) {

        SXSSFWorkbook wb = new SXSSFWorkbook(100);

        SXSSFSheet sheet = wb.createSheet("sheet0");
        SXSSFRow headRow = sheet.createRow(0);

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

        return wb;
    }


    /**
     * 新增一个sheet
     *
     * @param wb     SXSSFWorkbook
     * @param titles 标题数组
     * @return SXSSFWorkbook
     */
    public static SXSSFWorkbook addSheet(SXSSFWorkbook wb, String[] titles) {

        int sheetCount = wb.getNumberOfSheets();
        SXSSFSheet currentSheet = wb.createSheet("sheet" + sheetCount);
        SXSSFRow headRow = currentSheet.createRow(0);

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

        return wb;
    }


    public static void main(String[] args) {

        String relativePath = "E:\\temp\\excel";
        String fileName = "test";
        String[] titles = {"姓名", "年龄", "性别"};

        SXSSFWorkbook wb = createExcel(titles);
        wb = addSheet(wb, titles);

        downLoadExcelToLocalPath(wb, relativePath, fileName);
    }


}

2.4 用户VO类

package com.yzx.caasscs.vo.organiza;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonInclude;
import com.yzx.caasscs.vo.sys.MenuVO;
import io.swagger.annotations.ApiModelProperty;

import java.util.Date;
import java.util.List;

/**
 * @author duxuebo
 * @date 2018/9/3
 * @description 用户VO对象
 */
@JsonInclude(JsonInclude.Include.NON_NULL)
public class UserVO {

    @ApiModelProperty("用户ID")
    private Long uid;

    @ApiModelProperty("账号")
    private String username;

    @ApiModelProperty("昵称")
    private String nickname;

    @ApiModelProperty("职位")
    private String position;

    @ApiModelProperty("手机号")
    private String mobile;

    @ApiModelProperty("邮箱")
    private String email;

   
    @ApiModelProperty("创建时间 格式：yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    @ApiModelProperty("日期字符串 yyyyMMdd格式")
    private String Date;

   
}

2.5 用户控制类

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 duxuebo
 * @date 2018/8/30
 * @description 用户控制类
 */
@Api(tags = {"UserController"}, description = "用户Controller")
@RestController
@RequestMapping(value = "/user")
public class UserController extends BaseController {

    @Autowired
    private UserService userService;

    // http://localhost:8080/user/send/email
    @GetMapping("/send/email")
    public ResultVO<Void> sendUserEmail(@ApiIgnore UserVO userVO) throws Exception {
        return this.userService.sendUserEmail(userVO);
    }

}

2.6 用户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 duxuebo
 * @date 2018/8/30
 * @description 用户SERVICE
 */
public interface UserService {

    /**
     * 发送用户Email
     * @param userVO
     * @return
     * @throws Exception
     */
    ResultVO<Void> sendUserEmail(UserVO userVO) throws Exception;
}

2.7 用户serviceImpl

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

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.yzx.caasscs.constant.ExcelConstant;
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.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.*;

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

    @Autowired
    private UserMapper userMapper;


    @Override
    public ResultVO<Void> sendUserEmail(UserVO userVO) throws Exception {

        System.out.println("开始计时：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));


        // 有5个运行的任务
        for (int i = 0; i < 1; i++) {

            // 1. 创建excel表 并设置标题  excel的名称为任务的主键
            String relativePath = "E:\\temp\\excel\\2018\\09";
            String fileName = i + "";
            String[] titles = {"用户ID", "用户名", "昵称", "手机号", "创建时间"};
            SXSSFWorkbook wb = PoiUtil.createExcel(titles);


            // 2. 获取当前月份的所有日期格式化字符串集合
            List<String> dateList = new ArrayList<>();
            dateList.add("20180901");
            dateList.add("20180902");
            dateList.add("20180903");
            dateList.add("20180904");
            dateList.add("20180905");


            // 3. 遍历所有日期的符合条件的话单表获取到总数据量来确定sheet数量 并标记每天的数据量
            Integer totalRowCount = 0;
            Map<String, Integer> dayAndCountMap = new HashMap<>();
            for (String eachDate : dateList) {
                userVO.setDate(eachDate);
                Integer eachDayCount = this.userMapper.selectUserDateCount(userVO);
                dayAndCountMap.put(eachDate, eachDayCount);
                totalRowCount = totalRowCount + eachDayCount;
            }

            Integer sheetCount = totalRowCount % ExcelConstant.PER_SHEET_ROW_COUNT == 0 ?
                    totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT : (totalRowCount / ExcelConstant.PER_SHEET_ROW_COUNT + 1);
            for (int j = 1; j < sheetCount; j++) {
                PoiUtil.addSheet(wb, titles);
            }


            // 4. 将当前月份的日期表全部遍历一遍，并写到excel中
            Integer currentSheetIndex = 0;
            Integer startWriteRowIndex = 1;

            for (String eachDate : dateList) {

                userVO.setDate(eachDate);
                Integer currentDayCount = dayAndCountMap.get(eachDate);

                // 如果当前写的开始行索引 + 当前天的记录数量 大于一个sheet的存储上限，则换到另一个sheet开始写
                // 注意： 此处要大于+1  例如：50万零1 + 50W > 100W零1的时候才会创建新的sheet
                if(startWriteRowIndex + currentDayCount > ExcelConstant.PER_SHEET_ROW_COUNT) {
                    currentSheetIndex = currentSheetIndex + 1;
                    startWriteRowIndex = 1;
                }

                Integer writeCount = currentDayCount % ExcelConstant.PER_WRITE_ROW_COUNT == 0 ?
                        currentDayCount / ExcelConstant.PER_WRITE_ROW_COUNT : (currentDayCount / ExcelConstant.PER_WRITE_ROW_COUNT + 1);

                for (int j = 1; j <= writeCount; j++) {

                    if (j != writeCount) {

                        PageHelper.startPage(j, ExcelConstant.PER_WRITE_ROW_COUNT);
                        List<UserVO> userVOList = this.userMapper.selectUserDateList(userVO);

                        // 写数据
                        writeExcelData(wb, currentSheetIndex, startWriteRowIndex, userVOList);

                        startWriteRowIndex = startWriteRowIndex + ExcelConstant.PER_WRITE_ROW_COUNT;

                    } else {
                        PageHelper.startPage(j, currentDayCount % ExcelConstant.PER_WRITE_ROW_COUNT);
                        List<UserVO> userVOList = this.userMapper.selectUserDateList(userVO);

                        // 写数据
                        writeExcelData(wb, currentSheetIndex, startWriteRowIndex, userVOList);

                        startWriteRowIndex = startWriteRowIndex + currentDayCount % ExcelConstant.PER_WRITE_ROW_COUNT;
                    }
                }
            }


            // 4. 下载excel到webroot目录下
            String excelPath = PoiUtil.downLoadExcelToLocalPath(wb, relativePath, fileName);
            System.out.println(excelPath);

            // 5. 发送邮件

        }

        System.out.println("结束计时：" + DateUtil.formatDate(new Date(), DateUtil.YYYY_MM_DD_HH_MM_SS));

        return ResultVO.getSuccess("发送用户Email成功");
    }


    private void writeExcelData(SXSSFWorkbook wb, Integer currentSheetIndex, Integer startWriteRowIndex, List<UserVO> userVOList) {
        if (!CollectionUtils.isEmpty(userVOList)) {
            for (UserVO eachUserVO : userVOList) {
                SXSSFSheet currentSheet = wb.getSheetAt(currentSheetIndex);
                SXSSFRow row = currentSheet.createRow(startWriteRowIndex);
                if (null != eachUserVO.getUid()) {
                    row.createCell(0).setCellValue(eachUserVO.getUid());
                }
                row.createCell(1).setCellValue(eachUserVO.getUsername() == null ? "" : eachUserVO.getUsername());
                row.createCell(2).setCellValue(eachUserVO.getNickname() == null ? "" : eachUserVO.getNickname());
                row.createCell(3).setCellValue(eachUserVO.getMobile() == null ? "" : eachUserVO.getMobile());
                if (null != eachUserVO.getCreateTime()) {
                    row.createCell(0).setCellValue(DateUtil.formatDate(eachUserVO.getCreateTime(), DateUtil.YYYY_MM_DD_HH_MM_SS));
                }
                startWriteRowIndex = startWriteRowIndex + 1;
            }
        }
    }
}

2.8 用户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 duxuebo
 * @date 2018/8/29
 * @description 用户MAPPER
 */
public interface UserMapper {

     /**
     * 查询指定日期的用户表总数
     *
     * @param userVO
     * @return 总数
     * @throws Exception
     */
    Integer selectUserDateCount(UserVO userVO) throws Exception;


    /**
     * 查询指定日期的用户列表
     *
     * @param userVO
     * @return UserVO列表
     * @throws Exception
     */
    List<UserVO> selectUserDateList(UserVO userVO) throws Exception;
}

2.9 用户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>


  <!-- 查询指定日期的用户列表 -->
  <resultMap id="SelectUserDateListMap" type="com.yzx.caasscs.vo.organiza.UserVO" >
    <id column="uid" property="uid" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="nickname" property="nickname" jdbcType="VARCHAR" />
    <result column="mobile" property="mobile" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
  </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="selectUserDateCount" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultType="java.lang.Integer">
    SELECT
        count(1)
    FROM
        user_${date}
  </select>


  <!-- 查询指定日期的用户列表 -->
  <select id="selectUserDateList" parameterType="com.yzx.caasscs.vo.organiza.UserVO" resultMap="SelectUserDateListMap">
    SELECT
        uid, username, nickname, mobile, create_time
    FROM
        user_${date}
  </select>

</mapper>

 

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/17775.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！