MyBatis入门——动态SQL

前言

在我们日常工作中，使用MyBatis除了做一般的数据查询之外，还有很多业务场景下需要我们针对不同条件从数据库中获取到满足指定条件的数据，这时候我们应该如何来做呢？针对每种条件封装一个方法来使用？这肯定是不科学的，这样会导致项目中方法数量直线上升，大大增加了开发和维护的工作量。与之相反的就是把一些比较类似的查询操作封装为一个方法，然后通过传入条件不同来执行不同的SQL查询操作，这就需要使用到MyBatis所提供的动态SQL了，本篇我们就着手讲一下MyBatis动态SQL的相关知识。

准备工作

在开始讲动态SQL之前，我们先把所需要数据提前准备好。

创建一张数据表t_student

CREATE TABLE t_student (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自动编号',stu_no VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '学号',stu_name VARCHAR(50) NOT NULL COMMENT '姓名',gender VARCHAR(10) NOT NULL COMMENT '性别',birthday DATE COMMENT '出生日期',is_valid TINYINT(1) DEFAULT 1 COMMENT '是否有效1有效0无效');

然后在数据表中插入一部分数据

INSERT INTO t_student (stu_no, stu_name, gender, birthday)VALUES('20140010001', '学生一', '男', '1988-01-01'),('20140010002', '学生二', '女', '1989-01-01'),('20140010003', '学生三', '男', '1988-01-01'),('20140010004', '学生四', '男', '1988-11-04'),('20140010005', '学生五', '女', '1987-01-01'),('20140010006', '学生六', '男', '1988-01-08'),('20140010007', '学生七', '男', '1986-01-21'),('20140010008', '学生八', '女', '1990-01-01'),('20140010009', '学生九', '男', '1989-01-06');

数据准备完成之后接下来我们就要使用MyBatis的动态SQL了，这里我们主要讲如下几部分内容：if、choose、where、set、foreach。

动态SQL

if

if操作和Java中的if操作比较类似，具体使用如下（下面只列举了部分代码，全部的代码我会放在后面供大家下载）：

/** *  */package com.mhy.dao;import java.util.List;import com.mhy.model.Student;/** * @author [e-mail] * @date 2014年12月29日 */public interface StudentDao {/** * 根据性别获取学生列表 * @param gender * @return */public List<Student> queryStudentList(Student student);}

此处我们创建了一个接口，在接口中定义了一个方法用以获取满足指定条件的学生信息。

接下来我们创建一个SQL映射文件，如下：

<?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.mhy.dao.StudentDao"><resultMap type="com.mhy.model.Student" id="studentResult"><id property="id" column="id"/><result column="stu_no" property="stuNo" /><result column="stu_name" property="stuName"/><result column="gender" property="gender"/><result column="birthday" property="birthday"/></resultMap><sql id="queryStudent">SELECT id, stu_no, stu_name, gender, birthday FROM t_student </sql><select id="queryStudentList" parameterType="com.mhy.model.Student" resultMap="studentResult"><include refid="queryStudent"/> <if test="gender!=null">WHERE gender=#{gender} </if>ORDER BY id ASC</select></mapper>

如上所示，我们使用了if元素，其所代表的含义如下，如果传对的参数中gender不为空则获取所有的学生列表，否则只查询指定性别的学生，接下来我们以一个测试例子来看一下：

@Testpublic void testIf(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();student.setGender("女");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

执行后输出的结果如下：

如果我们想查询性别为女并且学号为20140010005的学生则可以修改if，变成如下：

<select id="queryStudentList" parameterType="com.mhy.model.Student" resultMap="studentResult"><include refid="queryStudent"/> where is_valid=1 <if test="gender!=null">AND gender=#{gender} </if><if test="stuNo!=null">AND stu_no=#{stuNo} </if>ORDER BY id ASC</select>

然后修改一下我们的测试用例

@Testpublic void testIf(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();student.setGender("女");student.setStuNo("20140010005");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

此处我们增加了stuNo="201400100005"条件，得到的结果如下：

可以看到就只有一条满足条件的记录了。

choose，when，otherwise

choose,when,otherwise很像我们Java中的switch操作，即在筛选条件中选择某一部分进行执行，而不是所有满足条件的都执行。比如上面的例子中，如果有性别的话我们就使用性别进行查询，如果没有性别我们再退而求其次使用姓名查询（一般情况下我们是优先查姓名的，这里只是了为方便演示结果明显才使用这种优先顺序）。

接下来我们就以gender="男"、stuName="学生九"为例来测试。

首先修改SQL映射文件

<select id="queryStudentList" parameterType="com.mhy.model.Student" resultMap="studentResult"><include refid="queryStudent"/> where is_valid=1 <choose><when test="gender!=null">AND gender=#{gender}  </when><when test="stuName!=null">AND stu_name=#{stuName}</when><otherwise><![CDATA[AND id < 6]]></otherwise></choose>ORDER BY id ASC</select>

上面配置文件的意思就是，如果存在性别条件则优先使用性别查询条件，如果性别条件不存在则退而求其次使用姓名查询条件，如果都没有的话则查询id<6的学生，接下来我们测试一下：

@Testpublic void testChoose(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();student.setGender("女");student.setStuName("学生九");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

查询性别为女的学生列表

如果没有传入性别，则使用姓名为条件：

@Testpublic void testChoose(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();//student.setGender("女");student.setStuName("学生九");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

什么都不传入，查询id<6的学生

@Testpublic void testChoose(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();//student.setGender("女");//student.setStuName("学生九");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

where

在前面的示例中，我们都显示的在动态SQL条件之前使用了WHERE is_valid=1条件来避免一些问题，如果此时我们要把is_valid也作为动态查询条件的话，那么我们前面的SQL语句就无法满足要求了，此时就需要我们使用where子元素。where元素知道只有在一个及一个以上if条件满足时才会插入WHERE。如果最后的的内容如果是以AND或OR开头的话where子元素也知道如何去掉它们，接下来我们就以一个例子来演示一下：

<select id="queryStudentList" parameterType="com.mhy.model.Student" resultMap="studentResult"><include refid="queryStudent"/> <where><if test="gender!=null">gender=#{gender} </if><if test="stuNo != null">AND stu_no=#{stuNo}</if></where>ORDER BY id ASC</select>

测试传入性别和学号

@Testpublic void testWhere(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();student.setGender("女");student.setStuNo("20140010005");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

测试只有学号

@Testpublic void testWhere(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();//student.setGender("女");student.setStuNo("20140010005");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

测试不传性别和学号

@Testpublic void testWhere(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);Student student = new Student();//student.setGender("女");//student.setStuNo("20140010005");List<Student> students = dao.queryStudentList(student);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

set

使用了前面的where之后，我们可能会想到如下问题，既然查询条件能动态化了，那么我们的修改操作是不是也可以动态化处理呢？比如：针对于某一条数据库中的记录，只修改传入的参数的内容，不传的字段内容则不做任何修改，此时我们就需要使用到set子元素了。接下来我们以修改学生信息为例来说明。

首先在StudentDao.java中增加一个方法

/** * 修改学生信息 * @param student */void updateStudent(Student student);

然后在SQL映射文件中增加如下配置

<update id="updateStudent" parameterType="com.mhy.model.Student">UPDATE t_student <set><if test="stuName!=null">stu_name=#{stuName},</if><if test="gender!=null">gender=#{gender},</if><if test="birthday!=null">birthday=#{birthday}</if><if test="isValid!=null">is_valid=#{isValid}</if></set>WHERE id=#{id}</update>

接下来我们写一个测试，测试中我们修改id=8的学生性别和是否有效信息。
修改之前的学生信息如下：

@Testpublic void testSet(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {Student student = new Student();student.setId(8);student.setGender("男");student.setIsValid(0);StudentDao dao = s.getMapper(StudentDao.class);dao.updateStudent(student);s.commit();//提交事务} catch(Exception e) {s.rollback();//事务回滚e.printStackTrace();} finally {s.close();//关闭Session}}

执行后的学生信息：

执行完之后可以看到该学生只有性别和是否有效两个字段值被修改了。

foreach

在日常工作中除了上面几种常量的查询情况以外，我们还经常使用IN形式的查询，用来获取满足指定集合内元素的记录集合，比如：查询一部分姓名的学生、查询一部分学号的学生。针对于这种情况我们可以在应该层把查询的条件进行拼接，然后使用${params}传给SQL映射文件来处理，另一种方式就是使用foreach。foreach用来对一个集合进行遍历，接下来我们就演示一下foreach的用法。

首先我们在StudentDao.java中增加一个方法：

/** * 查询指定学号的学生列表信息 * @param stuNoList学生学号列表 * @return */List<Student> getStudentByStuNo(List<String> stuNoList);

在SQL映射文件中增加如下配置

<select id="getStudentByStuNo" parameterType="list" resultMap="studentResult"><include refid="queryStudent"/>WHERE stu_no in <foreach collection="list" item="item" index="index" open="(" separator="," close=")">#{item}</foreach></select>

@Testpublic void testForeach(){SqlSessionFactory sf = MyBatisUtil.getSessionFactory();SqlSession s = sf.openSession();try {StudentDao dao = s.getMapper(StudentDao.class);List<String> list = new ArrayList<String>();list.add("20140010002");list.add("20140010004");list.add("20140010006");list.add("20140010008");List<Student> students = dao.getStudentByStuNo(list);if(!students.isEmpty()){for (Student stu : students) {System.out.println(stu);}}} finally {s.close();}}

到此，MyBatis动态SQL常用的子元素就讲完了。

在上面的例子中我们都看不到MyBatis所执行的SQL有哪些，如何来查看到MyBatis所执行的SQL语句到底是什么呢？这在我们开发过程中尤其必要。其实很简单，只要引入Log4j，然后把日志级别调整为DEBUG就能看到MyBatis所执行的SQL了。下面这张图就是带有MyBatis执行SQL日志的截图

前面为了使截图不占太大的空间，我这把日志级别调整为info了，不过关于这些示例的源码中已经调整日志级别为DEBUG了，有兴趣的可以下载下来到本地运行。

下载地址：http://download.csdn.net/detail/u010397369/8311583

测试代码都集中存放在：com.mhy.dao.DynamicSqlTest类下。

鉴于个人能力所限，难免有不足之处，如果有发现问题欢迎大家指正。