Mybatis三 | 动态SQL

目录

if

where

set

 foreach

 sql&include

---

ctrl + alt + l格式化SQL语句

 随着用户的输入或外部条件的变化而变化的SQL称为动态SQL

if

<if>用来判断条件是否成立，使用test属性进行条件判断，如果true，则拼接SQL 

where

wehre元素只会在有条件成立的情况下才插入where子句，而且会自动去除开头的AND或OR

如果存在只传递姓名的情况，之前的程序会无法成功查询，可以通过动态SQL解决上述问题 

EmpMapper.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
    <select id="list" resultType="com.itheima.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>

 SpringbootMybatisCrudApplicationTests.java

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    @Test
    public void testSelect(){
        List<Emp> list = empMapper.list(null,(short)1,null,null);
        System.out.println(list);
    }

}

 运行结果如下 

set

<set>动态地在行首插入SET关键字，并会删掉额外的逗号(用在update语句中) 

将id为18的员工的username改为Tom111，name改为Tom111，gender改为2，其他不变

按照之前的方法进行更新会使其他值均变为null

可以通过动态SQL解决

EmpMapper.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
    <update id="update">
        update emp
        <set>
            <if test="username != null">username=#{username},</if>
            <if test="password != null">password=#{password},</if>
            <if test="name != null">name=#{name},</if>
            <if test="gender != null">gender=#{gender},</if>
            <if test="image != null">image=#{image},</if>
            <if test="job != null">job=#{job},</if>
            <if test="entrydate != null">entrydate=#{entrydate},</if>
            <if test="deptId != null">dept_id=#{deptId},</if>
            <if test="updateTime != null">update_time=#{updateTime}</if>
        </set>
        where id=#{id}
    </update>
</mapper>

EmpMapper.java

@Mapper
public interface EmpMapper {
    public void update(Emp emp);

}

此次更新id为19的员工，SpringbootMybatisCrudApplicationTests.java

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    @Test
    public void testUpdate(){
        Emp emp = new Emp();
        emp.setId(19);
        emp.setUsername("Tom2222");
        emp.setName("Tom222");
        emp.setGender((short)1);
        emp.setUpdateTime(LocalDateTime.now());
        empMapper.update(emp);
    }

}

 运行结果如下，发现只更新了四个字段，其余字段不变

 foreach

实现批量删除

collection 集合名称

item 集合遍历出来的元素，项

separator 每一次遍历使用的分隔符

open 遍历开始前拼接的片段

close 遍历结束后拼接的片段

EmpMapper.xml

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
    <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

</mapper>

 SpringbootMybatisCrudApplicationTests.java

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    @Test
    public void testDeleteByIds(){
        List<Integer> ids = Arrays.asList(13,14,15);
        empMapper.deleteByIds(ids);
    }

}

EmpMapper.java

@Mapper
public interface EmpMapper {
    public void deleteByIds(List<Integer> ids);

}

运行发现删除成功

 sql&include

sql 定义可重用的片段

include 通过属性refid ，指定包含的sql片段

EmpMapper.xml  

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper">
    <sql id="commonSelect">
        select id, username, password, name, gender, image, job, entrydate, dept_id deptId, create_time createTime, update_time updateTime
        from emp
    </sql>
    <select id="list" resultType="com.itheima.pojo.Emp">
        <include refid="commonSelect"/>
        <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>