When learning hibernate, the editor has been exposed to various mappings. How does mapping in mybatis work? In this blog post today, the editor will mainly briefly introduce the advanced mapping in mybatis, including one-to-one, one-to-many, and many-to-many. I hope that more and more friends in need will be helpful. The editor mainly introduces the order product data model, one-to-one query, one-to-many query, and many-to-many query.
1. Order product data model
1. The database execution script is as follows:
<span style="font-family:Comic Sans MS;font-size:18px;">CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT, itemsname VARCHAR(32) NOT NULL COMMENT 'Product name', price FLOAT(10,1) NOT NULL COMMENT 'Product price', detail TEXT COMMENT 'Product description', pic VARCHAR(64) DEFAULT NULL COMMENT 'Product picture', createtime DATETIME 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 NOT NULL COMMENT 'Order id', items_id INT NOT NULL COMMENT 'Product id', items_num INT DEFAULT NULL COMMENT 'Product Purchase Quantity', 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 ) DEFAULT CHARSET=utf8; /*Table structure for table `orders` */ CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL COMMENT 'Order id', number VARCHAR(30) 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 `t_user` (`id`) ON 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(32) NOT NULL COMMENT 'User name', birthday DATE DEFAULT NULL COMMENT 'Birthday', sex CHAR(1) DEFAULT NULL COMMENT 'Gender', address VARCHAR(256) 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 ('Desktop',3000.0,'This computer is of great quality!',NULL,'2015-07-07 13:28:53'), ('Notebook',6000.0,'Notebook', good performance, good quality!',NULL,'2015-07-08 13:22:57'), ('Backpack',200.0,'Class-branded backpack, high capacity and 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 ('Wang Wu',NULL,'2',NULL), ('Zhang San','2014-07-10','1','Beijing'), ('Zhang Xiaoming',NULL,'1','Zhengzhou, Henan'), ('Chen Xiaoming',NULL,'1','Zhengzhou, Henan'), ('Zhang Sanfeng',NULL,'1','Zhengzhou, Henan'), ('Zhang Sanfeng',NULL,'1','Zhengzhou, Henan'), ('Chen Xiaoming',NULL,'1','Zhengzhou, Henan'), ('Wang Wu',NULL,NULL,NULL), ('Xiao A','2015-06-27','2','Beijing'), ('Xiao B','2015-06-27','2','Beijing'), ('Xiao C','2015-06-27','1','Beijing'), ('Xiao D','2015-06-27','2','Beijing'); </span>2. Data model analysis ideas
(1). Data content recorded in each table: familiarize yourself with the content recorded in each table in modules, which is equivalent to the process of learning system requirements (functions);
(2). Important field settings for each table: non-empty fields, foreign key fields;
(3). The relationship between database-level tables and tables: foreign key relationship;
(4). Business relationship between tables: When analyzing the business relationship between tables, it must be based on a certain business significance to analyze.
3. Analysis of database ideas for order product models, as shown in the figure below:
2. One-to-one query
2.1. Requirements: Query order information, and associate user information
2.2. resultType implementation
2.2.1 sql statement
Determine the main table of the query: the order table, determine the association table of the query, the user table, the code is as follows:
<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 an entity entity
The user entity User.java, the code is as follows:
<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; // Address private String address; // Birthday private Date birthday; // User-created ordersList 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; /** Order user id */ private Integer userid; /** Order number */ private String number; /** Order time*/ private Date createTime; /** Note*/ private String note; // User information private User user; // Order details private List<OrderDetail> orderdetails; // getter and setter ...... } </span>Product 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 (product entity class) * @author Ding Guohua*/ public class Items { /** Product table primary key Id */ private Integer id; /** Product name*/ private String itemsName; /** Product price*/ private float price; /** Product description*/ private String detail; /** Product picture*/ private String picture; /** 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 details entity) * @author Ding Guohua*/ public class OrderDetail { /** Main, details list Id */ private Integer id; /** Id */ private Integer ordersId; /** Product id */ private Integer itemsId; /** Product purchase quantity*/ private Integer itemsNum; // Product information corresponding to the details private Items items; // getter and setter ...... } </span>Create a wrapper class to map all the query information to this class OrdersCustom.java
<span style="font-family:Comic Sans MS;font-size:18px;">/** * @ClassName: OrdersCustom * @Description: TODO (the extended class of orders, which maps orders and user query results through this class to allow this class to inherit more entities) * @author: Ding Guohua*/ 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 is as follows:
<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 Guohua*/ public interface OrdersCustomMapper { /** Query orders, associate query user information*/ public List<OrdersCustom> findOrdersUser(); } </span>2.2.4 Creating OrdersCustomMapper.xml is the same as the corresponding interface name on it. While loading the configuration file through the mapper interface, the code is as follows:
<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 function is to classify the management of SQL, which is understood as SQL isolation Note: When using mapper agent development, namespace has a special function, namespace is equal to mapper interface address--> <mapper namespace="com.mybatis.mapper.OrdersCustomMapper"> <!-- Query orders, associate 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.3 resultMap implementation
2.3.1 SQL statement same as above
2.3.2 resultMap mapping ideas:
Use resultMap to map the order information in the query result to the Orders object, add the User attribute in the orders class, and map the associated query user information to the user attribute in the orders object (it has been added in the orders entity above).
2.3.3 ordersCustomMapper.xml
1. Define resultMap, the code is as follows:
<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"> <!-- Configure the mapping order information--> <!-- id: The unique identifier in the query column, the unique identifier in the order information. If multiple columns form a unique identifier (such as: the dictionary table in general database design uses a joint primary key), multiple ids need to be configured column: Unique identification column for order information property: The attribute mapped to the order information by the unique identification column for order information (if: the primary key in the orders table in the database is orders_id, and the entity attribute name is ordersId, then this configuration should be <id column="orders_id" property="ordersId"/>, similar to the hibernate entity mapping file configuration). --> <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"/> <result column="note"/> <result column="note" property="note"/> <!-- Configure mapped associated user information --> <!-- Association: Information used to map associated query single object property: To map the user information of the associated query to which property in Orders --> <association property="user" javaType="com.mybatis.entity.User"> <!-- id: The unique identifier of the associated query user column: Specify the column that uniquely identifies the user information property: the property mapped to the user --> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> </span>
2. Statement definition, the code is as follows:
<span style="font-family:Comic Sans MS;font-size:18px;"><!-- Query orders, associate query user information, use resultMap to implement --> <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 implement */ public List<Orders>findOrdersUserResultMap(); </span>
4. The junit test of resultType and resultMap implementation, the code is as follows:
<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.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 is to execute @Before public void setUp() throws Exception { String resource = "SqlMapConfig.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); // Create SqlSessionFcatory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } // Query the order, associate the query user information, and use resultType to test @Test public void TestFindOrdersUser() { SqlSession sqlSession = sqlSessionFactory.openSession(); // Create the proxy object OrdersCustomMapper oc = sqlSession.getMapper(OrdersCustomMapper.class); // Call mapper method List<OrdersCustom> list = oc.findOrdersUser(); System.out.println(list); sqlSession.close(); } // Query the order, associate the query user information, and tests implemented using resultMap @Test public void TestFindOrdersUserResultMap() { SqlSession sqlSession = sqlSessionFactory.openSession(); // Create 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 implement one-to-one query summary
Implement one-to-one query:
a.resultType: It is relatively simple to implement using resultType. If the query column name is not included in the pojo, you need to add the corresponding attributes of the column name to complete the mapping.
b. If there are no special requirements for query results, it is recommended to use resultType.
c.resultMap: The resultMap needs to be defined separately, which is a bit troublesome. If there are special requirements for query results, using resultMap can complete the attributes of the associated query mapping pojo.
d.resultMap can implement lazy loading, resultType cannot implement lazy loading.
3. One-to-many query
3.1 Requirements: Query orders (associated users) and order details;
3.2 Add the List<orderDetail> orderDetails property in the orders.java class (the above entity has been added). Finally, the order information will be mapped into orders, and the order details corresponding to the order are mapped into the orderDetails property in orders.
3.3 Add the following in ordersCustomMapper.xml
<span style="font-family:Comic Sans MS;font-size:18px;"><!-- Query Order Association Query User and Order Detail--> <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 ordersCustomMapper.xml
<span style="font-family:Comic Sans MS;font-size:18px;"><!-- resultMap for querying orders (associated users) and order details --> <resultMap type="com.mybatis.entity.Orders" id="ordersAndOrderDetailResultMap" extends="OrdersUserResultMap"> <!-- Order information--> <!-- Associated user information--> <!-- Use extends inheritance, do not configure the mapping of order information and user information in it --> <!-- Associated Order Detail Information One order association query has multiple order details. You need to use collection mapping collection: Map multiple records queried in the association property to the collection property ofType: Specify the type of pojo in the mapped collection property--> <collection property="orderdetails" ofType="com.mybatis.entity.OrderDetail"> <!-- id: Unique identification property: To map the unique identifier of the order details to the property of 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, the code is as follows:
<span style="font-family:Comic Sans MS;font-size:18px;">/**Query order (associated users) and order details*/ public List<OrderDetail>findOrdersAndOrderDetailResultMap(); </span>
3.5 Add test methods to the Junit test class, the code is as follows:
<span style="font-family:Comic Sans MS;font-size:18px;">// Test for querying orders (associated users) and order details @Test public void TestFindOrdersAndOrderDetailResultMap() { SqlSession sqlSession = sqlSessionFactory.openSession(); // Create 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 the associated query into a list collection property. Implementation using resultType: Map order details into orderdetails in orders, which needs to be processed by yourself, use double loop traversal, remove duplicate records, and place order details in orderdetails.
4. Many-to-many query
4.1 Requirements: Query the user and the product information purchased by the user
4.2 Mapping ideas
Map user information into user, add the order list attribute List<Orders>orderslist in the user class to map the orderslist; add the order details list attribute List<OrderDetail>orderdetials in Orders, map the order details to orderdetials; add the Items attribute in OrderDetail to map the items corresponding to the order details to Item.
4.3 OrdersCustomMapper.xml adds the following code:
<span style="font-family:Comic Sans MS;font-size:18px;"><!-- ResultMap for querying the product information purchased by the user --> <resultMap type="com.mybatis.entity.User" id="userAndItemsResultMap"> <!-- User information--> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> <!-- Order information A user corresponds to multiple orders, 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"/> <result column="note"/> <result column="note" property="note"/> <!-- Order Detail One order includes multiple details--> <collection property="orderdetails" ofType="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"/> <result column="items_name" property="itemsName"/> <result column="itemsName"/> <result column="itemsName"/> <result column="items" javaType="com.mybatis.entity.Items"> <id column="items_id" property="id"/> <result column="items_name" property="itemsName"/> <result column="items_name" property="itemsName"/> <result column="items_detail" property="detail"/> <result column="items_price" property="price"/> </association> </collection> </collection> </resultMap> <!-- To query the product information purchased by users and users, use 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 method in OrderCustomMapper.java:
<span style="font-family:Comic Sans MS;font-size:18px;"> /** Query the product information purchased by users and users*/ public List<User> findUserAndItemsResultMap(); </span>
4.5 Add test methods to Junit test, the code is as follows:
<span style="font-family:Comic Sans MS;font-size:18px;">// Query the information of the products purchased by the user and the user @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> 4.6 resultMap summary
resultType:
Function: Map the query results into pojo attribute names according to the SQL column name.
Occasion: Common display of detailed records, such as when users purchase product details and display all the associated query information on the page, you can directly use resultType to map each record into a pojo, and traverse the list (pojo in the list) on the front-end page.
resultMap:
Use: association and collection to complete one-to-one and one-to-many advanced mapping (there are special mapping requirements for the results).
association:
Function: Map the associated query information into a pojo object.
Occasion: In order to facilitate querying associated information, you can use association to map associated order information into pojo attributes of user objects, such as: querying orders and associated user information.
Using resultType cannot map the query results to the pojo attribute of the pojo object. Choose whether to use resultType or resultMap according to the needs of traversing the result set query.
Collection:
Function: Map the associated query information into a list collection.
Occasion: In order to facilitate querying traversal association information, you can use collection to map the association information to the list collection. For example: querying the user permission scope module and the menu under the module, you can use collection to map the module list to map the menu list attributes of the module object. The purpose of this is to facilitate traversal querying the query result set.
If you use resultType, you cannot map the query results to the list collection.
The above is the advanced mapping in Mybatis introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!