首页 > 数据库 >?Mybatis多表查询（1:1、1:N、N:N），MP多表查询（自定义SQL）

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

时间：2023-10-27 14:33:48浏览次数：47  
标签：多表 NAME 自定义 用户 查询 ROLE USER ORDER ID

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（一个订单对应一个用户）

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

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

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

1、SQL语句

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

2、实体

Order

@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

@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

/**
 * @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接口

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

4、配置OrderMapper.xml

<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、测试接口

[
  {
    "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语句

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

/**
 * @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

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

4、UserMapper.xml

<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、测试接口：一个用户可以有多个订单

[
  {
    "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（一个用户拥有多个角色，一个角色可以被多个用户使用）

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

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

?Mybatis多表查询（1:1、1:N、N:N），MP多表查询（自定义SQL）_mybatis-plus_02

1、SQL

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

@TableName("T_ROLE")
@Data
public class Role {

    /**
     * 主键
     */
    @TableId(value = "ID")
    private Long id;

    /**
     * 角色名称
     */
    @TableField(value = "F_ROLE_NAME")
    private String roleName;
}

UserRole

@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

@TableName("T_ROLE")
@Data
public class RoleDTO {

    /**
     * 角色名称
     */
    @TableField(value = "F_ROLE_NAME")
    private String roleName;
}

UserRoleDTO

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

3、UserMapper接口

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

4、UserMapper.xml

<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、测试接口

[
  {
    "id": 1,
    "name": "张三",
    "age": "23",
    "sex": "男",
    "address": "北京朝阳",
    "roleList": [
      {
        "roleName": "董事长"
      },
      {
        "roleName": "公司员工"
      }
    ]
  },
  {
    "id": 2,
    "name": "李四",
    "age": "24",
    "sex": "男",
    "address": "北京丰台",
    "roleList": [
      {
        "roleName": "公司员工"
      },
      {
        "roleName": "业务经理"
      },
      {
        "roleName": "部门经理"
      }
    ]
  }
]

MybatisPlus多表关联查询

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

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

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

通常写法：

/**
 *
 * @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中：

/**
 * @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}，其实就是在代码中拼接完wher条件后给到xml

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

OrderMapper.xml

<update id="updateOrderTotalPrice">
    update T_ORDER
    set F_TOTAL = F_TOTAL - #{amount}
        ${ew.customSqlSegment}
</update>

案例二：多表查询

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

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

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

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

UserMapper.xml

 

<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>

 

　　

 

　



标签：多表,NAME,自定义,用户,查询,ROLE,USER,ORDER,ID
From： https://blog.51cto.com/zhangzhixi/8056208

相关文章

  • Revit 自定义事务进行自动管理事务DBTrans实现
    第一步：自定义事务对象自定义事务对象///<summary>///自定义事务///</summary>publicclassDBTrans:IDisposable{#region私有字段privatebooldisposedValue;privatebool_commit;///<summary>......
  • javaweb--sql查询DQL
    基础查询别名selectmathas'数学成绩'english'英语成绩'fromstu条件查询几点注意事项1、null值的比较不能用=或者！=，只能使用isnull或者isnotnull2、可以使用betweenand来匹配一段数值whereagebetween20and303、<>和!=的作用相同4、or可以用数组的形式表示......
  • idea查询maven依赖情况
     ......
  • Fabric.js 自定义控件
    本文简介带尬猴，我是德育处主任虽然Fabric.js提供的基础功能已经很丰富了，但有时难免需要定制一些需求。比如本文要讲的『自定义控件』。掌握创建自定义控件这个功能，能够创建更加精美和实用的图形应用程序，提高用户体验和用户满意度。尽管Fabric.js的文档很一般，但demo还挺......
  • 浅析 es 查询文档的过程
    本文从一个示例入手，从代码层面分析elasticsearch查询文档的完整过程。新建索引cn-msg，设置3分片，1副本PUTlocalhost:9200/cn-msg{"settings":{"number_of_shards":3,"number_of_replicas":1}}写入文档POSTlocalhost:9200/cn-msg/_doc{"messag......
  • 自定义过滤器配置 Shiro 认证失败返回 json 数据
    byemanjusakafrom​https://www.emanjusaka.top/archives/11彼岸花开可奈何本文欢迎分享与聚合，全文转载请留下原文地址。Shiro权限框架认证失败默认是重定向页面的，这对于前后端分离的项目及其不友好，可能会造成请求404的问题。现在我们自定义过滤器实现认证失败返回json数......
  • c# winfom从0学习开发开发OA、BPM工作流程与自定义表单系统（十二）新建一个完整的工作流
     先设计一个表单 开始设计表单 设计一个表单例如请假表单 Tag十分的重要，再设计流程图节点的时候tag起到的作用是提示当前控件是谁，再设置可写字段环节十分重要 保存 设计流程图 设计请假流程图设计好请假的流程 设置每个节点的参数 所有部门下的人都......
  • 41 mysql 索引和慢查询优化
    MySQL之索引原理与慢查询优化 阅读目录一介绍二索引的原理三索引的数据结构四聚集索引与辅助索引五MySQL索引管理六测试索引七正确使用索引八联合索引与覆盖索引九查询优化神器-explain十慢查询优化的基本步骤十一慢日志管理一介绍为何要......
  • 数据库查询
    1、连接查询内连接查询与单表查询相似,都是使⽤select语句可以把多张表放到select后边,并⽤逗号隔开还可以结合as取别名,⽅便引⽤如果要查询多个表中的字段,⽆重名的情况下,可以省略表的指定⾮等值连接迪卡尔积--内连接中的非等值链接。select*fromgra......
  • Mac电脑使用BetterAndBetter软件自定义的脚本
    新建文件tellapplication"Finder" setselectedItemstoselection if(countofselectedItems)is1then setselectedItemtoitem1ofselectedItems ifclassofselectedItemisfolderthen displaydialog"请输入文件名："defaultansw......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99