使用mybatis的一对多查询

使用mybatis的一对多查询

一.思路

在写代码的时候经常有这种需求,一个订单对应多个商品,需要对订单以及商品进行分页模糊搜索,
在首页展示多个满足要求的订单,并且同时展示出订单中的商品信息,
此时需要返回一个list,并且list中对象是一对多的关系,就是对1对多种的多进行分页.这个时候的思路.
我们以一为主表,多为副表进行分析
1.实现:使用mybatis的一对多,就是resultMap中的collections进行数据的接收.
2.思路:

1. 分页主要是多主表进行分页
2. 将主表(一)和副表(多)进行连表查询,组合成一个临时表.
3. 临时表中进行条件筛选,选出符合条件的条目.
4. 使用主表的id进行分组,找出满足条件的主表条目.
5. 使用mybatis中的collection使用id进行一对多查询.

代码演示

1.模拟表

主表(一)为场景表 t_scene.sql
副表(二)为数据表 t_data.sql
映射关系为 t_scene_data_mapper.sql 表 一个场景对应多个数据
其中code都是唯一的,不使用id.

========
t_data.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_data
-- ----------------------------
DROP TABLE IF EXISTS `t_data`;
CREATE TABLE `t_data`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `data_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_data
-- ----------------------------
INSERT INTO `t_data` VALUES (1, 'data0001', '111');
INSERT INTO `t_data` VALUES (2, 'data0002', '233');
INSERT INTO `t_data` VALUES (3, 'data0003', '34q53');
INSERT INTO `t_data` VALUES (4, 'data0004', 'cvzxgf');
INSERT INTO `t_data` VALUES (5, 'data0005', '42rg');

SET FOREIGN_KEY_CHECKS = 1;

==============
t_scene.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_scene
-- ----------------------------
DROP TABLE IF EXISTS `t_scene`;
CREATE TABLE `t_scene`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scene_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `scene_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_scene
-- ----------------------------
INSERT INTO `t_scene` VALUES (1, 'scene0001', 'adf');
INSERT INTO `t_scene` VALUES (2, 'scene0002', 'adf');
INSERT INTO `t_scene` VALUES (3, 'scene0003', 'er');
INSERT INTO `t_scene` VALUES (4, 'scene0004', '43');

SET FOREIGN_KEY_CHECKS = 1;

============
t_scene_data_mappe

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_scene_data_mapper
-- ----------------------------
DROP TABLE IF EXISTS `t_scene_data_mapper`;
CREATE TABLE `t_scene_data_mapper`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `scene_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `data_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_scene_data_mapper
-- ----------------------------
INSERT INTO `t_scene_data_mapper` VALUES (1, 'scene0001', 'data0001');
INSERT INTO `t_scene_data_mapper` VALUES (2, 'scene0001', 'data0002');
INSERT INTO `t_scene_data_mapper` VALUES (3, 'scene0001', 'data0003');
INSERT INTO `t_scene_data_mapper` VALUES (4, 'scene0002', 'data0003');
INSERT INTO `t_scene_data_mapper` VALUES (5, 'scene0002', 'data0005');
INSERT INTO `t_scene_data_mapper` VALUES (6, 'scene0003', 'data0004');
INSERT INTO `t_scene_data_mapper` VALUES (7, 'scene0003', 'data0005');
INSERT INTO `t_scene_data_mapper` VALUES (8, 'scene0004', 'data0005');

SET FOREIGN_KEY_CHECKS = 1;

2.实体类

@lombok.Data
public class Data {
    private int id;
    private String dataCode;
    private String dataName;
}

=======

@Data
public class Scene {
    private int id;
    private String sceneCode;
    private String sceneName;
}

=======

@Data
public class SceneDataMapper {
    private int id;
    private String sceneCode;
    private List<com.gty.domain.Data> dataList;
}

3.主要是mapper文件

<?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.gty.dao.SceneMapper">

    <!--这是场景对应的map-->
    <resultMap id="sceneMap" type="com.gty.domain.Scene">
        <id column="id" property="id"/>
        <result column="scene_code" property="sceneCode"/>
        <result column="scene_name" property="sceneName"/>
    </resultMap>

    <!--这是数据对应的map-->
    <resultMap id="dataMap" type="com.gty.domain.Data">
        <id column="id" property="id"/>
        <result column="data_code" property="dataCode"/>
        <result column="data_name" property="dataName"/>
    </resultMap>

    <!--这是映射关系对应的map-->
    <resultMap id="sceneDataMapperMap" type="com.gty.domain.SceneDataMapper">
        <id column="id" property="id"/>
        <result column="scene_code" property="sceneCode"/>
        <!--这里对应的是实体类中的list-->
        <!--select是指查询的方法-->
        <!--column是指传递的参数,可多个{,,}-->
        <collection property="dataList" ofType="com.gty.domain.Data"
                    select="selectDataList" column="{sceneCode=scene_code}" />
    </resultMap>
    
    <!--(数据)多的查询方法-->
    <select id="selectDataList" resultMap="dataMap" parameterType="hashmap">
        select td.* from t_data td
        left JOIN t_scene_data_mapper sdm on td.data_code = sdm.data_code
        where scene_code =  #{sceneCode}
    </select>

    <!--分页记录总数-->
    <select id="sceneDataMapperCount" resultType="int">
        select count(1) from (select count(*)
        from t_scene ts
        right JOIN t_scene_data_mapper sdm on ts.scene_code = sdm.scene_code
        left join t_data td on td.data_code = sdm.data_code
        where ts.scene_code like concat('%','scene00','%')
        and td.data_name like concat('%','3','%')
        group by ts.scene_code) t
    </select>

    <!--这是分页的主要方法-->
    <select id="sceneDataMapperList" resultMap="sceneDataMapperMap">
        select * from t_scene ts
        right JOIN t_scene_data_mapper sdm on ts.scene_code = sdm.scene_code
        left join t_data td on td.data_code = sdm.data_code
        where ts.scene_code like concat('%','scene00','%')
        and td.data_name like concat('%','3','%')
        group by ts.scene_code
    </select>

</mapper>

4.测试入口

    @RequestMapping("/page22")
    public String page22() {
        List<SceneDataMapper> sceneList =  sceneMapper.sceneDataMapperList();
        for (SceneDataMapper scene : sceneList) {
            System.out.println(scene);
        }
        return "555";
    }