动态SQL1

If标签

动态SQL可以说是MyBatis最强大之处了，这块的应用主要有四个方面if,choose,trim和foreach，接下来先说说if。

顾名思义，if是用来判断条件的，现在假设我们有个需求，原先我们查询员工是靠id查询的，现在提出新的要求，要求用户输入携带了哪个字段查询条件就带上这个字段的值，

List<Employee> getEmpsByConditionIf(Employee e);

那么这个sql语句该怎么写呢？

<select id="EmployeeMapperDynamicTest" resultType="com.figsprite.bean.Employee">
select * from tb_employee where id = #{id} and last_name like #{lastName}
and email = #{email} and gender=#{gender}
</select>

这种写法显然是有问题的，如果传入的Employee中lastName为空，这个select语句就查不出我们想要的结果，还有很多类似的情况，此时，就要使用到if，<if test=""></if>，test属性中采用的是OGNL表达式，与EL表达式和你像，大家可以去官网查找具体的内容[http://commons.apache.org/proper/commons-ognl/download_ognl.cgi]

在书写test属性中的OGNL时要注意遇到特殊符号应该写转移字符，比如&要写成&;

具体的对应关系可以查找W3C的手册:

[https://www.w3cschool.cn/htmltags/ref-entities.html]

<select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">
select * from tb_employee
<if test="id!=null">
id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email = #{email}
</if>
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</select>

注意两点1.email判断时用到了email.trim() 2.gender原本是字符串，可此处将它当做是是数字,OGNL会进行字符串与数字的转化判断.

做一下测试：

public static SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-conf.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testGetEmpsByConditionIf() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);
Employee e = new Employee();
e.setId(5);
e.setLastName("%青%");
List<Employee> employeeList = mapper.getEmpsByConditionIf(e);
for (Employee employee:employeeList){
System.out.println(employee);
}
}finally {
openSession.close();
}
}

结果：Employee{id=5, lastName='吴青宁', gender='1', email='wq', department=null}

接着，再试试创建的Employee对象，只给lastName一个属性赋值的情况

@Test
public void testGetEmpsByConditionIf() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);
Employee e = new Employee();
e.setLastName("%青%");
List<Employee> employeeList = mapper.getEmpsByConditionIf(e);
for (Employee employee:employeeList){
System.out.println(employee);
}
}finally {
openSession.close();
}
}

结果：

报错！！！

分析一下，为什么？如果传入的Employee对象中没有id值，但是有lastName时，SQL语句会变成下面这个样子：

    select * from tb_employee and last_name like #{lastName}

显然是个有问题的查询语句，我们该如何解决这个问题？

我们可以将<select>查询中添加1=1，在之后的每个属性前都加上and XXXXXXX，就像下面这样：

<select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">
select * from tb_employee where 1=1
<if test="id!=null">
and id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email = #{email}
</if>
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</select>

这显然没有问题，另外一种方法，MyBatis的<where>标签

where标签

使用方法很简单，将之前的<if>标签都包含在<where></where>之中即可

<select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">
select * from tb_employee
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="lastName!=null and lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email = #{email}
</if>
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</where>
</select>

DEBUG [main] - ==>  Preparing: select * from tb_employee WHERE last_name like ?

可以发现<where>能将多余的and或者or去掉，当然where也有不好使的时候，有些人习惯将and放在后面，如下:

<select id="getEmpsByConditionIf" resultType="com.figsprite.bean.Employee">
select * from tb_employee
<where>
<if test="id!=null">
and id = #{id} and
</if>
<if test="lastName!=null and lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=""">
email = #{email} and
</if>
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</where>
</select>

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ? and

SQL语句明显出错，可见<where>只能去掉头部的and或者or

trim标签

如果我就想把and 放在后面，要怎么办？这是我们该用<trim>标签了，<trim>可以自定义截取标签

<trim>标签包含4个属性:

前缀：prefix
前缀覆盖：prefixOverrides
后缀：suffix
后缀覆盖：suffixOverrides

trim标签体中是整个字符串拼串后的结果，prefix给拼串后的整个字符串加一个前缀，比如我们可以不在SQL语句中写where而选择在<trim>中写：<trim prefix="where">……

前缀覆盖指的是去掉拼串前面多余的字符

后缀和后缀覆盖同理，那么我们在后面添加and就有可能出现后面多出and的情况，因此我们只要指定后缀覆盖的属性值为and，就可以处理上述情况：

<select id="getEmpsByConditionTrim" resultType="com.figsprite.bean.Employee">
select * from tb_employee
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id = #{id} and
</if>
<if test="lastName!=null and lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=""">
email = #{email} and
</if>
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>

DEBUG [main] - ==> Preparing: select * from tb_employee where last_name like ?

虽然看似功能强大的<trim>其实并不常用，大家作为了解即可

choose标签

这是一个分支选择标签，类似于Java中的switch-case，之前我们在<if>标签中是逐一判断哪个条件是合法存在的，然后使用该标签，接下来我们换一种要求，已知传入的Employee对象只会有一种属性是存在值的，那么我们就可以使用choose，当然也可以用if

    List<Employee> getEmpsByConditionChoose(Employee e);

<select id="getEmpsByConditionChoose" resultType="com.figsprite.bean.Employee">
select * from tb_employee
<where>
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="lastName!=null and lastName!=""">
last_name like #{lastName}
</when>
<when test="email!=null and email.trim()!=""">
email = #{email}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>

(其中test属性中的""要改成")

<otherwise>标签，就是当前面所有的when条件都没中的时候，拼接上这个标签里的语句

@Test
public void testGetEmpsByConditionIf() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamic mapper = openSession.getMapper(EmployeeMapperDynamic.class);
Employee e = new Employee();
e.setLastName("%青%");
List<Employee> employeeList = mapper.getEmpsByConditionChoose(e);
for (Employee employee:employeeList){
System.out.println(employee);
}
}finally {
openSession.close();
}
}

DEBUG [main] - ==> Preparing: select * from tb_employee WHERE last_name like ?

如果传入的对象同时包含id和lastName的话会查谁？显然是只会用id作为条件