Java
Java Web框架

MyBatis(2)输入输出、关联查询等

简介:MyBatis是一款优秀的持久层框架,它支持定制化SQL、存储过程以及高级映射。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以使用简单的XML或注解来配置和映射原生信息,将接口和Java的POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

1. parameterType输入类型

  • 传递简单类型
  • 传递pojo对象

这两种输入类型在前面的介绍已经讲解过了。需要注意的是Mybatis使用OGNL表达式解析对象字段的值,#{}或者${}括号中的值为POJO属性名称。我们着重讲解传递POJO包装对象的入参方式。

1.1. 传递pojo包装对象

我们创建一个QueryVo类包装User类,代码如下:

  • package com.coderap.mybatis.pojo;
  • import java.io.Serializable;
  • public class QueryVo implements Serializable {
  • private static final long serialVersionUID = 6756071375671726937L;
  • private User user;
  • public User getUser() {
  • return user;
  • }
  • public void setUser(User user) {
  • this.user = user;
  • }
  • }

使用了POJO包装对象,我们的Mapper.xml中SQL语句的写法如下:

  • <!-- 使用QueryVo包装类进行查询 -->
  • <select id="findUserByQueryVo" parameterType="QueryVo" resultType="User">
  • SELECT * FROM user WHERE id = #{user.id}
  • </select>

我们在UserMapper接口中添加查询方法:

  • public interface UserMapper {
  • public User findUserById(Integer id);
  • public User findUserByQueryVo(QueryVo vo);
  • }

然后编写测试代码:

  • @Test
  • public void findUserByQueryVo() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • QueryVo vo = new QueryVo();
  • User user = new User();
  • user.setId(10);
  • vo.setUser(user);
  • User resultUser = mapper.findUserByQueryVo(vo);
  • System.out.println(resultUser);
  • }

如上代码,就可以传入POJO包装对象进行查询了,运行结果如下:

  • DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
  • DEBUG [main] - Class not found: org.jboss.vfs.VFS
  • DEBUG [main] - JBoss 6 VFS API is not available in this environment.
  • DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
  • DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
  • DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Reader entry: QueryVo.class
  • DEBUG [main] - Reader entry: User.class
  • DEBUG [main] - Listing file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/QueryVo.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/QueryVo.class
  • DEBUG [main] - Reader entry: ���� 1 
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/User.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/User.class
  • DEBUG [main] - Reader entry: ���� 1 R
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.pojo.QueryVo matches criteria [is assignable to Object]
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.pojo.User matches criteria [is assignable to Object]
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Reader entry: UserMapper.class
  • DEBUG [main] - Reader entry: UserMapper.xml
  • DEBUG [main] - Listing file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.class
  • DEBUG [main] - Reader entry: ���� 1  
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.xml
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.xml
  • DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.mapper.UserMapper matches criteria [is assignable to Object]
  • DEBUG [main] - Opening JDBC Connection
  • DEBUG [main] - Created connection 640363654.
  • DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@262b2c86]
  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id = ?
  • DEBUG [main] - ==> Parameters: 10(Integer)
  • DEBUG [main] - <== Total: 1
  • User [id=10, username=张三, gender=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
  • Process finished with exit code 0

2. resultType输出类型

  • 输出POJO对象
  • 输出POJO列表

这两种输出类型在前面的介绍已经讲解过了,都是直接使用resultType指定对应的POJO类即可。

  • 输出简单类型

输出简单类型只需要将resultType写为简单类型即可,如我们要查询User的数量,有以下的Mapper查询语句:

  • <!-- 输出参数为简单类型 -->
  • <select id="countUser" resultType="Integer">
  • SELECT count(1) FROM user
  • </select>

对应的查询接口方法为:

  • public Integer countUser();

测试代码如下:

  • @Test
  • public void countUser() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • Integer count = mapper.countUser();
  • System.out.println(count);
  • }

3. resultType输出类型

我们这里着重讲解resultMapresultMap用于解决POJO类属性与数据库字段映射不一致的问题。例如我们的订单表中有一个user_id字段,而POJO类Order中该字段对应的属性为userId,这个时候就需要使用resultMap来指定映射了。Order类如下:

  • package com.coderap.mybatis.pojo;
  • import java.io.Serializable;
  • import java.util.Date;
  • public class Order implements Serializable {
  • private static final long serialVersionUID = 5507038469347455120L;
  • private Integer id;
  • private Integer userId;
  • private String number;
  • private Date createtime;
  • private String note;
  • public Integer getId() {
  • return id;
  • }
  • public void setId(Integer id) {
  • this.id = id;
  • }
  • public Integer getUserId() {
  • return userId;
  • }
  • public void setUserId(Integer userId) {
  • this.userId = userId;
  • }
  • public String getNumber() {
  • return number;
  • }
  • public void setNumber(String number) {
  • this.number = number == null ? null : number.trim();
  • }
  • public Date getCreatetime() {
  • return createtime;
  • }
  • public void setCreatetime(Date createtime) {
  • this.createtime = createtime;
  • }
  • public String getNote() {
  • return note;
  • }
  • public void setNote(String note) {
  • this.note = note == null ? null : note.trim();
  • }
  • @Override
  • public String toString() {
  • return "Orders [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
  • + ", note=" + note + "]";
  • }
  • }

我们需要编写OrderMapper.java和OrderMapper.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.coderap.mybatis.mapper.OrderMapper">
  • <!-- 通过ID查询一个订单指定resultMap -->
  • <resultMap id="OrderMap" type="Order">
  • <result property="userId" column="user_id" />
  • </resultMap>
  • <select id="findOrderById" parameterType="Integer" resultMap="OrderMap">
  • SELECT * FROM `order` WHERE id = #{value}
  • </select>
  • </mapper>

需要注意的是,order是MySQL的关键字,需要使用反撇号引起来。然后我们编写OrderMapper类:

  • package com.coderap.mybatis.mapper;
  • import com.coderap.mybatis.pojo.Order;
  • public interface OrderMapper {
  • public Order findOrderById(Integer id);
  • }

对应的测试代码如下:

  • @Test
  • public void findOrderById() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
  • // 查询数据
  • Order order = mapper.findOrderById(3);
  • System.out.println(order);
  • }

运行上述代码,查询结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM `order` WHERE id = ?
  • DEBUG [main] - ==> Parameters: 3(Integer)
  • DEBUG [main] - <== Total: 1
  • Orders [id=3, userId=1, number=1000010, createtime=Wed Feb 04 13:22:35 CST 2015, note=null]
  • Process finished with exit code 0

4. if和where标签

Mybatis提供了if和where标签便于我们组装复杂的SQL语句。例如我们需要根据username和gender查询用户数据,就会用到where子句的拼接,有些情况下会出现只传入username或者只传入gender的情况,这个时候where的子句就难以处理,此时可以使用Mybatis的if和where标签进行处理,对于的Mapper查询语句如下:

  • <!-- 根据用户名和性别查询用户 -->
  • <!-- where标签会去掉最后生成的SQL语句的第一个“AND” -->
  • <select id="findUserByUsernameAndGender" parameterType="User" resultType="User">
  • SELECT * FROM user
  • <where>
  • <if test="username != null and username != ''">
  • username LIKE '%${username}%'
  • </if>
  • <if test="gender != null and gender != ''">
  • and gender = #{gender}
  • </if>
  • </where>
  • </select>

对应的查询接口如下:

  • public List<User> findUserByUsernameAndGender(User user);

测试代码如下:

  • @Test
  • public void findUserByUsernameAndGender() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • User user = new User();
  • user.setGender("1");
  • user.setUsername("小");
  • List<User> users = mapper.findUserByUsernameAndGender(user);
  • for (User u : users) {
  • System.out.println(u);
  • }
  • }

运行后,查询结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE username LIKE '%小%' and gender = ?
  • DEBUG [main] - ==> Parameters: 1(String)
  • DEBUG [main] - <== Total: 3
  • User [id=16, username=张小明, gender=1, birthday=null, address=上海市]
  • User [id=22, username=陈小明, gender=1, birthday=null, address=广州市]
  • User [id=25, username=陈小明, gender=1, birthday=null, address=天津市]
  • Process finished with exit code 0

5. SQL片段

在我们编写的SQL语句中,可能会经常编写某些语句片段,Mybatis提供了sql标签允许我们将常用的SQL片段进行定义,并在其他的地方进行引用:

  • <!-- 使用SQL片段 -->
  • <sql id="userSelector">
  • SELECT * FROM user
  • </sql>
  • <select id="findUserByIdWithSQLPhrase" parameterType="User" resultType="User">
  • <include refid="userSelector"/>
  • WHERE id = #{value}
  • </select>

6. foreach标签

在某些查询需求中,会遇到where...in子句,这时候我们会传递一个数组或集合包含了in条件需要的数据,Mybatis提供了foreach标签供我们拼接in条件子句。

6.1. 传入POJO包装对象的情况

如我们需要根据多个id来查询用户,有如下的接口:

  • public List<User> findUserByIds(QueryVo vo);

此处我们测试在QueryVo包装了User对象的情况下,在QueryVo对象中,有一个List装载了需要的用户id:

  • private List<Integer> userIdsList;

我们需要编写Mapper查询语句如下:

  • <!-- 根据多个id查询用户,使用where...in子句-->
  • <select id="findUserByIds" parameterType="QueryVo" resultType="User">
  • SELECT * FROM user
  • <where>
  • <if test="userIdsList != null and userIdsList.size() > 0">
  • id IN
  • <foreach collection="userIdsList" item="id" separator="," open="(" close=")">
  • #{id}
  • </foreach>
  • </if>
  • </where>
  • </select>

foreach标签分别提供了collection、item、separator、open和open来满足我们拼接where…in子句的需求。对应的查询测试如下:

  • @Test
  • public void findUserByIds() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • QueryVo queryVo = new QueryVo();
  • List<Integer> ids = new ArrayList<Integer>();
  • ids.add(10);
  • ids.add(16);
  • ids.add(22);
  • queryVo.setUserIdsList(ids);
  • List<User> users = mapper.findUserByIds(queryVo);
  • for (User user : users) {
  • System.out.println(user);
  • }
  • }

运行结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id IN ( ? , ? , ? )
  • DEBUG [main] - ==> Parameters: 10(Integer), 16(Integer), 22(Integer)
  • DEBUG [main] - <== Total: 3
  • User [id=10, username=张三, gender=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
  • User [id=16, username=张小明, gender=1, birthday=null, address=上海市]
  • User [id=22, username=陈小明, gender=1, birthday=null, address=广州市]
  • Process finished with exit code 0

6.2. 直接传入数组的情况

有些情况下我们会直接传入数组来表明数个id,查询接口如下:

  • public List<User> findUserByIds2(Integer[] ids);

我们需要编写如下的Mapper查询语句:

  • <!-- 根据多个id查询用户,使用where...in子句-->
  • <select id="findUserByIds2" parameterType="Integer[]" resultType="User">
  • SELECT * FROM user
  • <where>
  • <if test="array != null and array.length > 0">
  • id IN
  • <foreach collection="array" item="id" separator="," open="(" close=")">
  • #{id}
  • </foreach>
  • </if>
  • </where>
  • </select>

需要注意的是,此处传入的数组的属性名应该为array。

测试代码如下:

  • @Test
  • public void findUserByIds2() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • Integer[] ids = new Integer[3];
  • ids[0] = 10;
  • ids[1] = 16;
  • ids[2] = 22;
  • List<User> users = mapper.findUserByIds2(ids);
  • for (User user : users) {
  • System.out.println(user);
  • }
  • }

运行后查询结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id IN ( ? , ? , ? )
  • DEBUG [main] - ==> Parameters: 10(Integer), 16(Integer), 22(Integer)
  • DEBUG [main] - <== Total: 3
  • User [id=10, username=张三, gender=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
  • User [id=16, username=张小明, gender=1, birthday=null, address=上海市]
  • User [id=22, username=陈小明, gender=1, birthday=null, address=广州市]

6.3. 直接传入List集合的情况

有时候也会传入List集合来表明数个id,查询接口如下:

  • public List<User> findUserByIds3(List<Integer> ids);

我们需要编写如下的Mapper查询语句:

  • <!-- 根据多个id查询用户,使用where...in子句-->
  • <select id="findUserByIds3" parameterType="List" resultType="User">
  • SELECT * FROM user
  • <where>
  • <if test="list != null and list.size() > 0">
  • id IN
  • <foreach collection="list" item="id" separator="," open="(" close=")">
  • #{id}
  • </foreach>
  • </if>
  • </where>
  • </select>

需要注意的是,此处传入的集合的属性名应该为list。

测试代码如下:

  • @Test
  • public void findUserByIds3() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • List<Integer> ids = new ArrayList<Integer>();
  • ids.add(10);
  • ids.add(16);
  • ids.add(22);
  • List<User> users = mapper.findUserByIds3(ids);
  • for (User user : users) {
  • System.out.println(user);
  • }
  • }

运行后查询结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id IN ( ? , ? , ? )
  • DEBUG [main] - ==> Parameters: 10(Integer), 16(Integer), 22(Integer)
  • DEBUG [main] - <== Total: 3
  • User [id=10, username=张三, gender=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]
  • User [id=16, username=张小明, gender=1, birthday=null, address=上海市]
  • User [id=22, username=陈小明, gender=1, birthday=null, address=广州市]

7. 关联查询

Mybatis对关联查询的支持也是十分完善的,这里我们介绍一对一和一对多两种关联查询,多对多关联查询由于实际业务中用的并不多,所以不做讲解。

7.1. 一对一关联查询

在之前的内容中我们有两个POJO类:User和Order。这两个POJO类可以组成相应的关联查询。以Order为中心,Order相对于User是一对一的关系,即一个Order只能对应于一个User;而以User为中心,User相对于Order是一对多的关系,即一个User可以对应于多个Order。

我们先以Order为中心,关联User进行一对一查询。首先需要在Order类中添加一个User对象以关联对应的User数据,同时重写toString()方法:

  • // 附加对象,关联用户对象
  • private User user;
  • public User getUser() {
  • return user;
  • }
  • public void setUser(User user) {
  • this.user = user;
  • }
  • @Override
  • public String toString() {
  • String userInfo = user != null ? user.toString() : "user is null";
  • return "Order{" +
  • "id=" + id +
  • ", userId=" + userId +
  • ", number='" + number + '\'' +
  • ", createtime=" + createtime +
  • ", note='" + note + '\'' +
  • ", user=" + userInfo +
  • '}';
  • }

接下来在OrderMapper类中定义一个查询接口:

  • public List<Order> findOrders();

然后需要编写Order对应的Mapper查询语句:

  • <!-- 一对一关联查询 -->
  • <resultMap id="findOrdersMap" type="Order">
  • <id property="id" column="id"/>
  • <result property="userId" column="user_id" />
  • <association property="user" javaType="User">
  • <result property="id" column="uid" />
  • <result property="username" column="username" />
  • <result property="birthday" column="birthday" />
  • <result property="gender" column="gender" />
  • <result property="address" column="address" />
  • </association>
  • </resultMap>
  • <select id="findOrders" resultMap="findOrdersMap">
  • SELECT
  • o.id, o.user_id, o.number, o.createtime, o.note,
  • u.id as uid, u.username, u.birthday, u.gender, u.address
  • FROM
  • `order` o LEFT JOIN `user` u
  • ON
  • o.user_id = u.id;
  • </select>

一对一关联查询时我们使用association标签来指定关联的对象信息。需要注意的是,我们要手动使用resultMap对关联的数据进行一一映射,同时需要手动指定Order的id映射,否则返回的数据中Order的id是空的,还会造成数据统计错误(由于没有id)。接下来编写测试类:

  • @Test
  • public void findOrders() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
  • // 查询数据
  • List<Order> orders = mapper.findOrders();
  • for (Order order : orders) {
  • System.out.println(order);
  • }
  • }

运行后返回的结果如下:

  • DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
  • DEBUG [main] - Class not found: org.jboss.vfs.VFS
  • DEBUG [main] - JBoss 6 VFS API is not available in this environment.
  • DEBUG [main] - Class not found: org.jboss.vfs.VirtualFile
  • DEBUG [main] - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
  • DEBUG [main] - Using VFS adapter org.apache.ibatis.io.DefaultVFS
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Reader entry: Order.class
  • DEBUG [main] - Reader entry: QueryVo.class
  • DEBUG [main] - Reader entry: User.class
  • DEBUG [main] - Listing file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/Order.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/Order.class
  • DEBUG [main] - Reader entry: ���� 1 e
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/QueryVo.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/QueryVo.class
  • DEBUG [main] - Reader entry: ���� 1 4
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/User.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/pojo/User.class
  • DEBUG [main] - Reader entry: ���� 1 R
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.pojo.Order matches criteria [is assignable to Object]
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.pojo.QueryVo matches criteria [is assignable to Object]
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.pojo.User matches criteria [is assignable to Object]
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Reader entry: OrderMapper.class
  • DEBUG [main] - Reader entry: OrderMapper.xml
  • DEBUG [main] - Reader entry: UserMapper.class
  • DEBUG [main] - Reader entry: UserMapper.xml
  • DEBUG [main] - Listing file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/OrderMapper.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/OrderMapper.class
  • DEBUG [main] - Reader entry: ���� 1
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/OrderMapper.xml
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/OrderMapper.xml
  • DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.class
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.class
  • DEBUG [main] - Reader entry: ���� 1    findUserById 4(Ljava/lang/Integer;)Lcom/coderap/mybatis/pojo/User; findUserByIdWithSQLPhrase findUserByQueryVo C(Lcom/coderap/mybatis/pojo/QueryVo;)Lcom/coderap/mybatis/pojo/User; countUser ()Ljava/lang/Integer; findUserByUsernameAndGender 1(Lcom/coderap/mybatis/pojo/User;)Ljava/util/List; Signature R(Lcom/coderap/mybatis/pojo/User;)Ljava/util/List<Lcom/coderap/mybatis/pojo/User;>;
  • DEBUG [main] - Find JAR URL: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.xml
  • DEBUG [main] - Not a JAR: file:/D:/WorkSpace/Idea/MybatisStudy/Study-01/target/classes/com/coderap/mybatis/mapper/UserMapper.xml
  • DEBUG [main] - Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.mapper.OrderMapper matches criteria [is assignable to Object]
  • DEBUG [main] - Checking to see if class com.coderap.mybatis.mapper.UserMapper matches criteria [is assignable to Object]
  • DEBUG [main] - Opening JDBC Connection
  • DEBUG [main] - Created connection 1773206895.
  • DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@69b0fd6f]
  • DEBUG [main] - ==> Preparing: SELECT o.id, o.user_id, o.number, o.createtime, o.note, u.id as uid, u.username, u.birthday, u.gender, u.address FROM `order` o LEFT JOIN `user` u ON o.user_id = u.id;
  • DEBUG [main] - ==> Parameters:
  • DEBUG [main] - <== Total: 3
  • Order{id=3, userId=1, number='null', createtime=null, note='null', user=User [id=1, username=王五, gender=2, birthday=null, address=null]}
  • Order{id=4, userId=1, number='null', createtime=null, note='null', user=User [id=1, username=王五, gender=2, birthday=null, address=null]}
  • Order{id=5, userId=10, number='null', createtime=null, note='null', user=User [id=10, username=张三, gender=1, birthday=Thu Jul 10 00:00:00 CST 2014, address=北京市]}
  • Process finished with exit code 0

数据库查询的结果如下:

1.一对一关联查询结果.png

7.2. 一对多关联查询

有了一对一的关联查询,一对多的关联查询就变得简单了。我们以查询User为例,一个User可以对应多个订单,在User中添加一个List对象来装载所有的Order,并重写其toString()方法:

  • private List<Order> orders;
  • public List<Order> getOrders() {
  • return orders;
  • }
  • public void setOrders(List<Order> orders) {
  • this.orders = orders;
  • }
  • @Override
  • public String toString() {
  • StringBuilder ordersInfo = new StringBuilder();
  • if (orders != null && orders.size() > 0) {
  • for (Order order : orders) {
  • ordersInfo.append(order + ", ");
  • }
  • } else {
  • ordersInfo.append("orders is null");
  • }
  • return "User{" +
  • "id=" + id +
  • ", username='" + username + '\'' +
  • ", gender='" + gender + '\'' +
  • ", birthday=" + birthday +
  • ", address='" + address + '\'' +
  • ", orders=" + ordersInfo +
  • '}';
  • }

然后指定一个查询接口如下:

  • public List<User> findUsersWithOrders();

我们需要编写以下的Mapper查询语句:

  • <!-- 根据用户查询订单信息,一对多关联 -->
  • <resultMap id="findUsersWithOrdersMap" type="User">
  • <id property="id" column="id"/>
  • <result property="username" column="username" />
  • <result property="birthday" column="birthday" />
  • <result property="gender" column="gender" />
  • <result property="address" column="address" />
  • <collection property="orders" ofType="Order">
  • <id property="id" column="oid"/>
  • <result property="number" column="number" />
  • <result property="createtime" column="createtime" />
  • <result property="note" column="note" />
  • </collection>
  • </resultMap>
  • <select id="findUsersWithOrders" resultMap="findUsersWithOrdersMap">
  • SELECT
  • u.id, u.username, u.birthday, u.gender, u.address,
  • o.id as oid, o.user_id, o.number, o.createtime, o.note
  • FROM
  • `user` u LEFT JOIN `order` o
  • ON
  • o.user_id = u.id;
  • </select>

在进行对多个对象的关联时,使用collection标签来指定多个对象的信息。我们的测试方法如下:

  • @Test
  • public void findUsersWithOrders() throws Exception {
  • // 加载核心配置文件
  • String path = "sqlMapConfig.xml";
  • InputStream resourceAsStream = Resources.getResourceAsStream(path);
  • // 创建SqlSessionFactory
  • SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
  • // 创建SqlSession
  • SqlSession sqlSession = sqlSessionFactory.openSession();
  • // 获取Mapper
  • UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  • // 查询数据
  • List<User> users = mapper.findUsersWithOrders();
  • for (User user : users) {
  • System.out.println(user);
  • }
  • }

运行后,可以得到下面的打印信息:

  • DEBUG [main] - ==> Preparing: SELECT u.id, u.username, u.birthday, u.gender, u.address, o.id as oid, o.user_id, o.number, o.createtime, o.note FROM `user` u LEFT JOIN `order` o ON o.user_id = u.id;
  • DEBUG [main] - ==> Parameters:
  • DEBUG [main] - <== Total: 10
  • User{id=1, username='王五', gender='2', birthday=null, address='null', orders=Order{id=3, userId=null, number='1000010', createtime=Wed Feb 04 13:22:35 CST 2015, note='null', user=user is null}, Order{id=4, userId=null, number='1000011', createtime=Tue Feb 03 13:22:41 CST 2015, note='null', user=user is null}, }
  • User{id=10, username='张三', gender='1', birthday=Thu Jul 10 00:00:00 CST 2014, address='北京市', orders=Order{id=5, userId=null, number='1000012', createtime=Thu Feb 12 16:13:23 CST 2015, note='null', user=user is null}, }
  • User{id=16, username='张小明', gender='1', birthday=null, address='上海市', orders=orders is null}
  • User{id=22, username='陈小明', gender='1', birthday=null, address='广州市', orders=orders is null}
  • User{id=24, username='张三丰', gender='1', birthday=null, address='深圳市', orders=orders is null}
  • User{id=25, username='陈小明', gender='1', birthday=null, address='天津市', orders=orders is null}
  • User{id=26, username='王五', gender='null', birthday=null, address='null', orders=orders is null}
  • User{id=27, username='李四', gender='男', birthday=Sat Mar 31 00:00:00 CST 2018, address='重庆市', orders=orders is null}
  • User{id=28, username='李四', gender='男', birthday=Sat Mar 31 00:00:00 CST 2018, address='重庆市', orders=orders is null}
  • Process finished with exit code 0

数据库查询的结果如下:

2.一对多关联查询结果.png

8. Mybatis和Spring整合

接下来我们将进行Mybatis和Spring的整合开发,首先我们需要修改Pom.xml文件引入整合需要的所有依赖:

  • <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
  • <dependency>
  • <groupId>org.mybatis</groupId>
  • <artifactId>mybatis</artifactId>
  • <version>3.2.7</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  • <dependency>
  • <groupId>mysql</groupId>
  • <artifactId>mysql-connector-java</artifactId>
  • <version>5.1.46</version>
  • </dependency>
  • <dependency>
  • <groupId>junit</groupId>
  • <artifactId>junit</artifactId>
  • <version>4.12</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
  • <dependency>
  • <groupId>org.apache.logging.log4j</groupId>
  • <artifactId>log4j-core</artifactId>
  • <version>2.0</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api -->
  • <dependency>
  • <groupId>org.apache.logging.log4j</groupId>
  • <artifactId>log4j-api</artifactId>
  • <version>2.0</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/log4j/log4j -->
  • <dependency>
  • <groupId>log4j</groupId>
  • <artifactId>log4j</artifactId>
  • <version>1.2.17</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-log4j12 -->
  • <dependency>
  • <groupId>org.slf4j</groupId>
  • <artifactId>slf4j-log4j12</artifactId>
  • <version>1.7.5</version>
  • <scope>test</scope>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.slf4j/slf4j-api -->
  • <dependency>
  • <groupId>org.slf4j</groupId>
  • <artifactId>slf4j-api</artifactId>
  • <version>1.7.5</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/commons-logging/commons-logging -->
  • <dependency>
  • <groupId>commons-logging</groupId>
  • <artifactId>commons-logging</artifactId>
  • <version>1.1.1</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
  • <dependency>
  • <groupId>commons-dbcp</groupId>
  • <artifactId>commons-dbcp</artifactId>
  • <version>1.2.2</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
  • <dependency>
  • <groupId>commons-pool</groupId>
  • <artifactId>commons-pool</artifactId>
  • <version>1.3</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/javax.servlet.jsp.jstl/jstl -->
  • <dependency>
  • <groupId>javax.servlet.jsp.jstl</groupId>
  • <artifactId>jstl</artifactId>
  • <version>1.2</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
  • <dependency>
  • <groupId>org.mybatis</groupId>
  • <artifactId>mybatis-spring</artifactId>
  • <version>1.2.2</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-aop</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-aspects -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-aspects</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-beans</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-context</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-context-support -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-context-support</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-core</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-expression -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-expression</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-jdbc</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-jms -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-jms</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-messaging -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-messaging</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-tx</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-web -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-web</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
  • <dependency>
  • <groupId>org.springframework</groupId>
  • <artifactId>spring-webmvc</artifactId>
  • <version>4.1.3.RELEASE</version>
  • </dependency>
  • <dependency>
  • <groupId>junit</groupId>
  • <artifactId>junit</artifactId>
  • <version>4.12</version>
  • </dependency>

8.1. 原始Dao开发

我们先进行原始的Dao开发的整合。首先需要创建Spring所需要的applicationContext.xml文件,基本内容如下:

  • <?xml version="1.0" encoding="UTF-8"?>
  • <beans xmlns="http://www.springframework.org/schema/beans"
  • xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
  • xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
  • xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  • xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
  • http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
  • http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
  • http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">
  • <context:property-placeholder location="classpath:jdbc.properties"/>
  • <!-- 数据库连接池 -->
  • <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  • destroy-method="close">
  • <property name="driverClassName" value="${jdbc.driver}" />
  • <property name="url" value="${jdbc.url}" />
  • <property name="username" value="${jdbc.username}" />
  • <property name="password" value="${jdbc.password}" />
  • <property name="maxActive" value="10" />
  • <property name="maxIdle" value="5" />
  • </bean>
  • </beans>

上面的代码中,做了基本的数据库连接池的配置,首先引入了jdbc.properties文件,有关数据库连接的配置全写在该文件中,内容如下:

  • jdbc.driver=com.mysql.jdbc.Driver
  • jdbc.url=jdbc:mysql://localhost:3306/javaeetest?characterEncoding=utf-8
  • jdbc.username=root
  • jdbc.password=12345678

接下来,我们需要让Spring容器来为我们生成Mybatis的SqlSessionFactory,直接添加如下bean配置:

  • <!-- Mybatis的工厂 -->
  • <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
  • <property name="dataSource" ref="dataSource"/>
  • <!-- 核心配置文件的位置 -->
  • <property name="configLocation" value="classpath:sqlMapConfig.xml"/>
  • </bean>

在使用SqlSessionFactoryBean工厂Bean创建SqlSessionFactory时,需要传入DataSource以及Mybatis的配置文件sqlMapConfig.xml的位置,DataSource在上面已经配置好了,直接引入即可,而此时sqlMapConfig.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>
  • <!-- 别名 -->
  • <typeAliases>
  • <package name="com.coderap.mybatis.pojo" />
  • </typeAliases>
  • <!-- Mapper文件的位置 -->
  • <mappers>
  • <package name="com.coderap.mybatis.mapper" />
  • </mappers>
  • </configuration>

可以发现,此时的sqlMapConfig.xml配置文件已经将原有的连接数据库的配置去掉了。接下来我们创建一个UserDao以及它的实现类:

  • package com.coderap.mybatis.dao;
  • public interface UserDao {
  • }
  • package com.coderap.mybatis.dao;
  • public class UserDaoImpl implements UserDao {
  • }

然后在applicationContext.xml中通过Spring容器生成UserDao的Bean:

  • <!-- 原始Dao -->
  • <bean id="userDao" class="com.coderap.mybatis.dao.UserDaoImpl">
  • <property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
  • </bean>

在之前的开发中我们知道,Dao的实现类需要拿到SqlSessionFactory对象,才可以使用该会话对象对数据库进行操作,也就意味着我们需要在每个Dao的实现类中定义一个属性用于接受该会话对象,在上面的配置中我们直接使用<property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>进行注入,这是因为,我们可以直接让UserDao的实现类继承SqlSessionDaoSupport,这个类内部就定义好了一个SqlSessionFactory对象用于接受外部注入的会话对象,此时的UserDaoImpl类应该写作这样:

  • package com.coderap.mybatis.dao;
  • import org.mybatis.spring.support.SqlSessionDaoSupport;
  • public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
  • }

SqlSessionDaoSupport是由org.mybatis.spring.support整合包提供的,同时,该类提供了getSession()方法方便子类拿到会话对象,我们就可以通过会话对象进行数据库的查询操作了,如:

  • package com.coderap.mybatis.dao;
  • import com.coderap.mybatis.pojo.User;
  • import org.mybatis.spring.support.SqlSessionDaoSupport;
  • public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
  • public User findUserById(Integer id) {
  • return this.getSqlSession().selectOne("com.coderap.mybatis.mapper.UserMapper.findUserById", id);
  • }
  • }

对应的Mapper查询语句如下:

  • <?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.coderap.mybatis.mapper.UserMapper">
  • <!-- 通过ID查询一个用户 -->
  • <select id="findUserById" parameterType="Integer" resultType="User">
  • SELECT * FROM user WHERE id = #{value}
  • </select>
  • </mapper>

测试代码如下:

  • @Test
  • public void findUserById() {
  • ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  • UserDao userDao = applicationContext.getBean(UserDao.class);
  • System.out.println(userDao.findUserById(10));
  • }

运行后可以得到下面的结果:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id = ?
  • DEBUG [main] - ==> Parameters: 10(Integer)
  • DEBUG [main] - <== Total: 1
  • DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1757cd72]
  • DEBUG [main] - Returning JDBC Connection to DataSource
  • User{id=10, username='张三', gender='1', birthday=Thu Jul 10 00:00:00 CST 2014, address='北京市', orders=orders is null}
  • Process finished with exit code 0

8.2. Mapper动态代理开发

使用Mapper动态代理开发的模式是类似的,我们先在com.coderap.mybatis.mapper包下创建UserMapper接口类,其内容如下:

  • package com.coderap.mybatis.mapper;
  • public interface UserMapper {
  • }

接下来,我们需要在applicationContext.xml文件中进行配置,让Spring容器为我们动态生成对应的Mapper类:

  • <!-- Mapper动态代理开发 -->
  • <bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
  • <property name="sqlSessionFactory" ref="sqlSessionFactoryBean"/>
  • <property name="mapperInterface" value="com.coderap.mybatis.mapper.UserMapper"/>
  • </bean>

上述代码中,我们声明了一个类型为org.mybatis.spring.mapper.MapperFactoryBean的bean对象userMapper,这是由整合包提供的,它可以根据SqlSessionFactory和MapperInterface生成我们需要的Mapper类,所以我们还需要将SqlSessionFactory和MapperInterface传入,SqlSessionFactory在之前已经定义,直接传入即可,而MapperInterface则需要传入我们需要动态生成实现类的Mapper接口。

接下来我们可以直接进行测试了,在UserMapper接口类中添加查询接口:

  • package com.coderap.mybatis.mapper;
  • import com.coderap.mybatis.pojo.User;
  • public interface UserMapper {
  • public User findUserById(Integer id);
  • }

对应的Mapper查询语句如下:

  • <?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.coderap.mybatis.mapper.UserMapper">
  • <!-- 通过ID查询一个用户 -->
  • <select id="findUserById" parameterType="Integer" resultType="User">
  • SELECT * FROM user WHERE id = #{value}
  • </select>
  • </mapper>

测试代码如下:

  • @Test
  • public void findUserById1() {
  • ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  • UserMapper userMapper = (UserMapper)applicationContext.getBean("userMapper");
  • System.out.println(userMapper.findUserById(22));
  • }

运行结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id = ?
  • DEBUG [main] - ==> Parameters: 22(Integer)
  • DEBUG [main] - <== Total: 1
  • DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37271612]
  • DEBUG [main] - Returning JDBC Connection to DataSource
  • User{id=22, username='陈小明', gender='1', birthday=null, address='广州市', orders=orders is null}
  • Process finished with exit code 0

8.3. Mapper动态代理开发增强

上面的方式,需要对每一个Mapper接口类进行容器装载,如果Mapper接口过多依旧是不方便的,所以我们可以使用扫描的方式来增强这种方式,相应的容器配置如下:

  • <!-- Mapper动态代理开发,使用扫描 -->
  • <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  • <!-- 基本包 -->
  • <property name="basePackage" value="com.coderap.mybatis.mapper"/>
  • </bean>

上面的配置中并不需要传入SqlSessionFactory,因为这种方式下会自动去容器中扫描并找到SqlSessionFactory,然后将Mapper接口所在的包传入即可。

相应的测试代码如下:

  • @Test
  • public void findUserById2() {
  • ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  • UserMapper userMapper = applicationContext.getBean(UserMapper.class);
  • System.out.println(userMapper.findUserById(26));
  • }

运行结果如下:

  • DEBUG [main] - ==> Preparing: SELECT * FROM user WHERE id = ?
  • DEBUG [main] - ==> Parameters: 26(Integer)
  • DEBUG [main] - <== Total: 1
  • DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@37271612]
  • DEBUG [main] - Returning JDBC Connection to DataSource
  • User{id=26, username='王五', gender='null', birthday=null, address='null', orders=orders is null}
  • Process finished with exit code 0

9. Mybatis逆向工程

我们可以使用使用官方网站的Mapper自动生成工具mybatis-generator-core-1.3.2来生成POJO类和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>
  • <context id="testTables" targetRuntime="MyBatis3">
  • <commentGenerator>
  • <!-- 是否去除自动生成的注释 true:是 : false:否 -->
  • <property name="suppressAllComments" value="true" />
  • </commentGenerator>
  • <!--数据库连接的信息:驱动类、连接地址、用户名、密码 -->
  • <jdbcConnection driverClass="com.mysql.jdbc.Driver"
  • connectionURL="jdbc:mysql://localhost:3306/javaeetest" userId="root"
  • password="12345678">
  • </jdbcConnection>
  • <!-- <jdbcConnection driverClass="oracle.jdbc.OracleDriver"
  • connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
  • userId="yycg"
  • password="yycg">
  • </jdbcConnection> -->
  • <!-- 默认false,把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer,为 true时把JDBC DECIMAL 和
  • NUMERIC 类型解析为java.math.BigDecimal -->
  • <javaTypeResolver>
  • <property name="forceBigDecimals" value="false" />
  • </javaTypeResolver>
  • <!-- targetProject:生成PO类的位置 -->
  • <javaModelGenerator targetPackage="com.coderap.mybatis.pojo"
  • targetProject=".\GeneratorSqlmapCustom\src">
  • <!-- enableSubPackages:是否让schema作为包的后缀 -->
  • <property name="enableSubPackages" value="false" />
  • <!-- 从数据库返回的值被清理前后的空格 -->
  • <property name="trimStrings" value="true" />
  • </javaModelGenerator>
  • <!-- targetProject:mapper映射文件生成的位置 -->
  • <sqlMapGenerator targetPackage="com.coderap.mybatis.mapper"
  • targetProject=".\GeneratorSqlmapCustom\src">
  • <!-- enableSubPackages:是否让schema作为包的后缀 -->
  • <property name="enableSubPackages" value="false" />
  • </sqlMapGenerator>
  • <!-- targetPackage:mapper接口生成的位置 -->
  • <javaClientGenerator type="XMLMAPPER"
  • targetPackage="com.coderap.mybatis.mapper"
  • targetProject=".\GeneratorSqlmapCustom\src">
  • <!-- enableSubPackages:是否让schema作为包的后缀 -->
  • <property name="enableSubPackages" value="false" />
  • </javaClientGenerator>
  • <!-- 指定数据库表 -->
  • <table schema="" tableName="user"></table>
  • <table schema="" tableName="order"></table>
  • <!-- 有些表的字段需要指定java类型
  • <table schema="" tableName="user">
  • <columnOverride column="id" javaType="Long" />
  • </table> -->
  • </context>
  • </generatorConfiguration>

修改主类文件GeneratorSqlmap.java如下:

  • import org.mybatis.generator.api.MyBatisGenerator;
  • import org.mybatis.generator.config.Configuration;
  • import org.mybatis.generator.config.xml.ConfigurationParser;
  • import org.mybatis.generator.internal.DefaultShellCallback;
  • import java.io.File;
  • import java.net.URL;
  • import java.util.ArrayList;
  • import java.util.List;
  • public class GeneratorSqlmap {
  • public void generator() throws Exception{
  • List<String> warnings = new ArrayList<String>();
  • boolean overwrite = true;
  • //指定 逆向工程配置文件
  • URL url = getClass().getClassLoader().getResource("generatorConfig.xml");
  • File configFile = new File(url.getFile());
  • ConfigurationParser cp = new ConfigurationParser(warnings);
  • Configuration config = cp.parseConfiguration(configFile);
  • DefaultShellCallback callback = new DefaultShellCallback(overwrite);
  • MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
  • callback, warnings);
  • myBatisGenerator.generate(null);
  • }
  • public static void main(String[] args) throws Exception {
  • try {
  • GeneratorSqlmap generatorSqlmap = new GeneratorSqlmap();
  • generatorSqlmap.generator();
  • } catch (Exception e) {
  • e.printStackTrace();
  • }
  • }
  • }

直接运行项目即可,它会给我们生成所需的POJO和Mapper文件。我们查看User.java文件内容如下:

  • package com.coderap.mybatis.pojo;
  • import java.util.Date;
  • public class User {
  • private Integer id;
  • private String username;
  • private Date birthday;
  • private String gender;
  • private String address;
  • public Integer getId() {
  • return id;
  • }
  • public void setId(Integer id) {
  • this.id = id;
  • }
  • public String getUsername() {
  • return username;
  • }
  • public void setUsername(String username) {
  • this.username = username == null ? null : username.trim();
  • }
  • public Date getBirthday() {
  • return birthday;
  • }
  • public void setBirthday(Date birthday) {
  • this.birthday = birthday;
  • }
  • public String getGender() {
  • return gender;
  • }
  • public void setGender(String gender) {
  • this.gender = gender == null ? null : gender.trim();
  • }
  • public String getAddress() {
  • return address;
  • }
  • public void setAddress(String address) {
  • this.address = address == null ? null : address.trim();
  • }
  • }

以及UserMapper.java文件:

  • package com.coderap.mybatis.mapper;
  • import com.coderap.mybatis.pojo.User;
  • import com.coderap.mybatis.pojo.UserExample;
  • import java.util.List;
  • import org.apache.ibatis.annotations.Param;
  • public interface UserMapper {
  • int countByExample(UserExample example);
  • int deleteByExample(UserExample example);
  • int deleteByPrimaryKey(Integer id);
  • int insert(User record);
  • int insertSelective(User record);
  • List<User> selectByExample(UserExample example);
  • User selectByPrimaryKey(Integer id);
  • int updateByExampleSelective(@Param("record") User record, @Param("example") UserExample example);
  • int updateByExample(@Param("record") User record, @Param("example") UserExample example);
  • int updateByPrimaryKeySelective(User record);
  • int updateByPrimaryKey(User record);
  • }

可以发现,这里使用了一个UserExample的类,它的内容如下:

  • package com.coderap.mybatis.pojo;
  • import java.util.ArrayList;
  • import java.util.Date;
  • import java.util.Iterator;
  • import java.util.List;
  • public class UserExample {
  • protected String orderByClause;
  • protected boolean distinct;
  • protected List<Criteria> oredCriteria;
  • public UserExample() {
  • oredCriteria = new ArrayList<Criteria>();
  • }
  • public void setOrderByClause(String orderByClause) {
  • this.orderByClause = orderByClause;
  • }
  • public String getOrderByClause() {
  • return orderByClause;
  • }
  • public void setDistinct(boolean distinct) {
  • this.distinct = distinct;
  • }
  • public boolean isDistinct() {
  • return distinct;
  • }
  • public List<Criteria> getOredCriteria() {
  • return oredCriteria;
  • }
  • public void or(Criteria criteria) {
  • oredCriteria.add(criteria);
  • }
  • public Criteria or() {
  • Criteria criteria = createCriteriaInternal();
  • oredCriteria.add(criteria);
  • return criteria;
  • }
  • public Criteria createCriteria() {
  • Criteria criteria = createCriteriaInternal();
  • if (oredCriteria.size() == 0) {
  • oredCriteria.add(criteria);
  • }
  • return criteria;
  • }
  • protected Criteria createCriteriaInternal() {
  • Criteria criteria = new Criteria();
  • return criteria;
  • }
  • public void clear() {
  • oredCriteria.clear();
  • orderByClause = null;
  • distinct = false;
  • }
  • protected abstract static class GeneratedCriteria {
  • protected List<Criterion> criteria;
  • protected GeneratedCriteria() {
  • super();
  • criteria = new ArrayList<Criterion>();
  • }
  • public boolean isValid() {
  • return criteria.size() > 0;
  • }
  • public List<Criterion> getAllCriteria() {
  • return criteria;
  • }
  • public List<Criterion> getCriteria() {
  • return criteria;
  • }
  • protected void addCriterion(String condition) {
  • if (condition == null) {
  • throw new RuntimeException("Value for condition cannot be null");
  • }
  • criteria.add(new Criterion(condition));
  • }
  • protected void addCriterion(String condition, Object value, String property) {
  • if (value == null) {
  • throw new RuntimeException("Value for " + property + " cannot be null");
  • }
  • criteria.add(new Criterion(condition, value));
  • }
  • protected void addCriterion(String condition, Object value1, Object value2, String property) {
  • if (value1 == null || value2 == null) {
  • throw new RuntimeException("Between values for " + property + " cannot be null");
  • }
  • criteria.add(new Criterion(condition, value1, value2));
  • }
  • protected void addCriterionForJDBCDate(String condition, Date value, String property) {
  • if (value == null) {
  • throw new RuntimeException("Value for " + property + " cannot be null");
  • }
  • addCriterion(condition, new java.sql.Date(value.getTime()), property);
  • }
  • protected void addCriterionForJDBCDate(String condition, List<Date> values, String property) {
  • if (values == null || values.size() == 0) {
  • throw new RuntimeException("Value list for " + property + " cannot be null or empty");
  • }
  • List<java.sql.Date> dateList = new ArrayList<java.sql.Date>();
  • Iterator<Date> iter = values.iterator();
  • while (iter.hasNext()) {
  • dateList.add(new java.sql.Date(iter.next().getTime()));
  • }
  • addCriterion(condition, dateList, property);
  • }
  • protected void addCriterionForJDBCDate(String condition, Date value1, Date value2, String property) {
  • if (value1 == null || value2 == null) {
  • throw new RuntimeException("Between values for " + property + " cannot be null");
  • }
  • addCriterion(condition, new java.sql.Date(value1.getTime()), new java.sql.Date(value2.getTime()), property);
  • }
  • public Criteria andIdIsNull() {
  • addCriterion("id is null");
  • return (Criteria) this;
  • }
  • public Criteria andIdIsNotNull() {
  • addCriterion("id is not null");
  • return (Criteria) this;
  • }
  • public Criteria andIdEqualTo(Integer value) {
  • addCriterion("id =", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdNotEqualTo(Integer value) {
  • addCriterion("id <>", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdGreaterThan(Integer value) {
  • addCriterion("id >", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdGreaterThanOrEqualTo(Integer value) {
  • addCriterion("id >=", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdLessThan(Integer value) {
  • addCriterion("id <", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdLessThanOrEqualTo(Integer value) {
  • addCriterion("id <=", value, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdIn(List<Integer> values) {
  • addCriterion("id in", values, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdNotIn(List<Integer> values) {
  • addCriterion("id not in", values, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdBetween(Integer value1, Integer value2) {
  • addCriterion("id between", value1, value2, "id");
  • return (Criteria) this;
  • }
  • public Criteria andIdNotBetween(Integer value1, Integer value2) {
  • addCriterion("id not between", value1, value2, "id");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameIsNull() {
  • addCriterion("username is null");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameIsNotNull() {
  • addCriterion("username is not null");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameEqualTo(String value) {
  • addCriterion("username =", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameNotEqualTo(String value) {
  • addCriterion("username <>", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameGreaterThan(String value) {
  • addCriterion("username >", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameGreaterThanOrEqualTo(String value) {
  • addCriterion("username >=", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameLessThan(String value) {
  • addCriterion("username <", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameLessThanOrEqualTo(String value) {
  • addCriterion("username <=", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameLike(String value) {
  • addCriterion("username like", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameNotLike(String value) {
  • addCriterion("username not like", value, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameIn(List<String> values) {
  • addCriterion("username in", values, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameNotIn(List<String> values) {
  • addCriterion("username not in", values, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameBetween(String value1, String value2) {
  • addCriterion("username between", value1, value2, "username");
  • return (Criteria) this;
  • }
  • public Criteria andUsernameNotBetween(String value1, String value2) {
  • addCriterion("username not between", value1, value2, "username");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayIsNull() {
  • addCriterion("birthday is null");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayIsNotNull() {
  • addCriterion("birthday is not null");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayEqualTo(Date value) {
  • addCriterionForJDBCDate("birthday =", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayNotEqualTo(Date value) {
  • addCriterionForJDBCDate("birthday <>", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayGreaterThan(Date value) {
  • addCriterionForJDBCDate("birthday >", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayGreaterThanOrEqualTo(Date value) {
  • addCriterionForJDBCDate("birthday >=", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayLessThan(Date value) {
  • addCriterionForJDBCDate("birthday <", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayLessThanOrEqualTo(Date value) {
  • addCriterionForJDBCDate("birthday <=", value, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayIn(List<Date> values) {
  • addCriterionForJDBCDate("birthday in", values, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayNotIn(List<Date> values) {
  • addCriterionForJDBCDate("birthday not in", values, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayBetween(Date value1, Date value2) {
  • addCriterionForJDBCDate("birthday between", value1, value2, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andBirthdayNotBetween(Date value1, Date value2) {
  • addCriterionForJDBCDate("birthday not between", value1, value2, "birthday");
  • return (Criteria) this;
  • }
  • public Criteria andGenderIsNull() {
  • addCriterion("gender is null");
  • return (Criteria) this;
  • }
  • public Criteria andGenderIsNotNull() {
  • addCriterion("gender is not null");
  • return (Criteria) this;
  • }
  • public Criteria andGenderEqualTo(String value) {
  • addCriterion("gender =", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderNotEqualTo(String value) {
  • addCriterion("gender <>", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderGreaterThan(String value) {
  • addCriterion("gender >", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderGreaterThanOrEqualTo(String value) {
  • addCriterion("gender >=", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderLessThan(String value) {
  • addCriterion("gender <", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderLessThanOrEqualTo(String value) {
  • addCriterion("gender <=", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderLike(String value) {
  • addCriterion("gender like", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderNotLike(String value) {
  • addCriterion("gender not like", value, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderIn(List<String> values) {
  • addCriterion("gender in", values, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderNotIn(List<String> values) {
  • addCriterion("gender not in", values, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderBetween(String value1, String value2) {
  • addCriterion("gender between", value1, value2, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andGenderNotBetween(String value1, String value2) {
  • addCriterion("gender not between", value1, value2, "gender");
  • return (Criteria) this;
  • }
  • public Criteria andAddressIsNull() {
  • addCriterion("address is null");
  • return (Criteria) this;
  • }
  • public Criteria andAddressIsNotNull() {
  • addCriterion("address is not null");
  • return (Criteria) this;
  • }
  • public Criteria andAddressEqualTo(String value) {
  • addCriterion("address =", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressNotEqualTo(String value) {
  • addCriterion("address <>", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressGreaterThan(String value) {
  • addCriterion("address >", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressGreaterThanOrEqualTo(String value) {
  • addCriterion("address >=", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressLessThan(String value) {
  • addCriterion("address <", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressLessThanOrEqualTo(String value) {
  • addCriterion("address <=", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressLike(String value) {
  • addCriterion("address like", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressNotLike(String value) {
  • addCriterion("address not like", value, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressIn(List<String> values) {
  • addCriterion("address in", values, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressNotIn(List<String> values) {
  • addCriterion("address not in", values, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressBetween(String value1, String value2) {
  • addCriterion("address between", value1, value2, "address");
  • return (Criteria) this;
  • }
  • public Criteria andAddressNotBetween(String value1, String value2) {
  • addCriterion("address not between", value1, value2, "address");
  • return (Criteria) this;
  • }
  • }
  • public static class Criteria extends GeneratedCriteria {
  • protected Criteria() {
  • super();
  • }
  • }
  • public static class Criterion {
  • private String condition;
  • private Object value;
  • private Object secondValue;
  • private boolean noValue;
  • private boolean singleValue;
  • private boolean betweenValue;
  • private boolean listValue;
  • private String typeHandler;
  • public String getCondition() {
  • return condition;
  • }
  • public Object getValue() {
  • return value;
  • }
  • public Object getSecondValue() {
  • return secondValue;
  • }
  • public boolean isNoValue() {
  • return noValue;
  • }
  • public boolean isSingleValue() {
  • return singleValue;
  • }
  • public boolean isBetweenValue() {
  • return betweenValue;
  • }
  • public boolean isListValue() {
  • return listValue;
  • }
  • public String getTypeHandler() {
  • return typeHandler;
  • }
  • protected Criterion(String condition) {
  • super();
  • this.condition = condition;
  • this.typeHandler = null;
  • this.noValue = true;
  • }
  • protected Criterion(String condition, Object value, String typeHandler) {
  • super();
  • this.condition = condition;
  • this.value = value;
  • this.typeHandler = typeHandler;
  • if (value instanceof List<?>) {
  • this.listValue = true;
  • } else {
  • this.singleValue = true;
  • }
  • }
  • protected Criterion(String condition, Object value) {
  • this(condition, value, null);
  • }
  • protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
  • super();
  • this.condition = condition;
  • this.value = value;
  • this.secondValue = secondValue;
  • this.typeHandler = typeHandler;
  • this.betweenValue = true;
  • }
  • protected Criterion(String condition, Object value, Object secondValue) {
  • this(condition, value, secondValue, null);
  • }
  • }
  • }

这个类是Mybatis为我们生成的User对象的扩展类,使用它可以进行更方便的查询,如:

  • @Test
  • public void selectByExample() {
  • ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
  • UserMapper userMapper = applicationContext.getBean(UserMapper.class);
  • UserExample userExample = new UserExample();
  • userExample.setOrderByClause("id desc");
  • userExample.setDistinct(true);
  • List<User> users = userMapper.selectByExample(userExample);
  • for (User user : users) {
  • System.out.println(user);
  • }
  • }

运行结果如下:

  • DEBUG [main] - ==> Preparing: select distinct id, username, birthday, gender, address from user order by id desc
  • DEBUG [main] - ==> Parameters:
  • DEBUG [main] - <== Total: 9
  • DEBUG [main] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5f8edcc5]
  • DEBUG [main] - Returning JDBC Connection to DataSource
  • User{id=28, username='李四', birthday=Sat Mar 31 00:00:00 CST 2018, gender='男', address='重庆市'}
  • User{id=27, username='李四', birthday=Sat Mar 31 00:00:00 CST 2018, gender='男', address='重庆市'}
  • User{id=26, username='王五', birthday=null, gender='null', address='null'}
  • User{id=25, username='陈小明', birthday=null, gender='1', address='天津市'}
  • User{id=24, username='张三丰', birthday=null, gender='1', address='深圳市'}
  • User{id=22, username='陈小明', birthday=null, gender='1', address='广州市'}
  • User{id=16, username='张小明', birthday=null, gender='1', address='上海市'}
  • User{id=10, username='张三', birthday=Thu Jul 10 00:00:00 CST 2014, gender='1', address='北京市'}
  • User{id=1, username='王五', birthday=null, gender='2', address='null'}
  • Process finished with exit code 0