博主
258
258
258
258
标签
Vue3 springboot elasticsearch linux jdk centos docker vmware kibana
专辑

第四节 Mybatis一对多查询的两种姿势

亮子 2023-03-31 02:11:56 5690 0 0 0

1、数据库表准备

图片alt

图片alt

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50727
 Source Host           : localhost:3306
 Source Schema         : db_test

 Target Server Type    : MySQL
 Target Server Version : 50727
 File Encoding         : 65001

 Date: 31/03/2023 09:52:13
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tbl_order
-- ----------------------------
DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE `tbl_order`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号',
  `order_time` datetime(0) NULL DEFAULT NULL COMMENT '下单时间',
  `pay_time` datetime(0) NULL DEFAULT NULL COMMENT '支付日期',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tbl_order
-- ----------------------------
INSERT INTO `tbl_order` VALUES (1, 'DD000001', '2023-03-24 09:19:18', '2023-03-24 09:19:22', '1号订单');
INSERT INTO `tbl_order` VALUES (2, 'DD000002', '2023-03-24 09:19:46', '2023-03-24 09:19:49', '2号订单');

-- ----------------------------
-- Table structure for tbl_order_detail
-- ----------------------------
DROP TABLE IF EXISTS `tbl_order_detail`;
CREATE TABLE `tbl_order_detail`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '订单号',
  `good_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `good_id` int(11) NULL DEFAULT NULL COMMENT '商品id',
  `good_count` int(11) NULL DEFAULT NULL COMMENT '商品数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '订单详情表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tbl_order_detail
-- ----------------------------
INSERT INTO `tbl_order_detail` VALUES (1, 'DD000001', '瓜子', 1, 1);
INSERT INTO `tbl_order_detail` VALUES (2, 'DD000001', '饮料', 2, 2);
INSERT INTO `tbl_order_detail` VALUES (3, 'DD000001', '矿泉水', 3, 2);
INSERT INTO `tbl_order_detail` VALUES (4, 'DD000002', '手机', 4, 1);
INSERT INTO `tbl_order_detail` VALUES (5, 'DD000002', '耳机', 5, 1);

SET FOREIGN_KEY_CHECKS = 1;

2、实体类的定义

  • tbl_order
package com.shenma2005.mysql.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.Date;
import java.util.List;

import lombok.Data;

/**
 * 
 * @TableName tbl_order
 */
@TableName(value ="tbl_order")
@Data
public class TblOrder implements Serializable {
    /**
     * 
     */
    @TableId(type = IdType.AUTO)
    private Integer id;

    /**
     * 订单号
     */
    private String orderNo;

    /**
     * 下单时间
     */
    private Date orderTime;

    /**
     * 支付日期
     */
    private Date payTime;

    /**
     * 备注
     */
    private String remark;

    /**
     * 订单明细
     */
    @TableField(exist = false)
    private List<TblOrderDetail> orderDetailList;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}
  • tbl_order_detail
package com.shenma2005.mysql.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import lombok.Data;

/**
 * 订单详情表
 * @TableName tbl_order_detail
 */
@TableName(value ="tbl_order_detail")
@Data
public class TblOrderDetail implements Serializable {
    /**
     * 
     */
    @TableId(type = IdType.AUTO)
    private Integer id;

    /**
     * 订单号
     */
    private String orderNo;

    /**
     * 商品名称
     */
    private String goodName;

    /**
     * 商品id
     */
    private Integer goodId;

    /**
     * 商品数量
     */
    private Integer goodCount;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

3、方法一：联合查询ResultMap映射

sql直接关联查询，然后结果集通过resultMap的collection映射

  • TblOrderMapper.xml
    <!--    左连接查询结果映射-->
    <resultMap id="BaseResultMap" type="com.shenma2005.mysql.pojo.TblOrder">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="orderNo" column="order_no" jdbcType="VARCHAR"/>
            <result property="orderTime" column="order_time" jdbcType="TIMESTAMP"/>
            <result property="payTime" column="pay_time" jdbcType="TIMESTAMP"/>
            <result property="remark" column="remark" jdbcType="VARCHAR"/>

        <collection property="orderDetailList" ofType="com.shenma2005.mysql.pojo.TblOrderDetail">
            <id column="d_id" property="id" jdbcType="INTEGER" />
            <result column="d_order_no" property="orderNo" jdbcType="VARCHAR" />
            <result column="good_name" property="goodName" jdbcType="VARCHAR" />
            <result column="good_id" property="goodId" jdbcType="INTEGER" />
            <result column="good_count" property="goodCount" jdbcType="INTEGER" />
        </collection>
    </resultMap>

    <!--左连接查询SQL语句    -->
    <select id="queryOrderList" resultMap="BaseResultMap">
        SELECT
        o.*, d.id as d_id,d.order_no as d_order_no,d.good_name,d.good_id,d.good_count
        FROM
        tbl_order o
        LEFT JOIN tbl_order_detail d ON d.order_no = o.order_no
        where 1=1
        <if test="orderNo != null and orderNo != ''">
            and o.order_no = #{orderNo}
        </if>
        ORDER BY o.order_time desc
    </select>
  • TblOrderMapper.java
    List<TblOrder> queryOrderList(Map map);
  • 测试代码
    @Test
    void contextLoads() {

        HashMap<String, String> map = new HashMap<>();
        List<TblOrder> tblOrders = tblOrderMapper.queryOrderList(map);
        tblOrders.forEach(item -> System.out.println(item.toString()));

    }
  • 测试结果
TblOrder(id=2, orderNo=DD000002, orderTime=Fri Mar 24 09:19:46 CST 2023, payTime=Fri Mar 24 09:19:49 CST 2023, remark=2号订单, orderDetailList=[TblOrderDetail(id=5, orderNo=DD000002, goodName=耳机, goodId=5, goodCount=1), TblOrderDetail(id=4, orderNo=DD000002, goodName=手机, goodId=4, goodCount=1)])
TblOrder(id=1, orderNo=DD000001, orderTime=Fri Mar 24 09:19:18 CST 2023, payTime=Fri Mar 24 09:19:22 CST 2023, remark=1号订单, orderDetailList=[TblOrderDetail(id=3, orderNo=DD000001, goodName=矿泉水, goodId=3, goodCount=2), TblOrderDetail(id=2, orderNo=DD000001, goodName=饮料, goodId=2, goodCount=2), TblOrderDetail(id=1, orderNo=DD000001, goodName=瓜子, goodId=1, goodCount=1)])
  • 总结

原理：sql直接关联查询，然后结果集通过resultMap的collection映射，将order_detail表对应的字段映射到orderDetailList字段中。
优点：条件查询方便；无论是订单表还是详情表如果要进行一些条件过滤的话，非常方便，直接写在where中限制就行。
不足：因为是先关联查询，后映射；如果需要进行分页查询的话，这种方式就无法满足。主表2条数据，详情表5条数据，关联之后就是10条，无法得主表进行分页；解决方法，就是先给主表套个子查询limit分页后，然后结果集再跟详情表进行关联查询；

4、方法二：子查询映射

通过resultMap中collection标签的select属性去执行子查询

  • TblOrderMapper.xml
    <!--主查询的resultMap-->
    <resultMap id="BaseResultMap2" type="com.shenma2005.mysql.pojo.TblOrder" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
        <result column="order_time" property="orderTime" jdbcType="TIMESTAMP" />
        <result column="pay_time" property="payTime" jdbcType="TIMESTAMP" />
        <result column="remark" property="remark" jdbcType="VARCHAR" />
        <!--select子查询， column 传给子查询的参数-->
        <collection property="orderDetailList" ofType="com.shenma2005.mysql.pojo.TblOrderDetail"
                    select="queryDetail" column="order_no">
        </collection>
    </resultMap>
    <!--主查询的sql-->
    <select id="queryOrderList2" resultMap="BaseResultMap2">
        SELECT
        o.*
        FROM
        tbl_order o
        where 1=1
        <if test="orderNo != null and orderNo != ''">
            and o.order_no = #{orderNo}
        </if>
        ORDER BY o.order_time desc
    </select>
    <!--子查询的resultMap-->
    <resultMap id="detailResuleMap" type="com.shenma2005.mysql.pojo.TblOrderDetail">
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="order_no" property="orderNo" jdbcType="VARCHAR" />
        <result column="good_name" property="goodName" jdbcType="VARCHAR" />
        <result column="good_id" property="goodId" jdbcType="INTEGER" />
        <result column="good_count" property="goodCount" jdbcType="INTEGER" />
    </resultMap>
    <!--子查询的sql-->
    <select id="queryDetail" resultMap="detailResuleMap">
        SELECT
            *
        FROM
            `tbl_order_detail` where order_no = #{order_no}
    </select>
  • TblOrderMapper.java
    List<TblOrder> queryOrderList2(Map map);
  • 测试代码
    @Test
    void testOrders() {

        HashMap<String, String> map = new HashMap<>();
        List<TblOrder> tblOrders = tblOrderMapper.queryOrderList2(map);

        String s = JSON.toJSONString(tblOrders);
        System.out.println(s);
    }
  • 测试结果
[{
	"id": 2,
	"orderDetailList": [{
		"goodCount": 1,
		"goodId": 4,
		"goodName": "手机",
		"id": 4,
		"orderNo": "DD000002"
	}, {
		"goodCount": 1,
		"goodId": 5,
		"goodName": "耳机",
		"id": 5,
		"orderNo": "DD000002"
	}],
	"orderNo": "DD000002",
	"orderTime": "2023-03-24 09:19:46",
	"payTime": "2023-03-24 09:19:49",
	"remark": "2号订单"
}, {
	"id": 1,
	"orderDetailList": [{
		"goodCount": 1,
		"goodId": 1,
		"goodName": "瓜子",
		"id": 1,
		"orderNo": "DD000001"
	}, {
		"goodCount": 2,
		"goodId": 2,
		"goodName": "饮料",
		"id": 2,
		"orderNo": "DD000001"
	}, {
		"goodCount": 2,
		"goodId": 3,
		"goodName": "矿泉水",
		"id": 3,
		"orderNo": "DD000001"
	}],
	"orderNo": "DD000001",
	"orderTime": "2023-03-24 09:19:18",
	"payTime": "2023-03-24 09:19:22",
	"remark": "1号订单"
}]

原理：通过collection的select方法去调用子查询；所需参数通过column传递；
优点：无论是分页还是普通查询都能满足；主表增加过滤条件也很方便，直接在主查询的sql中增加where条件就行
缺点：子查询不好增加过滤条件；column只能传递主表已有的字段。下面提供解决方式；
ps：column传递多个参数 column=“{prop1=col1,prop2=col2}”

例如：实际场景中，详情表有个状态字段，只展示状态正常的详情，需要过滤详情记录。
本例子没有状态字段，就查询订单列表，详情中不展示瓜子，即详情记录中过滤掉good_id = 1的；

参考文章

Mybatis一对多查询的两种姿势