Understand mybatis gradually through the project

Time:2020-10-23

Related reading:

1、Understand mybatis < 1 > step by step through the project

2、Understand mybatis < 2 > step by step through the project

All code and documents of this project are hosted in GitHub address:https://github.com/zhisheng17/mybatis

Advanced knowledge of mybatis

Schedule: analyze the order item data model

Order item data model

Understand mybatis  gradually through the project

Data model analysis ideas:

1. Data content recorded in each table (the module is familiar with the content recorded in each table, which is equivalent to the process of learning system requirements)

2. Important field setting of each table (non empty field, foreign key field)

3. Relationship between database level tables and tables (foreign key relationships)

4. Relationship between tables and table services (to be analyzed based on the meaning of each business)

Data model analysis model

  • User table: record the user information of purchasing goods

  • Order table: records the orders created by users (purchase orders)

  • Order detail: (records the details of the order, that is, the information of the purchased goods)

  • Item table: records the commodity information

Relationship between tables and table services

When analyzing the business relationship between tables, the analysis should be based on a certain business meaning.

First, analyze the business relationship between relational tables between data levels

1、Usre and orders

User — > orders: a user can create multiple orders, one to many

Orders — > User: an order is created by only one user, one-to-one

2、 Orders and orderdetail

Orders — > orderdetail: an order can include multiple order details, because a single order can purchase multiple goods, and the purchase information of each product is recorded in the orderdetail, with a one to many relationship

Orderdetail — > orders: an order detail can only be included in an order, one-to-one

3、 Orderdetail and items

Orderdetail — > items: an order details only corresponds to one item information, one-to-one

Items — > orderdetail: an item can be included in multiple order details, one to many

Then analyze whether there is a business relationship between unrelated tables at the database level

4、 Orders and items

The relationship between orders and items can be established through the orderdetail table.

Understand mybatis  gradually through the project

One to one query

Demand:

Query order information, associated query the user information of order creation

Use resulttype

  • SQL statement

Confirm the main table of query: order table

Determine the associated table of the query: user table

Use inner link in associated query? Or external links?

Because there is a foreign key (user) in the orders table_ ID). Only one record can be found by querying the user table through foreign key Association, and internal link can be used.

SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id
  • Create POJO

Orders.java

public class Orders {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    //User information
    private User user;
    //Order details
    private List<Orderdetail> orderdetails;
    //getter and setter
}

OrderCustom.java

//By mapping the results of orders and user queries, this class inherits POJO classes with more fields
public class OrdersCustom extends Orders{
  //Add user properties
  /*USER.username,
    USER.sex,
    USER.address */
  private String username;
  private String sex;
  private String address;
  //getter and setter 
}
  • Mapping file

OrdersMapperCustom.xml

<! -- Query order Association query user information -- >
    <select id="findOrdersUser" resultType="cn.zhisheng.mybatis.po.OrdersCustom">
        SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id
    </select>
  • Mapper file

OrdersMapperCustom.java

public interface OrdersMapperCustom
{
    public OrdersCustom findOrdersUser() throws Exception;
}
  • Test code (remember in SqlConfig.xml Add in load OrdersMapperCustom.xml Document)

    @Test
    public void testFindOrdersUser() throws Exception
    {
      SqlSession sqlSession = sqlSessionFactory.openSession();
      //Create an ordersmappercustom object, and mybatis automatically generates a proxy object
      OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
      //Method to call ordersmappercustom
       List<OrdersCustom> list = ordersMapperCustom.findOrdersUser();
      System.out.println(list);
      sqlSession.close();
    }
  • test result

Understand mybatis  gradually through the project

Using resultmap

  • SQL statement (same as above)

  • Thinking of mapping with resultmap

Use resultmap to map the order information in the query results to the orders object, add the user attribute in the orders class, and map the user information from the association query to the user attribute in the orders object.

//User information
 private User user;
  • Mapping file

OrdersMapperCustom.xml

Define the resultmap first

<! -- define the resultmap of query order Association query user information
        Map the entire query result to cn.zhisheng.mybatis . po.Orders
    -->
    <resultMap id="OrdersUserResultMap" type="cn.zhisheng.mybatis.po.Orders">
        <! -- configure the mapped order information -- >
        <! -- ID means the unique ID in the query result. Here, it is the unique ID of the order. If it is a unique ID composed of multiple columns, then you need to configure multiple IDs
        Column: ID is the unique identifying column in the order information
        Property: ID is the ID property mapped to orders by the unique identification column of order information
        Finally, resultmap makes a mapping relationship (corresponding relationship) between column and property
        -->
        <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"/>

        <! -- configure the associated user information of the mapping
            Association is used to map and associate information about a single object
            Property maps the user information to be associated with the query to the properties in orders
        -->
        <association property="user" javaType="cn.zhisheng.mybatis.po.User">
            <! -- id the unique identification of associated user information
                Column: specifies information that uniquely identifies the user
                Property: the property mapped to user
            -->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
            <result column="birthday" property="birthday"/>
        </association>
    </resultMap>
<! -- Query order Association query user information, use resultmap -- >
    <select id="findOrdersUserResultMap" resultMap="OrdersUserResultMap">
        SELECT orders.*, USER.username, USER.sex, USER.address FROM orders, USER WHERE orders.user_id = user.id
    </select>
  • Mapper file

    public List<Orders> findOrdersUserResultMap() throws Exception;
  • Test code

    @Test
     public void testFindOrdersUserResultMap() throws Exception
     {
         SqlSession sqlSession = sqlSessionFactory.openSession();
         //Create an ordersmappercustom object, and mybatis automatically generates a proxy object
         OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
         //Method to call ordersmappercustom
         List<Orders> list = ordersMapperCustom.findOrdersUserResultMap();
         System.out.println(list);
         sqlSession.close();
     }
  • test result

Understand mybatis  gradually through the project

Summary of one-to-one queries using resulttype and resultmap

  • Resulttype: it is relatively simple to use resulttype. If the POJO does not include the query column name, you need to add the attribute corresponding to the column name to complete the mapping. If there are no special requirements for query results, resulttype is recommended.

  • Resultmap: you need to define a resultmap separately, which is a bit cumbersome to implement. If you have special requirements for query results, you can use resultmap to map the associated query into the attributes of POJO. Resultmap can implement delayed loading, but resulttype cannot.

One to many query

demand: query order and order details

SQL statement

Confirm main query table: order table

Confirm associated query table: order details

On the basis of one-to-one query, you can add order details Association.

SELECT orders.*, USER.username, USER.sex, USER.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id FROM orders, USER,
orderdetail WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id

analysis:

Use resulttype to map the above query results to POJO, and the order information is repeated.

requirement:

Duplicate records cannot appear on the orders map.

In orders.java Class to add the list < orderdetail > orderdetails property.

Finally, the order information is mapped to orders, and the order details corresponding to the orders are mapped to the orderdetails property in the orders.

The number of orders records mapped to is two (the order information is not repeated)

The orderdetails property in each order stores the order details corresponding to the order.

Mapping file

First, define the resultmap

<! -- define the resultmap to query the order and order details, and use extends inheritance, without configuring the mapping of order information and user information in -- >
    <resultMap id="OrdersAndOrderDetailResultMap" type="cn.zhisheng.mybatis.po.Orders" extends="OrdersUserResultMap">
        <! -- order information -- >
        <! -- user information -- >
        <! -- using extends inheritance, you do not need to configure the mapping of order information and user information in -- >
        <! -- order details
        Multiple details have been found in an order Association, which needs to be mapped with collection
        Collection: query the association to multiple records and map them to the collection object
        Property: Map Association query to multiple records to cn.zhisheng.mybatis . po.Orders Which attribute
        Oftype: Specifies the type of POJO mapped to the list collection attribute
         -->
        <collection property="orderdetails" ofType="cn.zhisheng.mybatis.po.Orderdetail">
        <! -- ID: unique identification of order details
   Property: to map the unique ID of the order details to cn.zhisheng.mybatis . po.Orderdetail Which attribute of -- >
            <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>
<! -- to query the order and order details, use resultmap -- >
    <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap">
        SELECT orders.*, USER.username, USER.sex, USER.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id
        FROM orders, USER,orderdetail WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
    </select>

Mapper file

public List<Orders> findOrdersAndOrderDetailResultMap() throws Exception;

Test file

@Test
    public void testFindOrdersAndOrderDetailResultMap() throws Exception
    {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //Create an ordersmappercustom object, and mybatis automatically generates a proxy object
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        //Method to call ordersmappercustom
        List<Orders> list = ordersMapperCustom.findOrdersAndOrderDetailResultMap();
        System.out.println(list);
        sqlSession.close();
    }

test result

Understand mybatis  gradually through the project

Summary:

Mybatis uses the collection of resultmap to map multiple records of associated query to a list collection attribute.

Use resulttype: map the order details to orderdetails in orders, which needs to be processed by yourself. Double loop traversal is used to remove duplicate records, and the order details are placed in orderdetails.

Many to many queries

demand: query users and their purchase information.

SQL statement

The main query table is: user table

Associated table: since there is no direct association between the user and the product, it is associated through the order and order details

orders、orderdetail、items

SELECT   orders.*, USER.username, USER.sex, USER.address,  orderdetail.id orderdetail_id,
orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id, items.name items_name,
items.detail items_detail, items.price items_price FROM orders, USER, orderdetail, items WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id AND orderdetail.items_id = items.id

Mapping ideas

Map user information to user.
Add the order list attribute list < orders > orderslist in the user class to map the order created by the user to the ORDERLIST
Add the order details list attribute list < orderdetail > orderdetails in orders to map the order details to orderdetails
Add the items property in orderdetail to map the items corresponding to the order details

Define resultmap:

<! -- define the resultmap to query users and their purchase information -- >
    <resultMap id="UserAndItemsResultMap" type="cn.zhisheng.mybatis.po.User">
        <! -- user information -- >
        <id column="user_id" property="id"/>
        <result column="username" property="username"/>
        <result column="sex" property="sex"/>
        <result column="birthday" property="birthday"/>
        <result column="address" property="address"/>

        <! -- order information
            One user corresponds to multiple orders and uses collection mapping
        -->
        <collection property="ordersList" ofType="cn.zhisheng.mybatis.po.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
                An order includes multiple details
            -->
            <collection property="orderdetails" ofType="cn.zhisheng.mybatis.po.Orderdetail">

                <id column="orderdetail_id" property="id"/>
                <result column="orders_id" property="ordersId"/>
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>

                <! -- product information
                     An order detail corresponds to a commodity
                -->
                <association property="items" javaType="cn.zhisheng.mybatis.po.Items">
                    <id column="items_id" property="id"/>
                    <result column="items_name" property="name"/>
                    <result column="items_price" property="price"/>
                    <result column="items_pic" property="pic"/>
                    <result column="items_createtime" property="createtime"/>
                    <result column="items_detail" property="detail"/>
                 </association>
            </collection>
        </collection>
    </resultMap>

Mapping file

<! -- Query users and their purchase information, and use resultmap -- >
    <select id="findUserAndItemsResultMap" resultMap="UserAndItemsResultMap">
        SELECT orders.*, USER.username, USER.sex, USER.address, orderdetail.id orderdetail_id, orderdetail.items_id, orderdetail.items_num, orderdetail.orders_id
        FROM orders, USER,orderdetail WHERE orders.user_id = user.id AND orderdetail.orders_id=orders.id
    </select>

Mapper file

public List<User> findUserAndItemsResultMap() throws  Exception;

Test file

@Test
    public void testFindUserAndItemsResultMap() throws Exception
    {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //Create an ordersmappercustom object, and mybatis automatically generates a proxy object
        OrdersMapperCustom ordersMapperCustom = sqlSession.getMapper(OrdersMapperCustom.class);
        //Method to call ordersmappercustom
        List<User> list = ordersMapperCustom.findUserAndItemsResultMap();
        System.out.println(list);
        sqlSession.close();
    }

Test:

Understand mybatis  gradually through the project

I went, even reported a mistake, but don’t be afraid, by checking the error information, you can know that I forgot in the User.java The ORDERLIST attribute is added in. Next, I add it, and the getter and setter methods.

//User created order list
    private List<Orders> ordersList;
    public List<Orders> getOrdersList() {
        return ordersList;
    }
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }

We can do it again.

Understand mybatis  gradually through the project

Summary of many to many queries

The detailed list of goods purchased by users will be queried (user name, user address, name of purchased goods, time of purchasing goods and quantity of goods purchased)

According to the above requirements, we use resulttype to map the query records to an extended POJO, which is very simple to implement the function of detailed list.

One to many is a special case of many to many

The relationship between users and products is many to many.

Demand 1:

Query fields: user account number, user name, user gender, commodity name, commodity price (most common)

Common detailed list in enterprise development, detailed list of goods purchased by users,

Use resulttype to map the top query column to the POJO output.

Demand 2:

Query fields: user account number, user name, quantity of purchased goods, commodity details (the details are displayed by mouse over)

Use resultmap to map the detailed list of goods purchased by users to the user object.

Summary:

The use of resultmap is for those functions that have special requirements for query result mapping, such as multiple lists in a list with special requirements.

Resultmap summary

resultType:

effect:

The query results are mapped to POJO according to the consistency of SQL column name POJO attribute name.

Occasion:

In this case, you can map all the details of a product to the front-end page of the jopo list. For example, you can display all the details of a product in the front-end page of the jopo list.

resultMap:

Use association and collection to complete one-to-one and one to many high-level mapping (with special mapping requirements for the results).

association:

effect:

Map the association query information to a POJO object.

Occasion:

In order to facilitate the query of association information, association can be used to map the associated order information into the POJO attribute of the user object, such as query order and associated user information.
Using resulttype cannot map the query result to the POJO attribute of the POJO object. Select resulttype or resultmap according to the need of traversing the result set query.

collection:

effect:

Map the associated query information to a list collection.

Occasion:

In order to facilitate the query and traversal of association information, we can use collection to map the association information to the list collection, for example, to query the user permission range module and the menu under the module. We can use collection to map the module to the module list, and map the menu list to the menu list attribute of the module object. This is also to facilitate the traversal query of the query result set. If you use resulttype, you cannot map the query results to the list collection.

Recommended Today

Docker quickly builds redis cluster

What is redis cluster Redis cluster is a distributed database solution provided by redis. The cluster shares data through sharding, and provides replication and fail over functions. node A redis cluster is usually composed of multiple nodes. At the beginning, each node is independent of each other, and they are in a cluster that only […]