Share knowledge, not yourself.
preface
Complex mappings are a major feature of relational databases. A database that uses a relational model to organize data. It stores data in rows and columns to form a table for easy user understanding. In the actual development, we usually use the relationship between data tables to combine queries and extract the data we need. There are three types of complex relationships: one-to-one, one-to-many, and many-to-many
A, preparation,
To begin, we need to extend the test database mybatis_test that we created in the previous chapters. In addition to the User table, we need to create another table, the Orders order table, and simulate the actual requirements for an exercise.
Second, one-to-one relationship
First, we need to create the Orders table. The code is as follows:
CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) DEFAULT NULL, 'total_amount' double(1,2) DEFAULT NULL, 'create_time' datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Copy the codeInsert test data into the Orders table.
Insert into Orders values(1, 200,"2021-05-13 21:31:31"); Insert into orders values(2,1,4000,"2021-05-2 10:31:31"); Insert into orders values(3,2,30,"2021-05-5 21:11:31"); Insert into orders values(4,2,320,"2021-05-6 21:22:31"); Insert into orders values(5,3,64,"2021-05-7 21:33:31"); Insert into orders values(6,4,99,"2021-05-8 21:44:31"); Insert into orders values(7,3,12,"2021-05-9 21:55:31"); Insert into orders values(8,3,78,"2021-05-10 21:11:31");Copy the codeAfter the Orders table is created. Let’s first analyze the relationship between the two tables.
As shown in the figure above, we are going to simulate an order scenario. We use e-commerce software to place orders in our daily life. A user generates multiple orders. A reverse order belongs to only one user. So it’s one-to-many relative to the user and the order, and one-to-one relative to the order and the user. So, when we practice one-to-one relationships, we write code from an order point of view.
You have the user table and the Orders table. We also need to create the corresponding entity mapping class in the test project mybatis_Quick_start. The following code
public class Orders {
private Integer id;
private Integer uid;
private Double totalAmount;
private Date createTime;
public Integer getId(a) {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid(a) {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Double getTotalAmount(a) {
return totalAmount;
}
public void setTotalAmount(Double totalAmount) {
this.totalAmount = totalAmount;
}
public Date getCreateTime(a) {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime; }}Copy the codeSo now, let’s think about a problem. Since I am going to do the operation is multiple table combination query. What is the resultType of the select tag in mapper.xml? Our requirement is not only to query the order information but also carry the corresponding user information. The result should look like this:
So whether our resultType uses type Orders or type User does not satisfy the mapping. So what are we going to do? .Actually, it’s simple. We just need to add an attribute of type User to the Orders mapping entity to represent the one-to-one relationship between Orders and User
public class Orders {
private Integer id;
private Integer uid;
private Double totalAmount;
private Date createTime;
// indicates that the order belongs to that user
private User user;
public Integer getId(a) {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid(a) {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Double getTotalAmount(a) {
return totalAmount;
}
public void setTotalAmount(Double totalAmount) {
this.totalAmount = totalAmount;
}
public Date getCreateTime(a) {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public User getUser(a) {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString(a) {
return "Orders{" +
"id=" + id +
", uid=" + uid +
", totalAmount=" + totalAmount +
", createTime=" + createTime +
", user=" + user +
'} '; }}Copy the codeNext we create an OrdersDao interface under the DAO folder and let MyBatis create the dynamic proxy implementation object for us. And declare methods.
public interface OrdersDao {
List<Orders> getOrderAndUser(a);
}
Copy the codeNext, create orderSmapper.xml in the Resources folder, noting that the namespace path is correct.
<! DOCTYPEmapper
PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lgy.dao.OrdersDao">
<! -- Query orders and users -->
<select id="getOrderAndUser" resultType="orders">
select * from orders o, user u where o.uid = u.id;
</select>
</mapper>
Copy the codeFinally, we need to register our new mapping configuration file (orderSmapper.xml) in the core configuration file mybatis-config.xml.
<mappers>
<mapper resource="UserMapper.xml"></mapper>
<mapper resource="OrdersMapper.xml"></mapper>
</mappers>
Copy the codeWhat if you write a test class to execute it now? A BindingException occurred. Because even though we declared an object of type User in the Orders class. However, Mybatis does not understand that the queried user table information should be mapped to the user object, in order to solve this problem. MyBatis provides us with a resultMap tag that allows us to declare mapping relationships in multi-table combination queries.
<resultMap id="orderAndUser" type="com.lgy.pojo.Orders">
<result property="id" column="id"></result>
<result property="uid" column="uid"></result>
<result property="totalAmount" column="total_amount"></result>
<result property="createTime" column="create_time"></result>
<association property="user" javaType="com.lgy.pojo.User">
<result property="id" column="uid"></result>
<result property="username" column="username"></result>
<result property="gender" column="gender"></result>
<result property="age" column="age"></result>
</association>
</resultMap>
Copy the codeSo what does the above code mean? So we can draw a picture to make sense of it
So the resultType= Orders that we wrote earlier is wrong. We should change resultMap=orderAndUserM instead
<! -- Query orders and users -->
<select id="getOrderAndUser" resultMap="orderAndUser">
select * from orders o, user u where o.uid = u.id;
</select>
Copy the codeNow we are ready to write test methods. The code is as follows:
@Test
public void testGetOrderAndUser(a) throws IOException {
// Get the configuration file and convert it to the input stream
InputStream resourceAsStream =
Resources.getResourceAsStream("mybatis-config.xml");
// Get SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(resourceAsStream);
/ / get sqlSessin
SqlSession sqlSession = sqlSessionFactory.openSession();
// Get the interface proxy object using the JDK dynamic proxy
OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
List<Orders> orders = ordersDao.getOrderAndUser();
for (Orders order : orders) {
System.out.println(order);
}
/ / close the sqlSession
sqlSession.close();
}
Copy the codeThe results are not shown for space reasons. Test for yourself. If User object information cannot be displayed, override the toString method in the User class
One to many
For one-to-many relationships we can then use the Orders table and the User table. A user can have multiple orders relative to a user. The difference is that to represent a one-to-many mapping, we should declare a List
collection object in the User class. The code is as follows:
public class User {
private Integer id;
private String username;
private Integer gender;
private Integer age;
private List<Orders> orders;
public Integer getId(a) {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername(a) {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Integer getGender(a) {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Integer getAge(a) {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public List<Orders> getOrders(a) {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
@Override
public String toString(a) {
return "User{" +
"id=" + id +
", username='" + username + '\' ' +
", gender=" + gender +
", age=" + age +
", orders=" + orders +
'} '; }}Copy the codeWe then declare the getUserAndOrders method in the UserDao interface
---- omits other codeList<User> getUserAndOrders(a);
----
Copy the codeCompile resultMap mapping rules and query SQL in usermapper. XML mapping file
---- omits other code<resultMap id="userAndOrders" type="com.lgy.pojo.User">
<result property="id" column="id"></result>
<result property="username" column="username"></result>
<result property="gender" column="gender"></result>
<result property="age" column="age"></result>
<collection property="orders" ofType="com.lgy.pojo.Orders">
<result property="uid" column="uid"></result>
<result property="totalAmount" column="total_amount"></result>
<result property="createTime" column="create_time"></result>
</collection>
</resultMap>
<select id="getUserAndOrders" resultMap="userAndOrders">
select * from user u, orders o where u.id = o.uid
</select>
----
Copy the codeNote that because of the one-to-many mapping we use the collection tag and the mapping object attribute uses ofType. The test class code is as follows:
@Test
public void testGetUserAndOrders(a) throws IOException {
// Get the configuration file and convert it to the input stream
InputStream resourceAsStream =
Resources.getResourceAsStream("mybatis-config.xml");
// Get SqlSessionFactory
SqlSessionFactory sqlSessionFactory =
new SqlSessionFactoryBuilder().build(resourceAsStream);
/ / get sqlSessin
SqlSession sqlSession = sqlSessionFactory.openSession();
// Get the interface proxy object using the JDK dynamic proxy
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userAndOrders = userDao.getUserAndOrders();
for (User userAndOrder : userAndOrders) {
System.out.println(userAndOrder);
}
/ / close the sqlSession
sqlSession.close();
}
Copy the codeMany to many
One-to-one and one-to-many relationships can be represented by two tables, but in many-to-many relationships we need an intermediate table for recording relationships in addition to two entity tables for recording data. Let’s practice many-to-many queries using user permissions as an example. First we need to create two tables in the test database mybatis_test: ROLE (permission table) and USER_ROLE_MIDDLE (intermediate table for users and permissions)
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`role_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user_role_middle` (
`role_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the codeInsert into role values(1,' admin '); Insert into role values(2,' operator '); Insert into role values(3,' user '); Insert into user_role_middle values (1, 1); Insert into user_role_middle values (3, 1); Insert into user_role_middle values (3, 2); Insert into user_role_middle values (2, 2);Copy the codeIn this set of data, user 1 is both administrator and user, and user 2 is both operator and user. That’s exactly what we need in our many-to-many relationship. Next we will create the mapping entity classes for the permissions table (ROLE) and user permissions intermediate table (user_ROLE_MIDDLE) under the POJO report in the test project mybatis_Quick_START. The following code
public class Role {
private Integer id;
private String roleName;
private List<User> users;
public Integer getId(a) {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getRoleName(a) {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
@Override
public String toString(a) {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\' ' +
", users=" + users +
'} '; }}Copy the codepublic class UserRoleMiddle {
private Integer roleId;
private Integer userId;
public Integer getRoleId(a) {
return roleId;
}
public void setRoleId(Integer roleId) {
this.roleId = roleId;
}
public Integer getUserId(a) {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
@Override
public String toString(a) {
return "UserRoleMiddle{" +
"roleId=" + roleId +
", userId=" + userId +
'} '; }}Copy the codeBecause a User in a mapping relationship can have multiple roles, the List
attribute is also declared in the User class
---- omits get set toString methodprivate Integer id;
private String username;
private Integer gender;
private Integer age;
private List<Orders> orders;
private List<Role> roles;
----
Copy the codeCreate the RoleDao interface in the DAO folder and the RoleMapper.xml mapping configuration file in the Resources folder
public interface RoleDao {
// Query users with all permissions
List<Role> getRoles(a);
}
Copy the code<mapper namespace="com.lgy.dao.RoleDao">
<resultMap id="roleAndUsers" type="com.lgy.pojo.Role">
<result property="id" column="id"></result>
<result property="roleName" column="role_name"></result>
<collection property="users" ofType="com.lgy.pojo.User">
<result property="id" column="uid"></result>
<result property="username" column="username"></result>
<result property="gender" column="gender"></result>
<result property="age" column="age"></result>
</collection>
</resultMap>
<! -- Query orders and users -->
<select id="getRoles" resultMap="roleAndUsers">
select * from role r, user u, user_role_middle m where r.id = m.user_id and u.id = m.role_id;
</select>
</mapper>
Copy the codeFinally, you just need to configure the rolemapper.xml path in the core configuration class mybatis-config.xml to write the test class.
---- Omit other configurations<mappers>
<mapper resource="UserMapper.xml"></mapper>
<mapper resource="OrdersMapper.xml"></mapper>
<mapper resource="RoleMapper.xml"></mapper>
</mappers>
----
Copy the codeThe method of querying all user permissions is no longer shown as an extension exercise.
Test results are not shown for space reasons, you can test yourself
Five, the summary
As projects are recommended, the number of tables in the database increases. The more complex the relationships between tables become. You can use UML class diagrams to help you sort out complex relationships. Deepen the understanding of project organization. This development will also be handy. With a holistic understanding, it is easier to find the weaknesses and weaknesses in a project. It’s easier to give constructive opinions. Promotions and raises are just around the corner. Refueling workers!
6. Historical connection
- Chapter 1: Introduction and progress of MyBatis – introduction and introduction
- Chapter 2: MyBatis introduction and advanced – proxy mode and core configuration details
- Chapter 3: MyBatis introduction and advanced – mapper. XML dynamic SQL details
If there are ambiguities or errors. Welcome to point out. Thank you.