现在有三张数据表，表名为orders,orderdetail,items，分别表示订单，订单详情，商品。

其中一个订单包含多个订单详情，表示订单中的不同个具体的商品，订单详情唯一对应一件商品。所以orderdetail中的外键order_id为orders的主键，orderdetail中的外键items_id为items的主键。

DROP TABLE IF EXISTS `items`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `items` ( `id` int(11) NOT NULL auto_increment, `name` 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`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
---- Table structure for table `orderdetail`--DROP TABLE IF EXISTS `orderdetail`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `orderdetail` ( `id` int(11) NOT NULL auto_increment, `orders_id` int(11) NOT NULL COMMENT '
订单id'
, `items_id` int(11) NOT NULL COMMENT '
商品id'
, `items_num` int(11) 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) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
---- Table structure for table `orders`--DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `orders` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL COMMENT '
下单用户id'
, `number` varchar(32) 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 `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


根据表创建pojo类：

Items.java

package cn.elinzhou.mybatisTest.pojo;
import java.util.Date;
/** * Description: Items * Author: Elin Zhou * Create: 2015-06-30 00:57 */public class Items {
private Integer id;
private String name;
private Double price;
private String detail;
private String pic;
private Date createtime;
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getDetail() {
return detail;
}
public void setDetail(String detail) {
this.detail = detail;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPic() {
return pic;
}
public void setPic(String pic) {
this.pic = pic;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
@Override public String toString() {
return "Items{
" + "createtime=" + createtime + ", id=" + id + ", name='
" + name + '
'
'
+ ", price=" + price + ", detail='
" + detail + '
'
'
+ ", pic='
" + pic + '
'
'
+ '
}
'
;
}
}


Order.java

package cn.elinzhou.mybatisTest.pojo;
import java.util.Date;
/** * Description: Orders * Author: Elin Zhou * Create: 2015-06-30 00:06 */public class Orders {
// id | user_id | number | createtime | note | private Integer id;
private Integer user_id;
private String number;
private Date createtime;
private String note;
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
@Override public String toString() {
return "Orders{
" + "createtime=" + createtime + ", id=" + id + ", user_id=" + user_id + ", number='
" + number + '
'
'
+ ", note='
" + note + '
'
'
+ '
}
'
;
}
}


OrderDetail.java

package cn.elinzhou.mybatisTest.pojo;
/** * Description: OrderDetail * Author: Elin Zhou * Create: 2015-06-30 00:08 */public class OrderDetail {
// id | orders_id | items_id | items_num private Integer id;
private Integer orders_id;
private Integer items_id;
private Integer items_num;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getItems_id() {
return items_id;
}
public void setItems_id(Integer items_id) {
this.items_id = items_id;
}
public Integer getItems_num() {
return items_num;
}
public void setItems_num(Integer items_num) {
this.items_num = items_num;
}
public Integer getOrders_id() {
return orders_id;
}
public void setOrders_id(Integer orders_id) {
this.orders_id = orders_id;
}
@Override public String toString() {
return "OrderDetail{
" + "id=" + id + ", orders_id=" + orders_id + ", items_id=" + items_id + ", items_num=" + items_num + '
}
'
;
}
}


POJO由于对应这数据库字段，所以不方便修改，为了方便拓展，添加两个类OrderCustom和OrderDetailCustom，用来包含所需要的POJO对象

OrderCustom.java

package cn.elinzhou.mybatisTest.pojo;
/** * Description: OrderDetailCustom * Author: Elin Zhou * Create: 2015-06-30 00:56 */public class OrderDetailCustom extends OrderDetail {
private Items items;
public Items getItems() {
return items;
}
public void setItems(Items items) {
this.items = items;
}
@Override public String toString() {
return "OrderDetailCustom{
" + "items=" + items + '
}
'
;
}
}


OrderDetailCustom.java

package cn.elinzhou.mybatisTest.pojo;
import java.util.List;
/** * Description: OrdersCustrom * Author: Elin Zhou * Create: 2015-06-30 00:35 */public class OrdersCustrom extends Orders {
private List orderDetails;
public List getOrderDetails() {
return orderDetails;
}
public void setOrderDetails(List orderDetails) {
this.orderDetails = orderDetails;
}
@Override public String toString() {
return "OrdersCustrom{
" + "orderDetails=" + orderDetails + '
}
'
;
}
}


OrderCustom继承自Order，添加了一个List
OrderDetailCustom继承自OrderDetail,添加了一个Items

本案例只实现订单的获取功能，所以在OrderMapper中只定义个findOrders方法

OrderMapper.java

package cn.elinzhou.mybatisTest.mapper;
import cn.elinzhou.mybatisTest.pojo.OrdersCustrom;
import java.util.List;
/** * Description: OrdersMapper * Author: Elin Zhou * Create: 2015-06-30 00:32 */public interface OrdersMapper {
List findOrders() throws Exception;
}


这里需要用到两个标签，< collection >和< association >

< collection >用来表示一对多关系，如订单包含多个订单详情就可以用这个标签，主要用到两个属性：
property：属性名，可以理解为在该类型在父类型中的属性名
ofType：该属性所对应的POJO类型

< association >用来表示一对一关系，如订单详情对应一件商品，主要用到两个属性
property：属性名，可以理解为在该类型在父类型中的属性名
javaType：该属性所对应的POJO类型

特别注意，collection和association中描述POJO类型的属性名是不同的，collection是ofType，association是javaType

sql

为了方便重用，定了了三个sql标签，用来表示从三张表中索要查找的字段

orders.id orders_id,orders.user_id orders_user_id, orders.number orders_number,orders.createtime orders_createtime,orders.note orders_note orderdetail.id orderdetail_id,orderdetail.orders_id orderdetail_orders_id, orderdetail.items_id orderdetail_items_id,orderdetail.items_num orderdetail_items_num items.id items_id,items.name items_name,items.price items_price, items.detail items_detail,items.pic items_pic,items.createtime items_createtime

select标签

SELECT , , FROM orders INNER JOIN orderdetail ON orders.id = orderdetail.orders_id INNER JOIN items ON orderdetail.items_id = items.id

OrderResultMap就要勇当之前说过的collection和association标签，其余的部分与普通的resultMap的方式一样

POJO之间的关系，也相当于数据表之间的关系，只要定义好了主键和外键，mybatis会自动进行关联。

package cn.elinzhou.mybatisTest.test;
import cn.elinzhou.mybatisTest.mapper.OrdersMapper;
import cn.elinzhou.mybatisTest.pojo.Orders;
import cn.elinzhou.mybatisTest.pojo.OrdersCustrom;
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 java.io.Reader;
import java.util.List;
/** * Created by elin on 15-6-30. */public class OrderMapperTest {
SqlSession sqlSession = null;
@Before public void setUp() throws Exception {
// 通过配置文件获取数据库连接信息 Reader reader = Resources.getResourceAsReader("cn/elinzhou/mybatisTest/config/mybatis.xml");
// 通过配置信息构建一个SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
// 通过sqlSessionFactory打开一个数据库会话 sqlSession = sqlSessionFactory.openSession();
}
@Test public void testFindOrders() throws Exception {
OrdersMapper orderMapper = sqlSession.getMapper(OrdersMapper.class);
List list = orderMapper.findOrders();
System.out.println(list);
}
}