Mybatis统计报表动态SQL拼接-jasmine

1、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.xxx.da.centerplatform.repository.mapper.DaStbVideoMapper">


    <resultMap id="groupVideoResultMap" type="com.xxx.da.centerplatform.controller.stt.vo.StbVideoRes">
        <result column="sttDate" property="sttDate"/>
        <result column="stbv" property="stbv"/>
        <result column="app_id" property="appId"/>
        <result column="app_name" property="appName"/>
        <result column="species_names" property="speciesNames"/>
        <result column="species_ids" property="speciesIds"/>
    </resultMap>


    <!-- 查询信息 -->
    <select id="stbVideo" resultType="com.xxx.da.centerplatform.controller.stt.form.StbVideoForm"
            resultMap="groupVideoResultMap">
        SELECT
        <choose>
            <when test="stbType == 1">
                DATE_FORMAT(create_time, '%Y年%m月%d日') as sttDate,
            </when>
            <when test="stbType == 2">
                DATE_FORMAT(create_time, '%Y年第%u周') as sttDate,
            </when>
            <when test="stbType == 3">
                DATE_FORMAT(create_time, '%Y年%m月') as sttDate,
            </when>
            <otherwise>
                DATE_FORMAT(create_time, '%Y年') as sttDate,
            </otherwise>
        </choose>
        <choose>
            <when test="timeType == 2">
                -- SUM(file_size) as stbv 当file_size=0的时候给默认值9
                SUM(CASE WHEN file_size = 0 THEN 9 ELSE file_size END) as stbv
            </when>
            <when test="timeType == 3">
                -- SUM(play_time) as stbv
                SUM(CASE WHEN play_time = 0 THEN 10 ELSE play_time END) as stbv
            </when>
            <otherwise>
                count(1) as stbv
            </otherwise>
        </choose>

        <if test="selectSite != null">
            ,app_id,app_name
        </if>
        <if test="selectSpecies != null">
            ,species_ids,species_names
        </if>

        FROM da_stb_video

        <where>
            <if test="siteId != null">
                app_id = #{siteId}
            </if>
            <if test="speciesId > 0">
                AND JSON_CONTAINS(species_ids->'$', '${speciesId}')
            </if>
            <if test="start != null and end != null ">
                AND record_time Between #{start} and #{end}
            </if>
        </where>

        group by sttDate
        <if test="selectSite != null">
            ,app_id,app_name
        </if>
        <if test="selectSpecies != null">
            ,species_ids,species_names
        </if>

    </select>

</mapper>

2、对象入参与返回

//入参
public class StbVideoForm {
    /**
     * 统计类型：1统计视频条数2统计视频大小3统计视频时长
     */
    private Integer stbType;
    /**
     * 时间统计类型：1每天2每周3每月4每年
     */
    private Integer timeType;

    /**
     * 选择保护区
     */
    private Integer selectSite;

    /**
     * 选择物种
     */
    private Integer selectSpecies;

    /**
     * 保护区ID
     */
    private Integer siteId;

    /**
     * 物种
     */
    private Integer speciesId;

    /**
     * 当前页
     */
    private Integer pageNum;

    /**
     * 每页条数
     */
    private Integer pageSize;

    /**
     * 开始时间
     */
    private LocalDate start;

    /**
     * 结束时间
     */
    private LocalDate end;

    /**
     * 排序条件
     */
    private String orderCondition;
}

//返回值
public class StbVideoRes {
    private String sttDate;
    private String stbv;
    private String appId;
    private String appName;
    private String speciesNames;
    private String speciesIds;
}