sql一對多查詢，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類：

sql一對多查詢？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;}@Overridepublic 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;}@Overridepublic 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_numprivate 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;}@Overridepublic 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;}@Overridepublic String toString() {return "OrderDetailCustom{" +"items=" + items +'}';}
}

sql一對多。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<OrderDetailCustom> orderDetails;public List<OrderDetailCustom> getOrderDetails() {return orderDetails;}public void setOrderDetails(List<OrderDetailCustom> orderDetails) {this.orderDetails = orderDetails;}@Overridepublic 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<OrdersCustrom> findOrders() throws Exception;
}

• OrderMapper.xml

這里需要用到兩個標簽，< collection >和< association >

< collection >用來表示一對多關系，如訂單包含多個訂單詳情就可以用這個標簽，主要用到兩個屬性：?
property：屬性名，可以理解為在該類型在父類型中的屬性名?
ofType：該屬性所對應的POJO類型

Mybatis框架？<collection property="orderDetails" ofType="cn.elinzhou.mybatisTest.pojo.OrderDetailCustom">

< association >用來表示一對一關系，如訂單詳情對應一件商品，主要用到兩個屬性?
property：屬性名，可以理解為在該類型在父類型中的屬性名?
javaType：該屬性所對應的POJO類型

特別注意，collection和association中描述POJO類型的屬性名是不同的，collection是ofType，association是javaType

sql

為了方便重用，定了了三個sql標簽，用來表示從三張表中索要查找的字段

<!--orders字段-->
<sql id="orders_column_tablename">orders.id orders_id,orders.user_id orders_user_id,orders.number orders_number,orders.createtime orders_createtime,orders.note orders_note
</sql>
<!--orderdetail字段-->
<sql id="orderdetail_column_tablename">orderdetail.id orderdetail_id,orderdetail.orders_id orderdetail_orders_id,orderdetail.items_id orderdetail_items_id,orderdetail.items_num orderdetail_items_num
</sql>
<!--items字段-->
<sql id="items_column_tablename">items.id items_id,items.name items_name,items.price items_price,items.detail items_detail,items.pic items_pic,items.createtime items_createtime
</sql>

select標簽

<select id="findOrders" resultMap="OrderResultMap">SELECT<include refid="orders_column_tablename"/>,<include refid="orderdetail_column_tablename"/>,<include refid="items_column_tablename"/>FROMorders INNER JOIN orderdetail ON orders.id = orderdetail.orders_idINNER JOIN items ON orderdetail.items_id = items.id
</select>

Mybatis，OrderResultMap就要勇當之前說過的collection和association標簽，其余的部分與普通的resultMap的方式一樣

<resultMap id="OrderResultMap" type="cn.elinzhou.mybatisTest.pojo.OrdersCustrom"><id column="orders_id" property="id"/><result column="orders_user_id" property="user_id"/><result column="orders_number" property="number"/><result column="orders_createtime" property="createtime"/><result column="orders_note" property="note"/><!--訂單詳情list--><collection property="orderDetails" ofType="cn.elinzhou.mybatisTest.pojo.OrderDetailCustom"><id column="orderdetail_id" property="id"/><result column="orderdetail_orders_id" property="orders_id"/><result column="orderdetail_items_id" property="items_id"/><result column="orderdetail_items_num" property="items_num"/><!--商品信息--><association property="items" javaType="cn.elinzhou.mybatisTest.pojo.Items"><id column="items_id" property="id"/><result column="items_name" property="name"/><result column="items_price" property="price"/><result column="items_detail" property="detail"/><result column="items_pic" property="pic"/><result column="items_createtime" property="createtime"/></association></collection>
</resultMap>

POJO之間的關系，也相當于數據表之間的關系，只要定義好了主鍵和外鍵，mybatis會自動進行關聯。如果沒有定義外鍵需要在collection或者associatio中指明column_id

• 測試代碼

  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;@Beforepublic void setUp() throws Exception {// 通過配置文件獲取數據庫連接信息Reader reader = Resources.getResourceAsReader("cn/elinzhou/mybatisTest/config/mybatis.xml");// 通過配置信息構建一個SqlSessionFactorySqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);// 通過sqlSessionFactory打開一個數據庫會話sqlSession = sqlSessionFactory.openSession();}@Testpublic void testFindOrders() throws Exception {OrdersMapper orderMapper = sqlSession.getMapper(OrdersMapper.class);List<OrdersCustrom> list = orderMapper.findOrders();System.out.println(list);}
  }