mybatis 脚本处理语句（条件查询，批量增删改查）

最新推荐文章于 2024-07-24 17:29:42 发布

c568254965

最新推荐文章于 2024-07-24 17:29:42 发布

阅读量3.4k

点赞数

分类专栏： mybatis 文章标签： mybatis springboot

本文链接：https://blog.csdn.net/c568254965/article/details/73527347

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

模糊查询：

@Select({
        "SELECT * FROM account where account like CONCAT('%',#{query},'%') or email like CONCAT('%',#{query},'%')"
})
Account findAccountByAccountOrMail(@Param("query") String query);

批量添加：

@Insert({
        "<script>" +
                "INSERT INTO company_label(company_id,label_id) values " +
                "  <foreach collection=\"item\" item=\"item\" index=\"index\" separator=\",\" > " +
                "        (#{companyId},#{item}) " +
                "    </foreach>" +
                "</script>"
})
void insertLabelForCompany(@Param("companyId") Long companyId,@Param("item") List<Long> item);

批量删除：

@Delete({
        "<script>delete from company_label where company_id = #{companyId} and label_id in " +
                "<foreach collection = \"item\" item = \"item\" open=\"(\" separator=\",\" close=\")\">" +
                "#{item}" +
                "</foreach>" +
                "</script>"
})
void removeLabelForCompany(@Param("companyId") Long companyId,@Param("item") List<Long> item);

批量修改：


@Update(value = "<script>" + "update banner b set b.display = #{status} where b.id in "+
        "<foreach item = 'item' index = 'index' collection = 'ids' open = '(' separator = ',' close = ')'>#{item}</foreach>" +
        "" +
        "</script>")
int updateStatus(@Param("status") Long status, @Param("ids") Long[] ids);

批量查询：


@Select({
        "<script>" +
                "select * from product where id in" +
                "<foreach item = 'item' index = 'index' collection = 'idList' open = '(' separator = ',' close = ')'>#{item}</foreach>" +
                "</script>"
})
List<Product> findByIdList(@Param("idList")List<Long> idList);

条件查询，if里面不仅可以判空，还可以判断是否满足某个条件

 @Select({
            "<script>SELECT * FROM company where 1=1 and parent_id = #{companyId} " +
                    //平级
                    "<if test = \"isScanSameLevelValue == 1\">and  type = #{type}</if>" +
                     "<if test = \"isScanSameLevelValue == 0\">and  type != #{type}</if>" +

                    "</script> "
    })
    List<Company> findCompanyConditional(@Param("isScanSameLevelValue") String isScanSameLevelValue, @Param("isScanParentLevelValue") String isScanParentLevelValue, @Param("companyId") Long companyId, @Param("type") Integer type);

条件查询：

 */
@Lang(XMLLanguageDriver.class)
@Select({"<script>select DISTINCT p.* FROM `us_product`.`hot_category_surgery` hcs "+
        "LEFT JOIN `us_product`.`product` p ON hcs.`product_id` =p.`id`"+
        "LEFT JOIN `us_product`.`category_surgery` cs on cs.`product_id` =p.`id`"+
        "LEFT JOIN `us_product`.`merchant_product`  mp on mp.`product_id` = p.`id`"+
        "LEFT JOIN `us_product`.`org_product` op on op.`product_id` =p.`id`"+
        "where p.`type` =1 and p.`is_for_sale` =1 "+
        "                <if test=\"hId != null\">  and hcs.hot_category_id = #{hId} and p.id = hcs.product_id</if>" +  //热门类目id
        "                <if test=\"categoryId != null\">  and cs.category_id = #{categoryId} and p.id = cs.product_id</if>" +  //类目id
        "                <if test=\"input != null\">       and (p.name like CONCAT('%',#{input},'%') or p.company like CONCAT('%',#{input},'%')) </if> "+   //用户输入,包括商品名和店铺名,模糊
        "                <if test = \" location != null\"> and p.location  like CONCAT('%',#{location},'%') </if> "+        //位置..
        "                <if test=\"method != null\">      and mp.filter_id = #{method} and p.id = mp.product_id</if>  "+   //筛选条件    手术方式
        "                <if test=\"org != null\">         and op.filter_id = #{org} and p.id = op.product_id</if>  "+      //筛选条件    所属机构
        "                 ORDER BY sale_volume DESC"+
        "                </script>"
})
List<Product> findProductFromLocal(@Param("hId")Long hId,@Param("categoryId")Long categoryId,@Param("input")String input,@Param("method")Long method,@Param("org")Long org,@Param("location")String location);