- 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。
- 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
- MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。
1.if
新建接口
public interface IemployeeMapperDynamicSQL {
public List<employee> getEmpsByConditionIf(employee emp);
}
新建Mapper IemployeeMapperDynamicSQL.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.tangge.Mapper.IemployeeMapperDynamicSQL">
<!--
if
choose (when, otherwise)
trim (where, set)
foreach
-->
<!--public List<employee> getEmpsByConditionIf(employee emp);-->
<select id="getEmpsByConditionIf" resultType="com.tangge.model.employee">
select * from tbl_employee WHERE 1=1
<if test="id != 0">
and id=#{id}
</if>
<!--QGNL中的（和）：and 和 && ，XML不支持&&，所以可以写成 &&-->
<if test="lastName != null && lastName != ''">
and last_name like #{lastName}
</if>
<!--QGNL中的（或）：or 和 || -->
<if test=" gender ==0 || gender == 1">
and gender = #{gender}
</if>
<if test="email != null">
and email=#{email}
</if>
</select>
</mapper>
---->【测试】：
public static void getEmpsByConditionIf() {
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
try {
IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
employee employee = new employee(0,"%i%",null,null);
List<employee> employees = mapper.getEmpsByConditionIf(employee);
System.out.println(employees);
/**
* 结果：
* [employee{id=1, lastName='null', email='tom@guigu.com', gender=0, dept=null},
* employee{id=5, lastName='null', email='lily@xwf.com', gender=1, dept=null}]
*/
} finally {
session.close();
}
}
2.choose
类似带了 breack 的 swicth-case
<select id="getEmpsByConditionChoose" resultType="com.tangge.model.employee">
select * from tbl_employee
<where>
<choose>
<when test="id != 0">
id=#{id}
</when>
<when test="lastName != null && lastName != ''">
last_name like #{lastName}
</when>
<when test="email != null">
email=#{email}
</when>
<otherwise>
1 = 1
</otherwise>
</choose>
</where>
</select>
3.Trim
如果条件后面加and，最后生成的SQL条件也有一条and，我们需要删除掉。
使用 <trim>标签
<select id="getEmpsByConditionTrim" resultType="com.tangge.model.employee">
select * from tbl_employee
<!--
trim:
- prefix:前缀
- prefixOverrides:前缀覆盖
- suffix:后缀
- prefixOverrides:后缀覆盖
-->
<trim prefix="where" suffixOverrides="and">
<if test="id != 0">
id=#{id} and
</if>
<!--QGNL中的（和）：and 和 && ，XML不支持&&，所以可以写成 &&-->
<if test="lastName != null && lastName != ''">
last_name like #{lastName} and
</if>
<!--QGNL中的（或）：or 和 || -->
<if test=" gender ==0 || gender == 1">
gender = #{gender} and
</if>
<if test="email != null">
email=#{email} and
</if>
</trim>
</select>
3.1 where
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
3.2.set（封装修改条件）
<!--
set标签：
set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。
（因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留）
-->
<update id="updateEmp" >
UPDATE tbl_employee
<set>
<if test="lastName != null && lastName != ''">
last_name = #{lastName},
</if>
<!--QGNL中的（或）：or 和 || -->
<if test="gender ==0 || gender == 1">
gender = #{gender},
</if>
<if test="email != null">
email=#{email},
</if>
</set>
<where>
id = #{id}
</where>
</update>
若你对 set 元素等价的自定义 trim 元素的代码感兴趣，那这就是它的真面目：
<trim prefix="SET" suffixOverrides=",">
...
</trim>
4.foreach
动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：
定义个接口
public List<employee> getEmpsByConditionForeach(List<Integer> list1);
Mapper
<select id="getEmpsByConditionForeach" resultType="com.tangge.model.employee">
select * from tbl_employee WHERE id in
<!--
foreach:
- collection:指定要遍历的集合：list类型的参数会封装在map中，map的key就叫list
- item:将遍历出的元素赋值给指定变量
#{变量名} 当前遍历的元素
- separator:每个元素之间的分隔符
- open:开始的字符前缀
- close:结束字符后缀
- index:
- list: index是list的索引，item是当前值
- map: index是map的key，item是map的value
-->
<foreach collection="list" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
---->【测试】：
public static void getEmpsByConditionForeach() {
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
try {
IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
List<Integer> list1 = new ArrayList<>();
list1.add(5);
list1.add(6);
List<employee> rows = mapper.getEmpsByConditionForeach(list1);
//执行的SQL：select * from tbl_employee WHERE id in ( 5 , 6 )
System.out.println(rows);
} finally {
session.close();
}
}
4.1 批量插入使用foreach
接口声明：
public boolean addEmpList(@Param("emps") List<employee> employees);
Mapper
<!--批量插入-->
<!-- public boolean addEmpList(@Param("emps") List<employee> employees);-->
<insert id="addEmpList">
INSERT INTO tbl_employee(last_name,gender,email,dept_id) VALUES
<foreach collection="emps" item="item" separator="," >
(#{item.lastName},#{item.gender},#{item.email},#{item.dept.departmentId})
</foreach>
</insert>
---->【测试】：
public static void addEmpList() {
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
try {
IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
employee employee = new employee("wwa","sdas@sasa",null);
employee employee1 = new employee("wwc","sdas@sasa",null);
List<employee> emps = new ArrayList<>();
emps.add(employee);
emps.add(employee1);
boolean rows = mapper.addEmpList(emps);
System.out.println(rows);
} finally {
session.close();
}
}
5.内置参数：_parameter 与 _databaseId
public List<employee> getEmpsInnerParameter(employee emp);
配置
<!--
两个内置参数：
- _parameter：代表整个参数
* 单个参数：_parameter就是整个参数
* 多个参数：参数封装一个map,_parameter就代表真个map
- _databaseId：如果config xml配置了databaseIdProvider标签。
* _databaseId代表当前数据库的别名oracle
-->
<!--public List<employee> getEmpsInnerParameter(employee emp);-->
<select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
<if test="_databaseId=='mysql'">
select * from tbl_employee
<if test="_parameter!=null">
WHERE last_name LIKE #{_parameter.lastName}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
</if>
</select>
---->【测试】：
public static void getEmpsInnerParameter() {
SqlSessionFactory sqlSessionFactory = SqlSessionFactoryUtil.getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
try {
IemployeeMapperDynamicSQL mapper = session.getMapper(IemployeeMapperDynamicSQL.class);
employee employee = new employee(0,"%w%",null,null);
List<employee> employees = mapper.getEmpsInnerParameter(employee);
System.out.println(employees);
} finally {
session.close();
}
}
结果：
DEBUG 08-28 15:46:43,167 ==> Preparing: select * from tbl_employee WHERE last_name LIKE ? (BaseJdbcLogger.java:139)
DEBUG 08-28 15:46:43,192 ==> Parameters: %w%(String) (BaseJdbcLogger.java:139)
DEBUG 08-28 15:46:43,221 <== Total: 3 (BaseJdbcLogger.java:139)
[
employee{id=6, lastName='null', email='wewe@qq.com', gender=1, dept=null},
employee{id=7, lastName='null', email='sdas@sasa', gender=null, dept=null},
employee{id=8, lastName='null', email='sdas@sasa', gender=null, dept=null}
]
6.bind （OGNL 表达式绑定）
上面传参 %w%
employee employee = new employee(0,"%w%",null,null);
Mapper
<if test="_parameter!=null">
WHERE last_name LIKE #{_parameter.lastName}
</if>
然后输出的是
DEBUG 08-28 15:46:43,192 ==> Parameters: %w%(String)
---->【需求】：：现在我们想要传入参数%w%变为w，%在Mapper里添加。
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文
<select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
<!--bind使用-->
<bind name="pattern" value="'%' + _parameter.lastName + '%'" />
<if test="_databaseId=='mysql'">
select * from tbl_employee
<if test="_parameter!=null">
WHERE last_name LIKE #{pattern}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
</if>
</select>
7.sql 和 include（抽取可重用的SQL片段）
<select id="getEmpsInnerParameter" resultType="com.tangge.model.employee">
<bind name="pattern" value="'%' + _parameter.lastName + '%'" />
<if test="_databaseId=='mysql'">
<!--
引用外部的SQL
- property：自定义属性
这里${abc} 值 tbl_employee，可以在<sql>里运用
-->
<include refid="employee">
<property name="abc" value="tbl_employee"/>
</include>
<if test="_parameter!=null">
WHERE last_name LIKE #{pattern}
</if>
</if>
<if test="_databaseId=='oracle'">
select * from employees
</if>
</select>
<!--
抽取可重用的SQL片段，方便引用
- <sql>抽取：经常查询的列名，或者插入用的列名抽取出来方便引用
- <include>:引用抽取的SQL
- <include>还可以定义<property>,sql内部标签可以使用 ${prop}
-->
<sql id="employee">
select * from ${abc}
</sql>