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

一：搭建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：运行结果

DEBUG 05-22 01:00:11,683 ==>  Preparing: select * from tb_orders where id = ?  (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:00:11,708 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:00:11,730 ====>  Preparing: select * from tb_product where id in( select product_id from tb_ordersitem where orders_id = ? )  (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:00:11,730 ====> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:00:11,732 <====      Total: 2 (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:00:11,733 <==      Total: 1 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=1, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]

四：嵌套结果查询

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);
    }

结果：

DEBUG 05-22 01:06:27,738 ==>  Preparing: 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=?  (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:06:27,763 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:159) 
DEBUG 05-22 01:06:27,781 <==      Total: 2 (BaseJdbcLogger.java:159) 
ordersList = [Orders(id=1, number=1000011, userId=null, users=null, productList=[Product(id=1, name=Java基础入门, price=44.5, ordersList=null), Product(id=3, name=SSM框架整合实战, price=50.0, ordersList=null)])]