MyBatis浅尝笔记

MyBatis应属于一种轻量级的java持久层技术，它通过简单的SQL xml或注解，将数据库数据映射到接口与POJO。最近项目要用到mybatis，所以学习之后在这里做个总结，文中的示例以xml配置为主，mybatis也支持注解的方式。

测试数据

先给出demo所使用的表结构，以典型的用户（1）-文章（n）的关系表做demo数据

 1 #
 2 # mysql数据库：数据库名 :dblog
 3 #
 4 
 5 DROP TABLE IF EXISTS m_category;
 6 CREATE TABLE m_category (
 7   id int(11) NOT NULL AUTO_INCREMENT,
 8   name varchar(64) NOT NULL COMMENT '分类名称',
 9   parent_id INT NOT NULL ,
10   level INT NOT NULL DEFAULT 0,
11   path VARCHAR(64) NOT NULL COMMENT '栏目路径，rootId-xxId-xxId',
12   PRIMARY KEY (id)
13 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
14 
15 DROP TABLE IF EXISTS m_post;
16 CREATE TABLE m_post (
17   id int(11) NOT NULL AUTO_INCREMENT,
18   category_id INT NOT NULL ,
19   user_id INT NOT NULL ,
20   title varchar(64) NOT NULL COMMENT '标题',
21   content text COMMENT '正文',
22   created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
23   updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
24   PRIMARY KEY (id)
25 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
26 
27 DROP TABLE IF EXISTS m_user;
28 CREATE TABLE m_user (
29   id int(11) NOT NULL AUTO_INCREMENT,
30   username varchar(64) NOT NULL,
31   password varchar(255) NOT NULL,
32   salt VARCHAR(32) NOT NULL ,
33   avatar varchar(64) DEFAULT NULL,
34   type enum('customer','admin','root') NOT NULL DEFAULT 'customer',
35   remember_token varchar(128) DEFAULT NULL,
36   PRIMARY KEY (id)
37 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
38 
39 INSERT INTO m_user(id,username, password, salt,type)
40     VALUE (1,'lvyahui','XXXXXXX','abcs','admin');
41 
42 DROP TABLE IF EXISTS m_post_comment;
43 CREATE TABLE m_post_comment(
44   id int(11) AUTO_INCREMENT PRIMARY KEY ,
45   post_id INT NOT NULL ,
46   user_id INT NOT NULL ,
47   content VARCHAR(512) NOT NULL DEFAULT '',
48   created_at TIMESTAMP NOT NULL DEFAULT current_timestamp,
49   updated_at TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
50 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

对应的实体类

Post

 1 package org.lyh.java.mybatis.model;
 2 
 3 import java.sql.Timestamp;
 4 
 5 /**
 6  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 7  * @since 2017/1/1 14:00
 8  */
 9 @SuppressWarnings("unused")
10 public class Post extends BaseModel {
11 
12     private String title;
13     private String content;
14     private Timestamp createdAt;
15     private Timestamp updatedAt;
16 
17     private Integer userId;
18     private Integer categoryId;
19 
20     private User user;
21     private Category category;
22 
23     public String getTitle() {
24         return title;
25     }
26 
27     public void setTitle(String title) {
28         this.title = title;
29     }
30 
31     public String getContent() {
32         return content;
33     }
34 
35     public void setContent(String content) {
36         this.content = content;
37     }
38 
39     public Timestamp getCreatedAt() {
40         return createdAt;
41     }
42 
43     public void setCreatedAt(Timestamp createdAt) {
44         this.createdAt = createdAt;
45     }
46 
47     public Timestamp getUpdatedAt() {
48         return updatedAt;
49     }
50 
51     public void setUpdatedAt(Timestamp updatedAt) {
52         this.updatedAt = updatedAt;
53     }
54 
55     public Integer getUserId() {
56         return userId;
57     }
58 
59     public void setUserId(Integer userId) {
60         this.userId = userId;
61     }
62 
63     public Integer getCategoryId() {
64         return categoryId;
65     }
66 
67     public void setCategoryId(Integer categoryId) {
68         this.categoryId = categoryId;
69     }
70 
71     public User getUser() {
72         return user;
73     }
74 
75     public void setUser(User user) {
76         this.user = user;
77     }
78 
79     public Category getCategory() {
80         return category;
81     }
82 
83     public void setCategory(Category category) {
84         this.category = category;
85     }
86 
87 
88     @Override
89     public String toString() {
90         return "Post{" +
91                 "title='" + title + '\'' +
92                 ", content='" + content + '\'' +
93                 ", createdAt=" + createdAt +
94                 ", updatedAt=" + updatedAt +
95                 ", userId=" + userId +
96                 ", categoryId=" + categoryId +
97                 '}';
98     }
99 }
lvyahui

User

 1 package org.lyh.java.mybatis.model;
 2 
 3 import org.lyh.java.mybatis.type.UserType;
 4 
 5 import java.util.List;
 6 
 7 /**
 8  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 9  * @since 2017/1/12 22:44
10  */
11 @SuppressWarnings("unused")
12 public class User extends BaseModel {
13 
14 
15     private String username;
16     private String password;
17     private String salt;
18     private String avatar;
19     private UserType type;
20     private String rememberToken;
21 
22     private List<Post> posts ;
23     private List<PostComment> postComments;
24 
25     public String getUsername() {
26         return username;
27     }
28 
29     public void setUsername(String username) {
30         this.username = username;
31     }
32 
33     public String getPassword() {
34         return password;
35     }
36 
37     public void setPassword(String password) {
38         this.password = password;
39     }
40 
41     public String getSalt() {
42         return salt;
43     }
44 
45     public void setSalt(String salt) {
46         this.salt = salt;
47     }
48 
49     public String getAvatar() {
50         return avatar;
51     }
52 
53     public void setAvatar(String avatar) {
54         this.avatar = avatar;
55     }
56 
57     public UserType getType() {
58         return type;
59     }
60 
61     public void setType(UserType type) {
62         this.type = type;
63     }
64 
65     public String getRememberToken() {
66         return rememberToken;
67     }
68 
69     public void setRememberToken(String rememberToken) {
70         this.rememberToken = rememberToken;
71     }
72 
73     public List<Post> getPosts() {
74         return posts;
75     }
76 
77     public void setPosts(List<Post> posts) {
78         this.posts = posts;
79     }
80 
81     public List<PostComment> getPostComments() {
82         return postComments;
83     }
84 
85     public void setPostComments(List<PostComment> postComments) {
86         this.postComments = postComments;
87     }
88 }
lvyahui

一些辅助类

查询条件Condition

 1 package org.lyh.java.mybatis.bean;
 2 
 3 /**
 4  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 5  * @since 2016/12/12 13:27
 6  */
 7 @SuppressWarnings("unused")
 8 public class Condition {
 9 
10     private String key;
11     private String opt = "=";
12     private Object value;
13 
14     public Condition(String key, String opt, Object value) {
15         this.key = key;
16         this.opt = opt;
17         this.value = value;
18     }
19 
20     public Condition(String key, Object value){
21         this(key,"=",value);
22     }
23 
24     public String getKey() {
25         return key;
26     }
27 
28     public void setKey(String key) {
29         this.key = key;
30     }
31 
32     public String getOpt() {
33         return opt;
34     }
35 
36     public void setOpt(String opt) {
37         this.opt = opt;
38     }
39 
40     public Object getValue() {
41         return value;
42     }
43 
44     public void setValue(Object value) {
45         this.value = value;
46     }
47 }
lvyahui

分页工具类PageData

  1 package org.lyh.java.mybatis.bean;
  2 
  3 
  4 import org.lyh.java.mybatis.model.BaseModel;
  5 
  6 import java.util.List;
  7 
  8 /**
  9  *
 10  * Created by lvyahui on 2015/7/12.
 11  */
 12 @SuppressWarnings("unused")
 13 public class PageData<T extends BaseModel> {
 14 
 15     /**
 16      * 前端做分页，所以这里limit设置的非常大，相当于不分页
 17      */
 18     public static final int DEFAULT_SIZE = 1000;
 19 
 20     private List<T> datas;
 21 
 22     private int currentPage = 1;
 23 
 24     private int totalPage;
 25 
 26     private int totalItem;
 27 
 28 
 29     private int maxBtnCount = 10;
 30 
 31     private int pageSize = DEFAULT_SIZE;
 32 
 33     private int start = 1;
 34     private int end;
 35 
 36     /**
 37      * 总项目数
 38      */
 39     public int getTotalItem() {
 40         return totalItem;
 41     }
 42 
 43     public void setTotalItem(int totalItem) {
 44         this.totalItem = totalItem;
 45         paging();
 46     }
 47 
 48     private void paging() {
 49         totalPage = totalItem / pageSize + 1;
 50         if(totalPage > maxBtnCount){
 51             if(currentPage <= (maxBtnCount-1)/2){
 52                 // 靠近首页
 53                 start = 1;
 54             }else if(totalPage-currentPage < (maxBtnCount-1)/2){
 55                 // 靠近尾页
 56                 start = totalPage - maxBtnCount - 1;
 57             }else{
 58                 start = currentPage - (maxBtnCount-1)/2;
 59             }
 60             end = maxBtnCount-1 + start > totalPage ? totalPage : maxBtnCount - 1 + start;
 61         }else{
 62             end = totalPage;
 63         }
 64 //        System.out.println("start:"+start+",end:"+end);
 65     }
 66 
 67     /**
 68      * 总页数
 69      */
 70     public int getTotalPage() {
 71         return totalPage;
 72     }
 73 
 74     /**
 75      * 当前页
 76      */
 77     public int getCurrentPage() {
 78         return currentPage;
 79     }
 80 
 81     public void setCurrentPage(int currentPage) {
 82         this.currentPage = currentPage;
 83     }
 84 
 85     /**
 86      * 页面数据
 87      */
 88     public List<T> getDatas() {
 89         return datas;
 90     }
 91 
 92     public void setDatas(List<T> datas) {
 93         this.datas = datas;
 94     }
 95 
 96     /**
 97      * 每页大小，可放多少个项，默认为10
 98      */
 99 
100 
101     public int getPageSize() {
102         return pageSize;
103     }
104 
105     public void setPageSize(int pageSize) {
106         this.pageSize = pageSize;
107     }
108 
109     /**
110      * @return 最大分页按钮数，默认值为10
111      */
112     public int getMaxBtnCount() {
113         return maxBtnCount;
114     }
115 
116     public void setMaxBtnCount(int maxBtnCount) {
117         this.maxBtnCount = maxBtnCount;
118     }
119 
120     /**
121      * @return  第一个按钮的页号
122      */
123     public int getStart() {
124         return start;
125     }
126 
127     /**
128      * @return 最后一个按钮上的页号
129      */
130     public int getEnd() {
131         return end;
132     }
133 
134     public void setEnd(int end) {
135         this.end = end;
136     }
137 
138     public void setStart(int start) {
139         this.start = start;
140     }
141 
142 
143     private String listUrl;
144 
145     public String getListUrl() {
146         return listUrl;
147     }
148 
149     public void setListUrl(String listUrl) {
150         this.listUrl = listUrl;
151     }
152 
153     @Override
154     public String toString() {
155         return "PageData{" +
156                 "datas_size=" + datas.size() +
157                 ", currentPage=" + currentPage +
158                 ", totalPage=" + totalPage +
159                 ", totalItem=" + totalItem +
160                 ", maxBtnCount=" + maxBtnCount +
161                 ", pageSize=" + pageSize +
162                 ", start=" + start +
163                 ", end=" + end +
164                 '}';
165     }
166 
167 }

基础Model与注解

  1 package org.lyh.java.mybatis.annotation;
  2 
  3 import java.lang.annotation.ElementType;
  4 import java.lang.annotation.Retention;
  5 import java.lang.annotation.RetentionPolicy;
  6 import java.lang.annotation.Target;
  7 
  8 /**
  9  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 10  * @since 2017/1/16 10:44
 11  */
 12 @Target(value = { ElementType.FIELD })
 13 @Retention(RetentionPolicy.RUNTIME)
 14 public @interface JsonField {
 15     String value() default "";
 16 }
 17 
 18 
 19 package org.lyh.java.mybatis.annotation;
 20 
 21 import java.lang.annotation.Retention;
 22 import java.lang.annotation.RetentionPolicy;
 23 
 24 /**
 25  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 26  * @since 2017/1/15 15:18
 27  */
 28 @Retention(RetentionPolicy.RUNTIME)
 29 public @interface NonTableFiled {
 30 
 31 }
 32 
 33 
 34 package org.lyh.java.mybatis.model;
 35 
 36 import org.lyh.java.mybatis.annotation.JsonField;
 37 import org.lyh.java.mybatis.annotation.NonTableFiled;
 38 
 39 import java.lang.reflect.Field;
 40 import java.util.ArrayList;
 41 import java.util.HashMap;
 42 import java.util.List;
 43 import java.util.Map;
 44 
 45 /**
 46  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
 47  * @since 2017/1/12 22:40
 48  */
 49 @SuppressWarnings("unused")
 50 public class BaseModel {
 51 
 52     public Map<String,Object> jsonValues ;
 53 
 54     protected Integer id;
 55     public Integer getId() {
 56         return id;
 57     }
 58 
 59     public void setId(Integer id) {
 60         this.id = id;
 61     }
 62 
 63     public Map<String,String> getFieldMap(){
 64         Map<String,String> fieldMap = new HashMap<String,String>();
 65         Field[] fields = this.getClass().getDeclaredFields();
 66         for (Field field : fields){
 67             if(field.getAnnotation(NonTableFiled.class) == null){
 68                 fieldMap.put(
 69                         // table field -- snake
 70                         field.getName().replaceAll("([A-Za-z])([A-Z])","$1_$2").toLowerCase(),
 71                         // bean field -- hump
 72                         field.getName()
 73                 );
 74             }
 75         }
 76         return fieldMap;
 77     }
 78 
 79     public List<Field> getJsonFields(){
 80         Field fields[] = this.getClass().getDeclaredFields();
 81         List<Field> jsonFields = new ArrayList<Field>();
 82         for(Field field : fields){
 83             JsonField jsonField = field.getAnnotation(JsonField.class);
 84             if(jsonField == null){
 85                 continue;
 86             }
 87             jsonFields.add(field);
 88         }
 89         return jsonFields;
 90     }
 91 
 92     public Map<String,Object>  getJsonValues(){
 93         if(jsonValues != null){
 94             return jsonValues;
 95         }
 96         jsonValues = new HashMap<String, Object>();
 97         List<Field> fields = getJsonFields();
 98         for (Field field : fields){
 99             field.setAccessible(true);
100             JsonField jsonField = field.getAnnotation(JsonField.class);
101             try {
102                 jsonValues.put(jsonField.value(),field.get(this));
103             } catch (IllegalAccessException e) {
104                 //
105             } finally {
106                 field.setAccessible(false);
107             }
108         }
109         return jsonValues;
110     }
111 }

其中BaseModel方法，getFieldMap用来获取数据库字段名称->模型属性名称的映射关系，约定数据库中使用"_"分割单词的蛇形字符串，而属性名使用首字母小写的驼峰字符串，例如数据库字段created_at对应属性createdAt。个人认为编程时约定很重要，有了约定很多通用方法才好写。

一、单表查询

不涉及关系查询的情况还是比较简单的，并且有除去字段名与表名不一致外，有高度的可重用性。笔者在学习mybatis时，试图借助注解、泛型、反射等方法编写出一个通用的DAO类的集合，但因为xml或者注解无法继承包含等原因，一直没有完成一个很好的方案。单表查询示例以m_post表为示例。先来看看基础PostMapper与Xml ResultMap

PostMapper 接口

 1 package org.lyh.java.mybatis.mapper;
 2 
 3 import org.apache.ibatis.annotations.Param;
 4 import org.lyh.java.mybatis.bean.Condition;
 5 import org.lyh.java.mybatis.model.Post;
 6 
 7 import java.util.List;
 8 
 9 /**
10  * @author lvyahui (lvyahui8@gmail.com,lvyahui8@126.com)
11  * @since 2017/1/1 13:59
12  */
13 public interface PostMapper {
14     //String table = "m_post";
15     Post get(Integer id);
16     int insert(Post post);
17     int updateByPrimaryKey(Post post);
18     int updateByPrimaryKeySelective(@Param("post") Post post);
19     int deleteByPrimaryKey(Integer id);
20     int batchInsert(@Param("posts") List<Post> posts);
21 
22     int countSizeWithCondition(@Param("conditions") List<Condition> conditions);
23     List<Post> getPageDataByCondition(@Param("conditions") List<Condition> conditions,
24                                    @Param("offset") Integer offset,
25                                    @Param("size") Integer size,
26                                    @Param("orderProp") String orderProp,
27                                    @Param("desc") boolean desc);
28 }
 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="org.lyh.java.mybatis.mapper.PostMapper">
 6 
 7     <resultMap id="BaseResultMap" type="org.lyh.java.mybatis.model.Post" >
 8         <id column="id" property="id" jdbcType="INTEGER" />
 9         <result column="user_id" property="userId" jdbcType="INTEGER"/>
10         <result column="category_id" property="categoryId" jdbcType="INTEGER"/>
11         <result column="title" property="title" jdbcType="VARCHAR" />
12         <result column="content" property="content" jdbcType="VARCHAR" />
13         <result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
14         <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP"/>
15 
16         <result column="post_id" property="id" jdbcType="INTEGER"/>
17         <result column="post_user_id" property="userId" jdbcType="INTEGER"/>
18         <result column="post_category_id" property="categoryId" jdbcType="INTEGER"/>
19         <result column="post_title" property="title" jdbcType="VARCHAR" />
20         <result column="post_content" property="content" jdbcType="VARCHAR" />
21         <result column="post_created_at" property="createdAt" jdbcType="TIMESTAMP" />
22         <result column="post_updated_at" property="updatedAt" jdbcType="TIMESTAMP"/>
23     </resultMap>
24 
25     <resultMap id="BaseResultWithUserMap" type="org.lyh.java.mybatis.model.Post">
26         <association property="user" column="user_id" javaType="org.lyh.java.mybatis.model.User"
27             resultMap="org.lyh.java.mybatis.mapper.UserMapper.BaseResultMap"
28         />
29     </resultMap>
30     
31     <!-- SQL配置在下面一一给出 -->
32 
33 </mapper>

这里除定义了原字段名到模型属性的映射外，还定义了以"post_"前缀开头的字段名到模型属性的映射，这样做是为了后面做关系查询时要用到，是为了防止其余关系表中存在同名字段时，使用as 别名不冲突。

查询

查询使用select标签

按主键查询单条记录

1 <select id="get" resultMap="BaseResultMap">
2     select * from m_post where id = #{id}
3 </select>

按条件查询多条记录，这里按条件查询记录条数、查询记录只需要将count(1)换成*。

 1 <select id="countSizeWithCondition" resultType="int">
 2     SELECT  count(1) FROM m_post
 3     <if test="conditions != null">
 4         WHERE
 5         <foreach item="item" collection="conditions"
 6                  open="" separator="AND" close="">
 7             ${item.key} ${item.opt} #{item.value}
 8         </foreach>
 9     </if>
10 </select>

按条件查询记录并分页。看网上是有大量的mybatis的分页插件，这里是自己写的分页方法。

 1 <select id="getPageDataByCondition" resultMap="BaseResultMap">
 2     SELECT * FROM m_post
 3     <if test="conditions != null and conditions.size() > 0">
 4         WHERE
 5         <foreach item="item" collection="conditions"
 6                  open="" separator="AND" close="">
 7             ${item.key} ${item.opt} #{item.value}
 8         </foreach>
 9     </if>
10     <if test="orderProp != null">
11         ORDER BY ${orderProp}
12         <if test="desc">
13             DESC
14         </if>
15     </if>
16     LIMIT #{offset},#{size}
17 </select>

当SQL映射需要多个参数时，需要在Mapper对应的方法参数上注解上参数名称，否则只能按mybatis约定的名称或索引来访问变量，比如List会映射到list或者paramter1等等。

更新

更新使用update标签。

指定更新字段更新记录

 1 <update id="updateByPrimaryKey" parameterType="org.lyh.java.mybatis.model.Post">
 2     UPDATE m_post SET
 3     user_id = #{userId},
 4     category_id = #{categoryId},
 5     title = #{title},
 6     content = #{content},
 7     created_at = #{createdAt},
 8     updated_at = #{updatedAt}
 9     WHERE id = #{id}
10 </update>

判断属性值更新非null值字段

 1 <update id="updateByPrimaryKeySelective" parameterType="org.lyh.java.mybatis.model.Post">
 2     UPDATE m_post
 3     SET
 4     <foreach collection="post.fieldMap" item="value" index="key" separator=",">
 5       <if test="post[value] != null">
 6           ${key} = #{post.${value}}
 7       </if>
 8     </foreach>
 9     WHERE id = #{post.id}
10 </update>

注意这里，在foreach中#{post.${value}}基于ongl的语法，由内向外求值，并且，在mybatis中，$与#存在区别，$ 在动态 SQL 解析阶段将会进行变量值string形式替换，# 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符，所以上面xml中的写法是可行。当然还可以在where字句中继续迭代出查询条件。

删除

硬删除

1 <delete id="deleteByPrimaryKey" >
2     DELETE FROM m_post WHERE id = #{id}
3 </delete>

插入与批量插入

单条插入支持返回auto_increament类型的主键id值

1 <insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
2     INSERT INTO m_post (category_id,user_id,title,content)
3     VALUE (#{categoryId},#{userId},#{title},#{content})
4     <selectKey keyProperty="id" resultType="int" order="AFTER">
5         SELECT LAST_INSERT_ID();
6     </selectKey>
7 </insert>

批量插入，在批量插入时，加了if判断，如果传递的是个空集合，则执行一条select 0语句，insert的返回值为-1，如果执行成功（posts非空），返回值为插入成功的记录条数。

 1 <insert id="batchInsert" parameterType="java.util.List">
 2     <if test="posts.size > 0">
 3         INSERT INTO m_post
 4         (category_id,user_id,
 5         title,content,
 6         created_at,updated_at)
 7         VALUES
 8         <foreach collection="posts" item="post" index="index" separator=",">
 9             (#{post.categoryId},#{post.userId},
10             #{post.title},#{post.content},
11             #{post.createdAt},#{post.updatedAt})
12         </foreach>
13     </if>
14     <if test="posts.size == 0">
15         select 0;
16     </if>
17 </insert>

二、关联查询

resultMap中除了result标签指定字段映射外，还支持以association（1）与collection（n）来映射关系模型的查询结果。

一对一

双向绑定的话，只需要在两端以association配置映射即可。

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="org.lyh.java.mybatis.mapper.PostMapper">
 6 
 7     <resultMap id="BaseResultMap" type="org.lyh.java.mybatis.model.Post" >
 8         <id column="id" property="id" jdbcType="INTEGER" />
 9         <result column="user_id" property="userId" jdbcType="INTEGER"/>
10         <result column="category_id" property="categoryId" jdbcType="INTEGER"/>
11         <result column="title" property="title" jdbcType="VARCHAR" />
12         <result column="content" property="content" jdbcType="VARCHAR" />
13         <result column="created_at" property="createdAt" jdbcType="TIMESTAMP" />
14         <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP"/>
15 
16         <result column="post_id" property="id" jdbcType="INTEGER"/>
17         <result column="post_user_id" property="userId" jdbcType="INTEGER"/>
18         <result column="post_category_id" property="categoryId" jdbcType="INTEGER"/>
19         <result column="post_title" property="title" jdbcType="VARCHAR" />
20         <result column="post_content" property="content" jdbcType="VARCHAR" />
21         <result column="post_created_at" property="createdAt" jdbcType="TIMESTAMP" />
22         <result column="post_updated_at" property="updatedAt" jdbcType="TIMESTAMP"/>
23     </resultMap>
24 
25     <resultMap id="BaseResultWithUserMap" type="org.lyh.java.mybatis.model.Post">
26         <association property="user" column="user_id" javaType="org.lyh.java.mybatis.model.User"
27             resultMap="org.lyh.java.mybatis.mapper.UserMapper.BaseResultMap"
28         />
29     </resultMap>
30 </mapper>

主要这里，在association标签中，并没有通过字标签result来映射结果，而是直接通过resultMap属性来映射结果，注意英文UserMapper.BaseResultMap与PostMapper.BaseResultMap并不处在同一个命名空间，所以要写上命名空间。

一对多

一对多以在1端配置collection映射，并在n端配置association映射实现，其中collection配置如下

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="org.lyh.java.mybatis.mapper.UserMapper">
 6 
 7     <resultMap id="BaseResultMap" type="org.lyh.java.mybatis.model.User" >
 8         <!--<id column="id" property="id" jdbcType="INTEGER" />-->
 9         <result column="username" property="username" jdbcType="VARCHAR"/>
10         <result column="password" property="password" jdbcType="VARCHAR"/>
11         <result column="salt" property="salt" jdbcType="VARCHAR" />
12         <result column="avatar" property="avatar" jdbcType="VARCHAR" />
13         <result column="type" property="type" typeHandler="org.lyh.java.mybatis.type.UserTypeHandler"/>
14         <result column="remember_token" property="rememberToken" jdbcType="VARCHAR"/>
15 
16         <result column="user_id" property="id" jdbcType="INTEGER"/>
17         <result column="user_username" property="username" jdbcType="VARCHAR"/>
18         <result column="user_password" property="password" jdbcType="VARCHAR"/>
19         <result column="user_salt" property="salt" jdbcType="VARCHAR" />
20         <result column="user_avatar" property="avatar" jdbcType="VARCHAR" />
21         <result column="user_type" property="type"  typeHandler="org.lyh.java.mybatis.type.UserTypeHandler"/>
22         <result column="user_remember_token" property="rememberToken" jdbcType="VARCHAR"/>
23     </resultMap>
24 
25     <resultMap id="BaseResultWithPostsMap" type="org.lyh.java.mybatis.model.User" extends="BaseResultMap">
26         <collection property="posts" ofType="org.lyh.java.mybatis.model.Post"
27                     resultMap="org.lyh.java.mybatis.mapper.PostMapper.BaseResultMap"
28                     column="user_id"
29         />
30     </resultMap>
31 
32     <resultMap id="BaseResultSelectPostsMap" type="org.lyh.java.mybatis.model.User" >
33         <collection property="posts" ofType="org.lyh.java.mybatis.model.Post"
34                     select="org.lyh.java.mybatis.mapper.PostMapper.getByUserId"
35                     column="user_id"
36         />
37     </resultMap>
38 </mapper>

对应的SQL映射可以是关联查询或者先查询主表记录、再查询副表记录。注意如果字段可以确保不会有歧义，则可以直接写字段名，如果有歧义，则应该分别as一个别名，并且是已经在resultMap中配置好了的别名。

 1 <select id="getWithPosts" resultMap="BaseResultWithPostsMap">
 2      SELECT
 3      user.id AS user_id,
 4      username,
 5      password,
 6      salt,
 7      avatar,
 8      type,
 9      remember_token,
10  
11      post.id AS post_id,
12      category_id,
13      title,
14      content,
15      created_at,
16      updated_at
17      FROM m_user user
18      LEFT OUTER JOIN m_post post ON user.id = post.user_id
19      WHERE user.id = #{id}
20 </select>

拦截器

Mybatis为每次查询维护了一个拦截器链，通过调用InterceptorChain#pluginAll结合Plugin.wrap方法将待拦截对象转成代理对象，当调用待拦截对象的待拦截方法时，被转发到代理对象执行，而这个代理对象就是mybatis定义大插件或者说拦截器。拦截器通过定义在类上注解Signature说明拦截的class与method定义拦截，并通过配置注册插件。

下面在执行sql语句时拦截打印SQL及执行耗时的拦截器代码。

  1 package org.lyh.java.mybatis.interceptor;
  2 
  3 
  4 import org.apache.ibatis.executor.Executor;
  5 import org.apache.ibatis.mapping.BoundSql;
  6 import org.apache.ibatis.mapping.MappedStatement;
  7 import org.apache.ibatis.mapping.ParameterMapping;
  8 import org.apache.ibatis.plugin.*;
  9 import org.apache.ibatis.reflection.MetaObject;
 10 import org.apache.ibatis.session.Configuration;
 11 import org.apache.ibatis.session.ResultHandler;
 12 import org.apache.ibatis.session.RowBounds;
 13 import org.apache.ibatis.type.TypeHandlerRegistry;
 14 
 15 import java.text.DateFormat;
 16 import java.util.Date;
 17 import java.util.List;
 18 import java.util.Locale;
 19 import java.util.Properties;
 20 
 21 /**
 22  * @author samlv
 23  */
 24 @Intercepts({
 25         @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
 26         @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
 27 })
 28 public class SQLMonitorPlugin implements Interceptor {
 29 
 30     public Object intercept(Invocation invocation) throws Throwable {
 31         MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
 32         Object parameter = null;
 33         if (invocation.getArgs().length > 1) {
 34             parameter = invocation.getArgs()[1];
 35         }
 36         String sqlId = mappedStatement.getId();
 37         BoundSql boundSql = mappedStatement.getBoundSql(parameter);
 38         Configuration configuration = mappedStatement.getConfiguration();
 39         Object returnValue;
 40         long start = System.currentTimeMillis();
 41         returnValue = invocation.proceed();
 42         long end = System.currentTimeMillis();
 43         long time = (end - start);
 44         if (time > 1) {
 45             String sql = getSql(configuration, boundSql, sqlId, time);
 46             System.err.println(sql);
 47         }
 48         return returnValue;
 49     }
 50 
 51     public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {
 52         String sql = showSql(configuration, boundSql);
 53         return sqlId + " : " + sql + " : " + time + "ms";
 54     }
 55 
 56     private static String getParameterValue(Object obj) {
 57         String value;
 58         if (obj instanceof String) {
 59             value = "'" + obj.toString() + "'";
 60         } else if (obj instanceof Date) {
 61             DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
 62             value = "'" + formatter.format(new Date()) + "'";
 63         } else {
 64             if (obj != null) {
 65                 value = obj.toString();
 66             } else {
 67                 value = "";
 68             }
 69 
 70         }
 71         return value;
 72     }
 73 
 74     public static String showSql(Configuration configuration, BoundSql boundSql) {
 75         Object parameterObject = boundSql.getParameterObject();
 76         List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
 77         String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
 78         if (parameterMappings.size() > 0 && parameterObject != null) {
 79             TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
 80             if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
 81                 sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
 82 
 83             } else {
 84                 MetaObject metaObject = configuration.newMetaObject(parameterObject);
 85                 for (ParameterMapping parameterMapping : parameterMappings) {
 86                     String propertyName = parameterMapping.getProperty();
 87                     if (metaObject.hasGetter(propertyName)) {
 88                         Object obj = metaObject.getValue(propertyName);
 89                         sql = sql.replaceFirst("\\?", getParameterValue(obj));
 90                     } else if (boundSql.hasAdditionalParameter(propertyName)) {
 91                         Object obj = boundSql.getAdditionalParameter(propertyName);
 92                         sql = sql.replaceFirst("\\?", getParameterValue(obj));
 93                     }
 94                 }
 95             }
 96         }
 97         return sql;
 98     }
 99 
100 
101     public Object plugin(Object o) {
102         return Plugin.wrap(o, this);
103     }
104 
105     public void setProperties(Properties properties) {
106 
107     }
108 }

注册插件，xml方式，这里没有单独为mybatis创建配置文件，而是直接在spring配置文件中定义插件，效果是一样的。

 1 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 2     <property name="dataSource" ref="dataSource"/>
 3     <!--<property name="configLocation" value="classpath:mybatis-config.xml"/>-->
 4     <property name="plugins">
 5         <array>
 6             <bean class="org.lyh.java.mybatis.interceptor.SQLMonitorPlugin"/>
 7         </array>
 8     </property>
 9     <!-- 自动扫描mapping.xml文件 -->
10     <property name="mapperLocations" value="classpath:org/lyh/java/mybatis/mapper/*.xml"/>
11 </bean>

三、源码浅析

Mapper代理对象获取

首先看调用栈

Mybatis通过调用SqlSession.getMapper方法，传递mapperInterface(PostMapper.class)为参数，最后以sqlSession，mapperInterface，methodCache为参数构造得到代理对象MapperProxy。最后对mapperInterface（PostMapper）的方法调用，都转发到代理对象执行invoke方法。

调用mapper接口的方法，将调用mapperProxy.invoke方法。在invoke方法中，会封装一个MapperMethod对象，这是被调用的mapper方法的进一步封装。

 1 public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
 2     if (Object.class.equals(method.getDeclaringClass())) {
 3       try {
 4         return method.invoke(this, args);
 5       } catch (Throwable t) {
 6         throw ExceptionUtil.unwrapThrowable(t);
 7       }
 8     }
 9     final MapperMethod mapperMethod = cachedMapperMethod(method);
10     return mapperMethod.execute(sqlSession, args);
11 }

拦截器执行

sqlSession#selectOne调用被代理到org.mybatis.spring.SqlSessionTemplate.SqlSessionInterceptor#invoke方法上

有四个地方调用了拦截器链的pluginAll方法，pluginAll实际是将待执行对象代理到代理对象上，也就是Plugin对象，demo程序中就是SQLMonitorPlugin。下面列表顺序也代表了被拦截的顺序

  1. org.apache.ibatis.session.Configuration#newExecutor
  2. org.apache.ibatis.session.Configuration#newParameterHandler
  3. org.apache.ibatis.session.Configuration#newResultSetHandler
  4. org.apache.ibatis.session.Configuration#newStatementHandler

执行查询

在MapperProxy中调用org.apache.ibatis.binding.MapperMethod#execute方法，可以看到该方法默认时调用selectOne查询方法，在做多表（一对多）连接查询时，要保证主表与副表id不要一致，配置的resultMap不要相同，否则mybatis会认为主表查询结果返回了多条记录，从而抛出org.apache.ibatis.exceptions.TooManyResultsException异常。convertArgsToSqlCommandParam转换Mapper接口被调方法的参数为基础包装类、集合类等等。

 1 public Object execute(SqlSession sqlSession, Object[] args) {
 2   // ...
 3   Object result;
 4   switch (command.getType()) {
 5     case SELECT:
 6       if (method.returnsVoid() && method.hasResultHandler()) {
 7         executeWithResultHandler(sqlSession, args);
 8         result = null;
 9       } else if (method.returnsMany()) {
10         result = executeForMany(sqlSession, args);
11       } else if (method.returnsMap()) {
12         result = executeForMap(sqlSession, args);
13       } else if (method.returnsCursor()) {
14         result = executeForCursor(sqlSession, args);
15       } else {
16         Object param = method.convertArgsToSqlCommandParam(args);
17         result = sqlSession.selectOne(command.getName(), param);
18       }
19       break;
20   }
21   // ...
22   return result;
23 }

执行步骤如下：

  1. sqlSession实际是SQLSessionTemplate类的对象，调用其selectOne方法，最终调用的是代理方法SqlSessionInterceptor#invoke，在该方法中，获取到一个sqlSession（实际是DefaultSqlSession），
  2. 调用DefaultSqlSession#selectOne方法进行查询。DefaultSqlSession中封装了所有的对数据库的CRUD操作接口。
  3. 在DefaultSqlSession#selectList方法中获取了一个特殊的对象MappedStatement，这个对象是对mapper xml中sql、参数及resultMap的封装。
  4. 以MappedStatement、查询参数、分页参数、返回结果处理类（这里是null）为参数调用CachingExecutor#query方法
  5. 前面说到，因为Executor已经被代理到SQLMonitorPlugin对象，所以第一个拦截器被执行
  6. 在拦截器中，才再次调用CachingExecutor#query方法，在该方法中生成SQL，由SQL及查询参数得到查询缓存的Key
  7. 最后再缓存不存在的情况下，会调用到BaseExecutor#queryFromDatabase方法
  8. 最后调用SimpleExecutor#doQuery方法得到查询，在该方法中，会调用创建各种Handler（如StatementHandler），如果有对应拦截器，Handler就对被代理到拦截器
  9. 最后执行了查询之后，调用DefaultResultSetHandler#handleResultSets按照mappedStatement.getResultMaps()解析查询结果

具体步骤可以以测试代码debug一次

示例代码位置

https://github.com/lvyahui8/java-all/tree/master/mybatis-all 

另外可参考阅读笔者之前写的

基于原始JDBC+方式写的通用DAO类

http://www.cnblogs.com/lvyahui/p/4009961.html

与

通用数库查询

http://www.cnblogs.com/lvyahui/p/5626466.html

笔者一直希望能将一些简单基础的CRUD操作一键化，工程化，省去一些简单且重复的劳动。

posted @ 2017-01-20 16:07  lvyahui  阅读(800)  评论(2)  编辑  收藏  举报