One-to-one mapping for advanced mapping

MyBatis Study Notes (1) — Usage

MyBatis learning Notes (2) — Mapping relations

One-to-one mapping for advanced mapping

Mybatis Learning Notes (4) – Advanced mapping one of multiple mappings

Advanced mapping many-to-many mapping

. Stay tuned for

The previous section on mapping relationships focused on input and output mappings, but we found that all queries are based on a single table, so this section continues to talk about multi-table queries, which are called high-level mappings. High-level mappings are also for output mappings, which are divided into one-to-one, one-to-many, and many-to-many. So the previous database structure is no longer enough, so let’s create a new order goods data model to explain the above various mappings.

Database preparation

In our database, we have the following tables:

The customer table (customers)

CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
Copy the code

Order Sheet (Orders)

CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;
Copy the code

The order table contains CUST_ID, which can be associated with the Customers table to represent the customer who placed the order

OrderItems

CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char(10) NOT NULL , Quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY (order_num, order_item)) ENGINE=InnoDB;Copy the code

The order item contains order_NUM, which is associated with the Orders table to record which order the order item belongs to. Prod_id indicates what product the order item is, associated with the Products table below.

Products

CREATE TABLE products ( prod_id char(10) NOT NULL, vend_id int NOT NULL , prod_name char(255) NOT NULL , Prod_price decimal(8,2) NOT NULL, prod_desc text NULL, PRIMARY KEY(prod_id)) ENGINE=InnoDB;Copy the code

Vend_id is associated with the vendor table below to indicate which vendor made the product.

supplier

CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
Copy the code

With these tables, let’s look at some mappings.

One-to-one mapping

Suppose we need to query all the order information, associated query the customer information that created the order, because an order can only have one customer, so it is one-to-one query. Based on the previous knowledge, we can use resultType or resultMap to set the return type.

Use resultType for one-to-one mapping

Query statement:

SELECT o.order_num,o.order_date, c.*
    FROM orders AS o, customers AS c
    WHERE o.cust_id = c.cust_id
Copy the code

To create a POJO, since our query results contain the contents of both tables, we define Orders first

public class Orders {
    private Integer orderNum;
    private Date orderDate;
    private Integer custId;
    //setter and getter
    ...
}
Copy the code

Inherits Orders to define a custom OrdersCustomers class to hold query results.

public class OrdersCustomers extends Orders {
    private String custName;
    private String custAddress;
    private String custCity;
    private String custState;
    private String custZip;
    private String custCountry;
    private String custContact;
    private String custEmail;
    //setter and getter
    ...
}
Copy the code

Since poJOs are all hump names, database column names are underlined, so here we set mybatis config file:

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true" />
</settings>
Copy the code

This enables automatic mapping of database to POJO objects.

Defined in Mapper:

<select id="findOrdersCustomer" resultType="com.shuqing28.pojo.OrdersCustomers">
        SELECT o.order_num,o.order_date, c.*
        FROM orders AS o, customers AS c
        WHERE o.cust_id = c.cust_id
    </select>
Copy the code

DAO defines the interface:

List<OrdersCustomers> findOrdersCustomer();
Copy the code

Test code:

@Test
public void getOrdersCustomers(){ SqlSession sqlSession = sqlSessionFactory.openSession(); try { OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class); List<OrdersCustomers> orders = ordersDao.findOrdersCustomer(); System.out.println(orders); } finally { sqlSession.close(); }}Copy the code

Query result:

The resultMap is used for one-to-one mapping

The SQL statement does not change. We first add the Customer attribute to Orders:

private Customer customer;
Copy the code

Define the resultMap:

<resultMap id="OrdersCustomerResultMap" type="com.shuqing28.pojo.Orders">
    <id column="order_num" property="orderNum"/>
    <result column="order_date" property="orderDate"/>
    <result column="cust_id" property="custId"/>
    
    <association property="customer" javaType="com.shuqing28.pojo.Customer">
        <id column="cust_id" property="custId"/>
        <result column="cust_name" property="custName"/>
        <result column="cust_address" property="custAddress"/>
        <result column="cust_city" property="custCity"/>
        <result column="cust_state" property="custState"/>
        <result column="cust_zip" property="custZip"/>
        <result column="cust_country" property="custCountry"/>
        <result column="cust_contact" property="custContact"/>
        <result column="cust_email" property="custEmail"/>
    </association>
</resultMap>
Copy the code

Notice that the Association tag is used to configure the customer information associated with the mapping. The Map id is OrdersCustomerResultMap, which you can use later.

Define a SELECT statement:

 <select id="findOrdersCustomerMap" resultMap="OrdersCustomerResultMap">
    SELECT o.order_num,o.order_date, c.*
    FROM orders AS o, customers AS c
    WHERE o.cust_id = c.cust_id
</select>
Copy the code

We use the resultMap defined earlier

Defines the interface

public List<Orders> findOrdersCustomerMap();
Copy the code

The test code

@Test
public void getOrdersCustomersMap(){ SqlSession sqlSession = sqlSessionFactory.openSession(); try { OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class); List<Orders> orders = ordersDao.findOrdersCustomerMap(); System.out.println(orders); } finally { sqlSession.close(); }}Copy the code

The test results

Summary: One-to-one mapping, focusing on the Association tag of resultMap