MyBatis

在 Java 中使用 JDBC 来访问数据库，实现数据的增删改查。但是在实际工作中，单纯使用 JDBC 来编写程序，对于复杂 SQL 的处理往往非常繁琐，特别是参数设置以及获取结果集，还有将 SQL 和程序代码写在一起，也不易阅读和维护。为解决上述问题，就出现了 ORM（Object Relational Mapping）框架，其中 MyBatis 是比较常用的 ORM 框架。

第一个程序

（1）在 MySQL 中创建表并插入数据

create table tbl_employee(
id int(11) primary key auto_increment,
last_name varchar(255),
gender char(1),
email varchar(255));
INSERT INTO tbl_employee (last_name, gender, email) VALUES ('tom', '0', 'tom@stone.com');

（2）创建实体类

package com.stone.mybatis.bean;

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}
}

（3）创建实体类接口

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
}

（4）导入 JAR 包

mybatis-3.4.6.jar
ant-1.9.6.jar
ant-launcher-1.9.6.jar
asm-5.2.jar
cglib-3.2.5.jar
commons-logging-1.2.jar
javassist-3.22.0-GA.jar
log4j-1.2.17.jar
log4j-api-2.3.jar
log4j-core-2.3.jar
ognl-3.1.16.jar
slf4j-api-1.7.25.jar
slf4j-log4j12-1.7.25.jar
mysql-connector-java-5.1.23-bin.jar

（5）创建日志配置文件

### 设置###
log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

（6）创建 MyBatis 配置文件 mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://192.168.8.137:3306/mybatis" />
				<property name="username" value="stone" />
				<property name="password" value="stone" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- 写好的 SQL 映射文件 EmployeeMapper.xml 一定要注册到全局配置文件中 mybatis-config.xml" -->
	<mappers>
		<mapper resource="EmployeeMapper.xml" />
	</mappers>
</configuration>

（7）配置 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.stone.mybatis.dao.EmployeeMapper">
<!-- 
namespace：表示名称空间,指定为接口的全类名
id：唯一标识，指定为接口的方法名称
resultType：返回值类型
#{id}：从传递过来的参数中取出 id 值
 -->	
	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee">
		select id,last_name lastName,gender,email from tbl_employee where id = #{id}
	</select>
</mapper>

（8）创建测试类

package com.stone.mybatis.test;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.stone.mybatis.bean.Employee;
import com.stone.mybatis.dao.EmployeeMapper;

/**
 * 1、接口式编程
 * 原生：		Dao		===>	DaoImpl
 * mybatis	Mapper	===>	xxxMapper.xml
 * 
 * 2、SqlSession 代表和数据库的一次会话，用完必须关闭。
 * 3、SqlSession 和 Connection 一样都是非线程安全，每次使用都应该去获取新的对象
 * 4、mapper 接口没有实现类，但是 mybatis 会为这个接口生成一个代理对象（将接口和 XML 进行绑定）
 * EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
 * 5、两个重要的配置文件：
 * mybatis 的全局配置文件：包含数据库的连接池信息，事物管理器信息等系统运行环境信息
 * SQL 映射文件保存了每一个 SQL 语句的映射信息。
 * 
 *
 */
public class MybatisTest {
	
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		return sqlSessionFactory;
	}
	
	/**
	 * 1、根据 XML 配置文件（全局配置文件）创建一个 SqlSessionFactory 对象
	 * 2、SQL 映射文件，配置每一个 SQL 以及封装规则
	 * 3、将 SQL 映射文件注册到全局配置文件中
	 * 4、根据全局配置文件得到 SqlSessionFactory
	 * 5、使用 SqlSessionFactory 获取到 SqlSession 对象执行增删改查，一个 SqlSession 对象就是代码和数据库的一次会话，用完关闭
	 * 6、使用 SQL 的唯一标识来告诉 Mybatis 执行那个 SQL，SQL 都是保存在 SQL 映射文件中
	 * @throws IOException
	 */
	@Test
	public void test() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		
		//7、获取 SqlSession 实例，能直接执行已经映射的 SQL 语句
		SqlSession sqlSession = sqlSessionFactory.openSession();
		try {
			Employee employee = sqlSession.selectOne("EmployeeMapper.selectEmp", 1);
			System.out.println(employee);
		} finally {
			sqlSession.close();
		}
	}

	//接口式编程
	@Test
	public void test01() throws IOException {
		//1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		
		//2、获取 SqlSession 对象
		SqlSession openSession = sqlSessionFactory.openSession();
		
		//3、获取接口的实现类对象，会为接口自动创建一个代理对象，代理对象去执行增删改查方法
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = mapper.getEmpById(1);
			System.out.println(mapper.getClass());
			System.out.println(employee);
		} finally {
			openSession.close();
		}
	}
}

全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- 
		1、MyBatis 可以使用 properties 来引入外部 properties 配置文件的内容 ,了解（交由 spring 管理）
		resource：引入类路径下的资源
		url：引入网络路径或者磁盘路径下的资源
	-->
	<properties resource="db.properties"></properties>
	
	<!-- 
		2、settings 包含很多重要的设置项
		setting：用来设置每一个设置项
		name：设置项名称
		value：设置项取值
	 -->
	<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<setting name="jdbcTypeForNull" value="NULL"/>
	</settings>
	
	<!-- 3、typeAliases：别名处理器，可以为我们的 java 类型起别名，别名不区分大小写，建议在 Mapper 的 resultType 还是使用全类名 -->
	<typeAliases>
		<!-- 
			3.1、typeAlias：为某个 java 类型起别名
			type：指定要起别名的类型全类名；默认别名为类名小写
			alias：指定新的别名
		 -->
		<!-- <typeAlias type="com.stone.mybatis.bean.Employee" alias="emp"/> -->
		
		<!-- 
			3.2、package：为某个包下的所有类批量起别名 
			name：指定包名（为当前包以及下面所有子包的每一个类都起一个别名-类名小写）
		-->
		<package name="com.stone.mybatis.bean"/>
		
		<!—3.3、批量起别名的情况下，使用 @Alias 注解为某个类型指定新的别名 -->
	</typeAliases>
	
	<!-- 4、environments：可以配置多种环境，default 指定使用某种环境，可以达到快速切换 -->
	<environments default="mysql">
		<!-- 
			environment：配置一个具体的环境 ，id 表示当前环境的唯一标识
			必须有以下两个标签：
			transactionManager：事物管理器，
				type：指定事物管理器类型：JDBC(JdbcTransactionFactory)|MANAGED(ManagedTransactionFactory)
				自定义事物管理器：使用 TransactionFactory 接口，type 指定全类名
				一般交给 Spring 来进行事务管理
			dataSource：数据源；
				type：数据源类型：UNPOOLED|POOLED|JNDI
				自定义数据源：实现 DataSourceFactory 接口，type 是全类名
		-->
		<environment id="oracle">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${oracle.driverClass}" />
				<property name="url" value="${oracle.jdbcUrl}" />
				<property name="username" value="${oracle.username}" />
				<property name="password" value="${oracle.password}" />
			</dataSource>
		</environment>
		
		<environment id="mysql">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${mysql.driverClass}" />
				<property name="url" value="${mysql.jdbcUrl}" />
				<property name="username" value="${mysql.username}" />
				<property name="password" value="${mysql.password}" />
			</dataSource>
		</environment>
	</environments>
	
	<!-- 
		5、databaseIdProvider：支持多数据库厂商
		type="DB_VENDOR"，VendorDatabaseIdProvider，得到数据库厂商的标识（驱动 getDatabaseProductName()），
		Mybatis 就能根据数据库厂商标识来执行不同的SQL：MySQL，Oracle，SQL Server
	-->
	<databaseIdProvider type="DB_VENDOR">
		<!-- 为不同的数据库厂商起别名 -->
		<property name="MySQL" value="mysql"/>
		<property name="Oracle" value="oracle"/>
		<property name="SQL Server" value="sqlserver"/>
	</databaseIdProvider>
	
	<!-- 写好的 SQL 映射文件 EmployeeMapper.xml 一定要注册到全局配置文件中 mybatis-config.xml" -->
	<!-- 6、将 SQL 映射注册到全局配置中 -->
	<mappers>
		<!-- 
			mapper：注册一个 SQL 映射
				1）注册配置文件：
				resource：引用类路径下的 SQL 映射文件
				url：引用网络路径或者磁盘路径
				2）注册接口：
				class：注册接口
					1、有 SQL 映射文件，映射文件名必须和接口同名，并且放在与接口同一目录下 
					2、没有 SQL 映射文件，所有的 SQL 都是利用注解写在接口上
					推荐：
						重要复杂的 Dao 接口来写 SQL 映射文件
						不重要简单的 Dao 接口为了开发快速可以使用注解
		-->
		<!-- <mapper resource="com/stone/mybatis/mapper/EmployeeMapper.xml" /> -->
		<!-- <mapper class="com.stone.mybatis.dao.EmployeeMapperAnnotation"/> -->
		
		<!-- 批量注册 ，需要将映射文件放在 Dao 接口同一个包下-->
		<package name="com.stone.mybatis.dao"/>		
	</mappers>		
</configuration>

映射文件

在接口中创建对应的方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
	
	public void insertEmp(Employee employee);
	
	public boolean updateEmp(Employee employee);
	
	public void deleteEmpById(Integer id);
}

在映射文件中创建对应的 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.stone.mybatis.dao.EmployeeMapper">
<!-- 
namespace：表示名称空间,指定为接口的全类名
id：唯一标识，指定为接口的方法名称
parameterType：参数类型，可以不传，MyBatis 会根据 TypeHandler 自动推断
resultType：返回值类型
#{id}：从传递过来的参数中取出 id 值
 -->	
 	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where id = #{id}
	</select> 
 	
 	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee" databaseId="mysql">
		select * from tbl_employee where id = #{id}
	</select> 
	
	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee" databaseId="oracle">
		select employee_id id,last_name lastName,email from employees where employee_id=#{id}
	</select>
	
	<!-- public void insertEmp(Employee employee); -->
	<!-- parameterType：参数类型，可以省略 -->
	<!-- 
		获取自增主键的值：
			MySQL 支持自增主键，自增主键值的获取，MyBatis 也是利用 statement.getGeneratedKeys()
			useGeneratedKeys="true"：使用自增主键获取主键值策略
			keyProperty:指定对应的主键属性，也就是 MyBatis 获取到主键值以后，将这个值封装给 Javabean 的哪个属性
	 -->
	<insert id="insertEmp" useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
		insert into tbl_employee(last_name,gender,email)
		values(#{lastName},#{gender},#{email})
	</insert>
	
	<!-- 
		Oracle 不支持自增：Oracle 使用序列来模拟自增，每次插入的数据的主键是从序列中取值。
	 -->
	 <insert id="insertEmp" databaseId="oracle">
	 	<!-- 
	 		keyProperty：查出的主键值封装给 Javabean 的哪个属性
	 		order="BEFORE"：当前 SQL 在插入 SQL 之前运行
	 		resultType：查出的数据返回值类型
	 	-->
	 	<!-- 
	 		<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
	 			select emp_seq.nextval from dual
	 		</selectKey> 
	 	-->
		<!-- 插入时的主键是从序列中拿到的 -->
		<!-- 
			insert into emp(empno,ename)
			values(#{id},#{lastName}) 
		-->
		
		
		<!-- 也可以直接使用序列进行插入 -->
		insert into emp(empno,ename)
		values(emp_seq.nextval,#{lastName})
		
		<!-- 
			使用 order="AFTER"，先执行插入 SQL（从序列中取值），再来获取 id 的值 
			如果一次插入多条数据，则取到的是最后一个 id 的值
		-->
		<selectKey keyProperty="id" order="AFTER" resultType="Integer">
			select emp_seq.currval from dual
		</selectKey>
	</insert>
	
	<!-- public void updateEmp(Employee employee); -->
	<update id="updateEmp">
		update tbl_employee 
		set last_name=#{lastName},gender=#{gender},email=#{email}
		where id=#{id}
	</update>
	
	<!-- public void deleteEmpById(Integer id); -->
	<delete id="deleteEmpById">
		delete from tbl_employee where id=#{id}
	</delete>
	
</mapper>

测试：

	/**
	 * 测试增删改
	 * 1、MyBatis 允许增删改直接定义以下类型返回值：Integer、Long、Boolean、void
	 * 2、需要手动提交数据
	 * sqlSessionFactory.openSession() ===> 手动提交
	 * sqlSessionFactory.openSession(true) ===> 自动提交
	 * @throws IOException
	 */
	@Test
	public void test03() throws IOException {
		// 1、获取SQLSessionFactory对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取SqlSession对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			
			//测试添加
			Employee employee = new Employee("jerry", "jerry@stone.com", "1");
			mapper.insertEmp(employee);
			System.out.println(employee.getId());
			
			//测试修改
			//Employee employee2 = new Employee(1, "tom", "tom@stone.com", "1");
			//boolean updateEmp = mapper.updateEmp(employee2);
			//System.out.println(updateEmp);
			
			//测试删除
			//mapper.deleteEmpById(2);
			
			//3、手动提交
			openSession.commit();
		} finally {
			openSession.close();
		}
	}

SELECT 返回 List

接口方法：

	public List<Employee> getEmpsByLastNameLike(String lastName);

对应 SQL：

	<!-- public List<Employee> getEmpsByLastNameLike(String lastName); -->
	<!-- resultType：如果返回的是一个集合，要写集合中元素的类型 -->
	<select id="getEmpsByLastNameLike" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where last_name like #{lastName}
	</select>

测试：

	@Test
	public void test05() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			List<Employee> empsByLastNameLike = mapper.getEmpsByLastNameLike("%e%");
			for (Employee employee : empsByLastNameLike) {
				System.out.println(employee);
			}
		} finally {
			openSession.close();
		}
	}

输出：

Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]
Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1]

SELECT 返回 Map

接口方法：

	//返回一条记录的 map，key 就是列名，值就是对应的值
	public Map<String, Object> getEmpByIdReturnMap(Integer id);
	
	//多条记录封装一个 map：Map<Integer, Employee>：键是这条记录的主键，值是记录封装后的 Javabean
	//@MapKey("id") 告诉 MyBatis 封装这个 map 的时候使用哪个属性作为 map 的 key
	@MapKey("id")
	public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName);

对应 SQL：

	<!-- public Map<String, Object> getEmpByIdReturnMap(Integer id); -->
	<select id="getEmpByIdReturnMap" resultType="map">
		select * from tbl_employee where id=#{id}
	</select>
	
	<!-- public Map<Integer, Employee> getEmpByLastNameLikeReturnMap(String lastName); -->
	<select id="getEmpByLastNameLikeReturnMap" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where last_name like #{lastName}
	</select>

测试：

	@Test
	public void test06() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Map<String, Object> empByIdReturnMap = mapper.getEmpByIdReturnMap(1);
			System.out.println(empByIdReturnMap);
		} finally {
			openSession.close();
		}
	}
	//输出结果：{gender=1, last_name=tom, id=1, email=tom@stone.com}
	
	@Test
	public void test07() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Map<Integer, Employee> empByLastNameLikeReturnMap = mapper.getEmpByLastNameLikeReturnMap("%e%");
			System.out.println(empByLastNameLikeReturnMap);
		} finally {
			openSession.close();
		}
	}
	//输出结果：{3=Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1], 4=Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1]}

自定义结果映射规则

接口方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public Employee getEmpById(Integer id);
	
}

对应 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.stone.mybatis.dao.EmployeeMapperPlus">
	
	<!-- 
		resultMap：自定义某个 Javabean 的封装规则
		type：自定义规则的 Java 类型
		id：唯一 id，方便引用
	-->
	<resultMap type="com.stone.mybatis.bean.Employee" id="MyEmp">
		<!-- 
			id：指定主键列的封装规则 
			column：指定唯一列
			property：指定对应的 Javabean 属性
		-->
		<id column="id" property="id"/>
		
		<!-- result：定义普通列封装规则 -->
		<result column="last_name" property="lastName"/>
		
		<!-- 其他不指定的列会自动封装，推荐只要写 resultMap 就把全部的映射规则都写上 -->
		<result column="email" property="email"/>
		<result column="gender" property="gender"/>
	</resultMap>
	
	<!-- public Employee getEmpById(Integer id); -->
	<select id="getEmpById" resultMap="MyEmp">
		select * from tbl_employee where id=#{id}
	</select>
</mapper>

测试：

	@Test
	public void test08() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
			Employee empById = mapper.getEmpById(1);
			System.out.println(empById);
		} finally {
			openSession.close();
		}
	}

输出：

Employee [id=1, lastName=tom, email=tom@stone.com, gender=1]

关联查询

级联属性封装结果

创建表和约束：

CREATE TABLE tbl_dept (
  id int(11) NOT NULL AUTO_INCREMENT,
  dept_name varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ;
alter table tbl_employee add column d_id int(11);
alter table tbl_employee add constraint fk_emp_dept foreign key(d_id) references tbl_dept(id);

接口方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public Employee getEmpById(Integer id);
	
	public Employee getEmpAndDept(Integer id);
	
}

对应 SQL：

	<!-- 
		场景1：查询 Employee 的同时查询员工的部门
	 -->
	 <!-- 联合查询：级联属性封装结果集 -->
	 <resultMap type="com.stone.mybatis.bean.Employee" id="MyDifEmp">
	 	<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="gender" property="gender"/>
	 	<result column="did" property="dept.id"/>
	 	<result column="dept_name" property="dept.departmentName"/>
	 </resultMap>
	<!-- public Employee getEmpAndDept(Integer id); -->
	<select id="getEmpAndDept" resultMap="MyDifEmp">
		select e.id id,e.last_name last_name,e.gender gender,
				e.d_id d_id,d.id did,d.dept_name dept_name
		from tbl_employee e,tbl_dept d
		where e.d_id=d.id and e.id=#{id}
	</select>

测试：

	@Test
	public void test09() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
			Employee empAndDept = mapper.getEmpAndDept(1);
			System.out.println(empAndDept);
			System.out.println(empAndDept.getDept());
		} finally {
			openSession.close();
		}
	}

输出：

Employee [id=1, lastName=tom, email=null, gender=1]
Department [id=1, departmentName=开发部]

定义关联对象封装规则

关联员工表和部门表，查询员工信息及其部门名称，这里是 1 对 1 关系。

对应 SQL：

	 <!-- 使用 association 定义关联的单个对象的封装规则 -->
	 <resultMap type="com.stone.mybatis.bean.Employee" id="MyDifEmp2">
	 	<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="gender" property="gender"/>
	 	<!-- 
	 		association：可以指定联合的 Javabean 对象
	 		property="dept"：指定哪个属性是联合的对象
	 		javaType：指定这个属性对象的类型【不能省略】
	 	 -->
	 	<association property="dept" javaType="com.stone.mybatis.bean.Department">
	 		<id column="did" property="id"/>
	 		<result column="dept_name" property="departmentName"/>
	 	</association>
	 </resultMap>
	 
	<!-- public Employee getEmpAndDept(Integer id); -->
	<select id="getEmpAndDept" resultMap="MyDifEmp2">
		select e.id id,e.last_name last_name,e.gender gender,
				e.d_id d_id,d.id did,d.dept_name dept_name
		from tbl_employee e,tbl_dept d
		where e.d_id=d.id and e.id=#{id}
	</select>

分步查询

接口方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public Employee getEmpById(Integer id);
	
	public Employee getEmpAndDept(Integer id);
	
	public Employee getEmpByIdStep(Integer id);
}

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Department;

public interface DepartmentMapper {
	
	public Department getDeptById(Integer id);
	
}

对应 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.stone.mybatis.dao.DepartmentMapper">
	
	<!-- public Department getDeptById(Integer id); -->
	<select id="getDeptById" resultType="com.stone.mybatis.bean.Department">
		select id,dept_name departmentName from tbl_dept where id=#{id}
	</select>
	
</mapper>

	<!-- 
		使用 association 进行分步查询 
		1、先按照员工 id 查询员工信息
		2、根据查询员工信息的 d_id 值去部门表查询部门信息
		3、部门设置到员工中
	-->
	<resultMap type="com.stone.mybatis.bean.Employee" id="MyEmpByStep">
		<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="email" property="email"/>
	 	<result column="gender" property="gender"/>
	 	<!-- 
	 		association 定义关联对象的封装规则
	 		select： 表明当前属性是调用 select 指定的方法查询的结果
	 		column：指定将哪一列的值传给这个方法
	 		流程：使用 select 指定的方法（传入 column 指定的这列参数的值）查出对象，并封装给 property 属性
	 	-->
	 	<association property="dept" select="com.stone.mybatis.dao.DepartmentMapper.getDeptById" column="d_id">	
	 	</association>
	</resultMap>
	<!-- public Employee getEmpByIdStep(Integer id); -->
	<select id="getEmpByIdStep" resultMap="MyEmpByStep">
		select * from tbl_employee where id=#{id}
	</select>

测试：

	@Test
	public void test10() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
			Employee empByIdStep = mapper.getEmpByIdStep(1);
			System.out.println(empByIdStep);
			System.out.println(empByIdStep.getDept());
		} finally {
			openSession.close();
		}
	}

输出：

==>  Preparing: select * from tbl_employee where id=? 
==> Parameters: 1(Integer)
====>  Preparing: select id,dept_name departmentName from tbl_dept where id=? 
====> Parameters: 1(Integer)
<====      Total: 1
<==      Total: 1
Employee [id=1, lastName=tom, email=tom@stone.com, gender=1]
Department [id=1, departmentName=开发部]

延迟加载

延迟加载其实就是将数据加载时机推迟，比如推迟嵌套查询的执行时机。在 MyBatis 中经常用到关联查询，但是并不是任何时候都需要立即返回关联查询结果。比如查询订单信息，并不一定需要及时返回订单对应的产品信息，查询商品分类信息并不一定要及时返回该类别下有哪些产品，这种情况一下需要一种机制，当需要查看时，再执行查询，返回需要的结果集，这种需求在 MyBatis 中可以使用延迟加载机制来实现。延迟加载可以实现先查询主表，按需实时做关联查询，返回关联表结果集，一定程度上提高了效率。

全局配置文件：

	<!-- 
		2、settings 包含很多重要的设置项
		setting：用来设置每一个设置项
		name：设置项名称
		value：设置项取值
	 -->
	<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<setting name="jdbcTypeForNull" value="NULL"/>
		<!-- 显式的指定每个需要更改的配置的值，即使是默认的。防止版本更新带来的问题 -->
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>

测试：

	@Test
	public void test11() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
			Employee empByIdStep = mapper.getEmpByIdStep(1);
			System.out.println(empByIdStep.getLastName());
			System.out.println(empByIdStep.getDept());
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 10:37:27,672 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==>  Preparing: select * from tbl_employee where id=? 
2018-12-14 10:37:27,703 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==> Parameters: 1(Integer)
2018-12-14 10:37:27,782 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - <==      Total: 1
tom
2018-12-14 10:37:27,783 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - ==>  Preparing: select id,dept_name departmentName from tbl_dept where id=? 
2018-12-14 10:37:27,783 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - ==> Parameters: 1(Integer)
2018-12-14 10:37:27,785 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - <==      Total: 1
Department [id=1, departmentName=开发部]

定义关联集合封装规则

关联员工表和部门表，查询部门及其对应的员工，这里是 1 对 N 关系。

接口方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Department;

public interface DepartmentMapper {
	
	public Department getDeptById(Integer id);
	
	public Department getDeptByIdPlus(Integer id);
	
}

对应 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.stone.mybatis.dao.DepartmentMapper">
	
	<!-- public Department getDeptById(Integer id); -->
	<select id="getDeptById" resultType="com.stone.mybatis.bean.Department">
		select id,dept_name departmentName from tbl_dept where id=#{id}
	</select>
	
	<!-- 嵌套结果集的方式，使用 collection 标签定义关联的集合类型的封装属性规则 -->
	<resultMap type="com.stone.mybatis.bean.Department" id="MyDept">
		<id column="did" property="id"/>
		<result column="dept_name" property="departmentName"/>
		<!-- 
			collection：定义集合类型的属性的封装规则 
			ofType：指定集合里面元素的类型
		-->
		<collection property="emps" ofType="com.stone.mybatis.bean.Employee">
			<!-- 定义这个集合中元素的封装规则 -->
			<id column="eid" property="id"/>
			<id column="last_name" property="lastName"/>
			<id column="email" property="email"/>
			<id column="gender" property="gender"/>
		</collection>
	</resultMap>
	<!-- public Department getDeptByIdPlus(Integer id); -->
	<select id="getDeptByIdPlus" resultMap="MyDept">
		select d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender
		from tbl_dept d left join tbl_employee e on d.id=e.d_id where d.id=#{id}
	</select>
	
</mapper>

测试：

	@Test
	public void test12() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
			Department department = mapper.getDeptByIdPlus(1);
			System.out.println(department);
			System.out.println(department.getEmps());
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 13:17:11,604 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdPlus] - ==>  Preparing: select d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,e.email email,e.gender gender from tbl_dept d left join tbl_employee e on d.id=e.d_id where d.id=? 
2018-12-14 13:17:11,661 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdPlus] - ==> Parameters: 1(Integer)
2018-12-14 13:17:11,700 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdPlus] - <==      Total: 3
Department [id=1, departmentName=开发部]
[Employee [id=1, lastName=tom, email=tom@stone.com, gender=1], Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1], Employee [id=6, lastName=null, email=jerry3@stone.com, gender=1]]

分步查询及延迟加载

接口方法：

package com.stone.mybatis.dao;

import com.stone.mybatis.bean.Department;

public interface DepartmentMapper {
	
	public Department getDeptById(Integer id);
	
	public Department getDeptByIdPlus(Integer id);
	
	public Department getDeptByIdStep(Integer id);
}

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperPlus {
	
	public Employee getEmpById(Integer id);
	
	public Employee getEmpAndDept(Integer id);
	
	public Employee getEmpByIdStep(Integer id);
	
	public List<Employee> getEmpsByDeptId(Integer deptId);
}

对应 SQL：

	<!-- public List<Employee> getEmpsByDeptId(Integer deptId); -->
	<select id="getEmpsByDeptId" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where d_id=#{deptId}
	</select>

	<!-- collection：分步查询 -->
	<resultMap type="com.stone.mybatis.bean.Department" id="MyDeptStep">
		<id column="id" property="id"/>
		<result column="dept_name" property="departmentName"/>
			<!-- 
				扩展：collection 中多列的值传递过去：将多列的值封装 map 传递
				column="{key1=column1,key2=column2}" 
				fetchType="lazy"：表示使用延迟加载
					lazy：延迟
					eager：立即
			-->
		<collection property="emps" select="com.stone.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId" column="{deptId=id}" fetchType="lazy"></collection>
	</resultMap>
	<!-- public Department getDeptByIdStep(Integer id); -->
	<select id="getDeptByIdStep" resultMap="MyDeptStep">
		select id,dept_name from tbl_dept where id=#{id}
	</select>

测试：

	@Test
	public void test13() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
			Department department = mapper.getDeptByIdStep(1);
			System.out.println(department.getDepartmentName());
			System.out.println(department.getEmps());
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 14:03:22,262 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdStep] - ==>  Preparing: select id,dept_name from tbl_dept where id=? 
2018-12-14 14:03:22,290 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdStep] - ==> Parameters: 1(Integer)
2018-12-14 14:03:22,351 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptByIdStep] - <==      Total: 1
开发部
2018-12-14 14:03:22,352 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId] - ==>  Preparing: select * from tbl_employee where d_id=? 
2018-12-14 14:03:22,353 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId] - ==> Parameters: 1(Integer)
2018-12-14 14:03:22,356 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpsByDeptId] - <==      Total: 3
[Employee [id=1, lastName=tom, email=tom@stone.com, gender=1], Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1], Employee [id=6, lastName=null, email=jerry3@stone.com, gender=1]]

鉴别器

对应 SQL：

	<!-- 
		<discriminator javaType=""></discriminator> 
		鉴别器：MyBatis 可以使用 discriminator 判断某列的值，然后根据某列的值改变封装行为
		封装 Employee：
			如果查出来的是女生，就把部门信息查询出来，否则不查询
			如果查出来的是男生，把 last_name 这一列的值赋值给 email
	-->
	<resultMap type="com.stone.mybatis.bean.Employee" id="MyEmpDis">
		<id column="id" property="id"/>
	 	<result column="last_name" property="lastName"/>
	 	<result column="email" property="email"/>
	 	<result column="gender" property="gender"/>
	 	<!-- 
	 		column：指定判定的列名 
	 		javaType：列值对应的java类型
	 	-->
	 	<discriminator javaType="string" column="gender">
	 		<!-- 女生 -->
	 		<!-- resultType：指定封装的结果类型 -->
	 		<case value="0" resultType="com.stone.mybatis.bean.Employee">
	 			<association property="dept" select="com.stone.mybatis.dao.DepartmentMapper.getDeptById" column="d_id">	
	 			</association>
	 		</case>
	 		<!-- 男生，把last_name这一列的值赋值给email -->
	 		<case value="1" resultType="com.stone.mybatis.bean.Employee">
	 			<id column="id" property="id"/>
			 	<result column="last_name" property="lastName"/>
			 	<result column="last_name" property="email"/>
			 	<result column="gender" property="gender"/>
	 		</case>
	 	</discriminator>
	</resultMap>
	<select id="getEmpByIdStep" resultMap="MyEmpDis">
		select * from tbl_employee where id=#{id}
	</select>

测试：

	@Test
	public void test11() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
			Employee empByIdStep = mapper.getEmpByIdStep(1);
			System.out.println(empByIdStep);
			System.out.println(empByIdStep.getDept());
			empByIdStep = mapper.getEmpByIdStep(3);
			System.out.println(empByIdStep);
			System.out.println(empByIdStep.getDept());
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 14:28:57,869 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==>  Preparing: select * from tbl_employee where id=? 
2018-12-14 14:28:57,901 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==> Parameters: 1(Integer)
2018-12-14 14:28:57,967 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - <==      Total: 1
2018-12-14 14:28:57,968 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - ==>  Preparing: select id,dept_name departmentName from tbl_dept where id=? 
2018-12-14 14:28:57,968 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - ==> Parameters: 1(Integer)
2018-12-14 14:28:57,971 [main] DEBUG [com.stone.mybatis.dao.DepartmentMapper.getDeptById] - <==      Total: 1
Employee [id=1, lastName=tom, email=tom@stone.com, gender=0]
Department [id=1, departmentName=开发部]
2018-12-14 14:28:57,972 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==>  Preparing: select * from tbl_employee where id=? 
2018-12-14 14:28:57,972 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - ==> Parameters: 3(Integer)
2018-12-14 14:28:57,985 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperPlus.getEmpByIdStep] - <==      Total: 1
Employee [id=3, lastName=jerry, email=jerry, gender=1]
null

动态 SQL

使用 MyBatis 可以大大简化动态 SQL 的开发，无需在程序中根据不同条件拼接 SQL 语句。

if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
}

对应 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.stone.mybatis.dao.EmployeeMapperDynamicSQL">
	
	<!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
	<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
	<select id="getEmpsByConditionIf" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee
		<!-- 
			查询的时候如果某些条件没带，可能 SQL 拼接会有问题 
			解决方法：
				1、给 where 后面加上 1=1，以后的条件都加上 and
				2、MyBatis 使用 where 标签来将所有的查询条件包括在内
				Mybatis 就会将 where 标签中拼接的 SQL 多出来的 and 或者 or 去掉
		-->
		<where>
			<!-- test：判定表达式（OGNL），从参数中取值进行判断，遇到特殊符号应该使用转义字符 -->
			<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>

</mapper>

测试：

	@Test
	public void test14() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee = new Employee(null, "%e%", "jerry@stone.com", null);
			List<Employee> emps = mapper.getEmpsByConditionIf(employee);
			for (Employee employee2 : emps) {
				System.out.println(employee2);
			}
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 15:38:56,989 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionIf] - ==>  Preparing: select * from tbl_employee WHERE last_name like ? and email=? 
2018-12-14 15:38:57,020 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionIf] - ==> Parameters: %e%(String), jerry@stone.com(String)
2018-12-14 15:38:57,041 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionIf] - <==      Total: 2
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]
Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1]

trim

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);

}

对应 SQL：

	<!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
	<select id="getEmpsByConditionTrim" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee
		<!-- 
			trim：自定义字符串的截取规则，解决后面多出的 and 或者 or，where 标签不能解决 
			prefix=""：前缀，整个 trim 标签体拼接后加一个前缀
			prefixOverrides=""：前缀覆盖，去掉整个字符串前面多余的字符
			suffix=""：后缀，整个 trim 标签体拼接后加一个后缀
			suffixOverrides=""：后缀覆盖，去掉整个字符串后面多余的字符
		-->
		<trim prefix="where" suffixOverrides="and">
			<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>
		</trim>
	</select>

测试：

		@Test
		public void test15() throws IOException {
			// 1、获取 SQLSessionFactory 对象
			SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

			// 2、获取 SqlSession 对象，不会自动提交
			SqlSession openSession = sqlSessionFactory.openSession();
			
			try {
				EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
				Employee employee = new Employee(null, "%e%", "jerry@stone.com", null);
				List<Employee> emps = mapper.getEmpsByConditionTrim(employee);
				for (Employee employee2 : emps) {
					System.out.println(employee2);
				}
			} finally {
				openSession.close();
			}
		}

输出：

2018-12-14 15:53:37,339 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionTrim] - ==>  Preparing: select * from tbl_employee where last_name like ? and email=? 
2018-12-14 15:53:37,367 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionTrim] - ==> Parameters: %e%(String), jerry@stone.com(String)
2018-12-14 15:53:37,389 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionTrim] - <==      Total: 2
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]
Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1]

choose

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	
	public List<Employee> getEmpsByConditionChoose(Employee employee);

}

对应 SQL：

	<!-- 如果带了 id 就用 id 查，如果带了 lastName 就用 lastName 查，只会进入其中一个 -->
	<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
	<select id="getEmpsByConditionChoose" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee
		<where>
			<choose>
				<when test="id!=null">
					id=#{id}
				</when>
				<when test="lastName!=null">
					last_name like #{lastName}
				</when>
				<when test="email!=null">
					email=#{email}
				</when>
				<otherwise>
					gender=0
				</otherwise>
			</choose>
		</where>
	</select>

测试：

		@Test
		public void test16() throws IOException {
			// 1、获取 SQLSessionFactory 对象
			SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

			// 2、获取 SqlSession 对象，不会自动提交
			SqlSession openSession = sqlSessionFactory.openSession();
			
			try {
				EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
				Employee employee = new Employee(3, "%e%", "jerry@stone.com", null);
				List<Employee> emps = mapper.getEmpsByConditionChoose(employee);
				for (Employee employee2 : emps) {
					System.out.println(employee2);
				}
			} finally {
				openSession.close();
			}
		}

输出：

2018-12-14 16:14:41,174 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionChoose] - ==>  Preparing: select * from tbl_employee WHERE id=? 
2018-12-14 16:14:41,203 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionChoose] - ==> Parameters: 3(Integer)
2018-12-14 16:14:41,224 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionChoose] - <==      Total: 1
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]

set

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	
	public List<Employee> getEmpsByConditionChoose(Employee employee);
	
	public void updateEmp(Employee employee);
}

对应 SQL：

	<!-- set 标签用于更新 -->
	<!-- public void updateEmp(Employee employee); -->
	<update id="updateEmp">
		update tbl_employee
		<set>
		<!-- <trim prefix="set" suffixOverrides=","></trim> -->
			<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 test17() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee = new Employee(1, "Admin", null, null);
			mapper.updateEmp(employee);
			openSession.commit();
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 16:30:33,224 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.updateEmp] - ==>  Preparing: update tbl_employee SET last_name=? where id=? 
2018-12-14 16:30:33,253 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.updateEmp] - ==> Parameters: Admin(String), 1(Integer)
2018-12-14 16:30:33,258 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.updateEmp] - <==    Updates: 1

foreach

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	
	public List<Employee> getEmpsByConditionChoose(Employee employee);
	
	public void updateEmp(Employee employee);
	
	public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
}

对应 SQL：

	<!-- public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
	<select id="getEmpsByConditionForeach" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where id in 
		<!-- 
			collection：指定要遍历的集合 
				list 类型的参数会特殊处理封装在 map 里，map 的 key 就叫 list
			item：将当前遍历出的元素赋值给指定的变量
			separator：每个元素之间的分隔符
			open：遍历出所有结果拼接一个开始的字符
			close：遍历出所有结果拼接一个结束的字符
			index：
				遍历 list 的时候表示的是索引，item 就是当前值，
				遍历 map 的时候表示的是 map 的 key，item 就是 map 的值
			#{变量名}就能取出变量的值，也就是当前遍历出的元素
		-->
		<foreach collection="ids" item="item_id" separator="," open="(" close=")">
			#{item_id}
		</foreach>
	</select>

测试：

	@Test
	public void test18() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> empsByConditionForeach = mapper.getEmpsByConditionForeach(Arrays.asList(1,3));
			for (Employee employee : empsByConditionForeach) {
				System.out.println(employee);
			}
			
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 17:01:07,852 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionForeach] - ==>  Preparing: select * from tbl_employee where id in ( ? , ? ) 
2018-12-14 17:01:07,882 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionForeach] - ==> Parameters: 1(Integer), 3(Integer)
2018-12-14 17:01:07,902 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsByConditionForeach] - <==      Total: 2
Employee [id=1, lastName=Admin, email=tom@stone.com, gender=0]
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]

MySQL foreach

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	
	public List<Employee> getEmpsByConditionChoose(Employee employee);
	
	public void updateEmp(Employee employee);
	
	public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
	
	public void addEmps(@Param("emps")List<Employee> emps);
}

对应 SQL：

	<!-- 批量保存 -->
	<!-- public void addEmps(@Param("emps")List<Employee> emps); -->
	<!-- MySQL 下批量保存，可以 foreach 遍历，MySQL支持 values(),(),() 语法 -->
	<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>
	
	<!-- 这种方式需要数据库连接属性 allowMultiQueries=true；这种分号分隔多个 SQL 可以用于其他的批量操作 -->
	<!-- <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> -->

测试：

	@Test
	public void test19() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> emps = new ArrayList<>();
			emps.add(new Employee(null, "smith", "smith@stone.com", "1", new Department(1)));
			emps.add(new Employee(null, "allen", "allen@stone.com", "0", new Department(1)));
			mapper.addEmps(emps);
			openSession.commit();
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-14 17:33:09,263 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - ==>  Preparing: insert into tbl_employee(last_name,email,gender,d_id) values (?,?,?,?) , (?,?,?,?) 
2018-12-14 17:33:09,292 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - ==> Parameters: smith(String), smith@stone.com(String), 1(String), 1(Integer), allen(String), allen@stone.com(String), 0(String), 1(Integer)
2018-12-14 17:33:09,294 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - <==    Updates: 2

Oracle foreach

对应 SQL：

	<!-- 
		Oracle 数据库批量保存 
		1、不支持 values(),(),()
		2、Oracle 支持的批量方式
			2.1、多个 insert 放在 begin ... end 里面
				begin
				  insert into emp(empno,ename) values(emp_seq.nextval,'test01');
				  insert into emp(empno,ename) values(emp_seq.nextval,'test02');
				end;
			2.2、利用中间表
				insert into emp(empno,ename)
					select emp_seq.nextval,ename from(
						select 'test_a_01' ename from dual
						union
						select 'test_a_02' ename from dual
						union
						select 'test_a_03' ename from dual
				)
	-->
	<insert id="addEmps" databaseId="oracle">
		<!-- Oracle 第 1 种批量方式 -->
		<!-- <foreach collection="emps" item="emp" open="begin" close="end;">
			insert into emp(empno,ename) values(emp_seq.nextval,#{emp.lastName});
		</foreach> -->
		
		<!-- Oracle 第 2 种批量方式 -->
		insert into emp(empno,ename)
			select emp_seq.nextval,ename from(
				<foreach collection="emps" item="emp" separator="union">
					select #{emp.lastName} ename from dual
				</foreach>
			)
	</insert>

测试：

	@Test
	public void test19() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> emps = new ArrayList<>();
			emps.add(new Employee(null, "smith", "smith@stone.com", "1", new Department(1)));
			emps.add(new Employee(null, "allen", "allen@stone.com", "0", new Department(1)));
			mapper.addEmps(emps);
			openSession.commit();
		} finally {
			openSession.close();
		}

输出：

2018-12-17 11:15:16,293 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - ==>  Preparing: insert into emp(empno,ename) select emp_seq.nextval,ename from( select ? ename from dual union select ? ename from dual ) 
2018-12-17 11:15:16,666 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - ==> Parameters: smith(String), allen(String)
2018-12-17 11:15:16,689 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.addEmps] - <==    Updates: 2

内置参数

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQL {
	
	public List<Employee> getEmpsByConditionIf(Employee employee);
	
	public List<Employee> getEmpsByConditionTrim(Employee employee);
	
	public List<Employee> getEmpsByConditionChoose(Employee employee);
	
	public void updateEmp(Employee employee);
	
	public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
	
	public void addEmps(@Param("emps")List<Employee> emps);
	
	public List<Employee> getEmpsTestInnerParameter(Employee employee);
}

对应 SQL：

	<!-- 
		2 个内置参数：
		不只是方法传递过来的参数可以被用来判断，取值等，MyBatis 默认还有 2 个内置参数：
		_parameter：代表整个参数
			单个参数：_parameter 就是这个参数
			多个参数：参数会被封装为一个 map；_parameter 就是代表这个 map
		_databaseId：如果配置了 databaseIdProvider 标签：
			_databaseId：就是代表当前数据库别名
	 -->
	 <!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
	 <select id="getEmpsTestInnerParameter" resultType="com.stone.mybatis.bean.Employee">
	 	<if test="_databaseId=='mysql'">
	 		select * from tbl_employee
	 		<if test="_parameter!=null">
	 			where last_name=#{_parameter.lastName}
	 		</if>
	 	</if>
	 	<if test="_databaseId=='oracle'">
	 		select empno id,ename last_name,gender,email from emp
	 		<if test="_parameter!=null">
	 			where ename=#{_parameter.lastName}
	 		</if>
	 	</if>
	 </select>

测试：

	@Test
	public void test20() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			List<Employee> list = mapper.getEmpsTestInnerParameter(new Employee());
			for (Employee employee : list) {
				System.out.println(employee);
			}
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-17 11:50:25,812 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - ==>  Preparing: select empno id,ename last_name,gender,email from emp where ename=? 
2018-12-17 11:50:26,012 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - ==> Parameters: null
2018-12-17 11:50:26,197 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - <==      Total: 0

SQL 绑定

对应 SQL：

	 <select id="getEmpsTestInnerParameter" resultType="com.stone.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>
	 	<if test="_databaseId=='oracle'">
	 		select empno id,ename last_name,gender,email from emp
	 		<if test="_parameter!=null">
	 			where ename=#{_parameter.lastName}
	 		</if>
	 	</if>
	 </select>

测试：

	@Test
	public void test21() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取 SqlSession 对象，不会自动提交
		SqlSession openSession = sqlSessionFactory.openSession();

		try {
			EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
			Employee employee2 = new Employee();
			employee2.setLastName("e");
			List<Employee> list = mapper.getEmpsTestInnerParameter(employee2);
			for (Employee employee : list) {
				System.out.println(employee);
			}
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-17 13:18:07,078 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - ==>  Preparing: select * from tbl_employee where last_name like ? 
2018-12-17 13:18:07,106 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - ==> Parameters: %e%(String)
2018-12-17 13:18:07,137 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapperDynamicSQL.getEmpsTestInnerParameter] - <==      Total: 4
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]
Employee [id=4, lastName=jerry, email=jerry@stone.com, gender=1]
Employee [id=10, lastName=allen, email=allen@stone.com, gender=0]
Employee [id=12, lastName=allen, email=allen@stone.com, gender=0]

对于模糊查询来说，还是建议将查询条件写到代码中。

SQL 片段

对应 SQL：

	<!-- 
		sql 标签：抽取可重用的 SQL 片段，方便后面引用
		1、经常将要查询的列名，或者插入用的列名抽取出来方便引用
		2、include 标签来引用已经抽取的 SQL
		3、include 还可以自定义一些 property，sql 标签内部就能使用自定义的属性，取值的正确方式为：${prop}
	-->
	<sql id="insertColumn">
		<if test="_databaseId=='mysql'">
			last_name,email,gender,d_id,${testColumn}
		</if>
		<if test="_databaseId=='oracle'">
			ename,email,gender,deptno
		</if>
	</sql>
	<!-- <insert id="addEmps">
		insert into tbl_employee(
		引用外部定义的 SQL
		<include refid="insertColumn">
			<property name="testColumn" value="abc"/>
		</include>
		)
		values
		<foreach collection="emps" item="emp" separator=",">
			(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	</insert> -->

缓存

MyBatis 系统中默认定义了两级缓存：一级缓存和二级缓存。

• 默认情况下，只有一级缓存（SqlSession 级别的缓存，也称为本地缓存）开启。
• 二级缓存需要手动开启和配置，是基于 namespace 级别的缓存。
• 为了提高扩展性，MyBatis 定义了缓存接口 Cache。可以通过实现 Cache 接口来自定义二级缓存。

缓存的使用顺序：

• 先二级缓存
• 再一级缓存
• 最后数据库

一级缓存

测试：

	/**
	 * 两级缓存：
	 * 一级缓存（本地缓存）：SqlSession 级别的缓存，一级缓存一直开启的。SqlSession 级别的一个 map
	 *   与数据库同一次会话期间查询到的数据会放在本地缓存中。
	 *   以后如果需要获取相同的数据，直接从缓存中获取，没必要再去查询数据库。
	 *   一级缓存失效情况（没有使用到当前的一级缓存情况，效果就是还需要再向数据库发出查询）
	 *   	1、SqlSession 不同。
	 *   	2、SqlSession 相同，查询条件不同。
	 *   	3、SqlSession 相同，两次查询之间执行了增删改。
	 *   	4、SqlSession 相同，手动清除了一级缓存
	 *   	
	 *   
	 * 二级缓存（全局缓存）：	
	 * @throws IOException 
	 */
	@Test
	public void testFirstLevelCache() throws IOException {
		SqlSession openSession = getSqlSessionFactory().openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = mapper.getEmpById(1);
			System.out.println(employee);
			//openSession.clearCache();
			Employee employee2 = mapper.getEmpById(1);
			System.out.println(employee2);
			System.out.println(employee==employee2);
		} finally {
			openSession.close();
		}
	}

二级缓存

POJO：

package com.stone.mybatis.bean;

public class Employee implements Serializable {
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}
}

全局配置：

	<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<setting name="jdbcTypeForNull" value="NULL"/>
		<!-- 显式的指定每个需要更改的配置的值，即使是默认的。防止版本更新带来的问题 -->
		<setting name="cacheEnabled" value="true"/>
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>

Mapper 配置：

	<!-- 
 		eviction：缓存的回收策略，默认是 LRU
 		flushInterval：缓存刷新间隔，多长时间清空一次，默认不清空，设置一个毫秒值
 		readOnly：是否只读，
 			true：只读，MyBatis 认为所有从缓存中获取数据的操作都是只读操作，不会修改数据。为了加快获取速度，直接就会将数据在缓存中的引用交给用户，不安全，速度快
 			false：非只读，默认。MyBatis 认为获取的数据可能会被修改，会利用序列化&反序列化的技术克隆一份新的数据给用户，安全，速度慢
 		size：缓存存放多少元素
 		type：指定自定义缓存的全类名，实现Cache接口即可
 	 -->
 	<cache eviction="FIFO" flushInterval="60000" readOnly="false" size="1024"></cache>

测试：

	/**
	 * 两级缓存：
	 * 一级缓存（本地缓存）：SqlSession 级别的缓存，一级缓存一直开启的。SqlSession 级别的一个 map
	 *   与数据库同一次会话期间查询到的数据会放在本地缓存中。
	 *   以后如果需要获取相同的数据，直接从缓存中获取，没必要再去查询数据库。
	 *   一级缓存失效情况（没有使用到当前的一级缓存情况，效果就是还需要再向数据库发出查询）
	 *   	1、SqlSession 不同。
	 *   	2、SqlSession 相同，查询条件不同。
	 *   	3、SqlSession 相同，两次查询之间执行了增删改。
	 *   	4、SqlSession 相同，手动清除了一级缓存
	 *   	
	 *   
	 * 二级缓存（全局缓存）：基于 namespace 级别的缓存，一个 namespace 对应一个二级缓存。
	 * 工作机制：
	 * 	1、一个会话，查询一条数据，这个数据就会被放在当前会话的一级缓存中。
	 * 	2、如果会话关闭，一级缓存中的数据会被保存到二级缓存中。新的会话查询信息就可以参照二级缓存。
	 * 	3、不同 namespace 查询的数据会放在自己对应的缓存中（map）
	 * 	4、数据会从二级缓存中获取
	 * 	5、查出的数据都会被默认先放在一级缓存中，只有会话提交或者关闭后，一级缓存中的数据才会转移到二级缓存中
	 * 
	 * 使用步骤：
	 * 	1、开启全局二级缓存配置：<setting name="cacheEnabled" value="true"/>
	 * 	2、在 mapper.xml 中配置使用二级缓存:<cache></cache>
	 * 	3、POJO 需要实现序列化接口
	 * 
	 * 和缓存有关的设置/属性：
	 * 	1、cacheEnabled=true。如果为 false，则关闭二级缓存，一级缓存不受影响
	 * 	2、每个 select 标签都有 useCache="true" 属性，如果为 false，则不使用二级缓存，一级缓存不受影响
	 * 	3、每个增删改标签都有 flushCache="true" 属性，表示增删改执行完成后就会清空一级缓存和二级缓存
	 * 	4、每个 select 标签都有 flushCache="false"，如果为 true，每次查询之后都会清空缓存
	 *	5、openSession.clearCache() 只是清除当前 session 的一级缓存，二级缓存不受影响
	 *	6、localCacheScope：本地缓存作用域，默认为 session，当前会话的所有数据保存在会话缓存中。如果设置为 statement，则禁用一级缓存
	 * 	
	 * @throws IOException 
	 */
	@Test
	public void testFirstLevelCache() throws IOException {
		SqlSession openSession = getSqlSessionFactory().openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = mapper.getEmpById(1);
			System.out.println(employee);
			//openSession.clearCache();
			Employee employee2 = mapper.getEmpById(1);
			System.out.println(employee2);
			System.out.println(employee==employee2);
		} finally {
			openSession.close();
		}
	}
	
	@Test
	public void testSecondLevelCache() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		SqlSession openSession2 = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			EmployeeMapper mapper2 = openSession2.getMapper(EmployeeMapper.class);
			
			Employee employee = mapper.getEmpById(1);
			System.out.println(employee);
			openSession.close();
			
			//第二次查询是从二级缓存中拿到的数据，并没有发送新的 SQL
			Employee employee2 = mapper2.getEmpById(1);
			System.out.println(employee2);
			openSession2.close();
			
		} finally {
			
		}
	}

输出：

2018-12-18 13:29:28,198 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper] - Cache Hit Ratio [com.stone.mybatis.dao.EmployeeMapper]: 0.0
2018-12-18 13:29:28,207 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmpById] - ==>  Preparing: select * from tbl_employee where id = ? 
2018-12-18 13:29:28,234 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmpById] - ==> Parameters: 1(Integer)
2018-12-18 13:29:28,256 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmpById] - <==      Total: 1
Employee [id=1, lastName=Admin, email=tom@stone.com, gender=0]
2018-12-18 13:29:28,328 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper] - Cache Hit Ratio [com.stone.mybatis.dao.EmployeeMapper]: 0.5
Employee [id=1, lastName=Admin, email=tom@stone.com, gender=0]

整合 Spring

导入 JAR 包

创建 Dynamic Web Project，在 https://github.com/mybatis/spring/releases，http://tomcat.apache.org/download-taglibs.cgi#Standard-1.2.5 下载整合包，将所有 JAR 包拷贝到 /WebContent/WEB-INF/lib 目录下。

ant-1.9.6.jar
ant-launcher-1.9.6.jar
aopalliance-1.0.jar
asm-5.2.jar
aspectjweaver-1.9.2.jar
c3p0-0.9.5.2.jar
cglib-2.2.2.jar
cglib-3.2.5.jar
commons-logging-1.2.jar
ehcache-3.6.1.jar
ehcache-core-2.6.8.jar
javassist-3.22.0-GA.jar
log4j-1.2.17.jar
log4j-api-2.3.jar
log4j-core-2.3.jar
mchange-commons-java-0.2.11.jar
mybatis-3.4.6.jar
mybatis-ehcache-1.0.3.jar
mybatis-spring-1.3.2.jar
mysql-connector-java-5.1.23-bin.jar
ognl-3.1.16.jar
slf4j-api-1.7.25.jar
slf4j-log4j12-1.7.25.jar
spring-aop-4.3.2.RELEASE.jar
spring-aspects-4.3.2.RELEASE.jar
spring-beans-4.3.2.RELEASE.jar
spring-context-4.3.2.RELEASE.jar
spring-context-support-4.3.2.RELEASE.jar
spring-core-4.3.2.RELEASE.jar
spring-data-commons-1.13.3.RELEASE.jar
spring-data-redis-1.8.3.RELEASE.jar
spring-expression-4.3.2.RELEASE.jar
spring-instrument-4.3.2.RELEASE.jar
spring-instrument-tomcat-4.3.2.RELEASE.jar
spring-jdbc-4.3.2.RELEASE.jar
spring-jms-4.3.2.RELEASE.jar
spring-messaging-4.3.2.RELEASE.jar
spring-orm-4.3.2.RELEASE.jar
spring-oxm-4.3.2.RELEASE.jar
spring-test-4.3.2.RELEASE.jar
spring-tx-4.3.2.RELEASE.jar
spring-web-4.3.2.RELEASE.jar
spring-webmvc-4.3.2.RELEASE.jar
spring-webmvc-portlet-4.3.2.RELEASE.jar
spring-websocket-4.3.2.RELEASE.jar
taglibs-standard-compat-1.2.5.jar
taglibs-standard-impl-1.2.5.jar
taglibs-standard-jstlel-1.2.5.jar
taglibs-standard-spec-1.2.5.jar

配置 db.properties

mysql.username=stone
mysql.password=stone
mysql.driverClass=com.mysql.jdbc.Driver
mysql.jdbcUrl=jdbc:mysql://192.168.8.137:3306/mybatis

oracle.username=hr
oracle.password=hr
oracle.driverClass=oracle.jdbc.driver.OracleDriver
oracle.jdbcUrl=jdbc:oracle:thin:@192.168.247.133:1521/rac

配置 log4j.properties

log4j.rootLogger=DEBUG,Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n
log4j.logger.org.apache=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

创建 Bean

package com.stone.mybatis.bean;

import java.io.Serializable;

public class Employee implements Serializable {
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private Department dept;
	public Employee() {
		super();
	}
	public Employee(String lastName, String email, String gender) {
		super();
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	public Employee(Integer id, String lastName, String email, String gender) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	public Employee(Integer id, String lastName, String email, String gender, Department dept) {
		super();
		this.id = id;
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
		this.dept = dept;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}
	public Department getDept() {
		return dept;
	}
	public void setDept(Department dept) {
		this.dept = dept;
	}
}

创建 Dao

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapper {

	public Employee getEmpById(Integer id);
	
	public List<Employee> getEmps();

}

配置 mapper.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.stone.mybatis.dao.EmployeeMapper">
	
	<!-- public Employee getEmpById(Integer id);	 -->
 	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee where id = #{id}
	</select> 
	
	<!-- public List<Employee> getEmps(); -->
	<select id="getEmps" resultType="com.stone.mybatis.bean.Employee">
		select * from tbl_employee
	</select>
	
</mapper>

配置 mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
		<setting name="jdbcTypeForNull" value="NULL"/>
		<!-- 显式的指定每个需要更改的配置的值，即使是默认的。防止版本更新带来的问题 -->
		<setting name="cacheEnabled" value="true"/>
		<setting name="lazyLoadingEnabled" value="true"/>
		<setting name="aggressiveLazyLoading" value="false"/>
	</settings>

	<databaseIdProvider type="DB_VENDOR">
		<!-- 为不同的数据库厂商起别名 -->
		<property name="MySQL" value="mysql"/>
		<property name="Oracle" value="oracle"/>
		<property name="SQL Server" value="sqlserver"/>
	</databaseIdProvider>

</configuration>

配置 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>Mybatis_06_ssm</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
  <!-- Spring 配置：needed for ContextLoaderListener -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:applicationContext.xml</param-value>
	</context-param>

	<!-- Bootstraps the root web application context before servlet initialization -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
	
	<!-- SpringMVC 配置 -->
	<!-- The front controller of this Spring Web application, responsible for handling all application requests -->
	<servlet>
		<servlet-name>spring</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<!-- Map all requests to the DispatcherServlet for handling -->
	<servlet-mapping>
		<servlet-name>spring</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>
	  
</web-app>

配置 spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">

	<!-- SpringMVC 只是控制网站跳转逻辑 -->
	<!-- 只扫描控制器 -->
	<context:component-scan base-package="com.stone.mybatis" use-default-filters="false">
		<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
	</context:component-scan>
	
	<!-- 视图解析器 -->
	<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/pages/"></property>
		<property name="suffix" value=".jsp"></property>
	</bean>
	
	<mvc:annotation-driven></mvc:annotation-driven>
	<mvc:default-servlet-handler/>
	
</beans>

配置 applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	xsi:schemaLocation="http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- Spring 希望管理所有的业务逻辑 -->
	<context:component-scan base-package="com.stone.mybatis">
		<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
	</context:component-scan>
	
	<!-- 引入数据库的配置文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
	
	<!-- Spring 用来控制业务逻辑，数据源，事物控制，aop -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="jdbcUrl" value="${mysql.jdbcUrl}"></property>
		<property name="driverClass" value="${mysql.driverClass}"></property>
		<property name="user" value="${mysql.username}"></property>
		<property name="password" value="${mysql.password}"></property>
	</bean>
	
	<!-- Spring 事物管理 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 开启基于注解的事物 -->
	<tx:annotation-driven transaction-manager="transactionManager"/>
	
	<!-- 
		整合Mybatis 
		目的：
			1、Spring 管理所有组件，mapper 的实现类：Service==>Dao @Autowired 自动注入 mapper
			2、Spring 用来管理事物：Spring 声明式事物
	-->
	<!-- 创建出 sqlSessionFactory 对象 -->
	<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
		<!-- configLocation 指定全局配置文件的位置 -->
		<property name="configLocation" value="classpath:mybatis-config.xml"></property>
		<!-- mapperLocations 指定 mapper 文件的位置 -->
		<property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"></property>
	</bean>

	<!-- 配置一个可以进行批量执行的 sqlSession -->
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean"></constructor-arg>
		<constructor-arg name="executorType" value="BATCH"></constructor-arg>
	</bean>

	<!-- 
		扫描所有的 mapper 接口的实现，让这些 mapper 能够自动注入 
		base-package 指定 mapper 接口的包名
	-->
	<mybatis-spring:scan base-package="com.stone.mybatis.dao"/>

</beans>

创建 Service

package com.stone.mybatis.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.stone.mybatis.bean.Employee;
import com.stone.mybatis.dao.EmployeeMapper;

@Service
public class EmployeeService {	
	@Autowired
	private EmployeeMapper employeeMapper;
	
	public List<Employee> getEmps(){
		return employeeMapper.getEmps();
	}
}

创建 Controller

package com.stone.mybatis.controller;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.stone.mybatis.bean.Employee;
import com.stone.mybatis.service.EmployeeService;

@Controller
public class EmployeeController {
	
	@Autowired
	EmployeeService employeeService;
	
	@RequestMapping("/getemps")
	public String emps(Map<String, Object> map) {
		List<Employee> emps = employeeService.getEmps();
		map.put("allEmps", emps);
		return "list";
	}
}

创建 index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="getemps">查询所有员工</a>
</body>
</html>

创建 list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>员工列表</title>
</head>
<body>
	<table>
			<tr>
				<td>id</td>
				<td>lastName</td>
				<td>gender</td>
				<td>email</td>
			</tr>
		<c:forEach items="${allEmps}" var="emp">
			<tr>
				<td>${emp.id }</td>
				<td>${emp.lastName }</td>
				<td>${emp.gender }</td>
				<td>${emp.email }</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

MyBatis Generator

使用 MyBatis Generator 可以根据数据库表结构生成对应的 Javabean，Dao 接口以及 mapper 配置文件。

创建 generatorConfig.xml 配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
	<!-- MyBatis3Simple：生成简单版本 -->
	<!-- MyBatis3：生成动态 SQL -->
	<context id="MySQLTables" targetRuntime="MyBatis3">
		<!-- jdbc 连接 -->
		<jdbcConnection connectionURL="jdbc:mysql://192.168.8.137:3306/mybatis" driverClass="com.mysql.jdbc.Driver" password="stone" userId="stone" />
		
		<!-- Javabean 生成策略 -->
		<javaModelGenerator targetPackage="com.stone.mybatis.bean" targetProject="mybatis_07_mbg\src">
			<property name="enableSubPackages" value="true"/>
			<property name="trimStrings" value="true"/>
		</javaModelGenerator>
		
		<!-- 生成 mapper.xml 文件 -->
		<sqlMapGenerator targetPackage="com.stone.mybatis.dao" targetProject="mybatis_07_mbg\conf">
			<property name="enableSubPackages" value="true"/>
		</sqlMapGenerator>
		
		<!-- 生成 mapper.xml 对应 client，也就是 mapper 接口 -->
		<javaClientGenerator targetPackage="com.stone.mybatis.dao" targetProject="mybatis_07_mbg\src" type="XMLMAPPER">
			<property name="enableSubPackages" value="true"/>
		</javaClientGenerator>
		
		<!-- 指定要逆向分析哪些表，根据表创建 Javabean -->	
		<table tableName="tbl_dept" domainObjectName="Department"></table>
		<table tableName="tbl_employee" domainObjectName="Employee"></table>	
	</context>
</generatorConfiguration>

在开发工具中安装 MyBatis Generator 插件，右键点击 generatorConfig.xml，点击 Run As，选择 Run MyBatis Generator，生成 Javabean，Dao 接口以及 mapper 配置文件。

测试：

	@Test
	public void testMyBatis3Simple() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			List<Employee> selectAll = mapper.selectByExample(null);
			for (Employee employee : selectAll) {
				System.out.println(employee.getId());
			}
		} finally {
			openSession.close();
		}
	}
	
	
	@Test
	public void testMyBatis3() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			//xxxExample 就是封装查询条件
			//1.查询所有
			List<Employee> emps = mapper.selectByExample(null);
			for (Employee employee : emps) {
				System.out.println(employee.getId());
			}
			//2.查询员工名字中有 e 字母的，和员工性别是 1 的,或者 email 中有字母 e
			//封装员工查询条件的 employeeExample
			EmployeeExample employeeExample = new EmployeeExample();
			//创建一个 Criteria，这个 Criteria 就是拼装查询条件的
			Criteria criteria = employeeExample.createCriteria();
			criteria.andLastNameLike("%e%");
			criteria.andGenderEqualTo("1");

			Criteria criteria2 = employeeExample.createCriteria();
			criteria2.andEmailLike("%e%");
			employeeExample.or(criteria2);

			List<Employee> selectByExample = mapper.selectByExample(employeeExample);
			for (Employee employee : selectByExample) {
				System.out.println(employee.getId());
			}
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-19 13:15:22,371 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - ==>  Preparing: select id, last_name, gender, email, d_id from tbl_employee 
2018-12-19 13:15:22,436 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - ==> Parameters: 
2018-12-19 13:15:22,487 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - <==      Total: 11
1
3
4
5
6
7
8
9
10
11
12
2018-12-19 13:15:22,509 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - ==>  Preparing: select id, last_name, gender, email, d_id from tbl_employee WHERE ( last_name like ? and gender = ? ) or( email like ? ) 
2018-12-19 13:15:22,510 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - ==> Parameters: %e%(String), 1(String), %e%(String)
2018-12-19 13:15:22,516 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.selectByExample] - <==      Total: 11
1
3
4
5
6
7
8
9
10
11
12

分页插件

在 https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md 下载 JAR 包。

pagehelper-5.1.8.jar
jsqlparser-1.3.jar

或者使用 Maven 引入依赖。

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>最新版本</version>
</dependency>

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
	public List<Employee> getEmps();
}

对应 SQL：

	<!-- public List<Employee> getEmps(); -->
	<select id="getEmps" resultType="com.stone.mybatis.bean.Employee">
		select id,last_name lastName,gender,email from tbl_employee
	</select>

测试：

	@Test
	public void test02() throws IOException {
		//1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		
		//2、获取 SqlSession 对象
		SqlSession openSession = sqlSessionFactory.openSession();
		
		//3、获取接口的实现类对象，会为接口自动创建一个代理对象，代理对象去执行增删改查方法
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Page<Object> page = PageHelper.startPage(1, 2);
			List<Employee> emps = mapper.getEmps();
			for (Employee employee : emps) {
				System.out.println(employee);
			}
			System.out.println("当前页码：" + page.getPageNum());
			System.out.println("总记录数：" + page.getTotal());
			System.out.println("每页的记录数：" + page.getPageSize());
			System.out.println("总页码：" + page.getPages());
			
			//传入要连续显示多少页
			PageInfo<Employee> pageInfo = new PageInfo<>(emps, 5);
			System.out.println("当前页码：" + pageInfo.getPageNum());
			System.out.println("总记录数：" + pageInfo.getTotal());
			System.out.println("每页的记录数：" + pageInfo.getPageSize());
			System.out.println("总页码：" + pageInfo.getPages());
			System.out.println("是否第一页：" + pageInfo.isIsFirstPage());
			System.out.println("是否最后一页：" + pageInfo.isIsLastPage());
			System.out.println("连续显示的页码：");
			int[] nums = pageInfo.getNavigatepageNums();
			for (int i : nums) {
				System.out.println(i);
			}
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-19 15:58:23,277 [main] DEBUG [SQL_CACHE] - Cache Hit Ratio [SQL_CACHE]: 0.0
2018-12-19 15:58:23,321 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps_COUNT] - ==>  Preparing: SELECT count(0) FROM tbl_employee 
2018-12-19 15:58:23,352 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps_COUNT] - ==> Parameters: 
2018-12-19 15:58:23,392 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps_COUNT] - <==      Total: 1
2018-12-19 15:58:23,395 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps] - ==>  Preparing: select id,last_name lastName,gender,email from tbl_employee LIMIT ? 
2018-12-19 15:58:23,396 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps] - ==> Parameters: 2(Integer)
2018-12-19 15:58:23,407 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getEmps] - <==      Total: 2
Employee [id=1, lastName=Admin, email=tom@stone.com, gender=0]
Employee [id=3, lastName=jerry, email=jerry@stone.com, gender=1]
当前页码：1
总记录数：11
每页的记录数：2
总页码：6
当前页码：1
总记录数：11
每页的记录数：2
总页码：6
是否第一页：true
是否最后一页：false
连续显示的页码：
1
2
3
4
5

批量 SqlSession

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;

public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
	public List<Employee> getEmps();
	public Long addEmp(Employee employee);
}

对应 SQL：

	<!-- public Long addEmp(Employee employee); -->
	<insert id="addEmp">
		insert into tbl_employee(last_name,gender,email)
		values(#{lastName},#{gender},#{email})
	</insert>

测试：

	@Test
	public void testBatch() throws IOException {
		// 1、获取 SQLSessionFactory 对象
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();

		// 2、获取可以执行批量操作的 SqlSession 对象
		SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
		long start = System.currentTimeMillis();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			for (int i = 0; i < 100; i++) {
				mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0, 5), "b", "1"));
			}
			openSession.commit();
			long end = System.currentTimeMillis();
			//批量：预编译 SQL1次 ==> 设置参数100次 ==> 执行1次
			//Parameters: 112b7(String), 1(String), b(String)
			//非批量：预编译 SQL100次 ==> 设置参数100次==> 执行100次
			System.out.println("执行时间：" + (end-start));
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-19 17:01:44,708 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==>  Preparing: insert into tbl_employee(last_name,gender,email) values(?,?,?) 
2018-12-19 17:01:44,740 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 71dfb(String), 1(String), b(String)
2018-12-19 17:01:44,741 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 3eb75(String), 1(String), b(String)
2018-12-19 17:01:44,741 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 602bf(String), 1(String), b(String)
2018-12-19 17:01:44,741 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 700b2(String), 1(String), b(String)
2018-12-19 17:01:44,742 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: cac5d(String), 1(String), b(String)
2018-12-19 17:01:44,743 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 198df(String), 1(String), b(String)
2018-12-19 17:01:44,743 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: eeb07(String), 1(String), b(String)
2018-12-19 17:01:44,744 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: eb4da(String), 1(String), b(String)
2018-12-19 17:01:44,744 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: f0d38(String), 1(String), b(String)
2018-12-19 17:01:44,744 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: 52db1(String), 1(String), b(String)
执行时间：825

存储过程

Oracle数据库创建存储过程：

create or replace procedure hello_test(p_start in int,
                                       p_end   in int,
                                       p_count out int,
                                       p_emps  out sys_refcursor) as
begin
  select count(*) into p_count from emp;
  open p_emps for
    select *
      from (select rownum rn, e.* from emp e where rownum <= p_end)
     where rn >= p_start;
end hello_test;
/

封装存储过程的参数：

package com.stone.mybatis.bean;

import java.util.List;

public class OraclePage {
	private int start;
	private int end;
	private int count;
	private List<Employee> emps;
	public int getStart() {
		return start;
	}
	public void setStart(int start) {
		this.start = start;
	}
	public int getEnd() {
		return end;
	}
	public void setEnd(int end) {
		this.end = end;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
}

接口方法：

package com.stone.mybatis.dao;

import java.util.List;

import com.stone.mybatis.bean.Employee;
import com.stone.mybatis.bean.OraclePage;

public interface EmployeeMapper {
	public Employee getEmpById(Integer id);
	public List<Employee> getEmps();
	public Long addEmp(Employee employee);
	public void getPageByProcedure(OraclePage page);
}

全局配置：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	
	<properties resource="db.properties"></properties>
	
	<plugins>
		<plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
	</plugins>
	
	<!-- 4、environments：可以配置多种环境，default 指定使用某种环境，可以达到快速切换 -->
	<environments default="oracle">
		<!-- 
			environment：配置一个具体的环境 ，id 表示当前环境的唯一标识
			必须有以下两个标签：
			transactionManager：事物管理器，
				type：指定事物管理器类型：JDBC(JdbcTransactionFactory)|MANAGED(ManagedTransactionFactory)
				自定义事物管理器：使用 TransactionFactory 接口，type 指定全类名
				一般交给 Spring 来进行事务管理
			dataSource：数据源；
				type：数据源类型：UNPOOLED|POOLED|JNDI
				自定义数据源：实现 DataSourceFactory 接口，type 是全类名
		-->
		<environment id="oracle">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${oracle.driverClass}" />
				<property name="url" value="${oracle.jdbcUrl}" />
				<property name="username" value="${oracle.username}" />
				<property name="password" value="${oracle.password}" />
			</dataSource>
		</environment>
		
		<environment id="mysql">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${mysql.driverClass}" />
				<property name="url" value="${mysql.jdbcUrl}" />
				<property name="username" value="${mysql.username}" />
				<property name="password" value="${mysql.password}" />
			</dataSource>
		</environment>
	</environments>
	
	<databaseIdProvider type="DB_VENDOR">
		<!-- 为不同的数据库厂商起别名 -->
		<property name="MySQL" value="mysql"/>
		<property name="Oracle" value="oracle"/>
		<property name="SQL Server" value="sqlserver"/>
	</databaseIdProvider>
	
	<!-- 写好的 SQL 映射文件 EmployeeMapper.xml 一定要注册到全局配置文件中 mybatis-config.xml" -->
	<mappers>
		<mapper resource="EmployeeMapper.xml" />
	</mappers>
		
</configuration>

对应 SQL：

	<!-- public void getPageByProcedure(); -->
	<!-- 
		使用 select 标签定义调用存储过程
		statementType="CALLABLE"：表示要调用存储过程
	-->
	<select id="getPageByProcedure" statementType="CALLABLE" databaseId="oracle">
		{call hello_test(
			#{start,mode=IN,jdbcType=INTEGER},
			#{end,mode=IN,jdbcType=INTEGER},
			#{count,mode=OUT,jdbcType=INTEGER},
			#{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp}
			)}
	</select>
	<resultMap type="com.stone.mybatis.bean.Employee" id="PageEmp">
		<id column="empno" property="id"/>
		<result column="ename" property="lastName"/>
		<result column="gender" property="gender"/>
		<result column="email" property="email"/>
		<result column="deptno" property="deptno"/>
	</resultMap>

测试：

	/**
	 * 存储过程来实现 Oracle 分页
	 * @throws IOException 
	 */
	@Test
	public void testProcedure() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			OraclePage page = new OraclePage();
			page.setStart(1);
			page.setEnd(5);
			mapper.getPageByProcedure(page);
			System.out.println("总记录数：" + page.getCount());
			System.out.println("查出的数据数：" + page.getEmps().size());
			System.out.println("查出的数据：" + page.getEmps());
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-20 09:50:58,314 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getPageByProcedure] - ==>  Preparing: {call hello_test( ?, ?, ?, ? )} 
2018-12-20 09:50:58,569 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.getPageByProcedure] - ==> Parameters: 1(Integer), 5(Integer)
总记录数：51
查出的数据：5
查出的数据：[Employee [id=7369, lastName=SMITH, email=null, gender=1], Employee [id=7499, lastName=ALLEN, email=null, gender=1], Employee [id=7521, lastName=WARD, email=null, gender=1], Employee [id=7566, lastName=JONES, email=null, gender=1], Employee [id=7654, lastName=MARTIN, email=null, gender=1]]

枚举类型

默认处理

枚举类：

package com.stone.mybatis.bean;

public enum EmpStatus {
	LOGIN,LOGOUT,REMOVE
}

实体类：

package com.stone.mybatis.bean;

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private String gender;
	private String deptno;
	//员工状态
	private EmpStatus empStatus = EmpStatus.LOGOUT;
	public Employee() {
		super();
	}
	public Employee(String lastName, String email, String gender) {
		super();
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getDeptno() {
		return deptno;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	public EmpStatus getEmpStatus() {
		return empStatus;
	}
	public void setEmpStatus(EmpStatus empStatus) {
		this.empStatus = empStatus;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}
}

全局配置：

	<!-- 声明使用 EnumOrdinalTypeHandler 来处理枚举类，插入到数据库中的是枚举的索引值 -->
	<typeHandlers>
		<typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.stone.mybatis.bean.EmpStatus"/>
	</typeHandlers>

对应 SQL：

	<!-- public Long addEmp(Employee employee); -->
	<insert id="addEmp" useGeneratedKeys="true" keyProperty="id">
		insert into tbl_employee(last_name,gender,email,empStatus)
		values(#{lastName},#{gender},#{email},#{empStatus})
	</insert>

测试：

	@Test
	public void testEnumUse() {
		EmpStatus login = EmpStatus.LOGIN;
		System.out.println("枚举的索引：" + login.ordinal());
		System.out.println("枚举的名字：" + login.name());
	}
	
	/**
	 * MyBatis 在处理枚举对象的时候默认保存的是枚举的名字，使用 EnumTypeHandler
	 * 改变使用：EnumOrdinalTypeHandler，插入枚举的索引值
	 * @throws IOException
	 */
	@Test
	public void testEnum() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = new Employee("test_enum", "enum@stone.com", "1");
			mapper.addEmp(employee);
			System.out.println("保存成功" + employee.getId());
			openSession.commit();
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-20 10:50:44,261 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==>  Preparing: insert into tbl_employee(last_name,gender,email,empStatus) values(?,?,?,?) 
2018-12-20 10:50:44,289 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: test_enum(String), 1(String), enum@stone.com(String), 1(Integer)
2018-12-20 10:50:44,293 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - <==    Updates: 1
保存成功10356

自定义类型处理器

枚举类：

package com.stone.mybatis.bean;

/**
 * 希望数据库保存的是 100，200 这些状态码，而不是默认的 0，1 索引
 */
public enum EmpStatus {
	LOGIN(100,"用户登录"),LOGOUT(200,"用户登出"),REMOVE(300,"用户不存在");
	private Integer code;
	private String msg;
	private EmpStatus(Integer code, String msg) {
		this.code = code;
		this.msg = msg;
	}
	public Integer getCode() {
		return code;
	}
	public void setCode(Integer code) {
		this.code = code;
	}
	public String getMsg() {
		return msg;
	}
	public void setMsg(String msg) {
		this.msg = msg;
	}
	
	//按照状态码返回枚举对象
	public static EmpStatus getEmpStatusByCode(Integer code) {
		switch (code) {
		case 100:
			return LOGIN;
		case 200:
			return LOGOUT;
		case 300:
			return REMOVE;
		default:
			return LOGOUT;
		}
	}
}

全局配置：

	<!-- 声明使用 EnumOrdinalTypeHandler 来处理枚举类，插入到数据库中的是枚举的索引值 -->
	<typeHandlers>
		<!-- 1、配置自定义的 TypeHandler -->
		<typeHandler handler="com.stone.mybatis.typehandler.MyEnumEmpStatusTypeHandler" javaType="com.stone.mybatis.bean.EmpStatus"/>
		<!-- 
			2、也可以在处理某个字段的时候告诉 MyBatis 用什么类型的 TypeHandler
				保存：#{empStatus,typeHandler=xxx} 
				查询：<resultMap type="com.stone.mybatis.bean.Employee" id="MyEmp">
						<id column="id" property="id"/>
						<result column="empStatus" property="empStatus" typeHandler="com.stone.mybatis.typehandler.MyEnumEmpStatusTypeHandler"/>
					</resultMap>
				注意：如果在参数位置修改 TypeHandler，应该保证保存数据和查询数据用的 TypeHandler 是一样的
		-->
		
	</typeHandlers>

对应 SQL：

	<resultMap type="com.stone.mybatis.bean.Employee" id="MyEmp">
		<id column="id" property="id"/>
		<result column="empStatus" property="empStatus" typeHandler="com.stone.mybatis.typehandler.MyEnumEmpStatusTypeHandler"/>
	</resultMap>
	<select id="getEmpById" resultType="com.stone.mybatis.bean.Employee">
		select id,last_name lastName,gender,email,empStatus from tbl_employee where id = #{id}
	</select>

测试：

	@Test
	public void testEnumUse() {
		EmpStatus login = EmpStatus.LOGIN;
		System.out.println("枚举的索引：" + login.ordinal());
		System.out.println("枚举的名字：" + login.name());
		
		System.out.println("枚举的状态码：" + login.getCode());
		System.out.println("枚举的提示信息：" + login.getMsg());
	}
	
	/**
	 * MyBatis 在处理枚举对象的时候默认保存的是枚举的名字，使用 EnumTypeHandler
	 * 改变使用：EnumOrdinalTypeHandler，插入枚举的索引值
	 * @throws IOException
	 */
	@Test
	public void testEnum() throws IOException {
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try {
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			Employee employee = new Employee("test_enum", "enum@stone.com", "1");
			mapper.addEmp(employee);
			System.out.println("保存成功" + employee.getId());
			openSession.commit();
		} finally {
			openSession.close();
		}
	}

输出：

2018-12-20 11:23:29,862 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==>  Preparing: insert into tbl_employee(last_name,gender,email,empStatus) values(?,?,?,?) 
2018-12-20 11:23:29,891 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - ==> Parameters: test_enum(String), 1(String), enum@stone.com(String), 200(String)
2018-12-20 11:23:29,894 [main] DEBUG [com.stone.mybatis.dao.EmployeeMapper.addEmp] - <==    Updates: 1
保存成功10359