Mybatis三 | 动态SQL

目录

if

where

set

---

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 mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper"><!--resultType单条记录所封装的内容--><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内容如下

package com.itheima;import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest
class SpringbootMybatisCrudApplicationTests {@Autowiredprivate EmpMapper empMapper;@Testpublic 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 mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.EmpMapper"><!--resultType单条记录所封装的内容--><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内容如下 

package com.itheima.mapper;import com.itheima.pojo.Emp;
import org.apache.ibatis.annotations.*;@Mapper
public interface EmpMapper {public void update(Emp emp);}

此次更新id为19的员工，SpringbootMybatisCrudApplicationTests.java内容如下

package com.itheima;import com.itheima.mapper.EmpMapper;
import com.itheima.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.time.LocalDateTime;@SpringBootTest
class SpringbootMybatisCrudApplicationTests {@Autowiredprivate EmpMapper empMapper;@Testpublic 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);}}

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