Mybatis——动态sql

if标签

用于判断条件是否成立。使用test属性进行条件判断，如果条件为true，则拼接sql。

<where>标签用于识别语句是否需要连接词and，识别sql语句。

package com.t0.maybatisc.mapper;

import com.t0.maybatisc.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface EmpMapper {
//    条件查询
    public List<Emp> list( String name,  Short gender,  LocalDate begin,  LocalDate end);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper">
    <select id="list" resultType="com.t0.maybatisc.pojo.Emp">
        select * from emp
        <where>
            <if test="name != null">
                name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>
</mapper>

//    根据条件查询
    @Test
    public void list(){
//        List<Emp> empList =empMapper.list("张",(short) 1,LocalDate.of(2010,01,01),LocalDate.of(2020,01,01));
        List<Emp> empList =empMapper.list(null,(short)1,null,null);
        System.out.println(empList);
    }
}

用于select、upgrade。

foreach标签

package com.t0.maybatisc.mapper;

import com.t0.maybatisc.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface EmpMapper {
//    多条删除
    public  void deleteByids(List<Integer> ids);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper">
    <select id="deleteByids" resultType="com.t0.maybatisc.pojo.Emp">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </select>
    
</mapper>

collection="ids" item="id" separator="," open="(" close=")"

    @Test
    public void TestdeleteByids(){
        List<Integer> ids = Arrays.asList(19,18,16);
        empMapper.deleteByids(ids);
    }

sql和include

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper">
    <sql id="commonS">
        select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_time
        from emp
    </sql>
    <select id="list" resultType="com.t0.maybatisc.pojo.Emp">
        <include refid="commonS"></include>
        <where>
            <if test="name != null">
                name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>
</mapper>