mybatis-plus在Mapper类中使用@select标签进行多表联合动态条件查询

卫庄大人℡

已于 2023-03-02 17:18:57 修改

阅读量4k

点赞数 1

分类专栏： mybatis 文章标签： mybatis java mysql

于 2023-03-02 15:42:33 首次发布

本文链接：https://blog.csdn.net/qq_45621643/article/details/129301957

版权

mybatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.单表动态条件查询
1)单字段作为参数
直接用@param设置的值作为注入就好了

    @Select("select * from ppms_person_message where create_time = #{testValue}")
    void test(@Param("testValue") String testValue);

2）对象作为参数
注意：在@param内的值不为“ew”的时候会提示找不到条件参数

@Select("select * from ppms_person_message ${ew.customSqlSegment}")
    Page<PersonMessage> selectTestPage(@Param("page") Page<PersonMessage> page, @Param("ew") QueryWrapper<PersonMessage> qw);

2.多表联合动态条件查询
1>使用wrapper
注意：1）${ew.customSqlSegment}仅为where下的条件
2）join on后面的条件需要使用另外的param

    @Select("select m.* from ppms_person_message m " +
            "left join ppms_notice n on n.id = m.notice_id and n.create_time = #{testValue}" +
            "${ew.customSqlSegment}")
    void test(@Param("testValue") String testValue, @Param("ew") QueryWrapper<PersonMessage> qw);

2>使用标签
注意：1）动态条件外双引号改为单引号，内单引号改为转义双引号（"）
2）使用遍历条件时需要在最外成加上<script></script>标签

@Select("<script>" +
            "       ( SELECT" +
            "            '3' AS `type`," +
            "            COUNT(*) AS `num`," +
            "            SUBSTR( max( publish_time ), 6, 5 ) AS `time`" +
            "          FROM ppms_notice" +
            "          WHERE del_flag = '0' " +
            "        )" +
            "        UNION" +
            "        ( SELECT" +
            "            '1' AS `type`," +
            "            COUNT(*) AS `messageNum`," +
            "            SUBSTR( max( t.create_time ), 6, 5 ) AS `time`" +
            "          FROM" +
            "            `ppms_person_task` t" +
            "          WHERE" +
            "            t.`status` = '1'" +
            "            AND (t.receiver_id = #{dto.receiverId}" +
            "                <if test='dto.orgId != null and dto.orgId != \"\"'>" +
            "                    or  t.receiver_id = #{dto.orgId}" +
            "                </if>" +
            "                <if test='null != dto.roleIds and dto.roleIds.size > 0'>" +
            "                    or t.receiver_id in" +
            "                    <foreach collection='dto.roleIds' item='item' close=')' open='(' separator=','>" +
            "                        #{item}" +
            "                    </foreach>" +
            "                </if>" +
            "                )" +
            "            AND t.del_flag = '0'" +
            "        )" +
            "        UNION" +
            "        ( SELECT" +
            "            '2' AS `type`," +
            "            COUNT(*) AS `num`," +
            "            SUBSTR( max( m.create_time ), 6, 5 ) AS `time`" +
            "          FROM" +
            "            ppms_person_message m" +
            "          WHERE" +
            "            (m.receiver_id = #{dto.receiverId}" +
            "            <if test='dto.orgId != null and dto.orgId != \"\"'>" +
            "                or  m.receiver_id = #{dto.orgId}" +
            "            </if>" +
            "            <if test='null != dto.roleIds and dto.roleIds.size > 0'>" +
            "                or m.receiver_id in" +
            "                <foreach collection='dto.roleIds' item='item' close=')' open='(' separator=','>" +
            "                    #{item}" +
            "                </foreach>" +
            "            </if>" +
            "            )" +
            "            AND m.`status` = '0'" +
            "            AND m.del_flag = '0'" +
            "        )" +
            "</script>")
    List<MessageRespVo> selectUnReadNumByUserId2(@Param("dto") TaskPageDto dto);