【MyBatis】主子表分页查询问题

背景

今天遇到一个需求，主子表关联分页查询。

CREATE TABLE `inter_rule_history_log`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `proxy_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '供应商username',
  `operation_type` tinyint(4) NOT NULL DEFAULT 0 COMMENT '操作类型（0:批量操作，1单条操作）',
  `update_type` tinyint(4) NOT NULL DEFAULT 1 COMMENT '0增量白1增量黑2全量白\r\n3全量黑4新增5修改6删除',
  `dimension` tinyint(4) NOT NULL DEFAULT 0 COMMENT '0区域1供应商',
  `dimension_value` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '区域id-名字或者供应商区域id-名字',
  `update_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '操作人',
  `update_time` datetime(0) NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_update_type`(`update_type`) USING BTREE,
  INDEX `idx_dimension`(`dimension`) USING BTREE,
  INDEX `idx_proxy_id_update_type_dimension`(`proxy_id`, `update_type`, `dimension`) USING BTREE,
  INDEX `idx_proxy_id`(`proxy_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

CREATE TABLE `inter_rule_history_log_detail`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `operation_detail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '配置明细',
  `operation_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '操作id，对应inter_rule_history_log的主键id',
  `create_time` datetime(0) NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_operation_id`(`operation_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 75 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

目标：

根据proxyId,updateType,dimension,operationDetail关联inter_rule_history_log``inter_rule_history_log_detail两表查询信息。

原sql

<resultMap id="BaseResultMap" type="com.elong.nbops.model.resourceV2.InterRuleHistoryLogPo" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="proxy_id" property="proxyId" jdbcType="VARCHAR" />
    <result column="operation_type" property="operationType" jdbcType="TINYINT" />
    <result column="update_type" property="updateType" jdbcType="TINYINT" />
    <result column="dimension" property="dimension" jdbcType="TINYINT" />
    <result column="dimension_value" property="dimensionValue" jdbcType="VARCHAR" />
    <result column="update_user" property="updateUser" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <collection property="interRuleHistoryLogDetailPos" ofType="com.elong.nbops.model.resourceV2.InterRuleHistoryLogDetailPo">
      <result column="id" property="id" jdbcType="BIGINT" />
      <result column="operation_detail" property="operationDetail" jdbcType="VARCHAR" />
      <result column="operation_id" property="operationId" jdbcType="BIGINT" />
      <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    </collection>
</resultMap>
  
<select id="selectByCondition" resultMap="BaseResultMap">
      select
      <include refid="selectCondition"/>
      from
      inter_rule_history_log
      left join inter_rule_history_log_detail logDetail
      on log.id = logDetail.operation_id
      <where>
      	<if test="proxyId != null and proxyId != ''">
          proxy_id = #{proxyId}
        </if>
        <if test="updateType != null">
          and update_type = #{updateType}
        </if>
          <if test="dimension != null">
            and dimension = #{dimension}
          </if>
          <if test="operationDetail != null and operationDetail != ''">
            and logDetail.operation_detail like concat('%', #{operationDetail}, '%')
          </if>
      </where>
      order by log.update_time desc
      limit #{pageIndex}, #{pageSize}
  </select>

这样写是获取不到想要的结果的。

为什么会出现这种情况？

其实很简单,最终获取到的是1对多的集,limit是对最终的临时表进行分页,即对最终一对多形成的结果进行分页,而不是根据主表数据分页.

解决方案

修改后sql

<resultMap id="BaseResultMap" type="com.elong.nbops.model.resourceV2.InterRuleHistoryLogPo" >
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="proxy_id" property="proxyId" jdbcType="VARCHAR" />
    <result column="operation_type" property="operationType" jdbcType="TINYINT" />
    <result column="update_type" property="updateType" jdbcType="TINYINT" />
    <result column="dimension" property="dimension" jdbcType="TINYINT" />
    <result column="dimension_value" property="dimensionValue" jdbcType="VARCHAR" />
    <result column="update_user" property="updateUser" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <collection property="interRuleHistoryLogDetailPos" ofType="com.elong.nbops.model.resourceV2.InterRuleHistoryLogDetailPo">
      <result column="id" property="id" jdbcType="BIGINT" />
      <result column="operation_detail" property="operationDetail" jdbcType="VARCHAR" />
      <result column="operation_id" property="operationId" jdbcType="BIGINT" />
      <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    </collection>
</resultMap>
  
<select id="selectByCondition" resultMap="BaseResultMap">
      select
      <include refid="selectCondition"/>
      from
      (select *
      from inter_rule_history_log
      <where>
          <if test="proxyId != null and proxyId != ''">
              proxy_id = #{proxyId}
          </if>
          <if test="updateType != null">
              and update_type = #{updateType}
          </if>
          <if test="dimension != null">
              and dimension = #{dimension}
          </if>
      </where>
      order by update_time desc
      limit #{pageIndex}, #{pageSize}
      ) log
      left join inter_rule_history_log_detail logDetail
      on log.id = logDetail.operation_id
      <where>
          <if test="operationDetail != null and operationDetail != ''">
              and logDetail.operation_detail like concat('%', #{operationDetail}, '%')
          </if>
      </where>
      order by log.update_time desc
  </select>

先对你想要分页的表进行分页和条件之后,再进行关联查询.这样的结果经过mybatis的resultMap映射后,即可获取到根据inter_rule_history_log分页的结果。

注意：

在使用排序的时候子查询需要排序,外面的查询也需要排序。