MyBatis introduction and advanced - complex mapping

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 code

Insert 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 code

After 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 code

So 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 code

Next 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 code

Next, 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 code

Finally, 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 code

What 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 code

So 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 code

Now 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 code

The 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 code

We then declare the getUserAndOrders method in the UserDao interface

---- omits other codeList<User> getUserAndOrders(a);
----
Copy the code

Compile 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 code

Note 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 code

Many 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 code

Insert 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 code

In 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 code

public 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 code

Because 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 code

Create 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 code

Finally, 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 code

The 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.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MyBatis introduction and advanced – complex mapping

preface

A, preparation,

Second, one-to-one relationship

One to many

Many to many

Five, the summary

6. Historical connection

MyBatis introduction and advanced – complex mapping

preface

A, preparation,

Second, one-to-one relationship

One to many

Many to many

Five, the summary

6. Historical connection

Related Posts

With this article, coding in Python is no longer a nightmare

Ubuntu uses Nginx+ RTMP module to build streaming video on demand service

Springweb development in coding garble problem analysis