MyBatis 框架 动态Sql

动态 SQL，通过 MyBatis 提供的各种标签对条件作出判断以实现动态拼接 SQL 语句。这里的条件判断使用的表达式为 OGNL 表达式。

常用的动态 SQL标签有、、、等。

MyBatis 的动态 SQL 语句，与 JSTL 中的语句非常相似。

动态 SQL，主要用于解决查询条件不确定的情况：在程序运行期间，根据用户提交的查询条件进行查询。提交的查询条件不同，执行的SQL语句不同。若将每种可能的情况逐一列出，对所有条件进行排列组合，将会出现大量的SQL 语句。此时可使用动态SQL来解决这样的问题。

动态sql：同一个dao方法根据不同的条件可以表示不同的sql语句，主要是where部分的变化，使用mybatis提供的标签，实现动态sql的能力。

使用动态sql的时候，dao方法的形参使用java对象。

在 mapper 的动态 SQL 中若出现大于号（>）、小于号（<）、大于等于号（>=），小于等于号（<=）等符号，最好将其转换为实体符号。否则，XML 可能会出现解析出错问题。特别是对于小于号（<），在 XML 中是绝不能出现的。否则解析 mapper文件会出错。

实体符号表：

符号	说明	实体符号
<	小于	&lt ;
>	大于	&gt ;
<=	小于等于	&lt ;=
>=	大于等于	&gt ;=

动态 SQL-if

对于该标签的执行，当 test 的值为 true 时，会将其包含的 SQL 片断拼接到其所在的 SQL 语句中。

语法：<if test="boolean判断结果"> sql 语句的部分 </if>

在mapper文件中：

<select id="selectStudentIf" resultType="com.lln.vo.Student">select id,name,email,age from student<if test="条件">sql语句</if><if test="条件">sql语句</if>
</select>

方法接口：

    //ifList<Student> selectIf(Student student);

mapper文件：

    <!--iftest：使用对象的属性值作为条件--><select id="selectIf" resultType="com.lln.vo.Student">select * from studentwhere<if test="name != null and name != '' ">name = #{name}</if><if test="age > 0 ">or age = #{age}</if></select>

测试类1：

    @Testpublic void testSelectIf(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);Student student = new Student();student.setName("李四");student.setAge(26);List<Student> students = dao.selectIf(student);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果1：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 428910174.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
==>  Preparing: select * from student where name = ? or age = ? 
==> Parameters: 李四(String), 26(Integer)
<==    Columns: id, name, email, age
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 7, 李煜, liyu@163.com, 26
<==      Total: 2
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=7, name='李煜', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1990a65e]
Returned connection 428910174 to pool.

测试类2：

    @Testpublic void testSelectIf(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);Student student = new Student();student.setName(null);student.setAge(26);List<Student> students = dao.selectIf(student);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果2：

报错，多了一个 or ，不符合语法规范。

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1075738627.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
==>  Preparing: select * from student where or age = ? 
==> Parameters: 26(Integer)

动态 SQL-where

标签的中存在一个比较麻烦的地方：需要在 where 后手工添加 1=1的子句。因为，若 where 后的所有条件均为 false，而 where 若又没有 1=1 子句，则 SQL 中就会只剩下一个空的 where，SQL 出错。所以，在where 后，需要添加永为真子句 1=1，以防止这种情况的发生。但当数据量很大时，会严重影响查询效率。

使用标签，在有查询条件时，可以自动添加上 where 子句；没有查询条件时，不会添加 where 子句。需要注意的是，第一个标签中的SQL 片断，可以不包含 and。不过，写上 and 也不错，系统会将多出的 and去掉。但其它中 SQL 片断的 and，必须要求写上。否则 SQL 语句将拼接出错。

语法：
<where> <if test="条件"> sql 语句的部分 </if><if test="条件"> sql 语句的部分 </if>
</where>

接口方法：

List<Student> selectWhere(Student student);

mapper文件：

    <select id="selectWhere" resultType="com.lln.vo.Student">select * from student<where><if test="name != null and name != '' ">or name = #{name}</if><if test="age > 0 ">or age = #{age}</if></where></select>

测试类1：

    @Testpublic void testSelectWhere(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);Student student = new Student();student.setName("李四");student.setAge(26);List<Student> students = dao.selectWhere(student);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果1：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 775931202.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
==>  Preparing: select * from student WHERE name = ? or age = ? 
==> Parameters: 李四(String), 26(Integer)
<==    Columns: id, name, email, age
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 7, 李煜, liyu@163.com, 26
<==      Total: 2
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=7, name='李煜', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2e3fc542]
Returned connection 775931202 to pool.

测试类2：

    @Testpublic void testSelectWhere(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);Student student = new Student();student.setName(null);student.setAge(26);List<Student> students = dao.selectWhere(student);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果2：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1075738627.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
==>  Preparing: select * from student WHERE age = ? 
==> Parameters: 26(Integer)
<==    Columns: id, name, email, age
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 7, 李煜, liyu@163.com, 26
<==      Total: 2
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=7, name='李煜', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Returned connection 1075738627 to pool.

测试类3：

    @Testpublic void testSelectWhere(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);Student student = new Student();student.setName(null);student.setAge(null);List<Student> students = dao.selectWhere(student);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果3：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1075738627.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
==>  Preparing: select * from student 
==> Parameters: 
<==    Columns: id, name, email, age
<==        Row: 1, 张三, 123@163.com, 18
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 3, 王五, 789@163.com, 30
<==        Row: 4, 刘备, liubei@163.com, 55
<==        Row: 5, 关羽, guanyu@163.com, 50
<==        Row: 6, 张飞, zhangfei@163.com, 55
<==        Row: 7, 李煜, liyu@163.com, 26
<==      Total: 7
Student实体：{id=1, name='张三', email='123@163.com', age=18}
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=3, name='王五', email='789@163.com', age=30}
Student实体：{id=4, name='刘备', email='liubei@163.com', age=55}
Student实体：{id=5, name='关羽', email='guanyu@163.com', age=50}
Student实体：{id=6, name='张飞', email='zhangfei@163.com', age=55}
Student实体：{id=7, name='李煜', email='liyu@163.com', age=26}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@401e7803]
Returned connection 1075738627 to pool.

动态 SQL-foreach

使用foreach可以循环数组，list集合，一般使用在in语句中。

标签用于实现对于数组与集合的遍历。

对其使用，需要注意：
collection 表示要遍历的集合类型, list ，array 等。
open、close、separator 为对遍历内容的 SQL 拼接。

语法：

<foreach collection="集合类型" open="开始的字符" close="结束的字符" item="集合中的成员" separator="集合成员之间的分隔符">#{item 的值}
</foreach>
标签属性：
collection：表示循环的对象是数组还是list集合。如果dao接口方法的形参是数组，collection="array"；如果dao接口方法的形参是list集合，collection="list"；
open：循环开始的字符。
close：循环结束的字符。
item：集合成员，自定义变量。 Integer item = idlist.get(i);
separator：集合成员之间的分隔符。#{item 的值} ：获取集合成员的值。

简单类型

接口方法：

    //包含基本类型的集合List<Student> selectForeachOne(List<Integer> idList);

mapper文件：

    <select id="selectForeachOne" resultType="com.lln.vo.Student">select * from student<if test="list!=null and list.size>0">where id in<foreach collection="list" open="(" close=")" separator="," item="stuId">#{stuId}</foreach></if></select>

测试类：

    @Testpublic void testSelectForeachOne(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);List<Integer> idList = new ArrayList<>();idList.add(1);idList.add(2);idList.add(3);List<Student> students = dao.selectForeachOne(idList);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1858609436.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
==>  Preparing: select * from student where id in ( ? , ? , ? ) 
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<==    Columns: id, name, email, age
<==        Row: 1, 张三, 123@163.com, 18
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 3, 王五, 789@163.com, 30
<==      Total: 3
Student实体：{id=1, name='张三', email='123@163.com', age=18}
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=3, name='王五', email='789@163.com', age=30}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6ec8211c]
Returned connection 1858609436 to pool.

对象类型

接口方法：

List<Student> selectForeachTwo(List<Student> studentList);

mapper文件：

    <select id="selectForeachTwo" resultType="com.lln.vo.Student">select * from student<if test="list!=null and list.size >0">where id in<foreach collection="list" open="(" close=")" separator="," item="stu">#{stu.id}</foreach></if></select>

测试类：

    @Testpublic void testSelectForeachTwo(){//1.获取SqlSessionSqlSession session = MyBatisUtil.getSqlSession();//2.获取dao的代理StudentDao dao = session.getMapper(StudentDao.class);List<Student> list = new ArrayList<>();Student s1 = new Student();s1.setId(1);Student s2 = new Student();s2.setId(2);Student s3 = new Student();s3.setId(3);list.add(s1);list.add(s2);list.add(s3);List<Student> students = dao.selectForeachTwo(list);students.forEach(stu -> System.out.println(stu));//3.关闭SqlSession对象session.close();}

运行结果：

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1543974463.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
==>  Preparing: select * from student where id in ( ? , ? , ? ) 
==> Parameters: 1(Integer), 2(Integer), 3(Integer)
<==    Columns: id, name, email, age
<==        Row: 1, 张三, 123@163.com, 18
<==        Row: 2, 李四, 456@163.com, 26
<==        Row: 3, 王五, 789@163.com, 30
<==      Total: 3
Student实体：{id=1, name='张三', email='123@163.com', age=18}
Student实体：{id=2, name='李四', email='456@163.com', age=26}
Student实体：{id=3, name='王五', email='789@163.com', age=30}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@5c072e3f]
Returned connection 1543974463 to pool.

动态 SQL-代码片段

<sql/>标签用于定义 SQL 片断，以便其它 SQL 标签复用。而其它标签使用该 SQL 片断，需要使用<include/>子标签。
该<sql/>标签可以定义 SQL 语句中的任何部分，所以<include/>子标签可以放在动态 SQL 的任何位置。

步骤：
1.在mapper文件中定义sql代码片段；
2.在其他位置，使用include标签引用某个代码片段。