Advanced mappings in MyBatis a pair of one or one pairs of many, many to many _java

When learning Hibernate, small series has been exposed to more than a variety of maps, mybatis mapping in the end is how to operate, today's blog, small series mainly to introduce a simple mybatis in the advanced mapping, including a pair of more than one or one pairs, many to many, hope that the need for small partners to help, Small series mainly from four aspects of the introduction, order merchandise data model, one-to-one query, one-to-many query, Many-to-many query.

Data model of order commodity

1, the database execution script, as follows:

<span style= "Font-family:comic Sans ms;font-size:18px;" >create TABLE items (ID INT not NULL auto_increment, Itemsname VARCHAR (a) NOT null COMMENT ' commodity name ', Price FLOAT (10, 1) NOT null COMMENT ' commodity pricing ', detail TEXT COMMENT ' Description of goods ', pic VARCHAR ($) DEFAULT NULL COMMENT ' merchandise picture ', Createtime datetim 
E not NULL COMMENT ' production date ', PRIMARY KEY (ID)) DEFAULT Charset=utf8; /*table Structure for table ' OrderDetail '/CREATE table OrderDetail (id int not NULL auto_increment, orders_id int N OT null COMMENT ' order ID ', items_id int not null COMMENT ' commodity ID ', items_num int DEFAULT null COMMENT ' Quantity of merchandise purchased ', 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 the DELETE no action on UPDATE no action, CONSTRAINT ' fk_orderdetail_2 ' Forei 
GN KEY (' items_id ') REFERENCES ' items ' (' id ') on the DELETE no action on UPDATE no action) DEFAULT Charset=utf8; /*table Structure for TABLE ' orders '/CREATE TABLE orders (id int not NULL auto_increment, user_id int not null COMMENT ' under single User ID ', number Varc HAR () NOT NULL COMMENT ' order number ', Createtime DATETIME not null COMMENT ' Create order time ', note VARCHAR (MB) DEFAULT NULL COMMENT ' prepared Note ', PRIMARY key (' ID '), key ' Fk_orders_1 ' (' user_id '), CONSTRAINT ' fk_orders_id ' FOREIGN KEY (' user_id ') REFERENCES ' T_ 
User ' (' ID ') on the DELETE no action on UPDATE no action) DEFAULT Charset=utf8; /*table Structure for table ' T_user '/CREATE table T_user (id INT not NULL auto_increment, username VARCHAR () Null COMMENT ' user name ', birthday DATE default null COMMENT ' birthday ', sex CHAR (1) DEFAULT null COMMENT ' sex ', Address VARCHAR (25 
6 default NULL COMMENT ' address ', PRIMARY KEY (' id ')) default Charset=utf8; </span>

Test Data Code

<span style= "Font-family:comic Sans ms;font-size:18px;" >/*data for the table ' items '/INSERT into items (itemsname,price,detail,pic,createtime) VALUES (' Desktops ', 3000.0, ' the quality of the computer Very good! ', NULL, ' 2015-07-07 13:28:53 '), (' Notebook ', 6000.0, ' notebook performance good, good quality! ', NULL, ' 2015-07-08 13:22:57 '), (' Backpack ', 200.0, ' brand-name backpack, large capacity, good quality! 
', NULL, ' 2015-07-010 13:25:02 '); /*data for the table ' OrderDetail '/INSERT into ' orderdetail ' (' orders_id ', ' items_id ', ' Items_num ') VALUES (1,1,1), (1, 
2, 3), (2,3,4), (3,2,3); /*data for the table ' orders '/INSERT into ' orders ' (' user_id ', ' number ', ' createtime ', ' note ') VALUES (1, ' 1000010 ', ' 2015- 06-04 13:22:35 ', null ', (1, ' 1000011 ', ' 2015-07-08 13:22:41 ', null), (2, ' 1000012 ', ' 2015-07-17 14:13:23 ', null), (3, ' 1000012 ', ' 2015-07-16 18:13:23 ', null), (4, ' 1000012 ', ' 2015-07-15 19:13:23 ', NULL), (5, ' 1000012 ', ' 2015-07-14 17:13:23 ', 
NULL), (6, ' 1000012 ', ' 2015-07-13 16:13:23 ', null); /*data for the table ' user ' */INSERT into ' t_user ' (' username ', ' birthday ', ' sex ', ' address ') VALUES (' Harry ', NULL, ' 2 '), NULL), (' John ', ' 2014-07-10 ', ' 1 ', ' Beijing '), (' Zhang Xiaoming ', null, ' 1 ', ' Zhengzhou, Henan '), (' Chen Xiaoming ', null, ' 1 ', ' Henan Zhengzhou '), (' San Fung ', null, ' 1 ', ' Henan Zhengzhou '), ( ' Chen Xiaoming ', NULL, ' 1 ', ' Zhengzhou, Henan, (' Harry ', Null,null,null), (' Little a ', ' 2015-06-27 ', ' 2 ', ' Beijing '), (' small B ', ' 2015-06-27 ', ' 2 ', ' Beijing '), (' Small C ', ' 
2015-06-27 ', ' 1 ', ' Beijing '), (' Small d ', ' 2015-06-27 ', ' 2 ', ' Beijing '); </span>

2, data Model analysis ideas

(1). Each table records the data content: The module carries on the familiar to each table record content, is equivalent to you studies the system demand (function) the process;

(2). Each table important field setting: Non-empty field, foreign key field;

(3). The relationship between Database level table and table: foreign key relationship;

(4). Business relationship Between tables: When analyzing the business relationship between tables and tables, it must be based on a business meaning.

3, for the order product model of the database thinking analysis, as shown in the following figure:

Two, one-to-one inquiry

2.1, the demand: inquires the order information, the correlation inquires the user information

2.2, Resulttype implementation

2.2.1sql statement

Determine the main table of the query: Order table, determine the query's associated table, user table, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" >select t1.*, 
t2.username, 
t2.sex, 
t2.address 
from 
orders T1, t_user 
T2 
where T1.user_id=t2.id 
</span>

2.2.2 Create entity entity

User entity User.java, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" >package com.mybatis.entity; 
Import java.util.Date; 
Import java.util.List; 
/** 
* @ClassName: User 
* @Description: TODO (User entity) 
* @author Ahvari * * Public 
class User { 
private Integer ID; 
Name 
private String username; 
Gender 
private String sex; 
Addresses 
private String address; 
Birthday 
private Date birthday; 
User-created order list 
private list<orders> orderslist; 
Getter and setter ... 
} 
</span>

Order Entity Orders.java

<span style= "Font-family:comic Sans ms;font-size:18px;" >package com.mybatis.entity; 
Import java.util.Date; 
Import java.util.List; 
/** 
* @ClassName: Orders 
* @Description: TODO (Order entity) 
* @author Ahvari/public 
class orders { 
/** PRIMARY KEY Order ID * * 
private Integer ID; 
/** under the single user ID * * 
private Integer userid; 
/** Order No. * 
/private String number; 
/** Create Order Time * 
/private Date createtime; 
/** remark 
/private String note; 
User Information 
private users user; 
Order Details 
Private list<orderdetail> OrderDetails; 
Getter and setter ... 
} 
</span>

Commodity entity: Items.java

<span style= "Font-family:comic Sans ms;font-size:18px;" >package com.mybatis.entity; 
Import java.util.Date; 
/** 
* @ClassName: Items 
* @Description: TODO (Commodity entity Class) 
* @author Ding */public 
class Items { 
/** Commodity table PRIMARY Key ID * * 
private Integer ID; 
/** Commodity name * 
/private String itemsname; 
/** Commodity Pricing * * 
private float price; 
/** Product Description * 
/private String detail; 
/** Merchandise Picture * 
/private String pictures; 
/** Production Date * 
/private date createtime; 
Getter and setter ... 
} 
</span>

Order Detail Entity Orderdetail.java

<span style= "Font-family:comic Sans ms;font-size:18px;" >package com.mybatis.entity; 
/** 
* @ClassName: OrderDetail 
* @Description: TODO (Order detail entity) 
* @author Ding/public 
class OrderDetail { 
/** key, the list of the list ID * * 
private Integer ID; 
/** Subscription ID * 
/private Integer ordersid; 
/** Commodity ID * * 
private Integer itemsid; 
/** Commodity Purchase Quantity * * 
private Integer itemsnum; 
Details of the corresponding merchandise information 
private items items; 
Getter and setter ... 
} 
</span>

Create a wrapper class that maps all the information that is queried to this type of Orderscustom.java

<span style= "Font-family:comic Sans ms;font-size:18px;" >/** 
* @ClassName: Orderscustom 
* @Description: TODO (Order extension class, with this kind of mapping order and user's query result, let this kind of inheritance field more entity Class) 
* @ Author: Ding 
/public class Orderscustom extends Orders { 
//Add user's attributes 
private String username; 
Private String sex; 
Private String address; 
Getter and setter ... 
} 
</span>

2.2.3 Create Ordersccustommapper.java, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" >package Com.mybatis.Mapper; 
Import java.util.List; 
Import Com.mybatis.entity.OrdersCustom; 
/** 
* @ClassName: Ordersmappercustom 
* @Description: TODO (ordersmappercustom mapper) 
* @author Ding 
* /Public 
interface Orderscustommapper { 
/** query order, associated query user information * * Public 
list<orderscustom> Findordersuser (); 
} 
</span>

2.2.4 Create the Orderscustommapper.xml and the corresponding interface name on the same side, while loading the configuration file through the Mapper interface, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" ><?xml version= "1.0" encoding= "UTF-8"?> <! 
DOCTYPE Mapper Public 
"-//mybatis.org//dtd mapper 3.0//en" 
"HTTP://MYBATIS.ORG/DTD/MYBATIS-3-MAPPER.DTD" > 
<!--namespace namespace, the role is to classify the management of SQL, understood as SQL isolation 
Note: The use of mapper agent development, namespace has a special role, Namespace equals Mapper interface address 
--> 
<mapper namespace= "Com.mybatis.mapper.OrdersCustomMapper" > 
<! --Query Order, association query user Information--> 
<select id= "Findordersuser" resulttype= "Com.mybatis.entity.OrdersCustom" > 
SELECT t1.*, 
t2.username, 
t2.sex, 
t2.address 
from 
orders T1, 
t_user T2 
WHERE T1.user_id=t2.id 
</select> 
</mapper> 
</span>

2.3RESULTMAP implementation

2.3.1sql Statement ditto

2.3.2resultMap Mapping Ideas:

Use Resultmap to map the order information in the query results to the Orders object, add the user property in the Orders class, and map the user information from the associated query into the user attribute in the Orders object (which has been added above in the orders entity).

2.3.3 Orderscustommapper.xml

1, the definition Resultmap, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" ><!--Define the Resultmap of the query order associated user, map the entire query result to com.mybatis.entity.Orders--> <resultmap type= " Com.mybatis.entity.Orders "id=" Ordersuserresultmap > <!--configuration map order information--> <!--ID: Unique identification in the query column, unique identification in the order information, If multiple columns make up a unique identity (for example, a dictionary table in a general database design uses a federated primary key), you need to configure multiple ID columns: The unique identity column property for order information: The unique identity column for order information is mapped to that attribute in the orders ( If: The primary key in the Orders table in the database is orders_id, and the Entity property name is Ordersid, then this configuration should be <id column= "orders_id" property= "Ordersid"/&GT; 
Similar to Hibernate entity mapping file configuration). --> <id column= "id" property= "id"/> <result "column= user_id" property= "userid"/> <result column= "num ber "property=" number "/> <result column=" createtime "property=" Createtime "/> <result column=" Note " property= "Note"/> <!--the associated user information for the configuration map--> <!--Association: The information property used to map a single object for an associated query: To map the user information for the associated query to the order in that attribute--> <association property= "user" javatype= "Com.mybatis.entity.User" > <!--ID: Associate query user's unique identity column: Specifies a column property that uniquely identifies user information: mapping to useR's that attribute--> <id column= "user_id" property= "id"/> <result column= "username" property= "username"/> <res Ult column= "Sex" property= "sex"/> <result column= "Address" property= "Address"/> </association> </ Resultmap> </span>

2, statement definition, the code looks like this:

<span style= "Font-family:comic Sans ms;font-size:18px;" ><!--Query The order, the association inquires the user information, uses Resultmap to realize--> 
<select id= "Findordersuserresultmap" resultmap= " Ordersuserresultmap "> 
select t1.*, 
t2.username, 
t2.sex, 
t2.address 
from orders T1, 
T_user T2 
WHERE t1.user_id=t2.id 
</select></span>

3, add the following method to the Ordercustommapper.java interface:

<span style= "Font-family:comic Sans ms;font-size:18px;" >/** Query Order Association query user information, use RESLUTMAP to achieve * * public 
list<orders>findordersuserresultmap (); 
</span>

4. The JUnit tests implemented for Resulttype and Resultmap are shown in the following code:

<span style= "Font-family:comic Sans ms;font-size:18px;" 
>package com.mybatis.test; 
Import Java.io.InputStream; 
Import java.util.List; 
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 Com.mybatis.entity.Orders; 
Import Com.mybatis.entity.OrdersCustom; 
Import Com.mybatis.mapper.OrdersCustomMapper; 
public class Orderscustommappertest {private Sqlsessionfactory sqlsessionfactory; 
This method executes the @Before public void SetUp () throws Exception {String resource = "Sqlmapconfig.xml" before executing finduserbyidtest; 
InputStream InputStream = resources.getresourceasstream (Resource); 
Create Sqlsessionfcatory sqlsessionfactory = new Sqlsessionfactorybuilder (). Build (InputStream); //query order, associate query user information, test implemented using Resulttype @Test public void Testfindordersuser () {sqlsession sqlsession = sqlsessionFactory.opensession (); 
Create a proxy object orderscustommapper OC = Sqlsession.getmapper (Orderscustommapper.class); 
Call Mapper method List<orderscustom> List = Oc.findordersuser (); 
SYSTEM.OUT.PRINTLN (list); 
Sqlsession.close (); //query order, associate query user information, test implemented using Resultmap @Test public void Testfindordersuserresultmap () {sqlsession sqlsession = Sqlsessi 
Onfactory.opensession (); 
Create a proxy object orderscustommapper OC = Sqlsession.getmapper (Orderscustommapper.class); 
Call Mapper method List<orders> List = Oc.findordersuserresultmap (); 
SYSTEM.OUT.PRINTLN (list); 
Sqlsession.close ();

 }} </span>

5, Resulttype and RESULTMAP implementation of one-to-one query summary

To implement a one-to-one query:

A.resulttype: Using the Resulttype implementation is simpler, if the Pojo does not include the query out of the column name, you need to increase the column name corresponding to the property, you can complete the mapping.

B. The use of Resulttype is recommended if there are no special requirements for query results.

C.resultmap: Need to define the RESULTMAP, the implementation is a bit cumbersome, if there are special requirements for query results, using RESULTMAP can be completed to map the associated query Pojo properties.

D.resultmap can implement deferred loading, Resulttype cannot implement deferred loading.

Three or one to multiple query

3.1 Requirements: Search orders (related users) and order details;

3.2 Adding in the Orders.java class List<orderdetail> OrderDetails Property (the entity above has been added). The order information is eventually mapped to orders, and the order details for the order are mapped to the OrderDetails attribute in orders.

3.3 Add the following in Orderscustommapper.xml

<span style= "Font-family:comic Sans ms;font-size:18px;" ><!--Query order associated query users and order details--> 
<select id= "Findordersandorderdetailresultmap" resultmap= " Ordersandorderdetailresultmap "> 
select 
t1.*, 
t2.username, 
t2.sex, 
t2.address, 
t3.id orderdetail_id, 
t3.items_id, 
t3.items_num, 
t3.orders_id 
from 
orders T1, T_user 
T2, 
OrderDetail T3 
WHERE t1.user_id = t2.id and t3.orders_id=t1.id 
</select> 
</span>

The definition of Resultmap is also added to the Orderscustommapper.xml

 <span style= "font-family:comic Sans;" ><!--resultmap--> <resultmap type= "com.mybatis.entity.Orders" for orders (associated users) and order details Ordersandorderdetailresultmap "extends=" Ordersuserresultmap "> <!--order information--> <!--associated user information--> <!--using ex 
tends inheritance, no mapping of order information and user information in--> <!--associated Order details an order association query out of a number of order details, to use the collection mapping Collection: Mapping multiple records to the associated query to the collection Property: Maps The associated query to multiple records to that attribute of the Orders class OfType: Specifies the type of Pojo in the collection properties of the map--> <collection property= "OrderDetails" oftype= " Com.mybatis.entity.OrderDetail > <!--ID: Unique IDENTITY property: The attribute to map the unique identification of the order details to the Com.mybatis.entity.OrderDetail- > <id column= "orderdetail_id" property= "id"/> <result column= "items_id" property= "ItemsId"/> < Result column= "Items_num" property= "Itemsnum"/> <result column= "orders_id" property= "Ordersid"/> </ Collection> </resultMap> </span> 

3.4 Add a method to the Ordercustomemapper.java interface class, as shown in the following code:

<span style= "Font-family:comic Sans ms;font-size:18px;" >/** Inquiry Order (associated user) and order Details * * * public 
list<orderdetail>findordersandorderdetailresultmap (); 
</span>

3.5 Add a test method to the JUnit test class, as shown in the following code:

<span style= "Font-family:comic Sans ms;font-size:18px;" >//Query Order (associated user) and order Details test 
@Test public 
void Testfindordersandorderdetailresultmap () { 
sqlsession sqlsession = Sqlsessionfactory.opensession (); 
Create a proxy object 
orderscustommapper oc = Sqlsession.getmapper (orderscustommapper.class); 
Call mapper method 
list<orderdetail> List = Oc.findordersandorderdetailresultmap (); 
SYSTEM.OUT.PRINTLN (list); 
Sqlsession.close (); 
} 
</span>

3.6 Summary

MyBatis uses Resultmap's collection to map multiple records of an associated query to a list collection property. Use Resulttype implementation: Map the Order details to the OrderDetails in orders, you need to handle, use double loop traversal, remove duplicate records, put order details in the OrderDetails.

Four, multiple query

4.1 Requirements: Query users and users to buy the goods information

4.2 Mapping Ideas

Mapping user information into user, adding order List property List<orders>orderslist in the user class, mapping user-created orders to orderslist, and adding order detail list attributes to orders list< Orderdetail>orderdetials, map the order details to Orderdetials, add the items attribute in OrderDetail, and map the item corresponding to the order details.

4.3 orderscustommapper.xml Add the following code:

<span style= "Font-family:comic Sans ms;font-size:18px;" ><!--resultmap--> <resultmap type= "Com.mybatis.entity.User" id= "Useranditemsresultmap" to inquire about the goods purchased by users > <!--user information--> <id column= "user_id" property= "id"/> <result column= "username" property= "username"/&G 
T <result column= "Sex" property= "sex"/> <result "Address" column= "Address" property= <!--order Information A user corresponds to multiple sets Single, using collection mapping--> <collection property= "orderslist" oftype= "com.mybatis.entity.Orders" > <id column= " ID "property=" id "/> <result column=" user_id "property=" userid "/> <result column=" number "property=" number "/> <result column=" createtime "property=" Createtime "/> <result column=" Note "property=" note "/> <!- -Order Details One order includes multiple details--> <collection property= "OrderDetails" oftype= "Com.mybatis.entity.OrderDetail" > <id Co Lumn= "orderdetail_id" property= "id"/> <result column= "items_id" property= "Itemsid"/> <reSult column= "Items_num" property= "Itemsnum"/> <result column= "orders_id" property= "OrdersId"/> <!--commodity information Order details corresponding to a commodity--> <association property= "items" javatype= "Com.mybatis.entity.Items" > <id column= "items_id" property= "id"/> <result column= "items_name" property= "Itemsname"/> <result "column=" property= "Detail"/> <result column= "Items_price" property= "price"/> </association> </collection > </collection> </resultMap> <!--search for information about the goods purchased by users and users, using resulamap--> <select id= "  Finduseranditemsresultmap "resultmap=" Useranditemsresultmap "> SELECT t1.*, T2.username, T2.sex, t2.address, t3.id orderdetail_id, t3.items_id, T3.items_num, t3.orders_id, T4.itemsname items_name, T4.detail items_detail, T4.price Items_price from orders T1, T_user T2, OrderDetail T3, items t4 WHERE t1.user_id = T2.id and T3.orders_id=t1.id and t3.items_id = t4.id </select> </span>

4.4 Add the following methods to the Ordercustommapper.java:

<span style= "Font-family:comic Sans ms;font-size:18px;" >/** query users and users of the purchase of merchandise information * * Public 
list<user> finduseranditemsresultmap (); 
</span>

4.5 Add a test method to the JUnit test, as shown in the following code:

<span style= "Font-family:comic Sans ms;font-size:18px;" >//to inquire about the goods purchased by users and users 
@Test public 
void Testfinduseranditemsresultmap () { 
sqlsession sqlsession = Sqlsessionfactory.opensession (); 
Create a proxy object 
orderscustommapper oc = Sqlsession.getmapper (orderscustommapper.class); 
Call mapper method 
list<user> List = Oc.finduseranditemsresultmap (); 
SYSTEM.OUT.PRINTLN (list); 
Sqlsession.close (); 
} 
</span>

Summary of 4.6 Resultmap

Resulttype:

Role: Maps query results to Pojo in accordance with the SQL column name Pojo property name consistency.

Occasions: Common details of the display of records, such as user purchase details, will be associated with the query information all displayed in the page, at this time can be used directly resulttype each record map to Pojo, in the front-end Page traversal list (list is Pojo) can be.

Resultmap:

Use: Association and Collection complete one-to-one and one-to-many advanced mappings (special mapping requirements for results).

Association:

Role: Maps The associated query information to a Pojo object.

Occasion: In order to facilitate query association information, you can use association to map the associated order information to the Pojo attribute of the user object, such as: query order and associated user information.

Using Resulttype, you cannot map query results to the Pojo properties of Pojo objects, choosing whether to use Resulttype or Resultmap depending on the needs of the query traversal for the result set.

Collection

Role: Maps The associated query information to a list collection.

Occasion: In order to facilitate the query Traversal association information can use collection to map the association information to the list collection, for example: Query user permission scope module and the menu under the module, you can use collection to map the module to the module list, Map the menu list to the Menu list property of the module object, which is designed to facilitate traversal queries on the query result set.

If you use Resulttype, you cannot map query results to the list collection.

The above is a small set of MyBatis to introduce the advanced mapping of a pair of more than one or one pairs, many to many, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!