一、动态SQL
MyBatis 还有一个强大特性就是它的动态SQL
在实际项目开发中,经常需要根据不同条件拼接 SQL 语句,拼接是还要确保不能忘了必要的空格,有时候还要注意省掉列名列表的逗号,等等。
常用的动态 SQL 元素包括:
- if
- choose(when、otherwise)
- where
- foreach
- bind
二、DynamicSQL Test
2.1 准备工作
首先,给之前创建的数据库 mybatis 添加一个表 employee,并插入测试数据。SQL脚本如下:
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`username` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` char(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`phone` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sal` double NULL DEFAULT NULL,
`state` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `employee` VALUES (1, 'jack', '123456', '杰克', '男', 26, '13902019999', 9800, 'active');
INSERT INTO `employee` VALUES (2, 'rose', '123456', '露丝', '女', 21, '13902018888', 6800, 'active');
INSERT INTO `employee` VALUES (3, 'tom', '123456', '汤姆', '男', 22, '13902017777', 8800, 'active');
INSERT INTO `employee` VALUES (4, 'alice', '123456', '艾丽丝', '女', 20, '13902016666', 5600, 'unactive');
接下来创建一个 Employee 对象映射 employee 表
package com.zhang.pojo;
public class Employee {
private Integer id;
private String username;
private String password;
private String name;
private String sex;
private Integer age;
private String phone;
private Double sal;
private String state;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
public String getState() {
return state;
}
public void setState(String state) {
this.state = state;
}
@Override
public String toString() {
return "Employee [id=" + id + ", username=" + username + ", password=" + password + ", name=" + name + ", sex="
+ sex + ", age=" + age + ", phone=" + phone + ", sal=" + sal + ", state=" + state + "]";
}
}
2.2 if
动态 SQL 通常会做的事情有条件的包含 where 子句的一部分。比如:
<?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.zhang.mapper.EmployeeMapper">
<select id="selAllEmployee" resultType="Employee">
select * from employee
</select>
<select id="selEmployeeByCondition" resultType="Employee">
select * from employee where state = 'active'
<if test="id != null">
and id = #{id}
</if>
</select>
</mapper>
以上语句提供可一个可选的根据 id 查询员工的功能。如果没有传入 id 那么返回suoyou state属性为 active的员工,如果传入了id,那么就会把 查找 id 内容的员工结果返回。
-
不传递 id
package com.zhang.work; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.zhang.pojo.Classes; import com.zhang.pojo.Employee; import com.zhang.pojo.Person; import com.zhang.pojo.Student; import com.zhang.service.impl.EmployeeServiceImpl; import com.zhang.service.impl.StudentServiceImpl; public class MainApp { public static void main(String[] args) { ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); EmployeeServiceImpl employeeServiceImpl = applicationContext.getBean("employeeServiceImpl", EmployeeServiceImpl.class); Employee employee = new Employee(); List<Employee> employees = employeeServiceImpl.findEmployeeByCondition(employee); for(Employee e:employees) { System.out.println(e); } } }
测试结果:
-
传递id测试结果
package com.zhang.work; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.zhang.pojo.Classes; import com.zhang.pojo.Employee; import com.zhang.pojo.Person; import com.zhang.pojo.Student; import com.zhang.service.impl.EmployeeServiceImpl; import com.zhang.service.impl.StudentServiceImpl; public class MainApp { public static void main(String[] args) { ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); EmployeeServiceImpl employeeServiceImpl = applicationContext.getBean("employeeServiceImpl", EmployeeServiceImpl.class); Employee employee = new Employee(); employee.setId(3); List<Employee> employees = employeeServiceImpl.findEmployeeByCondition(employee); for(Employee e:employees) { System.out.println(e); } } }
测试结果:
2.3 choose(when、otherwise)
有时候我们不相同所有的条件语句,而只想从中择其一二。针对这种情况,MyBatis 提供了choose 元素,类似于java的 switch语句
还是上面的例子,这次变成了 提供了 id 就按 id 查询,提供了 username 和 password 就按 username 和 password 查询,若两者都没有提供就返回所有 sex 等于 男 的员工。
<?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.zhang.mapper.EmployeeMapper">
<select id="selAllEmployee" resultType="Employee">
select * from employee
</select>
<select id="selEmployeeByCondition" resultType="Employee">
select * from employee where state = 'active'
<choose>
<when test="id != null">
and id = #{id}
</when>
<when test="username !=null and password != null">
and username = #{username} and password =#{password}
</when>
<otherwise>
and sex = '男'
</otherwise>
</choose>
</select>
</mapper>
2.4 where
回到之前的 if 示例,这次我们将 state = ‘active’ 也设置成动态条件,看看会发生什么?
<?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.zhang.mapper.EmployeeMapper">
<select id="selAllEmployee" resultType="Employee">
select * from employee
</select>
<select id="selEmployeeByCondition" resultType="Employee">
select * from employee where
<if test="state != null">
and state = #{state}
</if>
<if test="id != null">
and id = #{id}
</if>
</select>
</mapper>
传入 state 参数,则实行正常。
如果没有传入参数,则会执行 sql 语句
select * from employee where
如果只传入了 id 参数,则会执行 sql 语句
select * from employee where and id = ?
也就是说,如果没有传入 state 参数 就会执行失败,MyBatis 有一个简单册处理方式
<?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.zhang.mapper.EmployeeMapper">
<select id="selAllEmployee" resultType="Employee">
select * from employee
</select>
<select id="selEmployeeByCondition" resultType="Employee">
select * from employee
<where>
<if test="state != null">
and state = #{state}
</if>
<if test="id != null">
and id = #{id}
</if>
</where>
</select>
</mapper>
where 元素知道只有子啊一个以上的 if 条件有值的情况下才去插入 WHERE 子句。而且,若最后的内容是 ‘and’ 或 ’or’开头,则 where 元素也知道如何将他们去除。
2.5 set
关于动态更新语句还可以使用 set 元素。set元素可以被用于动态包含需要更新的列,而舍弃其他的。
<?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.zhang.mapper.EmployeeMapper">
<update id="updateEmployeeById" parameterType="Employee">
update employee
<set>
<if test="username != null">username = #{username},</if>
<if test="password != null">password = #{password},</if>
<if test="name != null">name = #{name},</if>
<if test="sex != null">sex = #{sex},</if>
<if test="age != null">age = #{age},</if>
<if test="phone != null">phone = #{phone},</if>
<if test="sal != null">sal = #{sal},</if>
<if test="state != null">state = #{state}</if>
</set>
where id = #{id};
</update>
</mapper>
set 语句会动态前置 SET 关键字,同时也会消除无关的逗号。
2.6 foreach
关于动态SQL另外一个常用的操作就是需要对一个集合进行遍历,通常发生在构建 in 条件语句时。
<?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.zhang.mapper.EmployeeMapper">
<select id="selEmployeeByIds" resultType="Employee">
select * from employee
where id in
<foreach collection="list" item="ids" index="index" open="(" separator="," close=")">
#{ids}
</foreach>
</select>
</mapper>
-
item: 集合中元素迭代时的别名,该参数为必选。
-
index: 在list和数组中,index是元素的序号,在map中,index是元素的key,该参数可选
-
open: foreach代码的开始符号,一般是(和close=")"合用。常用在in(),values()时。该参数可选
-
separator: 元素之间的分隔符,例如在in()的时候,separator=","会自动在元素中间用“,“隔开,避免手动输入逗号导致sql错误,如in(1,2,)这样。该参数可选。
-
close: foreach代码的关闭符号,一般是)和open="("合用。常用在in(),values()时。该参数可选。
-
collection: 要做foreach的对象,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list .
- 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array .
- 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了
参考文章:
- 《Spring + MyBatis 企业应用开发》
- 《C语言中文网》