MyBatis(7) MyBatis复杂映射开发

MyBatis 复杂映射开发

一对一查询

比如：用户表和订单表 ，它们的关系为：
一个用户有多个订单，一个订单只属于一个用户。
一对一查询的需求：查询一个订单，与此同时查出该订单所属的用户信息

对应的sql语句为：

select * from `order`,user where order.uid=user.id

• 创建表：

  CREATE TABLE `order`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `order_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `total` double NULL DEFAULT NULL,
    `uid` int(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `uid`(`uid`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of order
  -- ----------------------------
  INSERT INTO `order` VALUES (1, '2019-12-12', 3000, 1);
  INSERT INTO `order` VALUES (2, '2019-12-12', 4000, 1);
  INSERT INTO `order` VALUES (3, '2019-12-12', 5000, 2);
  

  CREATE TABLE `user`  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of user
  -- ----------------------------
  INSERT INTO `user` VALUES (1, '张三');
  INSERT INTO `user` VALUES (2, 'coco');
  INSERT INTO `user` VALUES (3, 'kk');
  INSERT INTO `user` VALUES (4, 'ccham');
  INSERT INTO `user` VALUES (5, 'zz');
  

• 对应实体类：

  public class Order {
  
      private Integer id;
      private String orderTime;
      private Double total;
      private Integer uid;
      
  	// 用来存储对应的用户信息
      private User user;
      
      // 忽略getter/setter
  }
  

  public class User {
  
      private Integer id;
      private String username;
      // 忽略getter/setter
  }
  

• 对应的测试类：

  public class MultiTableTest {
  
      /**
       * 加载核心配置文件
       */
      private final InputStream resourceAsStream;
  
      /**
       * 获得sqlSession工厂对象
       */
      private final SqlSessionFactory sqlSessionFactory;
  
      /**
       * 获得sqlSession 对象
       */
      private final SqlSession sqlSession;
  
      public MultiTableTest() throws IOException {
          // 1.Resources工具类，配置文件的加载，把配置文件加载成字节输入流
          resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
          // 2.解析了配置文件，并创建了sqlSessionFactory
          sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
          // 3.生成sqlSession （默认开启一个事务，但是该事务不会自动提交， 参数为true为自动提交）
          sqlSession = sqlSessionFactory.openSession();
      }
  
      /**
       * 一对一查询
       */
      @Test
      public void oneOnOne(){
          OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
          List<Order> orders = mapper.findAll();
          for (Order order : orders) {
              System.out.println(order);
          }
      }
  }
  

• 对应的mapper接口

  public interface OrderMapper {
  
      public List<Order> findAll();
  }
  

• 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">
  <!-- namespace:命名空间，与id组成sql的唯一标识-->
  <mapper namespace="com.demo.mapper.OrderMapper">
  
      <!-- 手动配置实体属性与表字段的映射关系 -->
      <resultMap id="orderAndUser" type="com.demo.pojo.Order">
          <result property="id" column="id"/>
          <result property="orderTime" column="order_time"/>
          <result property="total" column="total"/>
          <association property="user" javaType="com.demo.pojo.User">
              <result property="id" column="uid"/>
              <result property="username" column="username"/>
          </association>
      </resultMap>
  
      <select id="findAll" resultMap="orderAndUser">
          select * from `order`,user where `order`.uid=user.id
      </select>
  
  </mapper>
  

  • 测试结果：
    成功的查询一个订单，与此同时查出该订单所属的用户信息

  Order{id=1, orderTime='2019-12-12', total=3000.0, uid=null, user=User{id=1, username='张三'}}
  Order{id=2, orderTime='2019-12-12', total=4000.0, uid=null, user=User{id=1, username='张三'}}
  Order{id=3, orderTime='2019-12-12', total=5000.0, uid=null, user=User{id=2, username='coco'}}
  

一对多

一对多查询的需求：查询用户，与此同时查出该用户所有的订单信息

对应的sql语句为：

select * from user u left join `order` o on u.id=o.uid

• 对应实体类：

  public class User {
  
  private Integer id;
  
  private String username;
  
  private List<Order> orderList = new ArrayList<>();
  
  }
  

• 对应的mapper接口

  public interface UserMapper {
  
  /**
   * 查询所有用户的所有订单
   */
  public List<User> findUserOrders();
  }
  

• mapper.xml

  <resultMap id="userMap" type="com.demo.pojo.User">
     <result property="id" column="uid"/>
     <result property="username" column="username"/>
     <collection property="orderList" ofType="com.demo.pojo.Order">
         <result property="id" column="id(1)"/>
         <result property="orderTime" column="order_time"/>
         <result property="total" column="total"/>
     </collection>
  </resultMap>
  
  <select id="findUserOrders" resultMap="userMap">
      select * from user u left join `order` o on u.id=o.uid
  </select>
  

• 测试类：

  /**
   * 一对多
   */
  @Test
  public void oneToMany(){
      UserMapper mapper = sqlSession.getMapper(UserMapper.class);
      List<User> userOrders = mapper.findUserOrders();
      for (User userOrder : userOrders) {
          System.out.println(userOrder);
      }
  }
  

• 测试结果：
  成功的查询出用户及其名下的所有订单信息息

  User{id=1, username='张三', orderList=[Order{id=null, orderTime='2019-12-12', total=3000.0, uid=null, user=null}, Order{id=null, orderTime='2019-12-12', total=4000.0, uid=null, user=null}]}
  User{id=2, username='coco', orderList=[Order{id=null, orderTime='2019-12-12', total=5000.0, uid=null, user=null}]}
  User{id=3, username='kk', orderList=[]}
  User{id=4, username='ccham', orderList=[]}
  User{id=5, username='zz', orderList=[]}
  

多对多

多对多其实和一对多查询是一样的，所以参考上面一对多的代码就好了