• 问题描述

现在有三张数据表，表名为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

根据表创建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

• Mapper接口

本案例只实现订单的获取功能，所以在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;}
     

• OrderMapper.xml

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

< collection >用来表示一对多关系，如订单包含多个订单详情就可以用这个标签，主要用到两个属性：

property：属性名，可以理解为在该类型在父类型中的属性名 ofType：该属性所对应的POJO类型

<collection property="orderDetails" ofType="cn.elinzhou.mybatisTest.pojo.OrderDetailCustom">

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