mybatis mapper接口中使用@select 注解编写动态sql

95 阅读 0 评论 63 点赞

我是靠谱客的博主迷人汉堡，最近开发中收集的这篇文章主要介绍mybatis mapper接口中使用@select 注解编写动态sql，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

package com.sanzhisoft.mybatis.boc.customer.mapper;

import com.sanzhisoft.mybatis.boc.customer.entity.CustomerMarketing;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 客户营销主表 Mapper 接口
 * </p>
 *
 * @author syetem
 * @since 2020-09-18
 */
public interface CustomerMarketingMapper extends BaseMapper<CustomerMarketing> {
    @Select("<script>"+
            "SELECT " +
            "  d.`name` as positionName, " +
            "  a.user_name as userName, " +
            "  a.org_name AS orgName, " +
            "  count(a.id) countNumber, " +
            "  ( " +
            "    SELECT " +
            "      count(1) " +
            "    FROM " +
            "      t_customer_marketing a2 " +
            "    WHERE " +
            "      a.user_name = a2.user_name " +
            "    AND a.org_id = a2.org_id " +
            "    AND ( " +
            "      a2.`status` = 'FALL' " +
            "      OR a2.`status` = 'FINISH' " +
            "    ) " +
            "  ) AS finishNumber, " +
            "  IFNULL( " +
            "    ( " +
            "      SELECT " +
            "        SUM(a3.create_integral) " +
            "      FROM " +
            "        t_customer_marketing a3 " +
            "      WHERE " +
            "        a3.user_name = a.user_name " +
            "    ), " +
            "    0 " +
            "  ) + IFNULL( " +
            "    ( " +
            "      SELECT " +
            "        SUM(a4.share_integral) " +
            "      FROM " +
            "        t_customer_marketing a4 " +
            "      WHERE " +
            "        a4.share_user_name = a.user_name " +
            "    ), " +
            "    0 " +
            "  ) AS integral " +
            "FROM " +
            "  t_customer_marketing a " +
            "LEFT JOIN t_system_dictionary d ON a.position = d.keyword WHERE 1=1 " +
            " <if test='keywords != null'> "+
            " and (a.user_name LIKE CONCAT('%',#{keywords},'%') OR a.org_name LIKE CONCAT('%',#{keywords},'%') )"+
            " </if> "+
            " <if test='position != null'> "+
            " and a.position = #{position} "+
            " </if> "+
            " <if test='startTime != null and endTime != null' > "+
            " and a.create_time BETWEEN #{startTime} AND  #{endTime} "+
            " </if> "+
            " GROUP BY " +
            "  d.`name`, " +
            "  a.user_name, " +
            "  a.org_id, " +
            "  a.org_name " +
            "ORDER BY " +
            "  field( " +
            "    d.`name`, " +
            "    '支行正职', " +
            "    '支行公司副职', " +
            "    '支行个金副职', " +
            "    '部门正职', " +
            "    '部门副职' " +
            "  ), " +
            "  countNumber DESC, " +
            "  finishNumber DESC" +
            " limit #{pageIndex},#{pageSize}" +
             "</script>" )
    List<Map<String,Object>> getCustomerMarketingCountPageList(
            @Param("keywords")String keywords,
            @Param("position")String position,
            @Param("pageIndex") int pageIndex,
            @Param("pageSize") int pageSize,
            @Param("startTime") String startTime,
            @Param("endTime") String endTime);
    
}


    @Select("<script>" +
            " SELECT DISTINCT a.user_name as userName,(select u.mobile from t_user u where u.`name` = a.user_name  LIMIT 1) as mobile," +
            " IFNULL(b.hourCount, 0) as hourCount, IFNULL(b.dayCount, 0) as dayCount, IFNULL(b.weekCount, 0) as weekCount FROM t_assembly_receiver a LEFT JOIN " +
            "( SELECT a.user_name, ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / 60, 1 ) AS hourCount," +
            " ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / (60 * 8), 1 ) AS dayCount," +
            " ROUND( AVG( timestampdiff( MINUTE, a.create_time, a.apply_time )) / (60 * 8 * 5), 1 ) AS weekCount " +
            "FROM t_assembly_receiver a WHERE a.apply_status = 1 AND a.create_time BETWEEN #{startTime} AND #{endTime} GROUP BY a.user_name ) b " +
            "ON a.user_name = b.user_name WHERE a.user_type = 'APPLY_USER' AND  a.org_id IN " +
            "    <foreach item='item' index='index' collection='orgIds' open='(' separator=',' close=')'>" +
            "       #{item}" +
            "    </foreach>" +
            "</script>")
    List<LinkedHashMap<String,Object>> getAssemblyCountList(String startTime, String endTime,List<Long> orgIds);

需要注意的就是

1.如果有动态参数,在sql语句的前后需要加入<script></script>

2.如果有使用like不能直接写 LIKE '%#{keywords}%',这样识别不出来keywords这个参数,会任务少了一个参数,会报错:Parameter index out of range (1 > number of parameters, which is 0),可以修改为 LIKE CONCAT('%',#{keywords},'%')

也可以直接传输Page对象来进行分页

@Override
public IPage<AssemblyOverView> getAssemblyOvertimePageList(Integer pageIndex, Integer pageSize, String keywords, String startTime, String endTime, UserRolePrincipal userRolePrincipal, String status) {
    Page<AssemblyOverView> page = new Page<>(pageIndex, pageSize);
    IPage<AssemblyOverView> returnPage =  assemblyMapper.getAssemblyOvertimePageList(page,keywords,startTime,endTime);
    return returnPage;
}

@Select("<script>"+
        "     SELECT" +
        "        a.id," +
        "        a.`name`," +
        "        a.create_user_name as 'createUserName'," +
        "        a.create_time," +
        "        a.end_time," +
        "        b.user_id as applyUserId,"+
        "        b.user_name as applyUserName,"+
        "        b.user_type," +
        "        a.`status`," +
        "        b.apply_status" +
        "        FROM" +
        "        t_assembly a," +
        "        t_assembly_receiver b WHERE " +
        "   a.id = b.assembly_id " +
        "   AND NOW() > a.end_time " +
        "   AND b.user_type = 'APPLY_USER' " +
        "   AND b.apply_status = 0" +
        "   AND a.`status` = 'UN_ARCHIVE' "+
        " <if test='startTime != null and endTime != null' > "+
        " and a.create_time BETWEEN #{startTime} AND  #{endTime} "+
        " </if> "+
        " <if test='keywords != null'> "+
        " and (a.name LIKE CONCAT('%',#{keywords},'%'))"+
        " </if> "+
        "</script>" )
IPage<AssemblyOverView> getAssemblyOvertimePageList(Page<AssemblyOverView> page,@Param("keywords") String keywords,@Param("startTime") String startTime,@Param("endTime") String endTime);