Springboot整合自定义动态SQL插件-jasmine

1、引入依赖

<!--引入PageHelper分页插件 → PageHelper-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.13</version>
</dependency>

2、Service类

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

@Slf4j
@Service
public class SttVideoDataService {
    public SttPageRes<SttVideoRes> pageSttVideoData(SttPageCondition condition) {
        String orderCondition = condition.getOrderCondition();
        String order = orderCondition.replace(";", ",").replace("@", " ");
        condition.setOrderCondition(order);
        //使用插件中的PageHelper对象
        PageHelper.startPage(condition.getPageNum(), condition.getPageSize());
        //SQL查询
        List<SttVideoRes> sttVideoRes = sttVideoMapper.sttAudioData(condition);
        //使用插件中的new PageInfo
        return new SttPageRes<>(new PageInfo<>(sttVideoRes), sttVideoRes);
    }
}

@Getter
@Setter
@NoArgsConstructor
public class SttPageRes<T> {
    private List<T> records;
    private Integer total;
    private Integer size;
    private Integer current;

    public SttPageRes(PageInfo<?> pageInfo, List<T> data) {
        this.records = data;
        this.total = (int) pageInfo.getTotal();
        this.size = pageInfo.getPageSize();
        this.current = pageInfo.getPageNum();
    }
}

3、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.birdsdata.da.centerplatform.repository.mapper.SttVideoMapper">
    <resultMap id="groupVideoResultMap" type="com.birdsdata.da.centerplatform.controller.stt.vo.SttVideoRes">
        <result column="sttDate" property="sttDate"/>
        <result column="c_meta_by_day" property="c_meta_by_day"/>
        <result column="cweek" property="cweek"/>
        <result column="cmonth" property="cmonth"/>
        <result column="cyear" property="cyear"/>
        <result column="site" property="site"/>
        <result column="species" property="species"/>
        <result column="up_user" property="up_user"/>
        <result column="sum_prelabel" property="sum_prelabel"/>
        <result column="sum_prelabel_has" property="sum_prelabel_has"/>
        <result column="sum_prelabel_f" property="sum_prelabel_f"/>
        <result column="sum_label" property="sum_label"/>
        <result column="sum_label_r" property="sum_label_r"/>
        <result column="sum_label_f" property="sum_label_f"/>
        <result column="sum_label_n" property="sum_label_n"/>
        <result column="sum_approve" property="sum_approve"/>
        <result column="sum_approve_y" property="sum_approve_y"/>
        <result column="sum_approve_n" property="sum_approve_n"/>
    </resultMap>


    <!--    SELECT DATE_FORMAT(stt_date, '%Y年%m月%d日') as sttDate-->
    <!--    , c_meta_by_day-->
    <!--    , site, species-->
    <!--    , up_user-->
    <!--    , SUM(c_prelabel) AS sum_prelabel-->
    <!--    , SUM(c_prelabel_has) AS sum_prelabel_has-->
    <!--    , SUM(c_prelabel_f) AS sum_prelabel_f-->
    <!--    , SUM(c_label) AS sum_label-->
    <!--    , SUM(c_label_r) AS sum_label_r-->
    <!--    , SUM(c_label_f) AS sum_label_f-->
    <!--    , SUM(c_label_n) AS sum_label_n-->
    <!--    , SUM(c_approve) AS sum_approve-->
    <!--    , SUM(c_approve_y) AS sum_approve_y-->
    <!--    , SUM(c_approve_n) AS sum_approve_n-->
    <!--    FROM da_stt_video WHERE 1=1-->
    <!--    GROUP BY sttDate,c_meta_by_day, site, species, up_user-->
    <!--    ORDER BY sttDate asc-->
    <!--    LIMIT 0,10-->


    <!-- 查询信息 -->
    <select id="sttAudioData" resultType="com.birdsdata.da.centerplatform.controller.stt.form.SttPageCondition"
            resultMap="groupVideoResultMap">
        SELECT
        <choose>
            <when test="timeType == 1">
                DATE_FORMAT(stt_date, '%Y年%m月%d日') as sttDate
                , SUM(c_meta_by_day) AS c_meta_by_day
            </when>
            <when test="timeType == 2">
                DATE_FORMAT(stt_date, '%Y年第%u周') as sttDate
                , SUM(c_meta_by_day) AS cweek
            </when>
            <when test="timeType == 3">
                DATE_FORMAT(stt_date, '%Y年%m月') as sttDate
                , SUM(c_meta_by_day) AS cmonth
            </when>
            <otherwise>
                DATE_FORMAT(stt_date, '%Y年') as sttDate
                , SUM(c_meta_by_day) AS cyear
            </otherwise>
        </choose>

        <if test="selectSite != null">
            ,site
        </if>
        <if test="selectSpecies != null">
            ,species
        </if>
        <if test="selectUpLoader != null">
            ,up_user
        </if>
        , SUM(c_gen_work) AS sum_gen_work
        , SUM(c_un_gen_work) AS sun_un_gen_work
        , SUM(c_prelabel) AS sum_prelabel
        , SUM(c_prelabel_has) AS sum_prelabel_has
        , SUM(c_prelabel_f) AS sum_prelabel_f
        , SUM(c_label) AS sum_label
        , SUM(c_label_r) AS sum_label_r
        , SUM(c_label_f) AS sum_label_f
        , SUM(c_label_n) AS sum_label_n
        , SUM(c_approve) AS sum_approve
        , SUM(c_approve_y) AS sum_approve_y
        , SUM(c_approve_n) AS sum_approve_n
        FROM da_stt_video
        <where>
            <if test="siteId != null">
                app_id = #{siteId}
            </if>
            <if test="species != null">
                AND species=#{species}
            </if>
            <if test="upLoader != null">
                AND up_loader=#{upLoader}
            </if>
        </where>
        group by sttDate
        <if test="selectSite != null">
            ,site
        </if>
        <if test="selectSpecies != null">
            ,species
        </if>
        <if test="selectUpLoader != null">
            ,up_user
        </if>

        <if test="selectUpLoader != null">
            order by #{orderCondition}
        </if>

        <!-- 使用分页插件的时候会自动生成limit，SQL中不要用limit-->
        <!-- <if test="pageNum != null and pageSize != null ">
             LIMIT #{pageNum},#{pageSize}
         </if>
         <if test="pageNum == null or pageSize == null ">
             LIMIT 0,10
         </if>-->

    </select>

</mapper>