myBatis系列之四：关联数据的查询

myBatis系列之三：增删改查是基于单表的查询，如果联表查询，返回的是复合对象，需要用association关键字来处理。
如User发表Article，每个用户可以发表多个Article，他们之间是一对多的关系。

1. 创建Article表，并插入测试数据：

-- Drop the table if exists

DROP TABLE IF EXISTS `Article`;  

-- Create a table named 'Article'

CREATE TABLE `Article` (

    `id` int NOT NULL AUTO_INCREMENT,

    `user_id` int NOT NULL,

    `title` varchar(100) NOT NULL,

    `content` text NOT NULL,

    PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;  

-- Add several test records

INSERT INTO `article`

VALUES

('', '', 'title1', 'content1'),

('', '', 'title2', 'content2'),

('', '', 'title3', 'content3'),

('', '', 'title4', 'content4');

2. com.john.hbatis.model.Article类：

public class Article {

    private int id;

    private User user;

    private String title;

    private String content;

    // Getters and setters are omitted

}

3. 在IUserMapper中添加：

List<Article> getArticlesByUserId(int id);

4. 在User.xml中添加：

<resultMap type="com.john.hbatis.model.Article" id="articleList">

    <id column="a_id" property="id" />

    <result column="title" property="title" />

    <result column="content" property="content" />  

    <association property="user" javaType="User"><!-- user属性映射到User类 -->

        <id column="id" property="id" />

        <result column="name" property="name" />

        <result column="address" property="address" />

    </association>

</resultMap>  

<select id="getArticlesByUserId" parameterType="int" resultMap="articleList">

    select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content

    from article a

    inner join user u

    on a.user_id=u.id and u.id=#{id}

</select>

5. 测试方法：

@Test

public void getArticlesByUserIdTest() {

    SqlSession session = sqlSessionFactory.openSession();

    try {

        IUserMapper mapper = session.getMapper(IUserMapper.class);

        List<Article> articles = mapper.getArticlesByUserId(1);

        for (Article article : articles) {

            log.info("{} - {}, author: {}", article.getTitle(), article.getContent(), article.getUser().getName());

        }

    } finally {

        session.close();

    }

}

附：除了在association标签内定义字段和属性的映射外，还可以重用User的resultMap:

<association property="user" javaType="User" resultMap="userList" />

<resultMap type="com.john.hbatis.model.Article" id="articleList">
<id column="a_id" property="id" />
<result column="title" property="title" />
<result column="content" property="content" />
<association property="user" javaType="User">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="address" property="address" />
</association>
</resultMap>
<select id="getArticlesByUserId" parameterType="int" resultMap="articleList">
select u.id, u.name, u.age, u.address, a.id a_id, a.title, a.content
from article a
inner join user u
on a.user_id=u.id and u.id=#{id}
</select>

秒客网

myBatis系列之四：关联数据的查询

相关文章