面试题90：说一下MyBatis级联collection

• 当我们有两张表，分别是用户表User和用户联系方式表UserContact，他们之间是一对多关系的。那么，我们想要查询User对象的时候，就连带着把UserContact内容也查询出来。那么，如果通过collection级联查询就可以轻松的实现了。

---

【实体类】

• User.java

代码语言：javascript复制

public class User implements Serializable {
    private Long id;
    private String name;
    private Integer age;
    // User里面包含了UserContact的集合，通过collection来填充该字段
    private List<UserContact> userContacts;
    ... ...
 }

• UserContact.java

代码语言：javascript复制

public class UserContact implements Serializable {
    private Long id;
    private Long userId;
    private Integer contactType;
    private String contactValue;
    private Date createTime;
    private Date updateTime;
    ... ...
}

我们看到，在User实体类里面有userContacts属性，我们将要通过collection来自动填充该内容。

---

【mapper xml】

• UserContactMapper.xml

代码语言：javascript复制

<mapper namespace="mybatis.mapper.UserContactMapper">
    <sql id="allColumn">
        id, user_id, contact_type, contact_value, create_time, update_time
    </sql>

    <select id="getUserContactByUserId" parameterType="long" resultType="vo.UserContact">
        select
            <include refid="allColumn"/>
        from
            tb_user_contact
        where
            user_id = #{userId}
    </select>
</mapper>

• UserMapper.xml

代码语言：javascript复制

<resultMap id="userResult" type="vo.User">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="age" property="age"/>
    <collection property="userContacts" column="id" select="mybatis.mapper.UserContactMapper.getUserContactByUserId"/>
</resultMap>

<select id="getUserAndUserContactById" parameterType="long" resultMap="userResult">
    select
        <include refid="allColumn"/>
    from
        tb_user
    where 
        id = #{id}
</select>

【解释】

在编写UserMapper.xml文件时，我们通过<collection property="userContacts" column="id" select="mybatis.mapper.UserContactMapper.getUserContactByUserId"/>来构建User和UserContact的一对多的关系。

---

【Mapper接口】

• UserContactMapper.java

代码语言：javascript复制

public interface UserContactMapper {
    List<UserContact> getUserContactByUserId(@Param("userId") Long userId);
}

• UserMapper.java

代码语言：javascript复制

public interface UserMapper {
    User getUserAndUserContactById(@Param("id") Long id);
}

---

【运行查看结果】

代码语言：javascript复制

User user = userMapper.getUserAndUserContactById(1L);
System.out.println("user = "   user);

运行结果：

代码语言：javascript复制

Opening JDBC Connection
Checked out connection 1122805102 from pool.
==>  Preparing: select id, name, age from tb_user where id = ? 
==> Parameters: 1(Long)
<==    Columns: id, name, age
<==        Row: 1, muse, 222
====>  Preparing: select id, user_id, contact_type, contact_value, create_time, update_time from tb_user_contact where user_id=? 
====> Parameters: 1(Long)
<====    Columns: id, user_id, contact_type, contact_value, create_time, update_time
<====        Row: 1, 1, 1, 18888888888, 2021-06-26 16:54:14, 2021-06-26 16:54:22
<====        Row: 2, 1, 2, muse@163.com, 2021-06-26 16:54:14, 2021-06-26 16:54:22
<====      Total: 2
<==      Total: 1
user = User{id=1, name='muse', age=222, userContacts=[UserContact{id=1, userId=1, contactType=1, contactValue='18888888888', createTime=Sun Jun 27 05:54:14 CST 2021, updateTime=Sun Jun 27 05:54:22 CST 2021}, UserContact{id=2, userId=1, contactType=2, contactValue='muse@163.com', createTime=Sun Jun 27 05:54:14 CST 2021, updateTime=Sun Jun 27 05:54:22 CST 2021}]}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@42eca56e]
Returned connection 1122805102 to pool.

mybatis private select 对象 集合

0 人点赞