orm-mybatis开发系列之二：一对多关联查询

这里介绍mybatis是如何处理一对多的关联查询的。

例如一个用户对应多个订单，他们之间是一对多的关系。在Java实体对象对中，一对多可以根据List和Set来实现，两者在mybatis中都是通过collection标签来配合使用，稍后会做详细配置介绍。

一、创建用户表（User）和订单表（Order），并插入测试数据

-- Create a table named 'user'  
CREATE TABLE ym_test.`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=1 DEFAULT CHARSET=utf8;  
  
-- Insert a test record  
insert into ym_test.`user` VALUES ('1', 'ym', '24', 'guangzhou,tianhe');  

DROP TABLE IF EXISTS `order`;  
-- Create a table named 'order'  
CREATE TABLE ym_test.`order` (  
    `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 ym_test.`order`  
VALUES  
('1', '1', 'title1', 'content1'),  
('2', '1', 'title2', 'content2'),  
('3', '1', 'title3', 'content3'),  
('4', '1', 'title4', 'content4');  

二、创建表对应的实体类

User.java

package orm.mybatis.model;

import java.io.Serializable;
import java.util.Set;

public class User implements Serializable {

	private static final long serialVersionUID = 685741321488921919L;

	private int id;
	private String name;
	private int age;
	private String address;
	private Set<Order> orders;	// 一个用户对应多个订单，一对多关系

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public Set<Order> getOrders() {
		return orders;
	}

	public void setOrders(Set<Order> orders) {
		this.orders = orders;
	}
	
}

Order.java

package orm.mybatis.model;

import java.io.Serializable;

public class Order implements Serializable {

	private static final long serialVersionUID = 5273490273354363601L;

	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;
	}

}

三、配置映射文件
UserMapper.xml

<?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="orm.mybatis.dao.IUserDao">

	<!-- 配置结果映射  -->
	<resultMap type="User" id="baseResultMap">
		<result column="id" property="id" />
		<result column="name" property="name" />
		<result column="age" property="age" />
		<result column="address" property="address" />
		
		<!-- 一对多配置(ofType指定集合中的对象类型) -->
		<collection property="orders" ofType="Order">
			<id property="id" column="order_id" javaType="int"  />
			<result property="title" column="title" javaType="string" />
			<result property="content" column="content" javaType="string" />
		</collection>
	</resultMap>
	
	<select id="getUserById" parameterType="int" resultMap="baseResultMap">
		<!-- 分别为user的主键id与order的主键id赋值别名，避免因为两个表字段名称相同而注入到对应对象名称冲突 -->
		select u.id, u.name, u.age, u.address, o.id as order_id, o.title, o.content from ym_test.order o, ym_test.user u where o.user_id=u.id and u.id=#{id}  
	</select>
	
</mapper>

四、编写IUserDao

package orm.mybatis.dao;

import orm.mybatis.model.User;

public interface IUserDao {
	
	User getUserById(int id); 

}

五、测试方法

package orm.mybatis.main;

import java.io.IOException;
import java.io.Reader;
import java.util.Set;

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.apache.log4j.Logger;
import org.junit.BeforeClass;
import org.junit.Test;

import orm.mybatis.dao.IUserDao;
import orm.mybatis.model.Order;
import orm.mybatis.model.User;

public class TestMyBatis {

	private static final Logger log = Logger.getLogger(TestMyBatis.class);
	private static SqlSessionFactory sqlSessionFactory;

	private static Reader reader;

	/**
	 * before和beforeclass区别
	 * @befor	在每个测试方法之前都会运行一次，只需声明成public
	 * @beforeclass	在类中只运行一次，必须声明成public static
	 */
	@BeforeClass
	public static void initial() {
		try {
			reader = Resources.getResourceAsReader("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			log.error("Error thrown while reading the configuration: {}", e);
		} finally {
			if (reader != null) {
				try {
					reader.close();
				} catch (IOException e) {
					log.error("Error thrown while closing the reader: {}", e);
				}
			}
		}
	}

	@Test
	public void queryTest() {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		IUserDao userDao = sqlSession.getMapper(IUserDao.class);
		User user = userDao.getUserById(1);
		Set<Order> orders = user.getOrders();
		if(orders != null && orders.size() > 0) {
			for(Order o : orders) {
				System.out.println("id:" + o.getId() + ";title:" + o.getTitle() + ";content:" + o.getContent());
			}
		}
	}

}

测试OK