mybatis多对多查询

1. 建立表

   -- ----------------------------
   -- Table structure for customer
   -- ----------------------------
   DROP TABLE IF EXISTS `customer`;
   CREATE TABLE `customer`  (
     `id` int(0) NOT NULL AUTO_INCREMENT,
     `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
     PRIMARY KEY (`id`) USING BTREE
   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
   
   -- ----------------------------
   -- Records of customer
   -- ----------------------------
   INSERT INTO `customer` VALUES (1, '张三');
   INSERT INTO `customer` VALUES (2, '李四');
   
   -- ----------------------------
   -- Table structure for customer_goods
   -- ----------------------------
   DROP TABLE IF EXISTS `customer_goods`;
   CREATE TABLE `customer_goods`  (
     `id` int(0) NOT NULL AUTO_INCREMENT,
     `cid` int(0) NULL DEFAULT NULL,
     `gid` int(0) NULL DEFAULT NULL,
     PRIMARY KEY (`id`) USING BTREE,
     INDEX `cid`(`cid`) USING BTREE,
     INDEX `gid`(`gid`) USING BTREE,
     CONSTRAINT `cid` FOREIGN KEY (`cid`) REFERENCES `customer` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
     CONSTRAINT `gid` FOREIGN KEY (`gid`) REFERENCES `goods` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
   
   -- ----------------------------
   -- Records of customer_goods
   -- ----------------------------
   INSERT INTO `customer_goods` VALUES (1, 1, 1);
   INSERT INTO `customer_goods` VALUES (2, 1, 2);
   INSERT INTO `customer_goods` VALUES (3, 1, 3);
   INSERT INTO `customer_goods` VALUES (4, 2, 2);
   INSERT INTO `customer_goods` VALUES (5, 2, 3);
   
   -- ----------------------------
   -- Table structure for goods
   -- ----------------------------
   DROP TABLE IF EXISTS `goods`;
   CREATE TABLE `goods`  (
     `id` int(0) NOT NULL AUTO_INCREMENT,
     `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
     PRIMARY KEY (`id`) USING BTREE
   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
   
   -- ----------------------------
   -- Records of goods
   -- ----------------------------
   INSERT INTO `goods` VALUES (1, '电脑');
   INSERT INTO `goods` VALUES (2, '手机');
   INSERT INTO `goods` VALUES (3, '电饭煲');
   

2. 建立Javabean

   package com.simon.app.entity;
   
   import lombok.Data;
   import java.util.List;
   
   @Data
   public class Customer {
       private long id;
       private String name;
       private List<Goods> goods;
   }
   
   

   package com.simon.app.entity;
   
   import lombok.Data;
   
   import java.util.List;
   
   @Data
   public class Goods {
       private long id;
       private String name;
       private List<Customer> customers;
   }
   
   

3. 假如需要查询Customer，并查询关联的商品，此时需要建立接口及CustomerRepository.xml

   package com.simon.app.repository;
   
   import com.simon.app.entity.Customer;
   
   public interface CustomerRepository {
       public Customer findById(long id);
   }
   
   

   <?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.simon.app.repository.CustomerRepository">
      <resultMap id="customerMap" type="com.simon.app.entity.Customer">
         <id column="cid" property="id"></id>
         <result column="cname" property="name"></result>
         <collection property="goods" ofType="com.simon.app.entity.Goods">
            <id column="gid" property="id"></id>
            <result column="gname" property="name"></result>
         </collection>
      </resultMap>
      <select id="findById" parameterType="long" resultMap="customerMap">
         select c.id cid,c.`name` cname,g.id gid,g.name gname from customer c,customer_goods cg,goods g where c.id=#{id} and c.id=cg.cid and g.id=cg.gid
      </select>
   </mapper>
   

   将该xml加入到config.xml中

4. 建立测试类

   package com.simon.app.test;
   
   import com.simon.app.entity.Customer;
   import com.simon.app.repository.CustomerRepository;
   import org.apache.ibatis.session.SqlSession;
   import org.apache.ibatis.session.SqlSessionFactory;
   import org.apache.ibatis.session.SqlSessionFactoryBuilder;
   
   import java.io.InputStream;
   
   public class Test6 {
       public static void main(String[] args) {
           InputStream resource = Test2.class.getClassLoader().getResourceAsStream("config.xml");
           SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
           SqlSessionFactory factory = sqlSessionFactoryBuilder.build(resource);
           SqlSession sqlSession = factory.openSession();
           //获取接口的代理对象
           CustomerRepository mapper = sqlSession.getMapper(CustomerRepository.class);
   
           Customer classes = mapper.findById(1L);
           System.out.println(classes);
           sqlSession.close();
       }
   }