Mybatis 条件查询 批量增删改查功能

模糊查询：

@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);

以上所述是小编给大家介绍的mybatis 条件查询 批量增删改查功能，希望对大家有所帮助