Running results: Several simple SQL statements were executed

Nested results:

<!-- Nested results: Using nested result maps to handle a subset of duplicated joint results -->
    <select id="findPersonById2" parameterType="Integer" 
                                       resultMap="IdCardWithPersonResult2">
        SELECT p.*,idcard.code
        from tb_person p,tb_idcard idcard
        where p.card_id=idcard.id 
        and p.id= #{id}
    </select>
    
    <resultMap type="Person" id="IdCardWithPersonResult2">
        <id property="id" column="id" /><!-- Declare the primary key, id Is the unique identifier of the associated query object -->
        <result property="name" column="name" />
        <result property="age" column="age" />
        <result property="sex" column="sex" />
        <association property="card" javaType="IdCard">
            <id property="id" column="card_id" />
            <result property="code" column="code" />
        </association>
    </resultMap>

    /**
     * Nested results
     */
    @Test
    public void findPersonByIdTest2() {
        // 1,Generating by Tool Class SqlSession object
        SqlSession session = MybatisUtils.getSession();
        // 2.Use MyBatis Method Query of Nested Results id Information for one person
        Person person = session.selectOne("com.itheima.mapper." 
                                   + "PersonMapper.findPersonById2", 1);
        // 3,Output query result information
        System.out.println(person);
        // 4,Close SqlSession
        session.close();
    }

Test results: Only one complex SQL statement was executed.

DEBUG [main] - ==> Preparing: SELECT p.*,idcard.code from tb_person p,tb_idcard idcard where p.card_id=idcard.id and p.id= ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
Person [id=1, name=Rose, age=29, sex = female, card=IdCard [id=1, code=152221198711020624]]

select p.*,idcard.code from tb_person p,tb_idcard idcard where p.card_id=idcard.id and p.id = #{id}

Here p is an alias for tb_person, because the alias is easy to write. The second tb_person is followed by a space and p is the format. If you don't use an alias, it is:

select tb_person.*,tb_idcard.code from tb_person,tb_idcard where tb_person.card_id=tb_idcard.id and tb_person.id = #{id}

One-to-one relationship configuration template:

9.3 One-to-many

The < resultMap > element contains a sub-element < Collection > element with the same attributes as the < Association > element, but with a special attribute ofType, which corresponds to javaType attributes and is used to specify the type of elements contained in the set class attributes of entity objects.

Use templates for <collection> elements:

1. Build tables tb_user and tb_orders in MySQL and insert several pieces of data

USE mybatis;

CREATE TABLE tb_user(
    id INT(32) PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32),
    address VARCHAR(256)
);
INSERT INTO tb_user VALUES('1','James','Cleveland');
INSERT INTO tb_user VALUES('2','Kobe','Los Angeles');
INSERT INTO tb_user VALUES('3','Paul','Los Angeles');

USER mybatis;
CREATE TABLE tb_orders(
    id INT(32) PRIMARY KEY AUTO_INCREMENT,
    number VARCHAR(32) NOT NULL,
    user_id INT(32) NOT NULL,
    FOREIGN KEY(user_id) REFERENCES tb_user(id)
);
INSERT INTO tb_orders VALUES('1','1000011','1');
INSERT INTO tb_orders VALUES('2','1000012','1');
INSERT INTO tb_orders VALUES('3','1000013','2');

Result:

2. Create persistent classes Orders and User

package com.itheima.po;
import java.util.List;
/**
 * User persistence class
 */
public class User {
    private Integer id;                 // User Number
    private String username;           // User Name
    private String address;            // User Address
    private List<Orders> ordersList; //User-related orders...................................
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public List<Orders> getOrdersList() {
        return ordersList;
    }
    public void setOrdersList(List<Orders> ordersList) {
        this.ordersList = ordersList;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", address="
                + address + ", ordersList=" + ordersList + "]";
    }
}

package com.itheima.po;

import java.util.List;

/**
 * Order persistence class
 */
public class Orders {
    private Integer id;    //Order id
    private String number;//Order Number
    //Relevant Merchandise Collection Information
    private List<Product> productList;

    
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number;
    }
//    @Override
//    public String toString() {
//        return "Orders [id=" + id + ", number=" + number + "]";
//    }
    public List<Product> getProductList() {
        return productList;
    }
    public void setProductList(List<Product> productList) {
        this.productList = productList;
    }
    @Override
    public String toString() {
        return "Orders [id=" + id + ", number=" + number + ", productList=" + productList + "]";
    }
    
}

3. Create the mapping file UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
<!-- namespace Represents a namespace -->
<mapper namespace="com.itheima.mapper.UserMapper">

    <!-- One-to-many: View a user and its associated order information 
          //Note: When the column names of the associated queries are the same, aliases are needed to distinguish them. -->   
    <select id="findUserWithOrders123" parameterType="Integer" 
                           resultMap="UserWithOrdersResult123">
        SELECT u.*,o.id as orders_id,o.number 
        from tb_user u,tb_orders o 
        WHERE u.id=o.user_id 
         and u.id=#{id}
    </select>
    
    <resultMap type="User" id="UserWithOrdersResult123">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="address" column="address"/>
        
        <!-- One-to-many Association mapping: collection 
            ofType Represents the type of element in the set of attributes. List<Orders>Attributes are Orders class -->
        <collection property="ordersList" ofType="Orders">
            <id property="id" column="orders_id"/>
            <result property="number" column="number"/>
        </collection>
        
    </resultMap>
</mapper>

4. Test methods:

/**
     * One-to-many    
     */
    @Test
    public void findUserTest() {
        // 1,Generating by Tool Class SqlSession object
        SqlSession session = MybatisUtils.getSession();
        // 2,query id User information for 1
        User user = session.selectOne("com.itheima.mapper."
                                + "UserMapper.findUserWithOrders123", 1);
        // 3,Output query result information
        System.out.println(user);
        // 4,Close SqlSession
        session.close();
    }

5. Query results:

DEBUG [main] - ==> Preparing: SELECT u.*,o.id as orders_id,o.number from tb_user u,tb_orders o WHERE u.id=o.user_id and u.id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 2
User [id=1, username = James, address = Cleveland, ordersList=[Orders [id=1, number=1000011, productList=null], Orders [id=2, number=1000012, productList=null]]

9.4 many-to-many

Taking commodities and orders as an example, an order can contain more than one commodity. A commodity can belong to more than one order. Many-to-many links in the database are usually maintained by an intermediate table. The order id in the intermediate table is used as the id of the foreign key reference order table, and the commodity id is used as the id of the foreign key reference commodity table.

(Foreign key: An attribute of this table is the primary key of another table, which can be repeated, multiple or null)

1. Tables: tb_product and tb_ordersitem

USE mybatis;

CREATE TABLE tb_product(
    id INT(32) PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(32),
    price DOUBLE
);
INSERT INTO tb_product VALUES('1','Java Basic Introduction','44.5');
INSERT INTO tb_product VALUES('2','Java Web Introduction to Programming','38.5');
INSERT INTO tb_product VALUES('3','SSM Framework Integration and Practical Operations','50');

CREATE TABLE tb_ordersitem(
    id INT(32) PRIMARY KEY AUTO_INCREMENT,
    orders_id INT(32),
    product_id INT(32),
    FOREIGN KEY(orders_id) REFERENCES tb_orders(id),
    FOREIGN KEY(product_id) REFERENCES tb_product(id)
);
INSERT INTO tb_ordersitem VALUES('1','1','1');
INSERT INTO tb_ordersitem VALUES('2','1','3');
INSERT INTO tb_ordersitem VALUES('3','3','3');

After the establishment of the table:

2. Create a persistent class Product to represent the commodity, and order with the previous Order

package com.itheima.po;
import java.util.List;
/**
 * Commodity persistence class
 */
public class Product {
    private Integer id;  //commodity id
    private String name; //Name of commodity
    private Double price;//item pricing
    private List<Orders> orders; //Attributes associated with orders...........................
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Double getPrice() {
        return price;
    }
    public void setPrice(Double price) {
        this.price = price;
    }
    public List<Orders> getOrders() {
        return orders;
    }
    public void setOrders(List<Orders> orders) {
        this.orders = orders;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name 
                           + ", price=" + price + "]";
    }
}

3. Create the order entity mapping file OrdersMapper.xml and Product Mapper. XML

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.OrdersMapper">

    <!-- Multiple-to-multiple nested queries: by executing another SQL Mapping statement to return the expected special type
    //A select statement with id findOrders WithPorduct is defined to query orders and their associated commodity information.-->
    <select id="findOrdersWithPorduct" parameterType="Integer" 
              resultMap="OrdersWithProductResult">
        select * from tb_orders WHERE id=#{id}    
    </select>
    
    <resultMap type="Orders" id="OrdersWithProductResult">
        <id property="id" column="id" />
        <result property="number" column="number" />
        
        <!-- property Property represents the commodity attribute in the order persistence class. ofType Attributes denote that the data in the collection is Product Type, and column Of
        //Property values are used as parameters to execute the execution statement defined in Product Mapper with id findProduct ById to query the commodity information in the order.-->
        <collection property="productList" column="id" ofType="Product" 
             select="com.itheima.mapper.ProductMapper.findProductById">
        </collection>
        
    </resultMap>
    
    
    
    <!-- Multiple-to-multiple nested results query: query the details of an order and its associated goods -->
    <select id="findOrdersWithPorduct2" parameterType="Integer" 
             resultMap="OrdersWithPorductResult2">
        select o.*,p.id as pid,p.name,p.price
        from tb_orders o,tb_product p,tb_ordersitem  oi
        WHERE oi.orders_id=o.id 
        and oi.product_id=p.id 
        and o.id=#{id}
    </select>
    
    <!-- Customizing Manual Mapping Types -->
    <resultMap type="Orders" id="OrdersWithPorductResult2">
        <id property="id" column="id" />
        <result property="number" column="number" />
        <!-- Many-to-many Association mapping: collection -->
        <collection property="productList" ofType="Product">
            <id property="id" column="pid" />
            <result property="name" column="name" />
            <result property="price" column="price" />
        </collection>
    </resultMap>
    
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.itheima.mapper.ProductMapper">
    <select id="findProductById" parameterType="Integer" 
                                       resultType="Product">
        SELECT * from tb_product where id IN(
           SELECT product_id FROM tb_ordersitem  WHERE orders_id = #{id}
        )
    </select>
</mapper>

4. Test methods:

    /**
     * Many-to-many
     */
    @Test
    public void findOrdersTest(){
        // 1,Generating by Tool Class SqlSession object
        SqlSession session = MybatisUtils.getSession();
        // 2,query id Commodity information in order 1
        Orders orders = session.selectOne("com.itheima.mapper."
                               + "OrdersMapper.findOrdersWithPorduct", 1);
        // 3,Output query result information
        System.out.println(orders);
        // 4,Close SqlSession
        session.close();
    }

5. Test results:

DEBUG [main] - ==> Preparing: select * from tb_orders WHERE id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
DEBUG [main] - ==> Preparing: SELECT * from tb_product where id IN( SELECT product_id FROM tb_ordersitem WHERE orders_id = ? )
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 2
Orders [id=1, number=1000011, productList=[Product [id=1, name=Java basic introduction, price=44.5], Product [id=3, name=SSM framework integration, price=50.0]]

Topics: PHP Mybatis xml Session Java