mybatis中的association

association为了解决“ 有一个‘’，也就是包含的关系。
假设学生对象里面 有老师对象。

表结构

public class Teacher {
    private Integer id;
    private String name;
}

public class User {

    private String consName;
    public User(Integer id, String name) {
        this.id = id;
        this.consName = name;
    }
    private Integer id;
    private String userName;
    private String password;
    private Teacher teacher;
    private Integer tId;
}

通过一次查询，将User填满。

MyBatis 有两种不同的方式加载关联

• 嵌套 Select 查询：通过执行另外一个 SQL 映射语句来加载期望的复杂类型。
• 嵌套结果映射：使用嵌套的结果映射来处理连接结果的重复子集。注意主键用<id>，否则可能会有性能问题。

1. 只用resultMap

<resultMap id="map0" type="com.example.springbootproject.infrastructure.dataobject.User">
        <result property="teacher.name" column="name"/>
        <result property="teacher.id" column="t_id"/>
</resultMap>
  
  <select id="selectOne" resultMap="map0">
        select userdata.id,userdata.user_name,userdata.t_id,userdata.password,
               teacher.name
        from userdata inner join teacher on userdata.t_id = teacher.id
        where  userdata.id =#{id}
    </select>
     
User(consName=null, id=12, userName=无极, password=wuji123456, 
     teacher=Teacher(id=14, name=李小龙), tId=null)   

2. 用association ，所有属性写在一个map里面

 <!--    用association。所有属性都写在一个map里 -->
    <resultMap id="map1" type="com.example.springbootproject.infrastructure.dataobject.User">
        <result property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <result property="tId" column="t_id"/>
        <association property="teacher">
            <id property="id" column="t_id"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>
   
    <select id="selectOne" resultMap="map1">
        select userdata.id,userdata.user_name,userdata.t_id,userdata.password,
               teacher.name
        from userdata inner join teacher on userdata.t_id = teacher.id
        where  userdata.id =#{id}
    </select>
User(consName=无极, id=12, userName=无极, password=wuji123456,
     teacher=Teacher(id=14, name=李小龙), tId=14)

3. association，用两个map

<!--  association。用了两个map-->
<resultMap id="map2" type="com.example.springbootproject.infrastructure.dataobject.User">
    <id property="id" column="id"/>
    <result property="userName" column="user_name"/>
    <result property="password" column="password"/>
    <result property="tId" column="t_id"/>
    <association property="teacher" resultMap="teacherMap">
    </association>
</resultMap>
<resultMap id="teacherMap"
           type="com.example.springbootproject.infrastructure.dataobject.Teacher">
    <id property="id" column="t_id"/>
    <result property="name" column="name"/>
</resultMap>

4. association + select

    <resultMap id="map3"
               type="com.example.springbootproject.infrastructure.dataobject.User">
        <result property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <result property="tId" column="t_id"/>
        <association property="teacher" column="t_id" select="selectTeacher">
        </association>
    </resultMap>
   <select id="selectTeacher"
            resultType="com.example.springbootproject.infrastructure.dataobject.Teacher">
        select * from teacher where id =#{id}
    </select>
    <select id="selectOne" resultMap="map3">
        select * from userdata where  id =#{id}
    </select>

这种方式虽然很简单，但在大型数据集或大型数据表上表现不佳。这个问题被称为“N+1 查询问题”。 概括地讲，N+1 查询问题是这样子的：

• 你执行了一个单独的 SQL 语句来获取结果的一个列表（就是“+1”）。
• 对列表返回的每条记录，你执行一个 select 查询语句来为每条记录加载详细信息（就是“N”）。

这个问题会导致成百上千的 SQL 语句被执行。有时候，我们不希望产生这样的后果。

好消息是，MyBatis 能够对这样的查询进行延迟加载，因此可以将大量语句同时运行的开销分散开来。 然而，如果你加载记录列表之后立刻就遍历列表以获取嵌套的数据，就会触发所有的延迟加载查询，性能可能会变得很糟糕。

5. association里面的conlumnPrefix

假设两个老师 教一个学生。学生对象更新为：

public class User {

    private String consName;

    public User(Integer id, String name) {
        this.id = id;
        this.consName = name;
    }

    private Integer id;
    private String userName;
    private String password;
    private Integer tId;//第一位老师
    private Integer cotId;// 第二位老师
    private Teacher teacher;
    private Teacher coTeacher;
}

sql应该是这样的

    <resultMap id="map2" type="com.example.springbootproject.infrastructure.dataobject.User">
        <id property="id" column="id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
        <result property="tId" column="t_id"/>
        <result property="cotId" column="co_tid"/>
        <association property="teacher" column="t_id" resultMap="teacherMap"/>
        <association property="coTeacher" column="t_id" resultMap="teacherMap" columnPrefix="co_"/>
    </resultMap>

    <resultMap id="teacherMap" type="com.example.springbootproject.infrastructure.dataobject.Teacher">
        <id property="id" column="tt_id"/>
        <result property="name" column="tt_name"/>
    </resultMap>

    <select id="selectOne" resultMap="map2">
        select userdata.id,userdata.user_name,userdata.t_id,userdata.password,userdata.co_tid,
				ta.id as tt_id, ta.name as tt_name,
				tb.id as co_tt_id, tb.name as co_tt_name
        from userdata inner join teacher ta on userdata.t_id = ta.id
        inner join teacher tb on userdata.co_tid = tb.id
        where  userdata.id =#{id}
    </select>

User(consName=无极, id=12, userName=无极, password=wuji123456, tId=14, cotId=15, teacher=Teacher(id=14, name=李小龙), coTeacher=Teacher(id=15, name=李元霸))

建表语句

SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for `userdata`
-- ----------------------------
DROP TABLE IF EXISTS `userdata`;
CREATE TABLE `userdata` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `t_id` bigint(20) NOT NULL,
  `co_tid` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1111111112 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of userdata
-- ----------------------------
INSERT INTO `userdata` VALUES ('1', 'jojo', '1,2,3,4,5', '0', '0');
INSERT INTO `userdata` VALUES ('2', '11', '111', '0', '0');
INSERT INTO `userdata` VALUES ('3', '122', 'one,two,three,four,null', '0', '0');
INSERT INTO `userdata` VALUES ('4', '11', '111', '0', '0');
INSERT INTO `userdata` VALUES ('5', '11', '111', '0', '0');
INSERT INTO `userdata` VALUES ('6', '11', '111', '0', '0');
INSERT INTO `userdata` VALUES ('7', '11', '111', '0', '0');
INSERT INTO `userdata` VALUES ('12', '无极', 'wuji123456', '14', '15');
INSERT INTO `userdata` VALUES ('1111111111', '10', '10', '0', '0');


-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `name` varchar(255) DEFAULT NULL,
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('李小龙', '14');
INSERT INTO `teacher` VALUES ('李元霸', '15');