【SSM - Mybatis篇08】多对对多关联查询

深藏阁楼爱情的钟 2022-12-11 01:29 131阅读 0赞

### 文章目录 ###

*   *  案例描述
     *   *  1. 创建orders、product表和中间表order\_product
         *  2. 创建javabean对象
         *  3. 创建dao层
         *  4. 创建连接数据库的属性文件db.properties(键值对形式)
         *  5. spring整合MyBatis，核心配置文件applicationContext.xml
         *  6. 创建映射文件mapper.xml
         *  7. 创建测试类
         *   *  运行结果

## 案例描述 ##

多对多关联，其实就是两个一对多相互关联实现。MyBatis并没有提供多对多关联的实现，还是用`resultMap`的`collection`实现。  
  订单和产品之间是多对多的关系，一个订单可以有多个产品，一个产品可以有多份订单，创建订单表orders、产品表product、和中间表order\_product。

### 1. 创建orders、product表和中间表order\_product ###

-- ----------------------------
    -- 创建订单表orders
    -- ----------------------------
    CREATE TABLE `orders` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `description` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    INSERT INTO `orders` VALUES ('1', '一季度采购订单');
    INSERT INTO `orders` VALUES ('2', '二季度采购订单');
    INSERT INTO `orders` VALUES ('3', '三季度采购订单');
    INSERT INTO `orders` VALUES ('4', '四季度采购订单');
    
    -- ----------------------------
    -- 创建产品表product
    -- ----------------------------
    CREATE TABLE `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
    
    INSERT INTO `product` VALUES ('1', '医用口罩');
    INSERT INTO `product` VALUES ('2', 'KN95口罩');
    INSERT INTO `product` VALUES ('3', '笔记本电脑');
    INSERT INTO `product` VALUES ('4', '数码相机');
    INSERT INTO `product` VALUES ('5', '冰箱');
    INSERT INTO `product` VALUES ('6', '空调');
    
    	-- ----------------------------
    -- 创建中间表 order_product，ordersId映射orders表的主键id，productId映射product表的主键id
    -- ----------------------------
    DROP TABLE IF EXISTS `orders_product`;
    CREATE TABLE `orders_product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `ordersId` int(11) DEFAULT NULL,
      `productId` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK_orders` (`ordersId`),
      KEY `FK_product` (`productId`),
      CONSTRAINT `FK_orders` FOREIGN KEY (`ordersId`) REFERENCES `orders` (`id`),
      CONSTRAINT `FK_product` FOREIGN KEY (`productId`) REFERENCES `product` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
    
    INSERT INTO `orders_product` VALUES ('1', '1', '1');
    INSERT INTO `orders_product` VALUES ('2', '1', '2');
    INSERT INTO `orders_product` VALUES ('3', '1', '3');
    INSERT INTO `orders_product` VALUES ('4', '2', '4');
    INSERT INTO `orders_product` VALUES ('5', '2', '3');
    INSERT INTO `orders_product` VALUES ('6', '1', '6');
    INSERT INTO `orders_product` VALUES ('7', '2', '5');

### 2. 创建javabean对象 ###

//orders表
    public class Order { 
        private Integer id;
        private String description;
        private List<Product> productList;
        //省略getter/setter方法
    }
    //product表
    public class Product { 
        private Integer id;
        private String name;
    }

### 3. 创建dao层 ###

@Repository
    public interface OrderMapper { 
    // 通过order订单的id查询订单，并输出该订单的所有产品product
        Order getOrderById(int i);
    }

### 4. 创建连接数据库的属性文件db.properties(键值对形式) ###

jdbc.driver = com.mysql.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8
    jdbc.username = root
    jdbc.password = 861221293

### 5. spring整合MyBatis，核心配置文件applicationContext.xml ###

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:mybatis="http://mybatis.org/schema/mybatis-spring" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
    
        
        <context:property-placeholder location="classpath*:db.properties" />
    
        
        <context:component-scan base-package="com.xgf.correlation.many_to_many"/>
    
        
        <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="username" value="${jdbc.username}"/>
            <property name="password" value="${jdbc.password}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="driverClassName" value="${jdbc.driver}"/>
        </bean>
    
        
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource" />
        </bean>
    
        
        <mybatis:scan base-package="com.xgf.correlation.many_to_many"/>
    
    </beans>

### 6. 创建映射文件mapper.xml ###

OrderMapper.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.xgf.correlation.many_to_many.dao.OrderMapper">
    
        <resultMap id="orderMap" type="com.xgf.correlation.many_to_many.bean.Order">
            <id column="oid" property="id"/>
            <result property="description" column="description"/>
            <collection property="productList" ofType="com.xgf.correlation.many_to_many.bean.Product">
                <id column="pid" property="id"/>
                <result property="name" column="name"/>
            </collection>
        </resultMap>
    
        <select id="getOrderById" parameterType="int" resultMap="orderMap">
               select o.id oid,o.description,p.id pid,p.name
                from orders o left outer join orders_product op on o.id = op.ordersId
                              left outer join product p on p.id = op.productId
                where o.id = #{id}
    
        </select>
        
    </mapper>

### 7. 创建测试类 ###

//测试类
    public class TestManyToMany { 
    
        private static ApplicationContext applicationContext = null;
        private static OrderMapper orderMapper = null;
    
        //静态代码块 只加载一次
        static { 
            //加载配置文件
            applicationContext = new ClassPathXmlApplicationContext("com/xgf/correlation/many_to_many/config/applicationContext.xml");
            //获取bean的两种方式
            // 1.类名首字母小写
    // studentMapper = (StudentMapper) applicationContext.getBean("orderMapper");
            // 2.类.class
            orderMapper = (OrderMapper) applicationContext.getBean(OrderMapper.class);
        }
    
        通过order订单的id查询订单，并输出该订单的所有产品product
        @Test
        public void test01(){ 
            System.out.println("===一条SQL语句查询 查找id为1的order和他的product列表：===");
            Order order = orderMapper.getOrderById(1);
            System.out.println(order);
        }
    }

#### 运行结果 ####

> Order\{id=1, description=‘一季度采购订单’, productList=\[Product\{id=1, name=‘医用口罩’\}, Product\{id=2, name=‘KN95口罩’\}, Product\{id=3, name=‘笔记本电脑’\}, Product\{id=6, name=‘空调’\}\]\}