MyBatis一对多关联查询

MyBatis一对多关联查询

一个用户可以有多个订单，而一个订单只属于一个用户。同样，国家和城市也属于一对多级联关系。

在 MyBatis 中，通过 元素的子元素 处理一对多级联关系，collection 可以将关联查询的多条记录映射到一个 list 集合属性中。示例代码如下。

<collection property="orderList" ofType="cn.edu.guet.bean.Order" column="id" select="cn.edu.guet.mapper.OrderMapper.selectOrderById"/>

在 元素中通常使用以下属性。

• property：指定映射到实体类的对象属性。
• column：指定表中对应的字段（即查询返回的列名）。
• javaType：指定映射到实体对象属性的类型。
• select：指定引入嵌套查询的子 SQL 语句，该属性用于关联映射中的嵌套查询。

一对多关联查询可采用以下两种方式：

• 分步查询，通过两次或多次查询，为一对多关系的实体 Bean 赋值
• 单步查询，通过关联查询实现

1、示例

创建Order表

CREATE TABLE `order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `orderno` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userId` (`user_id`),
  CONSTRAINT `order_user_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `order` (`id`, `orderno`, `user_id`) VALUES (1, 2022060501, 5);
INSERT INTO `order` (`id`, `orderno`, `user_id`) VALUES (2, 2022060502, 6);

创建User表

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `role_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE,
  KEY `role_fk` (`role_id`),
  CONSTRAINT `role_fk` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `user` (`user_id`, `user_name`, `role_id`) VALUES (5, 'liwei', '67426c770519471384fa87aa0fabbc45');
INSERT INTO `user` (`user_id`, `user_name`, `role_id`) VALUES (6, 'zhangsan', '26f627db863442a9966c69a9e69712a2');

创建Order.java

public class Order {
    private int id;
    private int orderNo;
}

创建User.java

public class User {
    private Integer id;
    private String name;
    private List<Order> orderList;
}

2、分步查询

新增OrderMapper.java

public List<Order> selectOrderById(int id);

新增OrderMapper.xml

<!-- 根据userid查询订单信息 -->
<select id="selectOrderByUserId" resultType="order" parameterType="int">
  SELECT * FROM order where user_id=#{id}
</select>

UserMapper增加如下代码

User selectUserOrderById(int id);

UserMapper.xml

<select id="getUserById" resultMap="userOrderMap" parameterType="int">
  SELECT *
  FROM user
  WHERE user_id = #{id}
</select>
<resultMap id="userOrderMap" type="user">
  <id property="id" column="user_id"></id>
  <result property="name" column="user_name"></result>
  <collection property="orderList" select="cn.edu.guet.mapper.OrderMapper.selectOrderByUserId" column="user_id"/>
</resultMap>

测试代码

UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=userMapper.getUserById(5);
System.out.println(user);

运行结果：

18:33:30 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserById - ==>  Preparing: SELECT * FROM user WHERE user_id = ?
18:33:30 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserById - ==> Parameters: 5(Integer)
18:33:30 [main] TRACE cn.edu.guet.mapper.UserMapper.getUserById - <==    Columns: user_id, user_name, role_id
18:33:30 [main] TRACE cn.edu.guet.mapper.UserMapper.getUserById - <==        Row: 5, liwei, 67426c770519471384fa87aa0fabbc45
18:33:30 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserById - <==      Total: 1
18:33:30 [main] DEBUG cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - ==>  Preparing: SELECT * FROM orders WHERE user_id = ?
18:33:30 [main] DEBUG cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - ==> Parameters: 5(Integer)
18:33:30 [main] TRACE cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - <==    Columns: id, orderno, user_id
18:33:30 [main] TRACE cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - <==        Row: 1, 2022060501, 5
18:33:30 [main] TRACE cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - <==        Row: 3, 2022060503, 5
18:33:30 [main] DEBUG cn.edu.guet.mapper.OrderMapper.selectOrderByUserId - <==      Total: 2
User{id=5, name='liwei', role=null, orderList=[Order{id=1, orderNo=2022060501}, Order{id=3, orderNo=2022060503}]}

3、单步查询

UserMapper 类代码如下

User getUserByIdSingle(int id);

UserMapper.xml 中相关映射 SQL 语句如下。

<resultMap id="userOrderMap" type="user">
  <id property="id" column="user_id"></id>
  <result property="name" column="user_name"></result>
  <collection property="orderList" column="user_id" ofType="order">
    <id property="id" column="id"></id>
    <result property="orderNo" column="orderno"></result>
  </collection>
</resultMap>
<select id="getUserByIdSingle" resultMap="userOrderMap" parameterType="int">
  SELECT *
  FROM user u,orders o
  WHERE u.user_id=o.user_id AND u.user_id = #{id}
</select>

测试代码如下

UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
User user=userMapper.getUserByIdSingle(5);
System.out.println(user);

运行结果如下

18:42:05 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserByIdSingle - ==>  Preparing: SELECT * FROM user u,orders o WHERE u.user_id=o.user_id AND u.user_id = ?
18:42:05 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserByIdSingle - ==> Parameters: 5(Integer)
18:42:05 [main] TRACE cn.edu.guet.mapper.UserMapper.getUserByIdSingle - <==    Columns: user_id, user_name, role_id, id, orderno, user_id
18:42:05 [main] TRACE cn.edu.guet.mapper.UserMapper.getUserByIdSingle - <==        Row: 5, liwei, 67426c770519471384fa87aa0fabbc45, 1, 2022060501, 5
18:42:05 [main] TRACE cn.edu.guet.mapper.UserMapper.getUserByIdSingle - <==        Row: 5, liwei, 67426c770519471384fa87aa0fabbc45, 3, 2022060503, 5
18:42:05 [main] DEBUG cn.edu.guet.mapper.UserMapper.getUserByIdSingle - <==      Total: 2
User{id=5, name='liwei', role=null, orderList=[Order{id=1, orderNo=2022060501}, Order{id=3, orderNo=2022060503}]}