MyBatis-高级映射:一对一

本篇将以一个简单的商品交易过程中的操作为实例，讲解MyBatis高级映射，即一对一，一对多，多对多的处理。

1.高级映射用到的数据库表

user表

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| username | varchar(32) | YES  |     | NULL    |                |
| birthday | date        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

item表

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
| price | float       | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

`order`表，order为关键字需要使用`order`，user_id为外键

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)     | YES  | MUL | NULL    |                |
| number     | varchar(32) | YES  |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

orderdetail表

+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| order_id | int(11) | YES  | MUL | NULL    |                |
| item_id  | int(11) | YES  | MUL | NULL    |                |
| item_num | int(11) | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

2.PO类

创建PO类，针对1中创建的数据表，分别创建对应的类对象。
由于属性和字段都是一致的这里就省略啦。

3.resultType实现

1）查询订单表，要求除了显示 订单ID，订单数量，订单时间以外，还需要显示 用户名，因为要显示用户名，所以会用到多表查询，这里由于关联的用户有一个外键，user_id,所以可以使用内连接进行查询，在控制台使用SQL命令操作如下

mysql> select `order`.id, user.username,`order`.number, `order`.createtime from `order`, user  where `order`.user_id=user.id;
+----+----------+--------+---------------------+
| id | username | number | createtime          |
+----+----------+--------+---------------------+
|  1 | high     | 3      | 2016-06-19 11:29:30 |
|  2 | high     | 2      | 2016-06-18 12:23:20 |
|  3 | lily     | 2      | 2016-05-12 12:22:20 |
+----+----------+--------+---------------------+

2）完成用于查询的UserOrder.java，由于从User中获取的数据只有username，大部分内容是从Order中获取，所以在构造PO类时，以需求最多的Order为父类，这样可以极大的减少工作量。

package pojo;

public class OrderUser extends Order {
	private String username;
	
	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	@Override
	public String toString() {
		return "\r\n OrderUser [username=" + username + super.toString() + "]";
	}
}

3）完成 OrderUserMapper.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="mapper.OrderUserMapper">  
	<select id="selectOrderUser" resultType="pojo.OrderUser">
		select `order`.id, user.username,`order`.number, `order`.createtime from `order`, user  where `order`.user_id=user.id;
	</select>
</mapper>  

4)完成 OrderUserMapper 接口

package mapper;

import java.util.List;

import pojo.OrderUser;

public interface OrderUserMapper {
	List<OrderUser> selectOrderUser() throws Exception;
}

5）测试类

package test;

import java.io.InputStream;import java.util.List;

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 mapper.OrderUserMapper;
import pojo.OrderUser;

public class UserOrderTest {
	public static void main(String[] args) throws Exception{
		InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
		SqlSession session = sessionFactory.openSession(true);
		OrderUserMapper orderUserMapper = session.getMapper(OrderUserMapper.class);
		
		List<OrderUser> list = orderUserMapper.selectOrderUser();
		System.out.println(list);
	}
}

3.resultMap实现

resultMap实现的思路是Order中有一个外键user_id，那么通过user_id可以将User作为子查询，从而将查询结果作为Order的一个属性。注意这里我们是映射了整个User到Order中，而不仅仅是username。OrderUser类修改如下：

package pojo;

public class OrderUser extends Order {
	private User user;

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}
}

mapper.xml,由于在SQL语句中没有查询出user的birthday等信息，返回结果中就没有这些信息。

<?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="mapper.OrderUserMapper"> 
	<resultMap id="orderUserMap" type="pojo.OrderUser">
		<id column="id" property="id"></id>
		<result column="user_id" property="user_id"></result>
		<result column="number" property="number"></result>
		<result column="createtime" property="createtime"></result>
		
		<!-- Order外键user_id关联的用户信息 -->
		<association property="user" javaType="pojo.User">
			<id column="user_id" property="id"></id>
			<result column="username" property="username"></result>
			<result column="birthday" property="birthday"></result> 
		</association>
	</resultMap> 
	
	<select id="selectOrderUserByResultMap" resultMap="orderUserMap">
		select `order`.id, user.username, `order`.number, `order`.createtime from `order`, user  where `order`.user_id=user.id;
	</select>
</mapper>  

测试类： 由于在SQL语句中没有查询出user的birthday等信息，返回结果中就没有这些信息。

package test;

import java.io.InputStream;
import java.util.List;

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 mapper.OrderUserMapper;
import pojo.OrderUser;

public class UserOrderTest {
	public static void main(String[] args) throws Exception{
		InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
		SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
		SqlSession session = sessionFactory.openSession(true);
		OrderUserMapper orderUserMapper = session.getMapper(OrderUserMapper.class);
		
		List<OrderUser> list = orderUserMapper.selectOrderUserByResultMap();
		System.out.println(list);
	}
}

<完>