mybatis in查询_jpa+mybatis 组合数据持久化

最新推荐文章于 2022-12-18 22:11:47 发布

weixin_39937635

最新推荐文章于 2022-12-18 22:11:47 发布

阅读量147

点赞数

文章标签： mybatis in查询

> 背景：公司技术框架为 spring boot +jpa，jpa 可以处理正常的业务，但是对于复杂的统计业务（尤其涉及对个聚合的复杂业务），jpa 过于消耗性能,所以研究在 spring boot 项目中，jpa+mybatis 组合的可能性

jpa && mybatis

jpa（Java Persistence API）：大大简化数据访问层代码的编码，无需手动维护数据的持久化，是面向对象的
mybatis：支持定制化 SQL、存储过程以及高级映射，是面向关系的

jpa+mybatis 组合 demo实例

实战

分页查询用户职位信息业务需求如下图：

入参查询条件涉及聚合：user,position,dept
出参数据来源涉及聚合：user,position,dept,admin,userLastLoginRec

jpa 实现代码

private Page<StatisticsUserDTO> queryStatisticsUserInfo(StatisticsSearchDTO searchDTO, Pageable pageable, boolean isPaging, Long tenantId) {
      QUser user = QUser.user;
      QPosition position = QPosition.position;
      BooleanExpression expression = user.id.isNotNull();
      BooleanExpression positionExpression = position.id.isNotNull();

      positionExpression = positionExpression.and(position.tenantId.eq(tenantId));
      if (searchDTO != null) {
          if (searchDTO.getGroupIds() != null && searchDTO.getGroupIds().length > 0) {
              positionExpression = positionExpression.and(position.deptId.in((Number[]) searchDTO.getGroupIds()));
          }
          if (!StringUtils.isEmpty(searchDTO.getJobCode())) {
              positionExpression = positionExpression.and(position.jobCode.contains(searchDTO.getJobCode()));
          }
          if (!StringUtils.isEmpty(searchDTO.getRealName())) {
              expression = expression.and(user.realName.contains(searchDTO.getRealName()));
          }
          if (!StringUtils.isEmpty(searchDTO.getPhoneNum())) {
              expression = expression.and(user.phoneNum.contains(searchDTO.getPhoneNum()));
          }

          List<PositionClassificationRec> attrs = searchDTO.getAttrs();
          //根据职位筛选信息
          if (!CollectionUtils.isEmpty(attrs)) {
              for (PositionClassificationRec attr : attrs) {
                  List<Long> positionIds = positionRepository.findAllByAttrsOptions(attr.getClassificationId(), attr.getOptions())
                          .stream().map(Position::getId).collect(Collectors.toList());
                  positionExpression = positionExpression.and(position.id.in(QueryPlanCacheOptimizeUtil.convertList(positionIds)));
              }
          }
      }

      Iterable<Position> positions = positionRepository.findAll(positionExpression);
      List<Long> userIds = new ArrayList<>();
      positions.forEach(position1 -> userIds.add(position1.getUserId()));

      expression = expression.and(user.id.in(userIds));

      Page<User> users;
      if (isPaging) {
          users = userRepository.findAll(expression, pageable);
      } else {
          Pageable pageable1 = null;
          users = userRepository.findAll(expression, pageable1);
      }

      Set<Long> newUserIds = users.getContent().stream().map(AbstractEntity::getId).collect(Collectors.toSet());
      List<Position> positionsList = positionRepository.findAllByUserIdIn(QueryPlanCacheOptimizeUtil.convertList(newUserIds));
      Map<Long, Position> positionMap = positionsList.stream().collect(Collectors.toMap(Position::getUserId, c -> c));
      Set<Long> deptIds = positionsList.stream().map(Position::getDeptId).collect(Collectors.toSet());
      Map<Long, Dept> deptMap = deptRepository.findAll(deptIds).stream().collect(Collectors.toMap(Dept::getId, c -> c));
      Map<Long, Long> userLastLoginMap = userLastLoginRecRepository.findAllByUserIdIn(newUserIds)
              .stream().collect(Collectors.toMap(UserLastLoginRec::getUserId, UserLastLoginRec::getLastLogin));

      return users.map(user1 -> {
          Position curPosition = positionMap.get(user1.getId());
          Dept curDept = deptMap.get(curPosition.getDeptId());
          return new StatisticsUserDTO(curPosition, curDept.getName(), user1.getRealName(), user1.getPhoneNum(), userLastLoginMap.get(user1.getId()), user1.getStartWorkTime());
      });
  }

disadvantage: 消耗不必要的时间和性能 难以在现有的框架上进行量级优化 代码复杂度高，维护不方便 jpa 无法控制生成的sql,OneToMany 导致生成的sql 过度，消耗 datasource 连接数（此处 jpa 生成的 sql 数量成百上千条）

mybatis 实现代码

<resultMap id="PositionStatisticsInfoMap" type="ky.edu.server.tenant.domain.model.PositionStatisticsInfo">
        <result property="deptName" column="deptName"></result>
        <result property="phoneNum" column="phoneNum"></result>
        <result property="realName" column="realName"></result>
        <result property="lastLogin" column="lastLogin"></result>
        <result property="startWorkTime" column="startWorkTime"></result>
        <association property="position" javaType="ky.edu.server.tenant.domain.model.Position">
            <id property="id" column="id"></id>
            <result property="createTime" column="createTime"/>
            <result property="lastModified" column="lastModified"/>
            <result property="jobCode" column="jobCode"/>
            <result property="tenantId" column="tenantId"/>
            <result property="deptId" column="deptId"/>
            <result property="accountId" column="accountId"/>
            <result property="userId" column="userId"/>
            <result property="admin" column="admin"/>
            <collection property="internalClassRecs" column="id" select="getInternalClassRecs">
                <result property="classificationId" column="classificationId"></result>
                <result property="optionId" column="optionId"></result>
            </collection>
        </association>
    </resultMap>

    <select id="findStatisticsPositionInfos" resultMap="PositionStatisticsInfoMap">
            select distinct
            up.id,d.deptName,ru.phoneNum,ru.realName,ullr.lastLogin,ru.startWorkTime,up.id,up.createTime,up.lastModified,up.jobCode,up.tenantId,
            up.deptId,up.accountId,up.userId,up.admin
            from user_position up
            inner join reg_user ru on ru.accountId=up.accountId
            left join position_class_rec pcr on up.id=pcr.positionId
            inner join dept d on up.deptId=d.id
            left join user_last_login_rec ullr on ullr.userId=up.userId
            where up.tenantId= #{tenantId}
            <if test="statisticsSearch.realName !=null and statisticsSearch.realName != '' ">
                and ru.realName REGEXP #{statisticsSearch.realName}
            </if>
            <if test="statisticsSearch.phoneNum !=null and  statisticsSearch.phoneNum != '' ">
                and ru.phoneNum REGEXP #{statisticsSearch.phoneNum}
            </if>
            <if test="statisticsSearch.jobCode !=null and statisticsSearch.jobCode != '' ">
                and up.jobCode REGEXP #{statisticsSearch.jobCode}
            </if>
            <if test="statisticsSearch.groupIds !=null and statisticsSearch.groupIds.size !=0">
                AND up.deptId in
                (<foreach collection="statisticsSearch.groupIds" item="groupId" separator=",">#{groupId}</foreach>)
            </if>
            <if test="statisticsSearch.internalClassRecs !=null and statisticsSearch.internalClassRecs.size !=0">
                AND (pcr.classificationId,pcr.optionId) in
                (<foreach collection="statisticsSearch.internalClassRecs" item="attr" separator=",">
                (#{attr.classificationId},#{attr.optionId})</foreach>)
            </if>
            order by ru.lastModified desc
            <if test="offset !=null and perPage!=null">
                limit #{offset}, #{perPage}
            </if>
        </select>

        <select id="getInternalClassRecs" resultType="ky.edu.server.common.domain.model.InternalClassRec">
                SELECT classificationId,optionId  FROM position_class_rec
                WHERE positionId = #{id}
            </select>

advantage: 根据复杂统计业务建模，在数据库层面只查询有用数据 降低 datasource 连接数（此处sql 数量近百条，OneToMany 关系映射产生的过度sql 仍然没有解决）

mybatis 实现代码（降低 datasource 连接数版）

映射关系map 去除 internalClassRecs

<!--<collection property="internalClassRecs" column="id" select="getInternalClassRecs">-->
                    <!--<result property="classificationId" column="classificationId"></result>-->
                    <!--<result property="optionId" column="optionId"></result>-->
    <!--</collection>-->

增加批量查询 internalClassRecs 方法：

<select id="queryInternalClassRecsByPositionIds" resultType="ky.edu.server.common.domain.model.InternalClassRec">
        SELECT classificationId,optionId,positionId  FROM position_class_rec
        WHERE positionId in
        (<foreach collection="positionIds" item="positionId" separator=",">#{positionId}</foreach>)
    </select>

然后业务层面进行数据组装：

List<PositionStatisticsInfo> positionInfos = iPositionDao.findStatisticsPositionInfos(searchDTO, tenantId, (long) offset, (long) size);

        if(CollectionUtils.isEmpty(positionInfos)){
            return new PageImpl<>(positionInfos, pageRequest, 0);
        }

        Set<Long> positionIds = new HashSet<>();
        positionInfos.forEach(positionStatisticsInfo -> positionIds.add(positionStatisticsInfo.getPosition().getId()));

        List<InternalClassRec> internalClassRecs = iPositionDao.queryInternalClassRecsByPositionIds(positionIds);
        Map<Long, Set<InternalClassRec>> internalClassRecMap = MapAssembler.markInternalClassRecAsPositionIdKey(internalClassRecs);

        Position position;
        for (PositionStatisticsInfo positionInfo : positionInfos) {
            position = positionInfo.getPosition();
            position.initAttrs(internalClassRecMap.get(position.getId()));
        }

此方案查询数据 sql 数量只有两条；