Mybatis 学习之路其三：编写sql
一、利用 mysql 官网提供的测试数据
在第一个简单的程序中，我们已经写了一个简单的 sql，下面，我们要尝试写一些比较复杂的 sql。
为了能够有足够的数据够我们测试使用，我们可以从 mysql 的 github 里下载 mysql 提供的测试数据。具体操作如下：
首先，请确保安装 mysql 的虚拟机已经安装了Git，然后我们创建一个专门放置 github repositories 的文件夹，进入该文件夹运行命令：
git clone https://github.com/datacharmer/test_db.git
完成之后，该文件夹下，会多出一个 test_db 文件夹，接着我们进入到该文件夹，运行命令:
mysql -h 192.168.52.128 -u root -p < employees.sql
完成之后，继续运行命令验证：
mysql -h 192.168.52.128 -u root -p -t < test_employees_md5.sql
输出结果：
+----------------------+ | INFO | +----------------------+ | TESTING INSTALLATION | +----------------------+ +--------------+------------------+----------------------------------+ | table_name | expected_records | expected_crc | +--------------+------------------+----------------------------------+ | employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 | | departments | 9 | d1af5e170d2d1591d776d5638d71fc5f | | dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e | | dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 | | titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 | | salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 | +--------------+------------------+----------------------------------+ +--------------+------------------+----------------------------------+ | table_name | found_records | found_crc | +--------------+------------------+----------------------------------+ | employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 | | departments | 9 | d1af5e170d2d1591d776d5638d71fc5f | | dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e | | dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 | | titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 | | salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 | +--------------+------------------+----------------------------------+ +--------------+---------------+-----------+ | table_name | records_match | crc_match | +--------------+---------------+-----------+ | employees | OK | ok | | departments | OK | ok | | dept_manager | OK | ok | | dept_emp | OK | ok | | titles | OK | ok | | salaries | OK | ok | +--------------+---------------+-----------+ +------------------+ | computation_time | +------------------+ | 00:00:11 | +------------------+ +---------+--------+ | summary | result | +---------+--------+ | CRC | OK | | count | OK | +---------+--------+
然后，我们再 db.properties 里把数据库修改为 employees。
为了能够更清楚的了解程序运行中 sql 的执行，我们使用 log4j 来打印我们执行的 sql，具体配置信息参考：https://www.cnblogs.com/JiKio/p/9734384.html。
二、创建一个生产 SqlSession 的工具类
为了方面构造 SqlSession，我们新建一个 Utils 的包，并在里面创建一个 SqlSessionFactoryUtils 的工具类：
1 package Utils; 2 3 import java.io.IOException; 4 import java.io.InputStream; 5 6 import org.apache.ibatis.io.Resources; 7 import org.apache.ibatis.session.SqlSession; 8 import org.apache.ibatis.session.SqlSessionFactory; 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder; 10 11 public class SqlSessionFactoryUtils { 12 private final static Class<SqlSessionFactoryUtils> LOCK = SqlSessionFactoryUtils.class; 13 private static SqlSessionFactory sqlSessionFactory = null; 14 private SqlSessionFactoryUtils() {} 15 public static SqlSessionFactory getSqlSessionFactory() { 16 synchronized(LOCK) { 17 if(sqlSessionFactory !=null ) { 18 return sqlSessionFactory; 19 } 20 String resource = "mybatis-config.xml"; 21 InputStream inputStream; 22 try { 23 inputStream = Resources.getResourceAsStream(resource); 24 sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 25 }catch(IOException e) { 26 e.printStackTrace(); 27 return null; 28 } 29 return sqlSessionFactory; 30 } 31 } 32 public static SqlSession openSqlSession() { 33 if(sqlSessionFactory!=null) { 34 getSqlSessionFactory(); 35 } 36 return sqlSessionFactory.openSession(); 37 } 38 }
这个类是完成了上篇文章 SqlSessionTest 里创建 SqlSession 的过程，同样也是通过 Resources.getResourceAsStream 加载 mybatis-config.xml 来生成 SqlSessiongFactory 的。它的构造方法是 private 的，所以只能通过 openSession() 去获取一个固定的 sqlSession。
三、select 元素——查询语句
在 mybatis 里 select 元素是使用最频繁的，也是最强大的，首先，我们先看 select 元素有哪些配置：
| 元素 | 说明 | 备注 |
| id | 和 Mapper 的命名空间组合起来是唯一的，供mybatis 调用 | 如果不唯一，会报错 |
| patameterType | 类的全名，或者定义过的别名 | 一般表示传入参数的类型 |
| resultType | 类的全名，或者定义过的别名，在允许自动匹配的情况下，结果集将通过 java bean 的规范映射 | 不能和 resultMap 同时使用 |
| resutlMap | 自定义个映射集 | |
| flushCache | 调用 sql 后，是否要求 mybatis 清空之间查询本地缓存和二级缓存 | 默认值为 false |
| useCache | 启动二级缓存的开关，是否要求 mybatis 将此次结果缓存 | 默认值为 true |
| timeout | 设置超时参数，超时将抛出异常，单位为秒 | 默认是 jdbc 提供的时间 |
| fetchSize | 获取记录的总条数设定 | |
| statementType | 告诉 mybatis 使用哪个 JDBC 的 statement 工作，取值为 STATEMENT,PERPARED,CALLABLE | 默认值是 PERPARED |
| resultSetType | ||
| databaseId | 辨别数据库所用，本篇不说明 | |
| resultOrder | ||
| resultSets | ||
查询语句的返回结果往往对应一个 POJO，所以，我们先创建一个 employee 的 model 类：
package employee.model; import java.util.Date; public class EmployeeModel { private int empNo; private Date birth_date; private String firstName; private String lastName; private String gender; private Date hireDate; public int getEmpNo() { return empNo; } public void setEmpNo(int empNo) { this.empNo = empNo; } public Date getBirth_date() { return birth_date; } public void setBirth_date(Date birth_date) { this.birth_date = birth_date; } public String getFirstName() { return firstName; } public void setFirstName(String firstName) { this.firstName = firstName; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Date getHireDate() { return hireDate; } public void setHireDate(Date hireDate) { this.hireDate = hireDate; } public String toString() { return "empNo : " + empNo + " firstName : " + + " lastName : " + lastName; } }
然后尝试从数据库获取一条信息：
先编写 dao 的接口文件：
package employee.dao; import java.util.List; import org.apache.ibatis.session.RowBounds; import employee.model.EmployeeModel; public interface EmployeeDao { //根据员工编号获取员工信息 EmployeeModel getEmployeeByEmpNo(int id); }
然后编写其对应的 mapper 文件：
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <mapper namespace="employee.dao.EmployeeDao"> 6 <select id="getEmployeeByEmpNo" resultType="employee.model.EmployeeModel"> 7 select * from employees where emp_no = #{emp_no} 8 </select> 9 </mapper>
测试根据员工编号来获取员工信息：
SqlSession sqlSession = SqlSessionFactoryUtils.openSqlSession(); EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class); EmployeeModel employee = employeeDao.getEmployeeByEmpNo(10001); System.out.print(employee);
输出为：
empNo : 0 firstName : null lastName : null
这说明，我们的确获取到了一条记录，但是映射到 POJO 时出现了问题，为了避免这种个问题，我们最好为结果集和 POJO 指定明确的映射关系，而不是单纯的依赖 mybatis 的自动映射机制（可以通过合理的命名去实现自动映射，但是不推荐），映射代码如下，把它添加到 EmployeeMapper.xml 的 <mapper> 标签内即可：
<resultMap type = "employee.model.EmployeeModel" id = "employee"> <id column = "emp_no" property = "empNo"/> <result column = "first_name" property = "firstName"/> <result column = "birth_date" property = "birthDate"/> <result column = "last_name" property = "lastName"/> <result column = "gender" property = "gender"/> <result column = "hire_date" property = "hireDate"/> </resultMap>
这就是一个 Map，它把我们从数据库获取的结果集的每一列都和 POJO 的每个属性对应起来，mybatis 获取结果后，会自动创建一个对应的 POJO，然后把每一个属性通过 setter 的方式注入值。
<select id="getEmployeeByEmpNo" resultMap="employee"> select * from employees where emp_no = #{emp_no} </select>
用 resultMap 的 id 代表它，同时在 select 元素里不在使用 resultType 而是 resultMap。注意 resultType 表示返回的是一个 POJO，resultMap 表示返回的是自定义的 resultMap。
现在再运行主程序，输出为：
empNo : 10001 firstName : Georgi lastName : Facello
在这边，我们虽然传递了一个参数 empNo 但是在 mapper.xml 里并没有传入参数的配置，而我们依然成功的获取到了想要的信息，这说明 mybatis 是能够对参数进行自动匹配的。那么一个参数可以自动匹配，多个呢？将 getEmployeeByEmpNo 稍加修改：
//根据员工编号和姓氏来获取员工信息 EmployeeModel getEmployeeByFirstName(int id,String firstName);
<select id="getEmployeeByFirstName" resultType="employee.model.EmployeeModel"> select * from employees where emp_no = #{emp_no} and first_name = #{first_name} </select>
进行测试：
EmployeeModel employee1 = employeeDao.getEmployeeByFirstName(10001, "Georgi");
System.out.print(employee1);
抛出异常。
那么当需要多个参数时，应该如何去配置，有两种方法。
第一，通过注解来添加参数，首先是 dao:
//根据员工编号和姓氏来获取员工信息 EmployeeModel getEmployeeByFirstName(@Param("empNo")int id,@Param("firstName")String firstName);
然后再 Mapper.xml 里就可以根据括号里的名称去引用参数：
<select id="getEmployeeByFirstName" resultType="employee.model.EmployeeModel"> select * from employees where emp_no = #{empNo} and first_name = #{firstName} </select>
第二种，参数类型为 POJO
//根据员工对象获取员工信息 EmployeeModel getEmployeeByObj(EmployeeModel employee);
<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee"> select * from employees where emp_no = #{empNo} and first_name = #{firstName} </select>
传入对象为参数是，我们可以通过 #{属性名} 来获取对象内对应属性的值。这种方式在 insert 语句中尤为常见。
第三种，参数类型为 map
这个和上述类似，区别在于将信息分装在 map 里而不是 POJO 里，当然它的 resultType 也要对应的改为 "map"。
获取单独的一条记录已经实现，那么如何获取多条呢？
一般将多条数据放置在一个 List 中，所以 dao 里的返回类型应该是 List<T>：
//获取员工列表(分页) List<EmployeeModel> getEmployeeList(RowBounds rowBounds);
这边传入的参数是分页信息，通常我们会设置它两个属性，开始行 A ，和返回行数 B，返回的数据从 A 开始，一共 B 条数据。
Mapper:
<select id="getEmployeeList" resultMap="employee"> select * from employees </select>
在 Mapper 不需要对 rowBounds 参数做处理，mybatis 会自己处理。这里不难发现，dao 里我们返回的应该是 List，但是这里我们返回的事 employee。说明对于多条参数，mybatis 会自动将其封装到我们给定的容器里。
测试:
RowBounds rowBounds = new RowBounds(0,20); List<EmployeeModel> employees = employeeDao.getEmployeeList(rowBounds); for(EmployeeModel f : employees) { System.out.print(f); }
输出：
empNo : 10001 firstName : Georgi lastName : FacelloempNo : 10002 firstName : Bezalel lastName : SimmelempNo ...
在之前的 sql 语句里，我偷懒的使用了 select * 的方式去获取所有列，这样不会出错，但是不够优雅，又或者，我们有时候并不需要查询所有列。但是把列全部列出来很麻烦，所以，我们最好能的只列出 1 次，以后都可以复用。<sql> 元素可以帮我们实现。
<sql> 简而言之就是给一串 sql 设置一个简短的名称，然后以后复用这个 sql 时，使用它的名称就行，比如，employee 的列：
<sql id = "employeeCols"> emp_no,first_name,birth_date,last_name,gender,hire_date </sql>
id 就是这串代码的名称，然后，我们就可以用这个 id 去写 sql 了，比如：
<select id="getEmployeeList" resultMap="employee"> select <include refid = "employeeCols" /> from employees </select>
引用的方式为：<include refid = "{我们命名的名称}" />
四、Insert 语句
Insert 没有 select 那么全面，只需要把语句放在 <insert> 元素内即可，而插入值可以通过传入参数的方式去实现，这点在 select 里也说明了，所以 insert 这里不在赘述。
在使用 Insert 时，可能需要注意的是主键的问题。
主键回填：
主键回填时值，我们插入数据之后，这条数据的主键是由 mysql 自动递增形成的，而我们希望能够获取到这个值。再使用一次 select 显然太麻烦，我们可以利用 insert 的 userGenerateedKeys 属性来实现，这个属性默认是 false 的，当打开之后，insert 语句会通过 jdbc 的 getGeneratedKes 方法来获取到自动生成的主键（jdbc 不支持则无效）。获取到值之后，还需要指定，把值放到 POJO 的那个属性里。这就需要我们定义 insert 的 keyProperty 属性。由于我们的测试库没有自增的字段，所以不在这里做测试。
假设，我们 employee 的 emp_no 是 mysql 自动自增来生成的，在 java 里应该是这样：
EmployeeModel model = new EmployeeModel(); { //设置除了 emp_no 以外的属性值 ... } employeeDao.insert(model)
记住这个 model，它现在的 emp_no 是空的，但是 insert 之后，它会被设置成新生成的主键。
Mapper:
<insert id = "insert" parameterType = "employee.model.EmployeeModel" useGenerateKeys = "true" keyProperty = "emp_no"> insert into employees(...) values (#{},#{}...) </insert>
另外，我们也会遇到这样的情况，employee 的 emp_no 设置为当前 emp_no 的最大值 + 1，并且我不想先去 select 这个最大值。这里我们可以是用 <selectKey> 元素。
以下是示例：
dao：
//插入员工信息 void insertEmployee(EmployeeModel model);
Mapper:
<insert id = "insertEmployee" parameterType = "employee.model.EmployeeModel"> <selectKey keyProperty = "empNo" resultType="int" order = "BEFORE"> select if(max(emp_no) = null,1,max(emp_no) + 1) from employees </selectKey> insert into employees(emp_no,birth_date,first_name,last_name,gender,hire_date) values(#{empNo},#{birthDate},#{firstName},#{lastName},#{gender},#{hireDate}) </insert>
五、动态sql
动态 sql 最常用的部分就是多条件查询时使用 <if> 去判断查询条件，查询一个员工的方式除了根据员工号查询，还可以根据姓名，性别、入职日期以及它们的组合。为了省去写多个方法和 sql 的麻烦，我们通常会使用 <if> 去判断处哪些条件是有真实值传过来的，然后把这些加入到筛选条件里。
以员工查询为例，我们原来传入的查询参数是 emp_no，但是现在我们传入一个 employee 对象，然后只要该对象属性是有值得，则作为查询的参数：
<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee"> select * from employees where emp_no = #{empNo} and first_name = #{firstName} </select>
这是先前写过的一段代码，它只能满足固定的通过 empNo 和 firstName 进行查询。现在用 <if> 对其修改：
<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee"> select <include refid = "employeeCols" /> from employees <where> <if test="empNo != null"> and emp_no = #{empNo} </if> <if test="firstName != null"> and first_name = #{firstName} </if> <if test="lastName != null"> and last_name = #{lastName} </if> </where> </select>
test = “” 双引号里面是放的判断语句，用来配合 <if> 使用，<where> 的作用是，如果所有<if> 都判断为空，则不在 sql 后面跟 where 语句。如果有一个不是空，才会有 where。避免了类似 "select * from table where" 这样的情况出现。又或者可以这样：
<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee"> select <include refid = "employeeCols" /> from employees where 1=1 <if test="empNo != null"> and emp_no = #{empNo} </if> <if test="firstName != null"> and first_name = #{firstName} </if> <if test="lastName != null"> and last_name = #{lastName} </if> </select>
根据使用习惯选择即可。
有 if 自然也有 switch 不过 mybatis 使用的是 choose 元素，下面是示例：
<select id="getEmployeeByObj" parameterType ="employee.model.EmployeeModel" resultMap="employee"> select <include refid = "employeeCols" /> from employees where 1=1 <choose> <when test="empNo != null"> and emp_no = #{empNo} </when> <when test="firstName != null"> and first_name = #{firstName} </when> <when test="lastName != null"> and last_name = #{lastName} </when> <otherwise> and birth_date = #{birthDate} </otherwise> </choose> </select>
chosse when otherwise 对应 java 里的 switch case default，所以不难理解。
<where> 元素可以防止出现，where 多余的情况，<set> 元素则可以避免出现 , 多余的情况。
