Mybatis一對一和一對多配置

Mybatis一對一和一對多配置

現在有三張數據表，表名為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);
    }
}