<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 '商品定價(jià)', 
detail TEXT COMMENT '商品描述', 
pic VARCHAR(64) DEFAULT NULL COMMENT '商品圖片', 
createtime DATETIME NOT NULL COMMENT '生產(chǎn)日期', 
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 '商品購買數(shù)量', 
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 '訂單號(hào)', 
createtime DATETIME NOT NULL COMMENT '創(chuàng)建訂單時(shí)間', 
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 
('臺(tái)式機(jī)',3000.0,'該電腦質(zhì)量非常好！',NULL,'2015-07-07 13:28:53'), 
('筆記本',6000.0,'筆記本性能好，質(zhì)量好！',NULL,'2015-07-08 13:22:57'), 
('背包',200.0,'名牌背包，容量大質(zhì)量好！',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(用戶實(shí)體) 
* @author 阿赫瓦里 
*/ 
public class User { 
private Integer id; 
// 姓名 
private String username; 
// 性別 
private String sex; 
// 地址 
private String address; 
// 生日 
private Date birthday; 
// 用戶創(chuàng)建的訂單列表 
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(訂單實(shí)體) 
* @author 阿赫瓦里 
*/ 
public class Orders { 
/** 主鍵訂單Id */ 
private Integer id; 
/** 下單用戶id */ 
private Integer userid; 
/** 訂單號(hào) */ 
private String number; 
/** 創(chuàng)建訂單時(shí)間 */ 
private Date createTime; 
/** 備注 */ 
private String note; 
// 用戶信息 
private User user; 
// 訂單明細(xì) 
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(商品實(shí)體類) 
* @author 丁國華 
*/ 
public class Items { 
/** 商品表主鍵Id */ 
private Integer id; 
/** 商品名稱 */ 
private String itemsName; 
/** 商品定價(jià) */ 
private float price; 
/** 商品描述 */ 
private String detail; 
/** 商品圖片 */ 
private String picture; 
/** 生產(chǎn)日期 */ 
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(訂單明細(xì)實(shí)體) 
* @author 丁國華 
*/ 
public class OrderDetail { 
/** 主鍵，訂單明細(xì)表Id */ 
private Integer id; 
/** 訂單Id */ 
private Integer ordersId; 
/** 商品id */ 
private Integer itemsId; 
/** 商品購買數(shù)量 */ 
private Integer itemsNum; 
// 明細(xì)對(duì)應(yīng)的商品信息 
private Items items; 
// getter and setter ...... 
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">/** 
* @ClassName: OrdersCustom 
* @Description: TODO(訂單的擴(kuò)展類,通過此類映射訂單和用戶的查詢結(jié)果,讓此類繼承字段較多的實(shí)體類) 
* @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 { 
/** 查詢訂單，關(guān)聯(lián)查詢用戶信息 */ 
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命名空間,作用就是對(duì)sql進(jìn)行分類化的管理,理解為sql隔離 
注意:使用mapper代理開發(fā)時(shí)，namespace有特殊作用,namespace等于mapper接口地址 
--> 
<mapper namespace="com.mybatis.mapper.OrdersCustomMapper"> 
<!-- 查詢訂單，關(guān)聯(lián)查詢用戶信息 --> 
<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;"><!-- 定義查詢訂單關(guān)聯(lián)用戶的 resultMap,將整個(gè)的查詢結(jié)果映射到com.mybatis.entity.Orders中 --> 
<resultMap type="com.mybatis.entity.Orders" id="OrdersUserResultMap"> 
<!-- 配置映射的訂單信息 --> 
<!-- id:查詢列中的唯一標(biāo)識(shí),訂單信息中的唯一標(biāo)識(shí)，如果多列組成唯一標(biāo)識(shí)(如：一般數(shù)據(jù)庫設(shè)計(jì)中的字典表 使用聯(lián)合主鍵)，就需要配置多個(gè)id 
column:訂單信息的唯一標(biāo)識(shí) 列 
property:訂單信息的唯一標(biāo)識(shí)列所映射到orders中的那個(gè)屬性(假如：數(shù)據(jù)庫中orders表中的主鍵為orders_id,而實(shí)體屬性名稱為ordersId, 
則這個(gè)配置應(yīng)為<id column="orders_id" property="ordersId"/>,類似hibernate實(shí)體映射文件配置)。 
--> 
<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"/> 
<!-- 配置映射的關(guān)聯(lián)用戶信息 --> 
<!--association:用于映射關(guān)聯(lián)查詢單個(gè)對(duì)象的信息 
property:要將關(guān)聯(lián)查詢的用戶信息映射到Orders中那個(gè)屬性 
--> 
<association property="user" javaType="com.mybatis.entity.User"> 
<!-- id:關(guān)聯(lián)查詢用戶的唯一標(biāo)識(shí) 
column:指定唯一標(biāo)識(shí)用戶信息的列 
property:映射到user的那個(gè)屬性 
--> 
<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;"><!-- 查詢訂單，關(guān)聯(lián)查詢用戶信息,使用resultMap實(shí)現(xiàn) --> 
<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;">/** 查詢訂單關(guān)聯(lián)查詢用戶信息，使用reslutMap實(shí)現(xiàn)*/ 
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; 
// 此方法是在執(zhí)行findUserByIdTest之前執(zhí)行 
@Before 
public void setUp() throws Exception { 
String resource = "SqlMapConfig.xml"; 
InputStream inputStream = Resources.getResourceAsStream(resource); 
// 創(chuàng)建SqlSessionFcatory 
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
} 
// 查詢訂單，關(guān)聯(lián)查詢用戶信息，使用resultType實(shí)現(xiàn)的測(cè)試 
@Test 
public void TestFindOrdersUser() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 創(chuàng)建代理對(duì)象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 調(diào)用mapper的方法 
List<OrdersCustom> list = oc.findOrdersUser(); 
System.out.println(list); 
sqlSession.close(); 
} 
// 查詢訂單，關(guān)聯(lián)查詢用戶信息，使用resultMap實(shí)現(xiàn)的測(cè)試 
@Test 
public void TestFindOrdersUserResultMap() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 創(chuàng)建代理對(duì)象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 調(diào)用mapper的方法 
List<Orders> list = oc.findOrdersUserResultMap(); 
System.out.println(list); 
sqlSession.close(); 
}
} 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;"><!-- 查詢訂單關(guān)聯(lián)查詢用戶及訂單明細(xì) --> 
<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;"><!-- 查詢訂單(關(guān)聯(lián)用戶)及訂單明細(xì)的resultMap --> 
<resultMap type="com.mybatis.entity.Orders" id="ordersAndOrderDetailResultMap" extends="OrdersUserResultMap"> 
<!-- 訂單信息 --> 
<!-- 關(guān)聯(lián)用戶信息 --> 
<!-- 使用extends繼承，不用在中配置訂單信息和用戶信息的映射--> 

<!-- 關(guān)聯(lián)訂單明細(xì)信息 
一個(gè)訂單關(guān)聯(lián)查詢出了多條訂單明細(xì),要使用collection映射 
collection:對(duì)關(guān)聯(lián)查詢到的多條記錄映射到集合中 
property:將關(guān)聯(lián)查詢到的多條記錄映射到orders類的那個(gè)屬性 
ofType:指定映射的集合屬性中pojo的類型 
--> 
<collection property="orderdetails" ofType="com.mybatis.entity.OrderDetail"> 
<!-- id:唯一標(biāo)識(shí) 
property：要將訂單明細(xì)的唯一標(biāo)識(shí)映射到com.mybatis.entity.OrderDetail的那個(gè)屬性 
--> 
<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;">/**查詢訂單（關(guān)聯(lián)用戶）以及訂單明細(xì)*/ 
public List<OrderDetail>findOrdersAndOrderDetailResultMap(); 
</span>

<span style="font-family:Comic Sans MS;font-size:18px;">// 查詢訂單（關(guān)聯(lián)用戶）以及訂單明細(xì)的測(cè)試 
@Test 
public void TestFindOrdersAndOrderDetailResultMap() { 
SqlSession sqlSession = sqlSessionFactory.openSession(); 
// 創(chuàng)建代理對(duì)象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 調(diào)用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"/> 
<!-- 訂單信息 
一個(gè)用戶對(duì)應(yīng)多個(gè)訂單，使用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"/> 
<!-- 訂單明細(xì) 
一個(gè)訂單包括 多個(gè)明細(xì) 
--> 
<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"/> 
<!-- 商品信息 
一個(gè)訂單明細(xì)對(duì)應(yīng)一個(gè)商品 
--> 
<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(); 
// 創(chuàng)建代理對(duì)象 
OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); 
// 調(diào)用mapper的方法 
List<User> list = oc.findUserAndItemsResultMap(); 
System.out.println(list); 
sqlSession.close(); 
} 
</span>