mybatis 高级映射 Mybatis中的高级映射一对一、一对多、多对多

<span style="font-family:Comic Sans MS;font-size:18px;">CREATE TABLE items ( 
id INT NOT NULL AUTO_INCREMENT, 
itemsname VARCHAR(32) NOT NULL COMMENT '商品名称', 
price FLOAT(10,1) NOT NULL COMMENT '商品定价', 
detail TEXT COMMENT '商品描述', 
pic VARCHAR(64) DEFAULT NULL COMMENT '商品图片', 
createtime DATETIME NOT NULL COMMENT '生产日期', 
PRIMARY KEY (id) 
) DEFAULT CHARSET=utf8; 
/*Table structure for table `orderdetail` */ 
CREATE TABLE orderdetail ( 
id INT NOT NULL AUTO_INCREMENT, 
orders_id INT NOT NULL COMMENT '订单id', 
items_id INT NOT NULL COMMENT '商品id', 
items_num INT DEFAULT NULL COMMENT '商品购买数量', 
PRIMARY KEY (id), 
KEY `FK_orderdetail_1` (`orders_id`), 
KEY `FK_orderdetail_2` (`items_id`), 
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, 
CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 
) DEFAULT CHARSET=utf8; 
/*Table structure for table `orders` */ 
CREATE TABLE orders ( 
id INT NOT NULL AUTO_INCREMENT, 
user_id INT NOT NULL COMMENT '下单用户id', 
number VARCHAR(30) NOT NULL COMMENT '订单号', 
createtime DATETIME NOT NULL COMMENT '创建订单时间', 
note VARCHAR(100) DEFAULT NULL COMMENT '备注', 
PRIMARY KEY (`id`), 
KEY `FK_orders_1` (`user_id`), 
CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 
) DEFAULT CHARSET=utf8; 
/*Table structure for table `t_user` */ 
CREATE TABLE t_user ( 
id INT NOT NULL AUTO_INCREMENT, 
username VARCHAR(32) NOT NULL COMMENT '用户名称', 
birthday DATE DEFAULT NULL COMMENT '生日', 
sex CHAR(1) DEFAULT NULL COMMENT '性别', 
address VARCHAR(256) DEFAULT NULL COMMENT '地址', 
PRIMARY KEY (`id`) 
) DEFAULT CHARSET=utf8; 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">/*Data for the table `items` */ 
INSERT INTO items(itemsname,price,detail,pic,createtime) VALUES 
('台式机',3000.0,'该电脑质量非常好！',NULL,'2015-07-07 13:28:53'), 
('笔记本',6000.0,'笔记本性能好，质量好！',NULL,'2015-07-08 13:22:57'), 
('背包',200.0,'名牌背包，容量大质量好！',NULL,'2015-07-010 13:25:02'); 
/*Data for the table `orderdetail` */ 
INSERT INTO `orderdetail`(`orders_id`,`items_id`,`items_num`) VALUES 
(1,1,1), 
(1,2,3), 
(2,3,4), 
(3,2,3); 
/*Data for the table `orders` */ 
INSERT INTO `orders`(`user_id`,`number`,`createtime`,`note`) VALUES 
(1,'1000010','2015-06-04 13:22:35',NULL), 
(1,'1000011','2015-07-08 13:22:41',NULL), 
(2,'1000012','2015-07-17 14:13:23',NULL), 
(3,'1000012','2015-07-16 18:13:23',NULL), 
(4,'1000012','2015-07-15 19:13:23',NULL), 
(5,'1000012','2015-07-14 17:13:23',NULL), 
(6,'1000012','2015-07-13 16:13:23',NULL); 
/*Data for the table `user` */ 
INSERT INTO `t_user`(`username`,`birthday`,`sex`,`address`) VALUES 
('王五',NULL,'2',NULL), 
('张三','2014-07-10','1','北京市'), 
('张小明',NULL,'1','河南郑州'), 
('陈小明',NULL,'1','河南郑州'), 
('张三丰',NULL,'1','河南郑州'), 
('陈小明',NULL,'1','河南郑州'), 
('王五',NULL,NULL,NULL), 
('小A','2015-06-27','2','北京'), 
('小B','2015-06-27','2','北京'), 
('小C','2015-06-27','1','北京'), 
('小D','2015-06-27','2','北京'); 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">SELECT t1.*, 
t2.username, 
t2.sex, 
t2.address 
FROM 
orders t1, 
t_user t2 
WHERE t1.user_id=t2.id 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.entity; 
import java.util.Date; 
import java.util.List; 
/** 
* @ClassName: User 
* @Description: TODO(用户实体) 
* @author 阿赫瓦里 
*/ 
public class User { 
private Integer id; 
// 姓名 
private String username; 
// 性别 
private String sex; 
// 地址 
private String address; 
// 生日 
private Date birthday; 
// 用户创建的订单列表 
private List<Orders> ordersList; 
// getter and setter ...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.entity; 
import java.util.Date; 
import java.util.List; 
/** 
* @ClassName: Orders 
* @Description: TODO(订单实体) 
* @author 阿赫瓦里 
*/ 
public class Orders { 
/** 主键订单Id */ 
private Integer id; 
/** 下单用户id */ 
private Integer userid; 
/** 订单号 */ 
private String number; 
/** 创建订单时间 */ 
private Date createTime; 
/** 备注 */ 
private String note; 
// 用户信息 
private User user; 
// 订单明细 
private List<OrderDetail> orderdetails; 
// getter and setter ...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.entity; 
import java.util.Date; 
/** 
* @ClassName: Items 
* @Description: TODO(商品实体类) 
* @author 丁国华 
*/ 
public class Items { 
/** 商品表主键Id */ 
private Integer id; 
/** 商品名称 */ 
private String itemsName; 
/** 商品定价 */ 
private float price; 
/** 商品描述 */ 
private String detail; 
/** 商品图片 */ 
private String picture; 
/** 生产日期 */ 
private Date createTime; 
// getter and setter ...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.entity; 
/** 
* @ClassName: OrderDetail 
* @Description: TODO(订单明细实体) 
* @author 丁国华 
*/ 
public class OrderDetail { 
/** 主鍵，訂單明细表Id */ 
private Integer id; 
/** 訂單Id */ 
private Integer ordersId; 
/** 商品id */ 
private Integer itemsId; 
/** 商品购买数量 */ 
private Integer itemsNum; 
// 明细对应的商品信息 
private Items items; 
// getter and setter ...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">/** 
* @ClassName: OrdersCustom 
* @Description: TODO(订单的扩展类,通过此类映射订单和用户的查询结果,让此类继承字段较多的实体类) 
* @author: 丁国华 
*/ 
public class OrdersCustom extends Orders { 
// 添加用户的属性 
private String username; 
private String sex; 
private String address; 
// getter and setter...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.Mapper; 
import java.util.List; 
import com.mybatis.entity.OrdersCustom; 
/** 
* @ClassName: OrdersMapperCustom 
* @Description: TODO(OrdersMapperCustom的mapper) 
* @author 丁国华 
*/ 
public interface OrdersCustomMapper { 
/** 查询订单，关联查询用户信息 */ 
public List<OrdersCustom> findOrdersUser(); 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><?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"> 
<!-- namespace命名空间,作用就是对sql进行分类化的管理,理解为sql隔离 
注意:使用mapper代理开发时，namespace有特殊作用,namespace等于mapper接口地址 
--> 
<mapper namespace="com.mybatis.mapper.OrdersCustomMapper"> 
<!-- 查询订单，关联查询用户信息 --> 
<select id="findOrdersUser" resultType="com.mybatis.entity.OrdersCustom"> 
SELECT t1.*, 
t2.username, 
t2.sex, 
t2.address 
FROM 
orders t1, 
t_user t2 
WHERE t1.user_id=t2.id 
</select> 
</mapper> 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 定义查询订单关联用户的 resultMap,将整个的查询结果映射到com.mybatis.entity.Orders中 --> 
<resultMap type="com.mybatis.entity.Orders" id="OrdersUserResultMap"> 
<!-- 配置映射的订单信息 --> 
<!-- id:查询列中的唯一标识,订单信息中的唯一标识，如果多列组成唯一标识(如：一般数据库设计中的字典表 使用联合主键)，就需要配置多个id 
column:订单信息的唯一标识 列 
property:订单信息的唯一标识列所映射到orders中的那个属性(假如：数据库中orders表中的主键为orders_id,而实体属性名称为ordersId, 
则这个配置应为<id column="orders_id" property="ordersId"/>,类似hibernate实体映射文件配置)。 
--> 
<id column="id" property="id"/> 
<result column="user_id" property="userid"/> 
<result column="number" property="number"/> 
<result column="createtime" property="createTime"/> 
<result column="note" property="note"/> 
<!-- 配置映射的关联用户信息 --> 
<!--association:用于映射关联查询单个对象的信息 
property:要将关联查询的用户信息映射到Orders中那个属性 
--> 
<association property="user" javaType="com.mybatis.entity.User"> 
<!-- id:关联查询用户的唯一标识 
column:指定唯一标识用户信息的列 
property:映射到user的那个属性 
--> 
<id column="user_id" property="id"/> 
<result column="username" property="username"/> 
<result column="sex" property="sex"/> 
<result column="address" property="address"/> 
</association> 
</resultMap> 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 查询订单，关联查询用户信息,使用resultMap实现 --> 
<select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap"> 
SELECT t1.*, 
t2.username, 
t2.sex, 
t2.address 
FROM 
orders t1, 
t_user t2 
WHERE t1.user_id=t2.id 
</select></span>

<span style="font-family:Comic Sans MS;font-size:18px;">/** 查询订单关联查询用户信息，使用reslutMap实现*/ 
public List<Orders>findOrdersUserResultMap(); 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">package com.mybatis.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 org.junit.Before; 
import org.junit.Test; 
import com.mybatis.entity.Orders; 
import com.mybatis.entity.OrdersCustom; 
import com.mybatis.mapper.OrdersCustomMapper; 
public class OrdersCustomMapperTest { 
private SqlSessionFactory sqlSessionFactory; 
// 此方法是在执行findUserByIdTest之前执行 
@Before 
public void setUp() throws Exception { 
String resource = "SqlMapConfig.xml"; 
InputStream inputStream = Resources.getResourceAsStream(resource); 
// 创建SqlSessionFcatory 
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
} 
// 查询订单，关联查询用户信息，使用resultType实现的测试 
@Test 
public void TestFindOrdersUser() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 创建代理对象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 调用mapper的方法 
List<OrdersCustom> list = oc.findOrdersUser(); 
System.out.println(list); 
sqlSession.close(); 
} 
// 查询订单，关联查询用户信息，使用resultMap实现的测试 
@Test 
public void TestFindOrdersUserResultMap() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 创建代理对象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 调用mapper的方法 
List<Orders> list = oc.findOrdersUserResultMap(); 
System.out.println(list); 
sqlSession.close(); 
}
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 查询订单关联查询用户及订单明细 --> 
<select id="findOrdersAndOrderDetailResultMap" resultMap="ordersAndOrderDetailResultMap"> 
SELECT 
t1.*, 
t2.username, 
t2.sex, 
t2.address, 
t3.id orderdetail_id, 
t3.items_id, 
t3.items_num, 
t3.orders_id 
FROM 
orders t1, 
t_user t2, 
orderdetail t3 
WHERE t1.user_id = t2.id AND t3.orders_id=t1.id 
</select> 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 查询订单(关联用户)及订单明细的resultMap --> 
<resultMap type="com.mybatis.entity.Orders" id="ordersAndOrderDetailResultMap" extends="OrdersUserResultMap"> 
<!-- 订单信息 --> 
<!-- 关联用户信息 --> 
<!-- 使用extends继承，不用在中配置订单信息和用户信息的映射--> 

<!-- 关联订单明细信息 
一个订单关联查询出了多条订单明细,要使用collection映射 
collection:对关联查询到的多条记录映射到集合中 
property:将关联查询到的多条记录映射到orders类的那个属性 
ofType:指定映射的集合属性中pojo的类型 
--> 
<collection property="orderdetails" ofType="com.mybatis.entity.OrderDetail"> 
<!-- id:唯一标识 
property：要将订单明细的唯一标识映射到com.mybatis.entity.OrderDetail的那个属性 
--> 
<id column="orderdetail_id" property="id"/> 
<result column="items_id" property="itemsId"/> 
<result column="items_num" property="itemsNum"/> 
<result column="orders_id" property="ordersId"/> 
</collection> 
</resultMap> 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">/**查询订单（关联用户）以及订单明细*/ 
public List<OrderDetail>findOrdersAndOrderDetailResultMap(); 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">// 查询订单（关联用户）以及订单明细的测试 
@Test 
public void TestFindOrdersAndOrderDetailResultMap() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 创建代理对象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 调用mapper的方法 
List<OrderDetail> list = oc.findOrdersAndOrderDetailResultMap(); 
System.out.println(list); 
sqlSession.close(); 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 查询用户即购买的商品信息的ResultMap --> 
<resultMap type="com.mybatis.entity.User" id="userAndItemsResultMap"> 
<!-- 用户信息 --> 
<id column="user_id" property="id"/> 
<result column="username" property="username"/> 
<result column="sex" property="sex"/> 
<result column="address" property="address"/> 
<!-- 订单信息 
一个用户对应多个订单，使用collection映射 --> 
<collection property="ordersList" ofType="com.mybatis.entity.Orders"> 
<id column="id" property="id"/> 
<result column="user_id" property="userid"/> 
<result column="number" property="number"/> 
<result column="createtime" property="createTime"/> 
<result column="note" property="note"/> 
<!-- 订单明细 
一个订单包括 多个明细 
--> 
<collection property="orderdetails" ofType="com.mybatis.entity.OrderDetail"> 
<id column="orderdetail_id" property="id"/> 
<result column="items_id" property="itemsId"/> 
<result column="items_num" property="itemsNum"/> 
<result column="orders_id" property="ordersId"/> 
<!-- 商品信息 
一个订单明细对应一个商品 
--> 
<association property="items" javaType="com.mybatis.entity.Items"> 
<id column="items_id" property="id"/> 
<result column="items_name" property="itemsName"/> 
<result column="items_detail" property="detail"/> 
<result column="items_price" property="price"/> 
</association> 
</collection> 
</collection> 
</resultMap> 
<!-- 查询用户及用户购买的商品信息，使用resulaMap--> 
<select id="findUserAndItemsResultMap" resultMap="userAndItemsResultMap"> 
SELECT 
t1.*, 
t2.username, 
t2.sex, 
t2.address, 
t3.id orderdetail_id, 
t3.items_id, 
t3.items_num, 
t3.orders_id, 
t4.itemsname items_name, 
t4.detail items_detail, 
t4.price items_price 
FROM 
orders t1, 
t_user t2, 
orderdetail t3, 
items t4 
WHERE t1.user_id = t2.id AND t3.orders_id=t1.id AND t3.items_id = t4.id 
</select> 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"> /** 查询用户及用户所购买的商品信息 */ 
public List<User> findUserAndItemsResultMap(); 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">// 查询用户及用户购买的商品的信息 
@Test 
public void TestFindUserAndItemsResultMap() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 创建代理对象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 调用mapper的方法 
List<User> list = oc.findUserAndItemsResultMap(); 
System.out.println(list); 
sqlSession.close(); 
} 
</span>