mybatis 一对多查询_mybatis（一对多数据结果集）

mybatis（一对多,数据结果集）合并单元格导出Excel。

<!--easyPoi依赖-->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.0.1</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.0.1</version>
		</dependency>

简单记录哈

1.对象实体属性添加注解 @ Excel(name ="备注",needMerge =true,width =20)

package com.zjt.shop.modules.order.entity.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * 作者：zhuLin
 * 日期：2020-08-22 11:06
 * 备注：订单管理-对象实体
 */
@Data
@ApiModel(value = "订单管理-实体")
public class OrderMapVo implements Serializable {

    private static final long serialVersionUID = 1L;

    @Excel(name = "备注",needMerge = true,width = 20)   //需要导出的列
    @ApiModelProperty("备注")
    private String buyRemark;

    @Excel(name = "下单时间",needMerge = true,width = 20)
    @ApiModelProperty("下单时间")
    private String createTime;

    @Excel(name = "收货地址",needMerge = true,width = 20)
    @ApiModelProperty("收货地址")
    private String address;

    @ExcelCollection(name = "")   //一对的多子集
    List<CodeVo> codeList;          

}

2.对象实体属性添加注解 @ Excel(name ="备注",needMerge =true,width =20)

package com.zjt.shop.modules.order.entity.vo;

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * 作者：zhuLin
 * 日期：2020-08-22 30:03
 * 备注：订单电子码参数-实体
 */
@Data
@ApiModel(value = "订单电子码参数-实体类")
public class CodeVo {

    @ApiModelProperty(value = "订单ID")
    private String orderId;

    @ApiModelProperty(value = "电子码ID")
    private String codeId;

    @Excel(name = "电子码", width = 20)
    @ApiModelProperty(value = "电子码")
    private String codeNo;

    @Excel(name = "电子码金额", width = 15)
    @ApiModelProperty(value = "电子码金额")
    private double codeAmt;

    @Excel(name = "电子码状态", width = 30)
    @ApiModelProperty(value = "电子码状态")
    private String stateName;

    @ApiModelProperty(value = "电子码状态")
    private Integer codeState;
}

3.mybatis xml 映射数据集

 <!--订单号对电子码（一对多）-->
    <resultMap id="orderManage" type="com.zjt.shop.modules.order.entity.vo.OrderMapVo">
        <id column="orderId" property="orderId"/>
        <result column="msgName" property="msgName"/>
        <result column="buyName" property="buyName"/>
        <result column="buyPhone" property="buyPhone"/>
        <result column="orderAmt" property="orderAmt"/>
        <result column="orderNo" property="orderNo"/>
        <result column="address" property="address"/>
        <result column="buyRemark" property="buyRemark"/>
        <result column="siteId" property="siteId"/>
        <result column="orderState" property="orderState"/>
        <result column="reqOrderState" property="reqOrderState"/>
        <result column="createTime" property="createTime"/>
        <collection property="codeList" ofType="com.zjt.shop.modules.order.entity.vo.CodeVo" column="{orderId=orderId,reqOrderState=reqOrderState}" select="selCodeList"/>
    </resultMap>

    <!--OA订单管理列表分页-->
    <select id = "selOrderListPage" parameterType = "com.zjt.shop.modules.order.entity.vo.OrderInfoVo" resultMap="orderManage">
        select o.id orderId,p.msg_name as msgName,o.buy_name as buyName,insert(o.buy_phone,4,4,'****') buyPhone,o.order_amt as orderAmt,o.order_no as orderNo,
        o.goods_address address,o.buy_remark as buyRemark,o.site_id siteId,DATE_FORMAT(o.create_time,'%Y-%m-%d %T') createTime,
        o.order_state orderState,#{reqOrderState} as reqOrderState
        from tb_order_info o
        left join tb_product_info p on o.product_id = p.id
        <if test="reqOrderState != null">
            left join (select t1.id as orderId,count(1) as ct from tb_order_info t1,tb_order_code_info t2 where t1.id = t2.order_id and
            t2.order_state = #{reqOrderState} group by t1.id) tb on o.id = tb.orderId
        </if>
        <where>
            o.order_state not in (0,4)
            <if test="reqOrderState != null">
                and tb.ct > 0
            </if>
            <if test = "siteId != null and siteId != ''">
                and o.site_id = #{siteId}
            </if>
            <if test = "startTime != null and startTime != ''">
                <![CDATA[ and DATE_FORMAT(o.create_time, '%Y-%m-%d') >= DATE_FORMAT(#{startTime},'%Y-%m-%d') ]]>
            </if>
            <if test = "endTime != null and endTime != ''">
                <![CDATA[ and DATE_FORMAT(o.create_time, '%Y-%m-%d') <= DATE_FORMAT(#{endTime}, '%Y-%m-%d') ]]>
            </if>
            <if test = "searchKey != null and searchKey != ''">
                and (
                p.msg_name like concat ('%',#{searchKey},'%') or
                o.buy_name = #{searchKey} or
                o.buy_phone = #{searchKey} or
                o.order_no = #{searchKey})
            </if>
        </where>
        order by o.create_time desc
    </select>

4.controller 请求

 @ApiOperation("订单管理导出")
    @PostMapping("/orderXls")
    @RequiresPermissions("orderManager:exportOrderGlXls")
    public void orderXls(HttpServletResponse response,@RequestBody OrderInfoVo orderInfoVo) throws Exception {
        log.info("订单管理导出参数：{}",orderInfoVo);
        List<OrderMapVo> list = orderInfoService.orderXls(orderInfoVo);     //mybatis 一对多数据格式合并单元格导出Excel文件
        cn.afterturn.easypoi.excel.entity.ExportParams params = new cn.afterturn.easypoi.excel.entity.ExportParams();
        params.setTitle("订单管理");      //设置表头
        params.setSheetName("订单管理");    //设置sheet名
        Workbook workbook = ExcelExportUtil.exportExcel(params, OrderMapVo.class, list);
        this.setExportExcelFormat(response, workbook, "订单管理导出");
    }


	public static void setExportExcelFormat(HttpServletResponse response, Workbook workbook, String fileName) throws Exception {
		response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
		response.setHeader("Pargam", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		ServletOutputStream outStream = null;
		try {
			outStream = response.getOutputStream();
			workbook.write(outStream);
		} finally {
			outStream.flush();
			outStream.close();
		}
	}