mybatis Travel Chapter 6 - Association Query

Posted by oaskedal on Fri, 10 May 2019 14:16:08 +0200

I. Introduction

Through dynamic SQL, we can compile complex SQL, but the previous examples are single-table queries. In actual development, of course, not all single-table queries. Many times we need to carry out related multi-table queries (some companies still try to use single-table queries for performance). The relationship between tables and tables can be divided into one-to-one, one-to-many, and many-to-many. How to write it?

Create tables and entities first:

CREATE TABLE `user` (
  `id` int(11) 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`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'Wang Wu', null, '2', null);
INSERT INTO `user` VALUES ('10', 'Zhang San', '2014-07-10', '1', 'Beijing City');
INSERT INTO `user` VALUES ('16', 'Zhang Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('22', 'Chen Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('24', 'Zhang Sanfeng', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('25', 'Chen Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('26', 'Wang Wu', null, null, null);

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT 'Order user id',
  `number` varchar(32) NOT NULL COMMENT 'Order number',
  `createtime` datetime NOT NULL COMMENT 'Order creation time',
  `note` varchar(100) DEFAULT NULL COMMENT 'Remarks',
  PRIMARY KEY (`id`),
  KEY `FK_order_1` (`user_id`),
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES ('3', '1', '1000010', '2015-02-04 13:22:35', null);
INSERT INTO `order` VALUES ('4', '1', '1000011', '2015-02-03 13:22:41', null);
INSERT INTO `order` VALUES ('5', '10', '1000012', '2015-02-12 16:13:23', null);

User:

@Data
public class User implements Serializable {
    private Integer id;
    // User name
    private String username;
    // Gender
    private String sex;
    // Birthday
    private Date birthday;
    // address
    private String address;
}

Order:

@Data
public class Order {

    // Order id
    private int id;
    // user id
    private Integer userId;
    // Order number
    private String number;
    // Order creation time
    private Date createtime;
    // Remarks
    private String note;
}

2. One-to-one

Order form and user table, an order is created by a customer, when we check the user through the order, it is a one-to-one relationship.

There are two ways to achieve this.

2.1. Use resultType

You can modify the order pojo class, which includes order information and user information. When returning to the object, mybatis automatically injects user information into it and creates OrderUserVO:

@Data
public class OrderUserVO extends Order {
    /**
     * Customer name
    */
    private String username;
    /**
     * Customer address
    */
    private String address;  
}

xml file:

 <select id="queryOrderUser" resultType="com.yuanqinnan.pojo.OrderUserVO">
    SELECTo.id,o.user_id,userId,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
  </select>

Interface:

public interface OrderMapper {
    List<Order> queryOrderAll();
    List<Order> queryOrderAll2();
    List<OrderUserVO> queryOrderUser();
}

Test:

@Test
public void queryOrderUser(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<OrderUserVO> list = orderMapper.queryOrderUser();
    for (OrderUserVO u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

Result:

A special pojo class is defined as the output type, in which all fields of the sql query result set are defined. This method is relatively simple and widely used in enterprises.

2.2. Use resultMap

Using resultMap, a special resultMap is defined to map one-to-one query results, modify the Order class, add the User attribute to the Order class, and use the user attribute to store the user information of the associated query. Because the order Association query user is a one-to-one relationship, a single User object is used to store the user information of the associated query.

@Data
public class Order {

    // Order id
    private int id;
    // user id
    private Integer userId;
    // Order number
    private String number;
    // Order creation time
    private Date createtime;
    // Remarks
    private String note;
    /**
     * Customer
    */
    private User user;
}

First define resultMap:

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
    <id property="id" column="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>
    <!-- association : Configure one-to-one attributes -->
    <!-- property:order Inside User Attribute name -->
    <!-- javaType:Attribute types -->
    <association property="user" javaType="user">
        <!-- id:Declare the primary key to indicate user_id Is the unique identifier of the associated query object-->
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
    </association>

</resultMap>

Add queries again

   <!-- One-to-one associations, queries orders, orders containing user attributes -->
 <select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
</select>

Interface:

List<Order> queryOrderUserResultMap();

Test:

@Test
public void queryOrderUserResultMap(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> list = orderMapper.queryOrderUserResultMap();
    for (Order u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

Result:

3. One-to-many

A customer will create multiple orders, when we query customer orders, it will produce a one-to-many situation, let's take this as an example.

First, we add an order list to the User class

@Data
public class User implements Serializable {

    //id
    private Integer id;
    //User name
    private String username;
    //Gender
    private String sex;
    //Birthday
    private Date birthday;
    //address
    private String address;
    //Order list
    private List<Order> orderList;
}

Then add resultMap:

<resultMap type="com.yuanqinnan.model.Order" id="orderUserResultMap">
    <id property="id" column="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>

    <!-- association : Configure one-to-one attributes -->
    <!-- property:order Inside User Attribute name -->
    <!-- javaType:Attribute types -->
    <association property="user" javaType="user">
        <!-- id:Declare the primary key to indicate user_id Is the unique identifier of the associated query object-->
        <id property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
    </association>
</resultMap>

Add queries:

   <!-- One-to-one associations, queries orders, orders containing user attributes -->
 <select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
    SELECT o.id,o.user_id,o.number,o.createtime,o.note,u.username,u.address
    FROM
    `order` o
    LEFT JOIN `user` u ON o.user_id = u.id
</select>

Test:

@Test
public void queryUserOrder(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> list = userMapper.queryUserOrder();
    for (User u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

Result:

4. Many-to-many

Many-to-many relationships require intermediate tables to maintain. For example, an order contains multiple products, and a product can appear in multiple orders. At this time, we need an order product table as the intermediate table. So we need to add two new tables, the product table and the order product table.

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT 'Product name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf;

-- ----------------------------
-- Records of product
-- ---------------------------
INSERT INTO product VALUES('1','Notebook computer');
INSERT INTO product VALUES('2','Mi phones');

CREATE TABLE `order_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `oid` int(11) DEFAULT NULL COMMENT 'Order id',
  `pid` int(11) DEFAULT NULL COMMENT 'product id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-- ----------------------------
-- Records of order_product
-- ---------------------------
INSERT INTO order_product VALUES('1','3','1');
INSERT INTO order_product VALUES('2','3','2');
INSERT INTO order_product VALUES('3','4','1');

The many-to-many queries we may encounter are: query all products of this order or query all orders of this product.

At this time, our orders and product lists should correspond to multiple intermediate tables, so entities should be designed as follows:

order entities add intermediate tables:

@Data
public class Order {

    // Order id
    private int id;
    // user id
    private Integer userId;
    // Order number
    private String number;
    // Order creation time
    private Date createtime;
    // Remarks
    private String note;
    //Customer
    private User user;
    //Intermediate table
    private List<OrderProduct> products;
}

OrderProduct:

@Data
public class OrderProduct {

    private Integer id;
    //Order key
    private Integer oid;
    //Product primary key
    private Integer pid;
    //Order
    private Order order;
    //product
    private Product product;
}

product:

@Data
public class Product {
    //product id
    private Integer id;
    //Product name
    private String name;
    //Intermediate table
    List<OrderProduct> orders;
}

resultmap:

<resultMap type="com.yuanqinnan.model.Order" id="orderBean">
    <id column="id" property="id"/>
    <result property="userId" column="user_id"/>
    <result property="number" column="number"/>
    <result property="createtime" column="createtime"/>
    <result property="note" column="note"/>

    <collection property="products" ofType="com.yuanqinnan.model.OrderProduct">
        <id column="oiid" property="id"/>
        <result column="oid" property="oid"/>
        <result column="pid" property="pid"/>
        <association property="product" javaType="com.yuanqinnan.model.Product">
            <id column="pid" property="id"/>
            <result column="pname" property="name"/>
        </association>
    </collection>
</resultMap>

New product information method for inquiring orders:

<select id="listOrder" resultMap="orderBean">
    SELECT
        o.*,
        o.id oid,
        p.id pid,
        oi.id oiid,
        p.NAME pname
    FROM
        `order` o
        LEFT JOIN order_product oi ON o.id = oi.oid
        LEFT JOIN product p ON p.id = oi.pid
</select>

Test:

@Test
public void listOrder(){
    SqlSession sqlSession = this.sqlSessionFactory.openSession();
    OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
    List<Order> list = orderMapper.listOrder();
    for (Order u : list) {
        System.out.println(u);
    }
    sqlSession.close();
}

Result:

Topics: Java Attribute SQL Mybatis xml

Programmer Think

mybatis Travel Chapter 6 - Association Query

I. Introduction

2. One-to-one

2.1. Use resultType

2.2. Use resultMap

3. One-to-many

4. Many-to-many

Hot Topics