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://i-blog.csdnimg.cn/blog_migrate/231665bfce5c326015e8f6ecbe107ad1.png)
![](https://i-blog.csdnimg.cn/blog_migrate/50828477840f9be8f01bb82af691c7d1.gif)
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 }
User
![](https://i-blog.csdnimg.cn/blog_migrate/231665bfce5c326015e8f6ecbe107ad1.png)
![](https://i-blog.csdnimg.cn/blog_migrate/50828477840f9be8f01bb82af691c7d1.gif)
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 }
一些辅助类
查询条件Condition
![](https://i-blog.csdnimg.cn/blog_migrate/231665bfce5c326015e8f6ecbe107ad1.png)
![](https://i-blog.csdnimg.cn/blog_migrate/50828477840f9be8f01bb82af691c7d1.gif)
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 }
分页工具类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。下面列表顺序也代表了被拦截的顺序
- 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操作一键化,工程化,省去一些简单且重复的劳动。