动态 sql
文章目录
1、环境搭建
先创建一个 EmployeeMapperDynamicSQL
接口:
package mybatis.dao;
public interface EmployeeMapperDynamicSQL {
}
创建对应的 EmployeeMapperDynamicSQL.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="mybatis.dao.EmployeeMapperDynamicSQL">
<!--
if
choose(when, otherwise)
trim(where, set)
foreach
-->
</mapper>
2、if 判断 & OGNL
在接口中定义查询方法:
public interface EmployeeMapperDynamicSQL {
//携带了哪个字段,查询条件就带上哪个字段的值
public List<Employee> getEmpByConditionIf(Employee employee);
}
在配置文件中实现 sql 查询:
<!-- 查询员工,要求:携带了哪个字段,查询条件就带上哪个字段的值 -->
<!-- public List<Employee> getEmpByConditionIf(Employee employee); -->
<select id="getEmpByConditionIf" resultType="mybatis.bean.Employee">
select * from tbl_employee
where
<!-- test:判断表达式(OGNL)
OGNL参照官方文档
从参数中取值进行判断
遇见特殊符号应该去写转义字符
c:if test -->
<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>
<!-- ognl会进行字符串与数字之间的转换 -->
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</select>
测试:
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);
List<Employee> empByConditionIf = mapper.getEmpByConditionIf(employee);
for (Employee emp:
empByConditionIf) {
System.out.println(emp);
}
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 18:55:30,112 ==> Preparing: select * from tbl_employee where id = ? and last_name like ? and email = ? (BaseJdbcLogger.java:137)
DEBUG 12-28 18:55:30,146 ==> Parameters: 1(Integer), %e%(String), jerry3@qq.com(String) (BaseJdbcLogger.java:137)
DEBUG 12-28 18:55:30,162 <== Total: 1 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
2.1、where 查询条件
上边配置文件中的 sql 存在问题:如果给定的参数中没有带 id
,直接给了 last_name
,那么 sql 语句中上来就是 and last_name like
,那么 sql 语句就会语法报错。
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
//Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);
Employee employee = new Employee(null, "%e%", null, null);
List<Employee> empByConditionIf = mapper.getEmpByConditionIf(employee);
for (Employee emp:
empByConditionIf) {
System.out.println(emp);
}
//查询的时候如果某些条件没带可能sql拼装会有问题
//1、给where后边加上 1=1,以后的条件都有 and xxx
//2、mybatis可以使用where标签来将所有的查询条件包括在内
//where只会去掉第一个多出来的and或者or
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 19:14:54,907 ==> Preparing: select * from tbl_employee where and last_name like ? (BaseJdbcLogger.java:137)
DEBUG 12-28 19:14:54,955 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and last_name like '%e%'' at line 6
...
第一种解决方法:
<select id="getEmpByConditionIf" resultType="mybatis.bean.Employee">
select * from tbl_employee
where 1=1
<!-- test:判断表达式(OGNL)
OGNL参照官方文档
从参数中取值进行判断
遇见特殊符号应该去写转义字符
c:if test -->
<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>
<!-- ognl会进行字符串与数字之间的转换 -->
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</select>
在 where
之后加上 1=1
,然后每个 if
标签里边加上 and
。
DEBUG 12-28 19:17:01,641 ==> Preparing: select * from tbl_employee where 1=1 and last_name like ? (BaseJdbcLogger.java:137)
DEBUG 12-28 19:17:01,671 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
DEBUG 12-28 19:17:01,688 <== Total: 3 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
第二种解决方法:
<select id="getEmpByConditionIf" resultType="mybatis.bean.Employee">
select * from tbl_employee
<!-- where -->
<where>
<!-- test:判断表达式(OGNL)
OGNL参照官方文档
从参数中取值进行判断
遇见特殊符号应该去写转义字符
c:if test -->
<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>
<!-- ognl会进行字符串与数字之间的转换 -->
<if test="gender == 0 or gender == 1">
and gender = #{gender}
</if>
</where>
</select>
mybatis 可以使用 where
标签来将所有的查询条件包括在内。
DEBUG 12-28 19:17:01,641 ==> Preparing: select * from tbl_employee where 1=1 and last_name like ? (BaseJdbcLogger.java:137)
DEBUG 12-28 19:17:01,671 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
DEBUG 12-28 19:17:01,688 <== Total: 3 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
但是 where
标签无法解决的问题:
<select id="getEmpByConditionIf" resultType="mybatis.bean.Employee">
select * from tbl_employee
<!-- where -->
<where>
<!-- test:判断表达式(OGNL)
OGNL参照官方文档
从参数中取值进行判断
遇见特殊符号应该去写转义字符
c:if test -->
<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>
<!-- ognl会进行字符串与数字之间的转换 -->
<if test="gender == 0 or gender == 1">
gender = #{gender}
</if>
</where>
</select>
DEBUG 12-28 19:24:40,253 ==> Preparing: select * from tbl_employee WHERE last_name like ? and (BaseJdbcLogger.java:137)
DEBUG 12-28 19:24:40,284 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
2.2、sql_trim 自定义字符串截取
public interface EmployeeMapperDynamicSQL {
public List<Employee> getEmpByConditionTrim(Employee employee);
<!-- public List<Employee> getEmpByConditionTrim(Employee employee); -->
<select id="getEmpByConditionTrim" resultType="mybatis.bean.Employee">
select * from tbl_employee
<!-- 后面多出的and或者or where标签不能解决
prefix="":前缀,trim标签体中是整个字符串拼串后的结果
prefix给拼串后的整个字符串加一个前缀
prefixOverrides="":前缀覆盖:去掉整个字符串前面多余的字符
suffix="":后缀:给拼串后的整个字符串加一个后缀
suffixOverrides="":后缀覆盖:掉整个字符串后面多余的字符
-->
<trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">
<!-- test:判断表达式(OGNL)
OGNL参照官方文档
从参数中取值进行判断
遇见特殊符号应该去写转义字符
c:if test -->
<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>
<!-- ognl会进行字符串与数字之间的转换 -->
<if test="gender == 0 or gender == 1">
gender = #{gender} and
</if>
</trim>
</select>
测试:
@Test
public void test2() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
//Employee employee = new Employee(1, "%e%", "jerry3@qq.com", null);
Employee employee = new Employee(null, "%e%", null, null);
List<Employee> empByConditionTrim = mapper.getEmpByConditionTrim(employee);
for (Employee emp:
empByConditionTrim) {
System.out.println(emp);
}
//查询的时候如果某些条件没带可能sql拼装会有问题
//1、给where后边加上 1=1,以后的条件都有 and xxx
//2、mybatis可以使用where标签来将所有的查询条件包括在内
//where只会去掉第一个多出来的and或者or
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 19:40:30,915 ==> Preparing: select * from tbl_employee where last_name like ? (BaseJdbcLogger.java:137)
DEBUG 12-28 19:40:30,966 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
DEBUG 12-28 19:40:30,990 <== Total: 3 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
3、choose 分支选择
public interface EmployeeMapperDynamicSQL {
public List<Employee> getEmpByConditionChoose(Employee employee);
<!-- public List<Employee> getEmpByConditionChoose(Employee employee); -->
<select id="getEmpByConditionChoose" resultType="mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- 如果带了id就用id查,如果带了last_name就用lastName查;只会进入其中一个 -->
<choose>
<when test="id != null">
id = #{id}
</when>
<when test="lastName != null">
last_name like #{lastName}
</when>
<when test="email != email">
email = #{email}
</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</where>
</select>
测试:
@Test
public void test3() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
//测试choose
//Employee employee = new Employee(null, "%e%", null, null);
Employee employee = new Employee(null, null, null, null);
List<Employee> empByConditionChoose = mapper.getEmpByConditionChoose(employee);
for (Employee emp:
empByConditionChoose) {
System.out.println(emp);
}
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 20:42:02,675 ==> Preparing: select * from tbl_employee WHERE gender = 0 (BaseJdbcLogger.java:137)
DEBUG 12-28 20:42:02,716 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 12-28 20:42:02,739 <== Total: 1 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='jerry2', email='jerry3@qq.com', gender='0', dept=null}
什么也没提供,只查出了 gender
为 0 的。
3.1、set 与 if 结合的动态更新
public interface EmployeeMapperDynamicSQL {
public void updateEmp(Employee employee);
<!-- public void updateEmp(Employee employee); -->
<update id="updateEmp">
update tbl_employee
<set>
<if test="lastName != null">
last_name = #{lastName},
</if>
<if test="email != null">
email = #{email},
</if>
<if test="gender != null">
gender = #{gender}
</if>
</set>
where id = #{id}
</update>
测试:
@Test
public void test4() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
//测试set标签
Employee employee = new Employee(1, "Admin", null, null);
mapper.updateEmp(employee);
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 21:23:03,559 ==> Preparing: update tbl_employee SET last_name = ? where id = ? (BaseJdbcLogger.java:137)
DEBUG 12-28 21:23:03,592 ==> Parameters: Admin(String), 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 12-28 21:23:03,594 <== Updates: 1 (BaseJdbcLogger.java:137)
mysql> select * from tbl_employee;
+----+-----------+--------+---------------+------+
| id | last_name | gender | email | d_id |
+----+-----------+--------+---------------+------+
| 1 | Admin | 0 | jerry3@qq.com | 1 |
| 2 | jerry1 | 1 | jerry1@qq.com | 2 |
| 3 | jerry | 1 | jerry@qq.com | 1 |
+----+-----------+--------+---------------+------+
3 rows in set (0.00 sec)
4、foreach 遍历集合
public interface EmployeeMapperDynamicSQL {
public List<Employee> getEmpsByConditionForeach(List<Integer> ids);
<!-- public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<select id="getEmpsByConditionForeach" resultType="mybatis.bean.Employee">
select * from tbl_employee where id in
<!--
collection:指定要遍历的集合:
list类型的参数会特殊处理封装在map中,map的key就叫list
item:将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open:遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候index就是索引
遍历map的时候index表示的是map的key,item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreach collection="list" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
测试:
@Test
public void test5() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> empsByConditionForeach = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3));
for (Employee emp:
empsByConditionForeach) {
System.out.println(emp);
}
}
finally {
sqlSession.close();
}
}
DEBUG 12-28 21:46:37,323 ==> Preparing: select * from tbl_employee where id in ( ? , ? , ? ) (BaseJdbcLogger.java:137)
DEBUG 12-28 21:46:37,359 ==> Parameters: 1(Integer), 2(Integer), 3(Integer) (BaseJdbcLogger.java:137)
DEBUG 12-28 21:46:37,378 <== Total: 3 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='Admin', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
更简洁的写法:
<!-- public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<select id="getEmpsByConditionForeach" resultType="mybatis.bean.Employee">
select * from tbl_employee
<!--
collection:指定要遍历的集合:
list类型的参数会特殊处理封装在map中,map的key就叫list
item:将当前遍历出的元素赋值给指定的变量
separator:每个元素之间的分隔符
open:遍历出所有结果拼接一个开始的字符
close:遍历出所有结果拼接一个结束的字符
index:索引。遍历list的时候index就是索引
遍历map的时候index表示的是map的key,item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreach collection="list" item="item_id" separator="," open="where id in (" close=")">
#{item_id}
</foreach>
</select>
4.1、foreach 批量插入的两种方式
第一种方法:
public interface EmployeeMapperDynamicSQL {
public void addEmps(@Param("emps")List<Employee> emps);
<!-- 批量保存 -->
<!-- public void addEmps(@Param("emps")List<Employee> emps); -->
<insert id="addEmps">
insert into tbl_employee(last_name, email, gender, d_id)
values
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName}, #{emp.email}, #{emp.gender}, #{emp.dept.id})
</foreach>
</insert>
测试:
@Test
public void test6() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(null, "smith", "smith@qq.com", "1", new Department(1)));
emps.add(new Employee(null, "allen", "allen@qq.com", "0", new Department(1)));
mapper.addEmps(emps);
}
finally {
sqlSession.close();
}
}
DEBUG 12-29 10:02:12,768 ==> Preparing: insert into tbl_employee(last_name, email, gender, d_id) values (?, ?, ?, ?) , (?, ?, ?, ?) (BaseJdbcLogger.java:137)
DEBUG 12-29 10:02:12,813 ==> Parameters: smith(String), smith@qq.com(String), 1(String), 1(Integer), allen(String), allen@qq.com(String), 0(String), 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 12-29 10:02:12,818 <== Updates: 2 (BaseJdbcLogger.java:137)
mysql> select * from tbl_employee;
+----+-----------+--------+---------------+------+
| id | last_name | gender | email | d_id |
+----+-----------+--------+---------------+------+
| 1 | Admin | 0 | jerry3@qq.com | 1 |
| 2 | jerry1 | 1 | jerry1@qq.com | 2 |
| 3 | jerry | 1 | jerry@qq.com | 1 |
| 4 | smith | 1 | smith@qq.com | 1 |
| 5 | allen | 0 | allen@qq.com | 1 |
+----+-----------+--------+---------------+------+
5 rows in set (0.00 sec)
第二种方法:
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
insert into tbl_employee(last_name, email, gender, d_id)
values (#{emp.lastName}, #{emp.email}, #{emp.gender}, #{emp.dept.id})
</foreach>
</insert>
但这种方式还需要更改配置,让 mysql 支持这种语法:
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://192.168.31.140:3306/mybatis?allowMultiQueries=true
jdbc.username = root
jdbc.password = Opfordream@0518
5、内置参数:_parameter & _databaseld
参数:_databaseld
public interface EmployeeMapperDynamicSQL {
public List<Employee> getEmpsTestInnerParameter(Employee employee);
<!--
两个内置参数:
不只是方法传递过来的参数可以被用来判断或者取值。
mybatis默认两个内置参数:
_parameter:代表整个参数
单个参数:_parameter就是这个参数
多个参数:参数会被封装为一个map,_parameter就是代表这个map
_databaseId:如果配置了DatabaseIdProvider标签
_databaseId就是代表当前数据库的别名
-->
<!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="mybatis.bean.Employee">
<if test="_databaseId == 'mysql'">
select * from tbl_employee
</if>
</select>
测试:
@Test
public void test7() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> empsTestInnerParameter = mapper.getEmpsTestInnerParameter(new Employee());
for (Employee emp :
empsTestInnerParameter) {
System.out.println(emp);
}
}
finally {
sqlSession.close();
}
}
DEBUG 12-29 10:50:03,989 ==> Preparing: select * from tbl_employee (BaseJdbcLogger.java:137)
DEBUG 12-29 10:50:04,022 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 12-29 10:50:04,047 <== Total: 7 (BaseJdbcLogger.java:137)
Employee{id=1, lastName='Admin', email='jerry3@qq.com', gender='0', dept=null}
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
Employee{id=4, lastName='smith', email='smith@qq.com', gender='1', dept=null}
Employee{id=5, lastName='allen', email='allen@qq.com', gender='0', dept=null}
Employee{id=6, lastName='smith', email='smith@qq.com', gender='1', dept=null}
Employee{id=7, lastName='allen', email='allen@qq.com', gender='0', dept=null}
参数:_parameter
<select id="getEmpsTestInnerParameter" resultType="mybatis.bean.Employee">
<if test="_databaseId == 'mysql'">
select * from tbl_employee
<if test="_parameter != null">
where last_name = #{_parameter.lastName}
</if>
</if>
</select>
DEBUG 12-29 10:54:39,038 ==> Preparing: select * from tbl_employee where last_name = ? (BaseJdbcLogger.java:137)
DEBUG 12-29 10:54:39,067 ==> Parameters: null (BaseJdbcLogger.java:137)
DEBUG 12-29 10:54:39,085 <== Total: 0 (BaseJdbcLogger.java:137)
6、bind 绑定
<!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="mybatis.bean.Employee">
<!-- bind:可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值 -->
<bind name="_lastName" value="'%'+lastName+'%'"/>
<if test="_databaseId == 'mysql'">
select * from tbl_employee
<if test="_parameter != null">
where last_name like #{_lastName}
</if>
</if>
</select>
@Test
public void test8() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee();
employee.setLastName("e");
List<Employee> empsTestInnerParameter = mapper.getEmpsTestInnerParameter(employee);
for (Employee emp :
empsTestInnerParameter) {
System.out.println(emp);
}
}
finally {
sqlSession.close();
}
}
DEBUG 12-29 11:26:31,361 ==> Preparing: select * from tbl_employee where last_name like ? (BaseJdbcLogger.java:137)
DEBUG 12-29 11:26:31,390 ==> Parameters: %e%(String) (BaseJdbcLogger.java:137)
DEBUG 12-29 11:26:31,409 <== Total: 4 (BaseJdbcLogger.java:137)
Employee{id=2, lastName='jerry1', email='jerry1@qq.com', gender='1', dept=null}
Employee{id=3, lastName='jerry', email='jerry@qq.com', gender='1', dept=null}
Employee{id=5, lastName='allen', email='allen@qq.com', gender='0', dept=null}
Employee{id=7, lastName='allen', email='allen@qq.com', gender='0', dept=null}
但是就模糊查询还是推荐以下形式:
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee();
employee.setLastName("%e%");
List<Employee> empsTestInnerParameter = mapper.getEmpsTestInnerParameter(employee);
for (Employee emp :
empsTestInnerParameter) {
System.out.println(emp);
}
}
7、抽取可重用的 sql 片段
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
insert into tbl_employee(
<!-- 引用外部定义的sql -->
<include refid="insertColumn"></include>)
values (#{emp.lastName}, #{emp.email}, #{emp.gender}, #{emp.dept.id})
</foreach>
</insert>
<!--
抽取可重用的sql片段,方便后面引用
1、经常将要查询的列名,或者插入用的列名抽取出来,方便引用;
2、include来引用已经抽取的
3、include还可以自定义一些property,sql标签内部就能使用自定义的属性
取值的正确方式:${prop}
#{prop}不能使用
-->
<sql id="insertColumn">
<if test="_databaseId == 'mysql'"></if>
last_name, email, gender, d_id
</sql>
测试:
@Test
public void test6() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession(true);
try {
EmployeeMapperDynamicSQL mapper = sqlSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> emps = new ArrayList<>();
emps.add(new Employee(null, "smith", "smith@qq.com", "1", new Department(1)));
emps.add(new Employee(null, "allen", "allen@qq.com", "0", new Department(1)));
mapper.addEmps(emps);
}
finally {
sqlSession.close();
}
}