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

        myBatis系列之三：增删改查是基于单表的查询，如果联表查询，返回的是复合对象，需要用association关键字来处理。 

        如User发表Article，每个用户可以发表多个Article，他们之间是一对多的关系。 

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

-- Create the database named 'hbatis'.
-- It's OK to use `, not OK to use ' or " surrounding the database name to prevent it from being interpreted as a keyword if possible.
CREATE DATABASE IF NOT EXISTS `hbatis` DEFAULT CHARACTER SET = `UTF8`;

-- Drop the table if exists
DROP TABLE IF EXISTS `user`;

-- Create a table named 'User'
CREATE TABLE `user` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(50) DEFAULT NULL,
	`age` int(11) DEFAULT NULL,
	`address` varchar(200) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Insert a test record
Insert INTO `user` VALUES ('1', 'bijian', '120', 'hangzhou,westlake');

-- 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
('1', '1', 'title1', 'content1'),
('2', '1', 'title2', 'content2'),
('3', '1', 'title3', 'content3'),
('4', '1', 'title4', 'content4');


-- drop table
drop table `user`;
drop table `article`;

 

二.Article类

package com.bijian.study.model;

public class Article {

    private int id;
    private User user;
    private String title;
    private String content;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
}

 

三.在IUserMapper中添加

List<Article> getArticlesByUserId(int id);

 

四.在User.xml中添加 

<resultMap type="com.bijian.study.model.Article" id="articleList">
	<id column="a_id" property="id" />
	<result column="title" property="title" />
	<result column="content" property="content" />
	
	<!-- user属性映射到User类 -->
	<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>

 

五.测试方法 

@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：

<resultMap type="User" id="userList"><!-- type为返回列表元素的类全名或别名 -->
	<id column="id" property="id" />
	<result column="name" property="name" />
	<result column="age" property="age" />
	<result column="address" property="address" />
</resultMap>

<resultMap type="com.bijian.study.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" resultMap="userList"/>
</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>

 

文章来源：http://czj4451.iteye.com/blog/1986762