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<OrderDetailCustom> orderDetails; public List<OrderDetailCustom> getOrderDetails() { return orderDetails; } public void setOrderDetails(List<OrderDetailCustom> 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<OrdersCustrom> 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字段--> <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"/> FROM orders INNER JOIN orderdetail ON orders.id = orderdetail.orders_id INNER JOIN items ON orderdetail.items_id = items.id </select>
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; @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<OrdersCustrom> list = orderMapper.findOrders(); System.out.println(list); } }
