🎡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语句
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（一个用户拥有多个角色，一个角色可以被多个用户使用）
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：一个用户可以有多个角色，一个角色可以被多个用户拥有
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}，其实就是在代码中拼接完where条件后给到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>
