🎡Mybatis多表查询（1:1、1:N、N:N），MP多表查询（自定义SQL）

 

Mybatis多表关联查询

Gitee地址：https://gitee.com/zhang-zhixi/mybatis-tables-query.git

数据表：oracle

复制代码
CREATE TABLE "T_ORDER" (
  "ID" NUMBER NOT NULL,
  "F_ORDER_TIME" DATE,
  "F_TOTAL" VARCHAR2(255 BYTE),
  "F_USER_ID" NUMBER
);
COMMENT ON COLUMN "ZHANGZHIXI"."T_ORDER"."F_ORDER_TIME" IS '下单时间';
COMMENT ON COLUMN "ZHANGZHIXI"."T_ORDER"."F_TOTAL" IS '下单金额';
COMMENT ON COLUMN "ZHANGZHIXI"."T_ORDER"."F_USER_ID" IS '下单用户';
COMMENT ON TABLE "ZHANGZHIXI"."T_ORDER" IS '用户订单表';

INSERT INTO "T_ORDER" VALUES ('2', TO_DATE('2023-09-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '2600', '1');
INSERT INTO "T_ORDER" VALUES ('1', TO_DATE('2023-09-18 09:50:23', 'SYYYY-MM-DD HH24:MI:SS'), '3500', '1');
INSERT INTO "T_ORDER" VALUES ('3', TO_DATE('2023-08-31 12:47:43', 'SYYYY-MM-DD HH24:MI:SS'), '2800', '2');

-- ------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "T_ROLE" (
  "ID" NUMBER NOT NULL,
  "F_ROLE_NAME" VARCHAR2(255 BYTE)
);
COMMENT ON COLUMN "T_ROLE"."F_ROLE_NAME" IS '角色名称';
COMMENT ON TABLE "T_ROLE" IS '用户角色表';

INSERT INTO "ZHANGZHIXI"."T_ROLE" VALUES ('1', '董事长');
INSERT INTO "ZHANGZHIXI"."T_ROLE" VALUES ('2', '业务经理');
INSERT INTO "ZHANGZHIXI"."T_ROLE" VALUES ('3', '部门经理');
INSERT INTO "ZHANGZHIXI"."T_ROLE" VALUES ('4', '公司员工');

-- ------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "T_USER" (
  "ID" NUMBER NOT NULL,
  "F_NAME" VARCHAR2(255 BYTE),
  "F_AGE" VARCHAR2(255 BYTE),
  "F_SEX" VARCHAR2(255 BYTE),
  "F_ADDRESS" VARCHAR2(255 BYTE)
);
COMMENT ON COLUMN "T_USER"."F_NAME" IS '用户名';
COMMENT ON COLUMN "T_USER"."F_AGE" IS '年龄';
COMMENT ON COLUMN "T_USER"."F_SEX" IS '性别';
COMMENT ON COLUMN "T_USER"."F_ADDRESS" IS '地址';
COMMENT ON TABLE  "T_USER" IS '用户表';

INSERT INTO "ZHANGZHIXI"."T_USER" VALUES ('2', '李四', '24', '男', '北京丰台');
INSERT INTO "ZHANGZHIXI"."T_USER" VALUES ('1', '张三', '23', '男', '北京朝阳');

-- ------------------------------------------------------------------------------------------------------------------------
CREATE TABLE "T_USER_ROLE" (
  "ID" NUMBER NOT NULL,
  "F_USER_ID" NUMBER,
  "F_ROLE_ID" NUMBER
);
COMMENT ON COLUMN "T_USER_ROLE"."F_USER_ID" IS '用户ID';
COMMENT ON COLUMN "T_USER_ROLE"."F_ROLE_ID" IS '角色ID';
COMMENT ON TABLE  "T_USER_ROLE" IS '用户与角色关联表';

INSERT INTO "T_USER_ROLE" VALUES ('1', '1', '1');
INSERT INTO "T_USER_ROLE" VALUES ('2', '2', '2');
INSERT INTO "T_USER_ROLE" VALUES ('3', '2', '3');
INSERT INTO "T_USER_ROLE" VALUES ('4', '1', '4');
INSERT INTO "T_USER_ROLE" VALUES ('5', '2', '4');
数据表
复制代码

一对一 1:1（一个订单对应一个用户）

用户表和订单表的关系为：一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

1、SQL语句

?
1
SELECT o.*,u.*FROM T_ORDER o,T_USER u WHERE o.F_USER_ID=u.ID

2、实体

Order

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
@TableName(value ="T_ORDER")
@Data
public class Order implements Serializable {
    /**
     *
     */
    @TableId(value = "ID")
    private Long id;
 
    /**
     * 下单时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)// 反序列化
    @JsonSerialize(using = LocalDateTimeSerializer.class)// 序列化
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")// 对入参进行格式化
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")// 对出参进行格式化
    @TableField(value = "F_ORDER_TIME")
    private LocalDateTime orderTime;
 
    /**
     * 下单金额
     */
    @TableField(value = "F_TOTAL")
    private String total;
 
    /**
     * 下单用户
     */
    @TableField(value = "F_USER_ID")
    private Long userId;
 
    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

User

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@TableName(value = "T_USER")
@Data
public class User implements Serializable {
    /**
     * 主键
     */
    @TableId(value = "ID")
    private Long id;
 
    /**
     * 用户名
     */
    @TableField(value = "F_NAME")
    private String name;
 
    /**
     * 年龄
     */
    @TableField(value = "F_AGE")
    private String age;
 
    /**
     * 性别
     */
    @TableField(value = "F_SEX")
    private String sex;
 
    /**
     * 地址
     */
    @TableField(value = "F_ADDRESS")
    private String address;
 
    @TableField(exist = false)
 
    private static final long serialVersionUID = 1L;
}

OrderDTO

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * @author zhangzhixi
 * @version 1.0
 * @description 订单DTO
 * @date 2023-09-18 9:52
 */
@EqualsAndHashCode(callSuper = true)
@Data
public class OrderDTO extends Order {
 
    /**
     * 下单用户
     */
    User user;
}

3、创建Mapper接口

?
1
2
3
4
5
/**
 * 查询所有订单，一个订单对应一个用户
 * @return 订单列表
 */
List<OrderDTO> findAll();

4、配置OrderMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<select id="findAll" resultMap="orderMapTo1">
    SELECT *
    FROM T_ORDER o,
         T_USER u
    WHERE o.F_USER_ID = u.ID
</select>
<resultMap id="orderMapTo1" type="orderDTO">
    <!--手动指定字段与实体属性的映射关系
        column: 数据表的字段名称
        property：实体的属性名称
    -->
    <id column="ID" property="id"/>
    <result column="F_ORDER_TIME" property="orderTime"/>
    <result column="F_TOTAL" property="total"/>
    <result column="F_USER_ID" property="userId"/>
    <result column="ID" property="user.id"/>
    <result column="F_NAME" property="user.name"/>
    <result column="F_AGE" property="user.age"/>
    <result column="F_SEX" property="user.sex"/>
    <result column="F_ADDRESS" property="user.address"/>
</resultMap>
<resultMap id="orderMapTo2" type="orderDTO">
    <!--手动指定字段与实体属性的映射关系
        column: 数据表的字段名称
        property：实体的属性名称
    -->
    <id column="ID" property="id"/>
    <result column="F_ORDER_TIME" property="orderTime"/>
    <result column="F_TOTAL" property="total"/>
    <result column="F_USER_ID" property="userId"/>
    <!--
        property: 当前实体(order)中的属性名称(private User user)
        javaType: 当前实体(order)中的属性的类型(User)
    -->
    <association property="user" javaType="user">
        <id column="ID" property="id"/>
        <result column="F_NAME" property="name"/>
        <result column="F_AGE" property="age"/>
        <result column="F_SEX" property="sex"/>
        <result column="F_ADDRESS" property="address"/>
    </association>
</resultMap>

5、测试接口

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[
  {
    "id": 2,
    "orderTime": "2023-09-18 00:00:00",
    "total": "2600",
    "userId": 1,
    "user": {
      "id": 2,
      "name": "张三",
      "age": "23",
      "sex": "男",
      "address": "北京朝阳"
    }
  },
  {
    "id": 1,
    "orderTime": "2023-09-18 09:50:23",
    "total": "3500",
    "userId": 1,
    "user": {
      "id": 1,
      "name": "张三",
      "age": "23",
      "sex": "男",
      "address": "北京朝阳"
    }
  },
  {
    "id": 3,
    "orderTime": "2023-08-31 12:47:43",
    "total": "2800",
    "userId": 2,
    "user": {
      "id": 3,
      "name": "李四",
      "age": "24",
      "sex": "男",
      "address": "北京丰台"
    }
  }
]

一对多 1:N（一个用户拥有多个订单）

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

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

1、SQL语句

?
1
2
3
4
5
6
7
8
9
10
11
12
SELECT u.ID        as ID,
       u.F_NAME    as F_NAME,
       u.F_AGE     as F_AGE,
       u.F_SEX     as F_SEX,
       u.F_ADDRESS as F_ADDRESS,
       o.ID        as O_ID,
       o.F_ORDER_TIME as F_ORDER_TIME,
       o.F_TOTAL   as F_TOTAL,
       o.F_USER_ID as F_USER_ID
FROM T_USER u
LEFT JOIN T_ORDER o
 ON o.F_USER_ID = u.id

2、实体

UserOrderDTO

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
 * @author zhangzhixi
 * @version 1.0
 * @description 一个用户对应多个订单
 * @date 2023-09-18 12:42
 */
@EqualsAndHashCode(callSuper = true)
@Data
public class UserOrderDTO extends User {
    /**
     * 1:N，一个用户对应多个订单
     */
    List<Order> orders;
}

3、UserMapper

?
1
2
3
4
5
/**
 * 查询用户订单列表，一个用户对应多个订单
 * @return 用户订单列表
 */
List<UserOrderDTO> findUserOrderList();

4、UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<select id="findUserOrderList" resultMap="userOrderList">
    SELECT u.ID        as ID,
           u.F_NAME    as F_NAME,
           u.F_AGE     as F_AGE,
           u.F_SEX     as F_SEX,
           u.F_ADDRESS as F_ADDRESS,
           o.ID        as O_ID,
           o.F_ORDER_TIME as F_ORDER_TIME,
           o.F_TOTAL   as F_TOTAL,
           o.F_USER_ID as F_USER_ID
    FROM T_USER u
    LEFT JOIN T_ORDER o
     ON o.F_USER_ID = u.id
</select>
<!--查询用户表，一个用户有多个订单信息-->
<resultMap id="userOrderList" type="UserOrderDTO">
    <id property="id" column="ID"/>
    <result property="name" column="F_NAME"/>
    <result property="age" column="F_AGE"/>
    <result property="sex" column="F_SEX"/>
    <result property="address" column="F_ADDRESS"/>
    <collection property="orders" ofType="order">
        <id property="id" column="O_ID"/>
        <result property="orderTime" column="F_ORDER_TIME"/>
        <result property="total" column="F_TOTAL"/>
        <result property="userId" column="F_USER_ID"/>
    </collection>
</resultMap>

5、测试接口：一个用户可以有多个订单

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[
  {
    "id": 1,
    "name": "张三",
    "age": "23",
    "sex": "男",
    "address": "北京朝阳",
    "orders": [
      {
        "id": 2,
        "orderTime": "2023-09-18 00:00:00",
        "total": "2600",
        "userId": 1
      },
      {
        "id": 1,
        "orderTime": "2023-09-18 09:50:23",
        "total": "3500",
        "userId": 1
      }
    ]
  },
  {
    "id": 2,
    "name": "李四",
    "age": "24",
    "sex": "男",
    "address": "北京丰台",
    "orders": [
      {
        "id": 3,
        "orderTime": "2023-08-31 12:47:43",
        "total": "2800",
        "userId": 2
      }
    ]
  }
]

多对多 N:N（一个用户拥有多个角色，一个角色可以被多个用户使用）

用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用

多对多查询的需求：一个用户可以有多个角色，一个角色可以被多个用户拥有

1、SQL

?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    u.ID "U_ID",
    u.F_NAME "U_NAME",
    u.F_AGE "U_AGE",
    u.F_SEX "U_SEX",
    u.F_ADDRESS "U_ADDRESS",
    r.F_ROLE_NAME "ROLE_NAME"
FROM
    T_USER u
    LEFT JOIN T_USER_ROLE ur ON u.ID = ur.F_USER_ID
    LEFT JOIN T_ROLE r ON ur.F_ROLE_ID = r.ID
ORDER BY
    U_NAME DESC

2、实体

Role

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@TableName("T_ROLE")
@Data
public class Role {
 
    /**
     * 主键
     */
    @TableId(value = "ID")
    private Long id;
 
    /**
     * 角色名称
     */
    @TableField(value = "F_ROLE_NAME")
    private String roleName;
}

UserRole

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@TableName("T_USER_ROLE")
@Data
public class UserRole {
    /**
     * 主键
     */
    @TableId(value = "ID")
    private Long id;
 
    @TableField(value = "F_USER_ID")
    private Long userId;
 
    @TableField(value = "F_ROLE_ID")
    private Long roleId;
}

RoleDTO

?
1
2
3
4
5
6
7
8
9
10
@TableName("T_ROLE")
@Data
public class RoleDTO {
 
    /**
     * 角色名称
     */
    @TableField(value = "F_ROLE_NAME")
    private String roleName;
}

UserRoleDTO

?
1
2
3
4
5
6
7
8
@EqualsAndHashCode(callSuper = true)
@Data
public class UserRoleDTO extends User {
    /**
     * 用户角色列表，一个用户可以对应多个角色
     */
    List<RoleDTO> roleList;
}

3、UserMapper接口

?
1
2
3
4
5
/**
 * 查询用户角色列表，一个用户对应多个角色，一个角色对应多个用户
 * @return 用户角色列表
 */
List<UserRoleDTO> findUserRoleList();

4、UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<select id="findUserRoleList" resultMap="userRoleList">
    SELECT u.ID          "U_ID",
           u.F_NAME      "U_NAME",
           u.F_AGE       "U_AGE",
           u.F_SEX       "U_SEX",
           u.F_ADDRESS   "U_ADDRESS",
           r.F_ROLE_NAME "ROLE_NAME"
    FROM T_USER u
             LEFT JOIN T_USER_ROLE ur ON u.ID = ur.F_USER_ID
             LEFT JOIN T_ROLE r ON ur.F_ROLE_ID = r.ID
    ORDER BY U_NAME DESC
</select>
<resultMap id="userRoleList" type="UserRoleDTO">
    <id property="id" column="U_ID"/>
    <result property="name" column="U_NAME"/>
    <result property="age" column="U_AGE"/>
    <result property="sex" column="U_SEX"/>
    <result property="address" column="U_ADDRESS"/>
    <collection property="roleList" ofType="RoleDTO">
        <result property="roleName" column="ROLE_NAME"/>
    </collection>
</resultMap>

5、测试接口

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[
  {
    "id": 1,
    "name": "张三",
    "age": "23",
    "sex": "男",
    "address": "北京朝阳",
    "roleList": [
      {
        "roleName": "董事长"
      },
      {
        "roleName": "公司员工"
      }
    ]
  },
  {
    "id": 2,
    "name": "李四",
    "age": "24",
    "sex": "男",
    "address": "北京丰台",
    "roleList": [
      {
        "roleName": "公司员工"
      },
      {
        "roleName": "业务经理"
      },
      {
        "roleName": "部门经理"
      }
    ]
  }
]

MybatisPlus多表关联查询

案例一：单表查询(带出自定义SQL的使用)

需求：按照订单ID扣减订单余额

?
1
update T_ORDER set F_TOTAL = F_TOTAL - 200.0 WHERE (id IN (1,2))

通常写法：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
 *
 * @param modifyTheAmount 修改的金额
 * @param ids         订单id列表
 * @return >0，更新成功
 */
@Override
public int updateOrderTotalPrice(Double modifyTheAmount, List<Long> ids) {
    if (modifyTheAmount == null || ids.isEmpty()) {
        throw  new RuntimeException("用户编号和需要扣减的余额不能为空");
    }
    LambdaUpdateWrapper<Order> orderLambdaUpdateWrapper = Wrappers.lambdaUpdate(Order.class);
    orderLambdaUpdateWrapper
            .setSql("F_TOTAL = F_TOTAL -" + modifyTheAmount)
            .in(Order::getId,ids);
    return orderMapper.update(null,orderLambdaUpdateWrapper);
}

使用自定义SQL

MybatisPlus提供了自定义SQL功能，可以让我们利用Wrapper生成查询条件，再结合Mapper.xml编写SQL

Service层拼接条件，将set语句要做的东操作，放在xml中：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * @param modifyTheAmount 修改的金额
 * @param ids             订单id列表
 * @return >0，更新成功
 */
@Override
public int updateOrderTotalPrice(Double modifyTheAmount, List<Long> ids) {
    if (modifyTheAmount == null || ids.isEmpty()) {
        throw new RuntimeException("用户编号和需要扣减的余额不能为空");
    }
    // 按照订单ID修改订单余额
    QueryWrapper<Order> orderQueryWrapper = new QueryWrapper<Order>().in("id", ids);
    int updateResult = orderMapper.updateOrderTotalPrice(modifyTheAmount, orderQueryWrapper);
    if (updateResult == 0) {
        throw new RuntimeException("更新失败");
    }
    return updateResult;
}

OrderMapper.java

需要注意的是，编写自定义SQL，@Params中的参数必须是ew，接收参数使用：${ew.customSqlSegment}，其实就是在代码中拼接完where条件后给到xml

?
1
2
3
4
5
6
7
/**
 * 更新订单总价
 *
 * @param modifyTheAmount 修改的金额
 * @param queryWrapper    查询条件
 */
int updateOrderTotalPrice(@Param("amount") Double modifyTheAmount, @Param("ew") QueryWrapper<Order> queryWrapper);

OrderMapper.xml

?
1
2
3
4
5
<update id="updateOrderTotalPrice">
    update T_ORDER
    set F_TOTAL = F_TOTAL - #{amount}
        ${ew.customSqlSegment}
</update>

案例二：多表查询

需求：一个用户拥有多个订单信息，添加查询条件进行筛选指定订单信息

?
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
    u.ID U_ID,
    u.F_NAME U_NAME,
    u.F_AGE U_AGE,
    u.F_SEX U_SEX,
    u.F_ADDRESS U_ADDRESS,
    o.ID O_ID,
    o.F_TOTAL O_TOTAL
FROM
    T_USER u
    LEFT JOIN T_ORDER o ON u.ID = o.F_USER_ID
WHERE
    ( o.F_TOTAL >= 1500 AND u.F_NAME = '张三' )

Service层拼接SQL

?
1
2
3
4
5
6
7
public List<UserOrderDTO> findUserOrderListByCondition() {
    // 拼接查询条件
    QueryWrapper<UserOrderDTO> userOrderQueryWrapper = new QueryWrapper<>(UserOrderDTO.class)
            .ge("o.F_TOTAL", 1500)
            .eq("u.F_NAME", "张三");
    return userMapper.findUserOrderListByCondition(userOrderQueryWrapper);
}

UserMapper.java

?
1
2
3
4
5
6
/**
 * 查询用户的所有订单，并添加查询条件
 *
 * @return 满足条件的用户及订单信息
 */
List<UserOrderDTO> findUserOrderListByCondition(@Param("ew") QueryWrapper<UserOrderDTO> wrapper);

UserMapper.xml

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<select id="findUserOrderListByCondition" resultMap="findUserOrderListByConditionReaultMap">
    SELECT
        u.ID U_ID,
        u.F_NAME U_NAME,
        u.F_AGE U_AGE,
        u.F_SEX U_SEX,
        u.F_ADDRESS U_ADDRESS,
        o.ID O_ID,
        o.F_TOTAL O_TOTAL
    FROM
        T_USER u
            LEFT JOIN T_ORDER o ON u.ID = o.F_USER_ID
     ${ew.customSqlSegment}
</select>
<resultMap id="findUserOrderListByConditionReaultMap" type="UserOrderDTO">
    <id property="id" column="U_ID"/>
    <result property="name" column="U_NAME"/>
    <result property="age" column="U_AGE"/>
    <result property="sex" column="U_SEX"/>
    <result property="address" column="U_ADDRESS"/>
    <collection property="orders" ofType="order">
        <id property="id" column="O_ID"/>
        <result property="total" column="O_TOTAL"/>
    </collection>
</resultMap>

 

　　

 

　

posted @   Java小白的搬砖路  阅读(477)  评论(0)  编辑  收藏  举报
相关博文：
·  🥪MyBatis操作Oracle（实现兼容Oracle和MySQL）
·  🧃MyBatisPlus操作Oracle（插入数据主键自增）
·  Mybatis20_mybatis的多表操作8
·  Mybatis Plus 多表联查（包含分页关联查询,图文讲解）
·  35_mybatis
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 按钮权限的设计及实现
· 25岁的心里话
历史上的今天：
2020-09-20 插值查找算法
2020-09-20 二分查找(解决数据重复值问题)

喜欢请打赏

扫描二维码打赏

支付宝打赏

点击右上角即可分享
微信分享提示
SQL AI 助手
❉
❉
❉