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
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuUTOlNDM1UTO4cTMiVjM4UjY2M2NmVWN1YTO4UjM0YGOfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
1 package org.lyh.java.mybatis.model;
2
3 import java.sql.Timestamp;
4
5 /**
6 * @author lvyahui ([email protected],[email protected])
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
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuUTOlNDM1UTO4cTMiVjM4UjY2M2NmVWN1YTO4UjM0YGOfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
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 ([email protected],[email protected])
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 }
一些辅助类
查询条件Condition
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuUTOlNDM1UTO4cTMiVjM4UjY2M2NmVWN1YTO4UjM0YGOfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
1 package org.lyh.java.mybatis.bean;
2
3 /**
4 * @author lvyahui ([email protected],[email protected])
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 }
分页工具类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 ([email protected],[email protected])
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 ([email protected],[email protected])
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 ([email protected],[email protected])
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 ([email protected],[email protected])
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。下面列表顺序也代表了被拦截的顺序
- org.apache.ibatis.session.Configuration#newExecutor
- org.apache.ibatis.session.Configuration#newParameterHandler
- org.apache.ibatis.session.Configuration#newResultSetHandler
- 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 }
执行步骤如下:
- sqlSession实际是SQLSessionTemplate类的对象,调用其selectOne方法,最终调用的是代理方法SqlSessionInterceptor#invoke,在该方法中,获取到一个sqlSession(实际是DefaultSqlSession),
- 调用DefaultSqlSession#selectOne方法进行查询。DefaultSqlSession中封装了所有的对数据库的CRUD操作接口。
- 在DefaultSqlSession#selectList方法中获取了一个特殊的对象MappedStatement,这个对象是对mapper xml中sql、参数及resultMap的封装。
- 以MappedStatement、查询参数、分页参数、返回结果处理类(这里是null)为参数调用CachingExecutor#query方法
- 前面说到,因为Executor已经被代理到SQLMonitorPlugin对象,所以第一个拦截器被执行
- 在拦截器中,才再次调用CachingExecutor#query方法,在该方法中生成SQL,由SQL及查询参数得到查询缓存的Key
- 最后再缓存不存在的情况下,会调用到BaseExecutor#queryFromDatabase方法
- 最后调用SimpleExecutor#doQuery方法得到查询,在该方法中,会调用创建各种Handler(如StatementHandler),如果有对应拦截器,Handler就对被代理到拦截器
- 最后执行了查询之后,调用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操作一键化,工程化,省去一些简单且重复的劳动。