MyBatis多表关联分页查询

林小木要努力

已于 2024-03-04 12:39:44 修改

阅读量888

点赞数 8

文章标签： mybatis java 开发语言

于 2024-03-04 12:37:13 首次发布

本文链接：https://blog.csdn.net/weixin_56106605/article/details/136446324

版权

纯纯使用 MyBatis 框架，不使用 MyBatis-Plus 和分页插件等。

1. 建表

1. 用户表 user

2. 订单表 order

3. 工作表 work

4. 角色表 role

5. 用户角色表 user_role

6. 薪资表 salary

上述6张表的建表语句及插入数据语句：

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `gender` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别，0：女 1：男',
  `wid` int(11) NULL DEFAULT NULL COMMENT '工作ID',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '犬小哈', 30, 1, 1);
INSERT INTO `user` VALUES (2, '关羽', 46, 1, 2);
INSERT INTO `user` VALUES (3, '诸葛亮', 26, 1, 4);
INSERT INTO `user` VALUES (4, '张三', 34, 0, 2);
INSERT INTO `user` VALUES (5, '李四', 21, 1, 3);
INSERT INTO `user` VALUES (6, '王五', 44, 0, 8);
INSERT INTO `user` VALUES (7, '赵六', 43, 0, 2);
INSERT INTO `user` VALUES (8, '陈七', 25, 1, 3);
INSERT INTO `user` VALUES (9, '钱途', 50, 1, 6);
INSERT INTO `user` VALUES (10, '肖八', 38, 0, 9);


-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `uid` int(11) NOT NULL COMMENT '用户id',
  `rid` int(11) NOT NULL COMMENT '角色id',
  PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色中间表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 2);
INSERT INTO `user_role` VALUES (2, 3);
INSERT INTO `user_role` VALUES (3, 3);
INSERT INTO `user_role` VALUES (4, 1);
INSERT INTO `user_role` VALUES (5, 2);
INSERT INTO `user_role` VALUES (6, 1);
INSERT INTO `user_role` VALUES (7, 2);
INSERT INTO `user_role` VALUES (8, 1);
INSERT INTO `user_role` VALUES (9, 3);
INSERT INTO `user_role` VALUES (10, 2);

-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_id` bigint(20) UNSIGNED NOT NULL COMMENT '订单ID',
  `user_id` bigint(20) UNSIGNED NOT NULL COMMENT '下单用户ID',
  `goods_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
  `goods_price` decimal(10, 2) NOT NULL COMMENT '商品价格',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_order_id`(`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 805646264648356, 1, 'Switch 游戏机', 1400.00);
INSERT INTO `order` VALUES (2, 551787441310504, 1, '小米手机', 2000.00);
INSERT INTO `order` VALUES (3, 938562101633493, 2, '《三国演义》', 66.00);
INSERT INTO `order` VALUES (4, 791129917310894, 3, '华为手机', 1200.00);
INSERT INTO `order` VALUES (5, 208722395587361, 3, '《西游记》', 56.00);

-- ----------------------------
-- Table structure for work
-- ----------------------------
DROP TABLE IF EXISTS `work`;
CREATE TABLE `work`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of work
-- ----------------------------
INSERT INTO `work` VALUES (1, '教师');
INSERT INTO `work` VALUES (2, '程序员');
INSERT INTO `work` VALUES (3, '作家');
INSERT INTO `work` VALUES (4, '编剧');
INSERT INTO `work` VALUES (5, '演员');
INSERT INTO `work` VALUES (6, '歌手');
INSERT INTO `work` VALUES (7, '咖啡师');
INSERT INTO `work` VALUES (8, '厨师');
INSERT INTO `work` VALUES (9, '外卖员');
INSERT INTO `work` VALUES (10, '快递员');

-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) NOT NULL COMMENT '角色ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '超级管理员');
INSERT INTO `role` VALUES (2, '管理员');
INSERT INTO `role` VALUES (3, '普通用户');


-- ----------------------------
-- Table structure for salary
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary`  (
  `uid` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪资'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '薪资表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES (1, 3000.00);
INSERT INTO `salary` VALUES (3, 5000.00);
INSERT INTO `salary` VALUES (2, 2000.00);
INSERT INTO `salary` VALUES (9, 1000.00);
INSERT INTO `salary` VALUES (10, 10000.00);
INSERT INTO `salary` VALUES (8, 4000.00);
INSERT INTO `salary` VALUES (7, 3500.00);
INSERT INTO `salary` VALUES (4, 5500.00);
INSERT INTO `salary` VALUES (5, 6000.00);
INSERT INTO `salary` VALUES (6, 90000.00);

model层、dao层、service层、controller层代码及mapper.xml代码略，可以使用一些代码生成工具生成基本的单表增删改查功能。

2.association 和 collection 标签介绍

association 用于一对一、多对一场景使用。
collection 用于一对多、多对多场景使用。
association 标签用于关联对象。
collection 标签用于关联集合。

3.mybatis多表关联查询

3.1 一对一

user -> salary

每个人的薪资都不一样，一个人对应一个薪资。（只是个学习demo，现实当然可能多人对应一个薪资。）

1. 修改UserMapper.xml，添加 association 标签。

<resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="gender" property="gender" jdbcType="TINYINT" />
        <result column="wid" property="wid" jdbcType="INTEGER" />
        <association property="salary" javaType="float"
                     column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
    </resultMap>

association 标签中的 property 名称可自定义，对应 resultMap 标签中的类（即 type 值）中的自定义属性，两者名称要相同。column 为 user 表中的某个属性，用于关联 user 表和 salary 表，javaType 为该 select 的返回结果类型。 select 为该 association 的查询语句。

2. 修改User.java，添加 salary 属性

public class User implements Serializable {
    @ApiModelProperty("主键ID")
    private Long id;

    @ApiModelProperty("姓名")
    private String name;

    @ApiModelProperty("年龄")
    private Integer age;

    @ApiModelProperty("性别，0：女 1：男")
    private Byte gender;

    @ApiModelProperty("工作ID")
    private Integer wid;


    private Float salary;


    private static final long serialVersionUID = 1L;
}

3. 修改SalaryMapper.xml，添加 getSalaryById 的查询语句

    <select id="getSalaryById" resultType="float" parameterType="long">
        select salary from salary
        where uid = #{uid,jdbcType=BIGINT}
    </select>

dao层、service层、controller层添加该方法的代码省略。

4. 修改UserMapper.xml，使用 limit 实现分页

    <sql id="Pagination" >
        <if test="page != null and page.start !=null" >
            LIMIT #{page.start,jdbcType=INTEGER},#{page.pageSize,jdbcType=INTEGER}
        </if>
    </sql>

    <select id="selectList" resultMap="BaseResultMap" parameterType="Map" >
        select t.* from user t 
        <include refid="Pagination" />
    </select>

dao层、service层、controller层实现selectList的代码省略。

3.2 多对一

与 3.1相似，没多少区别。

user -> work

一个人只能有一份工作，一个工作可以有多人来做。（也不太严谨，一个人也可以有很多副业，学习demo别太认真x）

UserMapper.xml 添加 association 标签，关联 work

    <resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="gender" property="gender" jdbcType="TINYINT" />
        <result column="wid" property="wid" jdbcType="INTEGER" />
        <association property="salary" javaType="float"
                     column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
        <association property="work" javaType="string"
                     column="wid" select="cn.study.demo.dao.WorkMapper.getWorkNameById" />
    </resultMap>

在 User.java 中添加 work 属性

 private String work;

修改WorkMapper.xml

    <select id="getWorkNameById" resultType="string" parameterType="integer">
        select name from work where id = #{id}
    </select>

其他步骤及代码省略。

3.3 一对多

user -> order

一个用户拥有多个订单。

修改UserMapper.xml，添加 collection 标签，因为返回的是多个订单的集合

    <resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="age" property="age" jdbcType="INTEGER" />
        <result column="gender" property="gender" jdbcType="TINYINT" />
        <result column="wid" property="wid" jdbcType="INTEGER" />
        <association property="salary" javaType="float"
                     column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
        <association property="work" javaType="string"
                     column="wid" select="cn.study.demo.dao.WorkMapper.getWorkNameById" />
        <collection property="orderList" javaType="java.util.ArrayList"
                    column="id" ofType="cn.study.demo.model.Order"
                    select="cn.study.demo.dao.OrderMapper.getOrderByUserId" />
    </resultMap>

修改User类，添加 orderList 属性

private List<Order> orderList;

修改OrderMapper.xml，添加getOrderByUserId

    <select id="getOrderByUserId" resultMap="BaseResultMap" parameterType="long">
        select * from `order`
        where user_id = #{userId,jdbcType=BIGINT}
    </select>

其他代码略。

3.4 多对多

user->user_role->role

一个用户对应多个角色，一个角色对应多个用户。

修改RoleMapper.xml，添加 collection 标签

    <resultMap id="BaseResultMap" type="cn.study.demo.model.Role" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <collection property="userList" ofType="cn.study.demo.model.UserDTO">
            <id column="user_id" property="id" jdbcType="BIGINT" />
            <result column="uname" property="name" jdbcType="VARCHAR" />
            <result column="age" property="age" jdbcType="INTEGER" />
            <result column="gender" property="gender" jdbcType="TINYINT" />
        </collection>
    </resultMap>

其中，ofType为该collection的类型，collection 标签中的内容对应 UserDTO类的属性。

UserDTO类代码如下，省略了一些User类的属性

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserDTO implements Serializable {
    @ApiModelProperty("主键ID")
    private Long id;

    @ApiModelProperty("姓名")
    private String name;

    @ApiModelProperty("年龄")
    private Integer age;

    @ApiModelProperty("性别，0：女 1：男")
    private Byte gender;

    private static final long serialVersionUID = 1L;
}

修改RoleMapper.xml，查询代码如下

    <select id="selectUserRole" resultMap="BaseResultMap" parameterType="Map">
        select t1.id,
               t1.name,
               t3.id as user_id,
               t3.name as uname,
               t3.age,
               t3.gender
        from role t1
        left outer join user_role t2 on t1.id=t2.rid
        left outer join user t3 on t2.uid = t3.id
        <include refid="Pagination" />
    </select>

    <sql id="Pagination" >
        <if test="page != null and page.start !=null" >
            LIMIT #{page.start,jdbcType=INTEGER},#{page.pageSize,jdbcType=INTEGER}
        </if>
    </sql>

其他代码均省略。

该sql的查询结果如下

接口查询结果如下

分页条件为空时，查询所有，结果如下

{
    "isError": false,
    "data": [
        {
            "id": 2,
            "name": "管理员",
            "userList": [
                {
                    "id": 1,
                    "name": "犬小哈",
                    "age": 30,
                    "gender": 1
                },
                {
                    "id": 5,
                    "name": "李四",
                    "age": 21,
                    "gender": 1
                },
                {
                    "id": 7,
                    "name": "赵六",
                    "age": 43,
                    "gender": 0
                },
                {
                    "id": 10,
                    "name": "肖八",
                    "age": 38,
                    "gender": 0
                }
            ]
        },
        {
            "id": 3,
            "name": "普通用户",
            "userList": [
                {
                    "id": 2,
                    "name": "关羽",
                    "age": 46,
                    "gender": 1
                },
                {
                    "id": 3,
                    "name": "诸葛亮",
                    "age": 26,
                    "gender": 1
                },
                {
                    "id": 9,
                    "name": "钱途",
                    "age": 50,
                    "gender": 1
                }
            ]
        },
        {
            "id": 1,
            "name": "超级管理员",
            "userList": [
                {
                    "id": 4,
                    "name": "张三",
                    "age": 34,
                    "gender": 0
                },
                {
                    "id": 6,
                    "name": "王五",
                    "age": 44,
                    "gender": 0
                },
                {
                    "id": 8,
                    "name": "陈七",
                    "age": 25,
                    "gender": 1
                }
            ]
        }
    ]
}