【MyBatis】动态SQL的使用

// Carmapper.xml
<select id="selectByMultiCondition" resultType="Car">
    select * from car where 1 = 1 // 1 = 1 是为了防止<if>都为空, 如果使用where标签则不用1=1
    // 1. if标签中test标签的值是false/true
    // 2. 如果test是true, 则if标签中的sql语句就会拼接, 反之则不会拼接
    // 3. test属性中可以使用的是:
    //        当使用@Param注解, 那么test中要出现的是@Param注解指定的参数名
    //                (@Param("brand"), 那么这里能使用brand)
    //        当没有使用@Param注解, 那么test要出现的是param1,parm2,arg0.arg1
    //        当使用POJO, 那么test中出现的是POJO类的属性名
    // 4. && 不能使用, 只能使用 and
    <if test="brand != null and brand != ''">
        and brand like "%"#{brand}"%" </if>
    <if test="guidePrice != null and guidePrice != ''">
        // guide_price用的是数据库表中的字段名
        and guide_price > #{guidePrice} </if>
    <if test="carType != null and carType != ''">
        // car_type用的是数据库表中的字段名
        and car_type = #{carType} </if>
</select>
// 接口
public interface CarMapper{
    // 多条件查询(品牌,指导价,汽车类型)
    List<Car> selectByMultiCondition(
        @Param("brand") String brand,
        @Param("guidePrice") Double guidePrice,
        @Param("carType") String carType);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiCondition(.....参数.....);
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

// CarMapper.xml
<select id="selectByMultiConditionWithWhere" resultType="Car">
    select * from car 
    // where标签是专门负责where子句动态生成的
    <where>
        <if test="brand != null and brand != ''">
            and brand like "%"#{brand}"%" </if>
        <if test="guidePrice != null and guidePrice != ''">
            // guide_price用的是数据库表中的字段名
            and guide_price > #{guidePrice} </if>
        <if test="carType != null and carType != ''">
            // car_type用的是数据库表中的字段名
            and car_type = #{carType} </if>
    </where>
</select>
// 接口
public interface CarMapper{
    // 多条件查询(品牌,指导价,汽车类型)
    // 使用where标签让where子句更加智能
    List<Car> selectByMultiConditionWithWhere(
        @Param("brand") String brand,
        @Param("guidePrice") Double guidePrice,
        @Param("carType") String carType);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    List<Car> cars = mapper.selectByMultiConditionWithWhere(.....参数.....);
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

不使用set标签
// CarMapper.xml
<update id="updateById">
    update car set
        car_num = #{carNum},
        brand = #{brand},
        guide_price = #{guidePrice},
        produce_time = #{produceTime},
        car_type = #{carType}
    where
        id = #{id}
</update>
// 接口
public interface CarMapper{
    // 更新car
    int updateById(Car car);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    // 会将字段更改为null
    Car car = new Car(66L,,null,null,null,null,null);
    mapper.updateById(car);
    sqlSession.commit();
    sqlSession.close();
}

使用set标签
// CarMapper.xml
<update id="updateBySet">
    update car 
    <set>
        <if test="carNum != null and carNum != ''">car_num=#{carNum},</if>
        <if test="brand != null and brand != ''">brand=#{brand},</if>
        <if test="guidePrice != null and guidePrice != ''">guide_price=#{guidePrice},</if>
        <if test="produceTime != null and produceTime != ''">produce_time=#{produceTime},</if>
        <if test="carType != null and carType != ''">car_type=#{carType}</if>    
    </set>
    where
        id = #{id}
</update>
// 接口
public interface CarMapper{
    // 更新car
    int updateBySet(Car car);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    // 为null的部分将不会修改, 还是保持原样
    Car car = new Car(66L,,null,null,null,null,null);
    mapper.updateBySet(car);
    sqlSession.commit();
    sqlSession.close();
}

语法格式:
<choose>
    <when></when>
    <when></when>
    <otherwise></otherwise>
</choose>
等同于:
if(){
}else if(){
}else if(){
}else{
}

// CarMapper.xml
<select id="selectByChoose" resultType="Car">
    select * from car 
    <where>
        <choose>
            <when test="brand != null and brand != ''">
                brand like "%"#{brand}"%"
            </when>
            <when test="guidePrice != null and guidePrice != ''">
                guide_price > #{guidePrice}
            </when>
            <otherwise>
                car_type = #{carType}
            </otherwise>
        </choose>
    </where>
</select>
// 接口
public interface CarMapper{
    // 使用choose,when,otherwise标签
    List<Car> selectByChoose(
        @Param("brand") String brand,
        @Param("guidePrice") Double guidePrice,
        @Param("carType") String carType);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    // 如果参数全部为null, 那么走到otherwise
    List<Car> cars = mapper.selectByChoose(.....参数.....);
    cars.forEach(car -> System.out.println(car));
    sqlSession.close();
}

// CarMapper.xml
<delete id="deleteByIds">
    // delete from car where id in(1,2,3);
    delete from car where id in(
        // collection: 指定数组或者集合
        // item: 代表数组或集合中的元素
        // separator: 循环之间的分隔符
        // 如果没有使用@Param注解那么collection应该写array,arg0
        <foreach collection="ids" item="aaaaa" separator=",">
            #{aaaaa}
        </foreach>
    )
</delete>
// 接口
public interface CarMapper{
    // 批量删除, foreach标签
    int deleteByIds(@Param("ids") Long[] ids);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Long[] ids = {56L,233L,65L};
    int count = mapper.deleteByIds(ids);
    System.out.println(count);
    sqlSession.commit(); 
    sqlSession.close();
}

// CarMapper.xml
<delete id="deleteByIds2">
    // delete from car where id=1 or id=2 or id=3
    delete from car where id 
        <foreach collection="ids" item="aaaaa" separator="or">
            id=#{aaaaa}
        </foreach>
</delete>
// 接口
public interface CarMapper{
    // 批量删除, foreach标签, 使用or关键字
    int deleteByIds2(@Param("ids") Long[] ids);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Long[] ids = {56L,233L,65L};
    int count = mapper.deleteByIds2(ids);
    System.out.println(count);
    sqlSession.commit(); 
    sqlSession.close();
}

// CarMapper.xml
<insert id="insertBatch">
    insert into car values 
        // collection: 指定数组或者集合
        // item: 代表数组或集合中的元素
        // separator: 循环之间的分隔符
        // 如果没有使用@Param注解那么collection应该写array,arg0
        <foreach collection="cars" item="aaaaa" separator=",">
            (null,#{aaa.carNum},#{aaa.brand},
                #{aaa.guidePrice},#{aaa.produceTime},#{aaa.carType})
        </foreach>
</insert>
// 接口
public interface CarMapper{
    // 批量插入, 一次插入多条信息
    int insertBatch(@param("cars") List<Car> cars);
}
// @test
public static void main(String[] args) {
    SqlSession sqlSession = SqlSessionUtil.openSession();
    CarMapper mapper = sqlSession.getMapper(CarMapper.class);
    Car car1 = new Car(...);
    Car car2 = new Car(...);
    Car car3 = new Car(...);
    Car car4 = new Car(...);
    List<Car> cars = new ArrayList<>();
    cars.add(car1);
    cars.add(car2);
    cars.add(car3);
    cars.add(car4);
    mapper.insertBatch(cars);
    sqlSession.commit(); 
    sqlSession.close();
}