MyBatis的关联映射(多对多查询）

public List<Orders> findOrdersWithProductByNestedResult(Integer id);

一：搭建mybatis框架

二：创建数据库

三：创建查询映射 与 代码补齐

        1：创建Orders实体

        2：创建mapper

        3：创建映射

        4：写测试类

        5：运行结果

四： 嵌套结果查询

二：创建数据库

USE mybatis;
# 创建一个名称为tb_product的表
CREATE TABLE tb_product (
  id INT(32) PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(32),
  price DOUBLE 
 );
# 插入3条数据
INSERT INTO tb_product VALUES ('1', 'Java基础入门', '44.5');
INSERT INTO tb_product VALUES ('2', 'Java Web程序开发入门', '38.5');
INSERT INTO tb_product VALUES ('3', 'SSM框架整合实战', '50');
# 创建一个名称为tb_ordersitem 的中间表
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)
);
# 插入3条数据
INSERT INTO tb_ordersitem  VALUES ('1', '1', '1');
INSERT INTO tb_ordersitem  VALUES ('2', '1', '3');
INSERT INTO tb_ordersitem  VALUES ('3', '3', '3');
# 订单表tb_orders由于在上面已经创建,所以这里不用再次创建

三：创建查询映射 与 代码补齐

        1：创建Orders、product实体

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Orders {
    private Integer id;
    private String number;
    private Integer userId;
    private Users users;
    private List<Product> productList;

}

 product实体

package com.biem.pojo;

import lombok.*;

import java.util.List;

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Product {
    private Integer id;
    private String name;
    private Double price;
    private List<Orders> ordersList;
}

        2：创建mapper

public List<Orders> findOrdersWithProduct(Integer id);

public List<Product> findProductById(Integer id);

        3：创建映射（ordermapper、productmapper）

<select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
        select * from tb_orders where id = #{id}
    </select>
    <resultMap id="OrdersWithProductResult" type="Orders">
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <collection property="productList" column="id" ofType="Product" select="com.biem.mapper.ProductMapper.findProductById">
        </collection>
    </resultMap>

product mapper

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

        4：写测试类

package com.biem.test;

import com.biem.mapper.OrdersMapper;
import com.biem.pojo.Orders;
import com.biem.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;


public class OrdersTest {
    @Test
    public void testFindOrdersWithProduct(){
        SqlSession session = MybatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.findOrdersWithProduct(1);
        System.out.println("ordersList = " + ordersList);
    }

}

        5：运行结果

​

 四：嵌套结果查询

ordernapper.java添加：

public List<Orders> findOrdersWithProductByNestedResult(Integer id);

 ordermapper.xml添加：

    <!-- public List<Orders> findOrdersWithProductByNestedResult(Integer id); -->
    <select id="findOrdersWithProductByNestedResult" parameterType="Integer" resultMap="OrdersWithProductResult2">
        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>
    <resultMap id="OrdersWithProductResult2" type="Orders">
        <id property="id" column="id"></id>
        <result property="number" column="number"></result>
        <collection property="productList" ofType="product">
            <id property="id" column="pid"/>
            <result property="name" column="name"/>
            <result property="price" column="price"/>
        </collection>
    </resultMap>

测试类添加：

	@Test
    public void findOrdersWithProductByNestedResult(){
        SqlSession session = MyBatisUtil.openSession();
        OrdersMapper mapper = session.getMapper(OrdersMapper.class);
        List<Orders> ordersList = mapper.findOrdersWithProductByNestedResult(1);
        System.out.println("ordersList = " + ordersList);
    }

结果：