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

相关阅读:
libusbwin32
KMP
windows.h
iomanip
C++继承
 LIST
fstream
VS2010中调试c++程序的方法
 sstream
char 与char* 字符串与字符
原文地址：https://www.cnblogs.com/nayitian/p/15084316.html

热门文章
实习生电话面试
 **pointer
湘潭大学
 c_str()
类名
 C提取数字
 数组移动
 C/C++位域学习
 ASCII的转换
 裘宗燕：C/C++ 语言中的表达式求值