Mybatis one-to-one and one-to-multiple configurations
Problem description
There are currently three data tables named orders, orderdetail, and items, indicating orders, order details, and commodities respectively.
One order contains multiple order details, indicating different specific items in the order. The Order details are unique for one item. Therefore, the foreign key order_id in orderdetail is the primary key of orders, and the foreign key items_id in orderdetail is the primary key of items.
Database Structure
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 'item name ', 'price' float () not null comment 'item price', 'detail' text COMMENT 'item description', 'pic 'varchar (64) default null comment 'item image ', 'createtime' datetime not null comment 'production date', 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 'order id', 'items _ id' int (11) not null comment 'item id', 'items _ num' int (11) default null comment 'item purchase qty ', primary key ('id'), KEY 'fk _ orderdetail_1' ('Orders _ id '), KEY 'fk _ orderdetail_2 '('items _ id'), CONSTRAINT 'fk _ orderdetail_1' foreign key ('Orders _ id') RE FERENCES '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 'order user id', 'number' varchar (32) not null comment 'order number', 'createtime' datetime not null comment' create order time ', 'note' varchar (100) default null comment 'note', 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
Create a pojo Class Based on the table:
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 is not easy to modify because it corresponds to this database field. To facilitate expansion, add two classes OrderCustom and OrderDetailCustom to include the required POJO object.
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 inherits from Order and adds a List
OrderDetailCustom inherits from OrderDetail and adds an Items
Mapper Interface
In this case, only the order acquisition function is implemented. Therefore, only the findOrders method is defined in OrderMapper.
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
Two tags are required here: <collection> and <association>
<Collection>This label is used to indicate a one-to-multiple relationship. For example, an Order contains multiple order details. This label is mainly used in two attributes:
Property: attribute name, which can be understood as the attribute name of this type in the parent type
OfType: The POJO type corresponding to this attribute
<Association>It is used to represent a one-to-one relationship. For example, order details correspond to a commodity and mainly use two attributes.
Property: attribute name, which can be understood as the attribute name of this type in the parent type
JavaType: The POJO type corresponding to this attribute
Note that the attribute names of the POJO type in collection and association are different. The collection type is ofType, and the association type is javaType.
sql
For ease of reuse, three SQL labels are set to indicate the fields to be searched from the three tables.
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 tag
SELECT , , FROM orders INNER JOIN orderdetail ON orders.id = orderdetail.orders_id INNER JOIN items ON orderdetail.items_id = items.id
OrderResultMap should be the collection and association labels mentioned earlier. The rest is the same as the general resultMap method.
The relationship between POJO is also equivalent to the relationship between data tables. As long as the primary key and foreign key are defined, mybatis is automatically associated.
Test code
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 {// obtain the database connection information through the configuration file Reader = Resources. getResourceAsReader ("cn/elinzhou/mybatisTest/config/mybatis. xml "); // construct a SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder () through configuration information (). build (reader); // use sqlSessionFactory to open a database session sqlSession = sqlSessionFactory. openSession () ;}@ Test public void testFindOrders () throws Exception {OrdersMapper orderMapper = sqlSession. getMapper (OrdersMapper. class); List List = orderMapper. findOrders (); System. out. println (list );}}