Mybatis小技巧之批量删除、模糊查询

---

一、批量删除

一次删除多条记录
接口方法：

    /**
     * 批量删除 根据id
     * @param ids
     * @return
     */
    int deleteBatch(String ids);

批量删除的SQL语句有两种语法：
第一种or： delete from t_car where id=1 or id=2 or id=3
第二种in：delete from t_car where id in(1,2,3)

    <delete id="deleteBatch">
        delete from t_car where id in (${ids})
    </delete>

二、模糊查询

接口方法：

    /**
     * 根据汽车品牌模糊查询
     * @param brand
     * @return
     */
    List<Car> selectByBrandLike(String brand);

SQL语句编写：
方案一（字符串拼接）：

<select id="selectByBrandLike" resultType="com.powernode.mybatis.pojo.Car">
    select
    id,
    car_num as carNum,
    brand,
    guide_price as guidePrice,
    produce_time as produceTime,
    car_type as carType
    from t_car
    where brand like '%${brand}%'
</select>

方案二 concat函数：
这个是mysql数据库当中的一个函数，专门进行字符串拼接的
contact(‘%’,#{brand},‘%’) 或者 contact(‘%’,‘${brand}’,‘%’)

<select id="selectByBrandLike" resultType="com.powernode.mybatis.pojo.Car">
    select
    id,
    car_num as carNum,
    brand,
    guide_price as guidePrice,
    produce_time as produceTime,
    car_type as carType
    from t_car
    where brand like contact('%',#{brand},'%')
</select>

或者：

<select id="selectByBrandLike" resultType="com.powernode.mybatis.pojo.Car">
    select
    id,
    car_num as carNum,
    brand,
    guide_price as guidePrice,
    produce_time as produceTime,
    car_type as carType
    from t_car
    where brand like contact('%','${brand}','%')
</select>

方案三 “%”#{brand}“%”：

<select id="selectByBrandLike" resultType="com.powernode.mybatis.pojo.Car">
    select
    id,
    car_num as carNum,
    brand,
    guide_price as guidePrice,
    produce_time as produceTime,
    car_type as carType
    from t_car
    where brand like "%"#{brand}"%"
</select>