MyBatis开发要点：resultType和resultMap的区别
一、背景介绍
MyBatis，在进行select映射的时候，返回类型可以用resultType，也可以用resultMap；resultType表示直接返回类型，而resultMap则是对外部ResultMap的引用；resultType跟resultMap不能同时存在。
在MyBatis进行查询映射时，其实查询出来的每一个属性都是放在一个对应的Map里面的；其中键是属性名，值则是其对应的值。
- 当提供的返回类型属性是resultType时，MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。所以其实MyBatis的每一个查询映射的返回类型都是ResultMap，只是当提供的返回类型属性是resultType的时候，MyBatis对自动的给把对应的值赋给resultType所指定对象的属性。
- 当提供的返回类型是resultMap时，因为Map不能很好表示领域模型，就需要自己再进一步的把它转化为对应的对象，这常常在复杂查询中很有作用。
二、resultType
resultType可以直接返回给出的返回值类型，比如String、int、Map，等等，其中返回List也是将返回类型定义为Map，然后mybatis会自动将这些map放在一个List中，resultType还可以是一个对象。
- resultType对应的是java对象中的属性，大小写不敏感；
- resultType如果放的是java.lang.Map，key是查询语句的列名，value是查询的值，大小写敏感；
- 如果列名和JavaBean不一致，但列名符合单词下划线分割，Java是驼峰命名法，则mapUnderscoreToCamelCase可设置为true；
1. 返回常见类型
<select id="getLogCount" resultType="int"> select COUNT(*) from AttLog where attTime = #{attTime} and userId = #{userId}; </select>
2. 返回Map
<select id="getDeviceInfoByDeviceId" resultType="Map"> select userCount as usercount, fingerCount as fingercount, faceCount as facecount, attRecordCount as recordcount, lastOnline, state as status from DeviceInfo where deviceId = #{deviceId} and tb_isDelete = 0; </select>
3. 返回一个对象或者一个list
<select id="queryAllDeviceInfo" resultType="com.cachee.ilabor.att.clientmodel.DeviceInfo"> select * from deviceInfo where tb_isDelete = 0; </select>
4. 返回一个对象
对于SQL语句查询出的字段在相应的pojo中必须有和它相同的字段对应。但是，如果列名没有精确匹配,你可以在列名上使用 select 字句的别名来匹配标签。
<select id="selectUsers" parameterType="int" resultType="User"> select user_id as "id", user_name as "userName", hashed_password as "hashedPassword" from some_table where id = #{id} </select>
三、代码演示（resultType）
1. 创建实体表和插入数据（MySQL）
CREATE TABLE `t_user_test` ( `id` int(20) NOT NULL AUTO_INCREMENT, `user_name` varchar(60) DEFAULT NULL COMMENT '用户名称', `real_name` varchar(60) DEFAULT NULL COMMENT '真实名称', `sex` tinyint(3) DEFAULT NULL COMMENT '性别', `mobile` varchar(20) DEFAULT NULL COMMENT '电话', `email` varchar(60) DEFAULT NULL COMMENT '邮箱', `note` varchar(200) DEFAULT NULL COMMENT '备注', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8;
INSERT INTO `t_user_test` (`id`,`user_name`,`real_name`,`sex`,`mobile`,`email`,`note`) VALUES (1,'张三','张三三',1,'13988888888','zhangshan@163.com','没有'); INSERT INTO `t_user_test` (`id`,`user_name`,`real_name`,`sex`,`mobile`,`email`,`note`) VALUES (2,'李四','李四四',1,'13899999999','ls@163.com','也没有'); INSERT INTO `t_user_test` (`id`,`user_name`,`real_name`,`sex`,`mobile`,`email`,`note`) VALUES (3,'赵MM','赵MM啊',0,'13977777777','zhao@163.com','同样没有');
2. 创建实体类
本例引用到lombok.jar包，讲自行下载：https://projectlombok.org/download；如果配置有问题，请参考：lombok的@Data/@ToString注解不生效：MyEclipse2020环境下，如何正确安装lombok
package com.clzhang.mybatis.entity; import lombok.Data; @Data public class TUser { private Integer id; private String userName; private String realName; private Byte sex; private String mobile; private String email; private String note; }
3. 创建Mapper接口
package com.clzhang.mybatis.mapper; import java.util.*; import com.clzhang.mybatis.entity.TUser; public interface TUserTestMapper { TUser selectByPrimaryKey(Integer id); List<TUser> selectAll(); }
5. 创建Mapper映射文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.clzhang.mybatis.mapper.TUserTestMapper"> <select id="selectByPrimaryKey" resultType="TUser"> select id, user_name, real_name, sex, mobile, email, note from t_user_test where id = #{id,jdbcType=INTEGER} </select> <select id="selectAll" resultType="TUser"> select id, user_name, real_name, sex, mobile, email, note from t_user_test </select> </mapper>
6. 创建配置文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="cacheEnabled" value="false" /> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType" value="REUSE" /> <!-- 设置自动驼峰转换 --> <setting name="mapUnderscoreToCamelCase" value="true" /> <!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。默认：true --> <setting name="aggressiveLazyLoading" value="false" /> </settings> <typeAliases> <typeAlias alias="TUser" type="com.clzhang.mybatis.entity.TUser"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/xuejia"/> <property name="username" value="root"/> <property name="password" value="admin"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/clzhang/mybatis/mapper/TUserMapper.xml" /> </mappers> </configuration>
7. 创建测试类
package com.clzhang.mybatis; import java.io.IOException; import java.io.Reader; import java.util.*; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import com.clzhang.mybatis.entity.TUser; import com.clzhang.mybatis.mapper.TUserTestMapper; public class MyBatisTest4 { private static final String MYBATIS_CONFIG_FILENAME = "config/mybatis-config.xml"; private static SqlSessionFactory sqlSessionFactory; static { Reader reader = null; try { reader = Resources.getResourceAsReader(MYBATIS_CONFIG_FILENAME); } catch (IOException e) { System.out.println(e.getMessage()); } sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } @Test public void testAutoMapping() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); TUserTestMapper mapper = sqlSession.getMapper(TUserTestMapper.class); List<TUser> users = mapper.selectAll(); for (TUser tUser : users) { System.out.println(tUser); } } }
8. 运行结果
TUser(id=1, userName=张三, realName=张三三, sex=1, mobile=13988888888, email=zhangshan@163.com, note=没有) TUser(id=2, userName=李四, realName=李四四, sex=1, mobile=13899999999, email=ls@163.com, note=也没有) TUser(id=3, userName=赵MM, realName=赵MM啊, sex=0, mobile=13977777777, email=zhao@163.com, note=同样没有)
四、resultMap
resultMap 元素是 MyBatis 中最重要最强大的元素。它可以让你从 90% 的 JDBC ResultSets 数据提取代码中解放出来。它的设计思想，是简单的语句不需要明确的结果映射，而复杂一点的语句只需要描述它们的关系就行了。
1. 基本使用
下面是最基本的结果集映射，其实就是把数据库字段与PO类进行关联。
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.clzhang.mybatis.mapper.AuthorMapper"> <resultMap id="authorMap" type="com.clzhang.mybatis.entity.Author"> <id property="id" column="author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </resultMap> <select id="getAuthor" parameterType="int" resultMap="authorMap"> SELECT * FROM tb_author WHERE author_id=#{id} </select> </mapper>
2. 高级使用
MyBatis的创建基于这样一个思想：数据库并不是您想怎样就怎样的。虽然我们希望所有的数据库都能遵守第三范式或BCNF（修正的第三范式），但它们不是。resultMap就是MyBatis为解决这些问题，而提供的解决方案。
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.clzhang.mybatis.mapper.BlogMapper"> <resultMap id="blogMap" type="com.clzhang.mybatis.entity.Blog"> <id property="id" column="blog_id"/> <result property="title" column="blog_title"/> <association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association> <collection property="posts" ofType="com.clzhang.mybatis.entity.Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="content" column="post_content"/> <result property="draftStatus" column="post_draft_status"/> <association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="post_author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association> <collection property="comments" ofType="com.clzhang.mybatis.entity.Comment"> <id property="id" column="comment_id"/> <result property="content" column="comment_content"/> </collection> <collection property="tags" ofType="com.clzhang.mybatis.entity.Tag"> <id property="id" column="tag_id"/> <result property="content" column="tag_content"/> </collection> </collection> </resultMap> <select id="getBlog" parameterType="int" resultMap="blogMap"> select B.blog_id, B.blog_title, A.author_id, A.author_username, A.author_password, A.author_email, A.author_bio, A.author_favourite_section, P.post_id, P.post_subject, P.author_id as post_author_id, P.draft_status as post_draft_status, P.post_content, C.comment_id, C.comment_content, T.tag_id, T.tag_content from tb_blog B left outer join tb_author A on B.author_id=A.author_id left outer join tb_post P on B.blog_id=P.blog_id left outer join tb_comment C on C.post_id=P.post_id left outer join tb_tag T on T.post_id=P.post_id where B.blog_id=#{id} </select> </mapper>
解读：
constructor元素
通常情况下， java实体类的属性都有get和set方法，但是在有的不变类中，没有get和set方法，只能在构造器中注入属性，这个时候就要constructor元素。本例没有，但仍然写下来。
<constructor> <idArg column="id" javaType="int"/> <arg column=”username” javaType=”String”/> </constructor>
对应的实体类大概如下：
public class User { public User(Integer id, String username, int age) { //... } }
association元素
association元素处理【一对一】的关系。比如，在上述们示例中，一个博客有一个用户。
<association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association>
collection元素
collection元素处理【一对多】关系。比如，在上述示例中，一个博客有若干Post；一个Post对应一个Author、若干Comments、若干Tags。
<collection property="posts" ofType="com.clzhang.mybatis.entity.Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="content" column="post_content"/> <result property="draftStatus" column="post_draft_status"/> <association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="post_author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association> <collection property="comments" ofType="com.clzhang.mybatis.entity.Comment"> <id property="id" column="comment_id"/> <result property="content" column="comment_content"/> </collection> <collection property="tags" ofType="com.clzhang.mybatis.entity.Tag"> <id property="id" column="tag_id"/> <result property="content" column="tag_content"/> </collection> </collection>
五、代码演示（resultMap）
1. 创建表结构和插入测试数据（MySQL），注意：必须先选择数据库。
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_author -- ---------------------------- DROP TABLE IF EXISTS `tb_author`; CREATE TABLE `tb_author` ( `author_id` int(11) NOT NULL AUTO_INCREMENT, `author_username` varchar(255) DEFAULT NULL, `author_password` varchar(255) DEFAULT NULL, `author_email` varchar(255) DEFAULT NULL, `author_bio` varchar(255) DEFAULT NULL, `author_favourite_section` varchar(255) DEFAULT NULL, PRIMARY KEY (`author_id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of tb_author -- ---------------------------- BEGIN; INSERT INTO `tb_author` VALUES (2, 'yitian', '123', 'yitian.z@foxmail.com', 'my_bio', '12'); COMMIT; -- ---------------------------- -- Table structure for tb_blog -- ---------------------------- DROP TABLE IF EXISTS `tb_blog`; CREATE TABLE `tb_blog` ( `blog_id` int(11) NOT NULL, `blog_title` varchar(255) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, PRIMARY KEY (`blog_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of tb_blog -- ---------------------------- BEGIN; INSERT INTO `tb_blog` VALUES (1, 'yitian_blog', 2); COMMIT; -- ---------------------------- -- Table structure for tb_comment -- ---------------------------- DROP TABLE IF EXISTS `tb_comment`; CREATE TABLE `tb_comment` ( `comment_id` int(11) NOT NULL, `comment_content` varchar(255) DEFAULT NULL, `post_id` int(11) DEFAULT NULL, PRIMARY KEY (`comment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of tb_comment -- ---------------------------- BEGIN; INSERT INTO `tb_comment` VALUES (1, 'Comment1', 1); INSERT INTO `tb_comment` VALUES (2, 'Comment2', 1); INSERT INTO `tb_comment` VALUES (3, 'Comment3', 2); INSERT INTO `tb_comment` VALUES (4, 'Comment4', 3); COMMIT; -- ---------------------------- -- Table structure for tb_post -- ---------------------------- DROP TABLE IF EXISTS `tb_post`; CREATE TABLE `tb_post` ( `post_id` int(11) NOT NULL, `post_subject` varchar(255) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, `draft_status` int(11) DEFAULT NULL, `post_content` varchar(255) DEFAULT NULL, `blog_id` int(11) DEFAULT NULL, PRIMARY KEY (`post_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of tb_post -- ---------------------------- BEGIN; INSERT INTO `tb_post` VALUES (1, 'subject1', 2, 1, 'Post1', 1); INSERT INTO `tb_post` VALUES (2, 'subject2', 2, 1, 'Post2', 1); INSERT INTO `tb_post` VALUES (3, 'subject3', 2, 1, 'Post3', 1); COMMIT; -- ---------------------------- -- Table structure for tb_tag -- ---------------------------- DROP TABLE IF EXISTS `tb_tag`; CREATE TABLE `tb_tag` ( `tag_id` int(11) NOT NULL, `tag_content` varchar(255) DEFAULT NULL, `post_id` int(11) DEFAULT NULL, PRIMARY KEY (`tag_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of tb_tag -- ---------------------------- BEGIN; INSERT INTO `tb_tag` VALUES (1, 'Tag1', 1); INSERT INTO `tb_tag` VALUES (2, 'Tag2', 2); INSERT INTO `tb_tag` VALUES (3, 'Tag3', 3); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
2. 创建实体类
package com.clzhang.mybatis.entity; import lombok.Data; import lombok.ToString; @Data public class Author { private Integer id; private String userName; private String password; private String email; private String bio; private String favouriteSection; }
package com.clzhang.mybatis.entity; import java.util.*; import lombok.Data; import lombok.ToString; @Data public class Blog { private Integer id; private String title; private Author author; private List<Post> posts; }
package com.clzhang.mybatis.entity; import java.util.*; import lombok.Data; import lombok.ToString; @Data public class Comment { private Integer id; private String content; }
package com.clzhang.mybatis.entity; import java.util.*; import lombok.Data; import lombok.ToString; @Data public class Post { private Integer id; private String subject; private Author author; private List<Comment> comments; private List<Tag> tags; private Integer draftStatus; private String content; }
package com.clzhang.mybatis.entity; import java.util.*; import lombok.Data; import lombok.ToString; @Data public class Tag { private Integer id; private String content; }
实体关系：一个Blog包含一个Author和多个Post，一个Post包含一个Author，多个Comment和多个Tag，Comment和Tag相互独立。
3. 单表ResultMap简单使用
3.1 使用Author来实现单表查询，AuthorMapper.xml文件内容如下所示：
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.clzhang.mybatis.mapper.AuthorMapper"> <resultMap id="authorMap" type="com.clzhang.mybatis.entity.Author"> <id property="id" column="author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </resultMap> <select id="getAuthor" parameterType="int" resultMap="authorMap"> SELECT * FROM tb_author WHERE author_id=#{id} </select> </mapper>
说明：
- select元素和resultMap元素使用resultMap中的id属性与select元素中的resultMap属性进行关联。select中若使用resultMap则resultType属性则会失效。
- resultMap元素中，type属性指明返回集的类型。
- <id>和<result>元素都是表明表中的列名和类对象之间的映射关系，id元素用于表中主键（文档中说如此设置有利于提高查询性能，但没有说明原因，为提高性能需要进一步探索），result元素用于普通列名和属性名的对应，property指明类中属性，column指明对应的查询表列名，如果查询中对查询列名设置了as 别名，则column应是对应的别名。
3.2 创建接口类
package com.clzhang.mybatis.mapper; import org.springframework.stereotype.Repository; import com.clzhang.mybatis.entity.Author; @Repository public interface AuthorMapper { Author getAuthor(Integer id); }
3.3 创建配置文件mybatis-config2.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="cacheEnabled" value="false" /> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType" value="REUSE" /> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/xuejia"/> <property name="username" value="root"/> <property name="password" value="admin"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/clzhang/mybatis/mapper/AuthorMapper.xml" /> </mappers> </configuration>
3.4 创建测试程序
package com.clzhang.mybatis; import java.io.IOException; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import com.clzhang.mybatis.entity.Author; import com.clzhang.mybatis.mapper.AuthorMapper; public class MyBatisTest5 { private static final String MYBATIS_CONFIG_FILENAME = "config/mybatis-config2.xml"; private static SqlSessionFactory sqlSessionFactory; static { Reader reader = null; try { reader = Resources.getResourceAsReader(MYBATIS_CONFIG_FILENAME); } catch (IOException e) { System.out.println(e.getMessage()); } sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } @Test public void testAutoMapping() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); AuthorMapper mapper = sqlSession.getMapper(AuthorMapper.class); Author author = mapper.getAuthor(2); System.out.println(author); } }
3.5 运行结果
id=[2],userName=[yitian],email=[yitian.z@foxmail.com],bio[my_bio]
4. 复杂的ResultMap映射关系解析
4.1 创建BlogMapper.xml文件，对此文件的解读参考前面
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.clzhang.mybatis.mapper.BlogMapper"> <resultMap id="blogMap" type="com.clzhang.mybatis.entity.Blog"> <id property="id" column="blog_id"/> <result property="title" column="blog_title"/> <association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association> <collection property="posts" ofType="com.clzhang.mybatis.entity.Post"> <id property="id" column="post_id"/> <result property="subject" column="post_subject"/> <result property="content" column="post_content"/> <result property="draftStatus" column="post_draft_status"/> <association property="author" javaType="com.clzhang.mybatis.entity.Author"> <id property="id" column="post_author_id"/> <result property="userName" column="author_username"/> <result property="password" column="author_password"/> <result property="email" column="author_email"/> <result property="bio" column="author_bio"/> <result property="favouriteSection" column="author_favourite_section"/> </association> <collection property="comments" ofType="com.clzhang.mybatis.entity.Comment"> <id property="id" column="comment_id"/> <result property="content" column="comment_content"/> </collection> <collection property="tags" ofType="com.clzhang.mybatis.entity.Tag"> <id property="id" column="tag_id"/> <result property="content" column="tag_content"/> </collection> </collection> </resultMap> <select id="getBlog" parameterType="int" resultMap="blogMap"> select B.blog_id, B.blog_title, A.author_id, A.author_username, A.author_password, A.author_email, A.author_bio, A.author_favourite_section, P.post_id, P.post_subject, P.author_id as post_author_id, P.draft_status as post_draft_status, P.post_content, C.comment_id, C.comment_content, T.tag_id, T.tag_content from tb_blog B left outer join tb_author A on B.author_id=A.author_id left outer join tb_post P on B.blog_id=P.blog_id left outer join tb_comment C on C.post_id=P.post_id left outer join tb_tag T on T.post_id=P.post_id where B.blog_id=#{id} </select> </mapper>
4.2 创建接口类
package com.clzhang.mybatis.mapper; import org.springframework.stereotype.Repository; import com.clzhang.mybatis.entity.Blog; @Repository public interface BlogMapper { Blog getBlog(Integer id); }
4.3 修改配置文件mybatis-config2.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="cacheEnabled" value="false" /> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType" value="REUSE" /> </settings> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://127.0.0.1:3306/xuejia"/> <property name="username" value="root"/> <property name="password" value="admin"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/clzhang/mybatis/mapper/AuthorMapper.xml" /> <mapper resource="com/clzhang/mybatis/mapper/BlogMapper.xml" /> </mappers> </configuration>
4.4 创建测试程序
package com.clzhang.mybatis; import java.io.IOException; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import com.clzhang.mybatis.entity.Blog; import com.clzhang.mybatis.mapper.BlogMapper; public class MyBatisTest6 { private static final String MYBATIS_CONFIG_FILENAME = "config/mybatis-config2.xml"; private static SqlSessionFactory sqlSessionFactory; static { Reader reader = null; try { reader = Resources.getResourceAsReader(MYBATIS_CONFIG_FILENAME); } catch (IOException e) { System.out.println(e.getMessage()); } sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } @Test public void testAutoMapping() throws IOException { SqlSession sqlSession = sqlSessionFactory.openSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = mapper.getBlog(1); System.out.println(blog); } }
4.5 运行结果
Blog(id=1, title=yitian_blog, author=Author(id=2, userName=yitian, password=123, email=yitian.z@foxmail.com, bio=my_bio, favouriteSection=12), posts=[Post(id=1, subject=subject1, author=Author(id=2, userName=yitian, password=123, email=yitian.z@foxmail.com, bio=my_bio, favouriteSection=12), comments=[Comment(id=1, content=Comment1), Comment(id=2, content=Comment2)], tags=[Tag(id=1, content=Tag1)], draftStatus=1, content=Post1), Post(id=2, subject=subject2, author=Author(id=2, userName=yitian, password=123, email=yitian.z@foxmail.com, bio=my_bio, favouriteSection=12), comments=[Comment(id=3, content=Comment3)], tags=[Tag(id=2, content=Tag2)], draftStatus=1, content=Post2), Post(id=3, subject=subject3, author=Author(id=2, userName=yitian, password=123, email=yitian.z@foxmail.com, bio=my_bio, favouriteSection=12), comments=[Comment(id=4, content=Comment4)], tags=[Tag(id=3, content=Tag3)], draftStatus=1, content=Post3)])
本文主要参考：
https://blog.csdn.net/xushiyu1996818/article/details/89075069
https://www.jianshu.com/p/d5fec4fdf320
https://blog.csdn.net/yitian_z/article/details/104193480
