背景：公司技術(shù)框架為 spring boot +jpa长窄，jpa 可以處理正常的 業(yè)務(wù)光涂，但是對(duì)于復(fù)雜的統(tǒng)計(jì)業(yè)務(wù)（尤其涉及對(duì)個(gè)聚合的復(fù)雜業(yè)務(wù)）文黎，jpa 過(guò)于消耗性能,所以研究在 spring boot 項(xiàng)目中状共，jpa+mybatis 組合的可能性

jpa && mybatis

• jpa（Java Persistence API）：大大簡(jiǎn)化數(shù)據(jù)訪(fǎng)問(wèn)層代碼的編碼蚓哩，無(wú)需手動(dòng)維護(hù)數(shù)據(jù)的持久化，是面向?qū)ο蟮?/li>
• mybatis：支持定制化 SQL件已、存儲(chǔ)過(guò)程以及高級(jí)映射牍疏，是面向關(guān)系的

jpa+mybatis 組合 demo實(shí)例

實(shí)戰(zhàn)

分頁(yè)查詢(xún)用戶(hù)職位信息 業(yè)務(wù)需求如下圖：

需求效果

入?yún)⒉樵?xún)條件涉及聚合：user,position,dept
出參數(shù)據(jù)來(lái)源涉及聚合：user,position,dept,admin,userLastLoginRec

jpa 實(shí)現(xiàn)代碼

 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();
          //根據(jù)職位篩選信息
          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:

• 消耗不必要的時(shí)間和性能
• 難以在現(xiàn)有的框架上進(jìn)行量級(jí)優(yōu)化
• 代碼復(fù)雜度高，維護(hù)不方便
• jpa 無(wú)法控制生成的sql,OneToMany 導(dǎo)致生成的sql 過(guò)度拨齐，消耗 datasource 連接數(shù)
  （此處 jpa 生成的 sql 數(shù)量 成百上千條）

mybatis 實(shí)現(xiàn)代碼

<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,pcr.classificationId,pcr.optionId
            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:

• 根據(jù)復(fù)雜統(tǒng)計(jì)業(yè)務(wù)建模鳞陨，在數(shù)據(jù)庫(kù) 層面 只查詢(xún)有用數(shù)據(jù)
• 降低 datasource 連接數(shù)
  （此處sql 數(shù)量 近 百條，OneToMany 關(guān)系 映射 產(chǎn)生的 過(guò)度sql 仍然沒(méi)有解決）

mybatis 實(shí)現(xiàn)代碼（降低 datasource 連接數(shù)版）

映射關(guān)系map 去除 internalClassRecs

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

增加 批量查詢(xún) 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>

然后 業(yè)務(wù)層面進(jìn)行數(shù)據(jù)組裝：

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()));
        }

此方案 查詢(xún)數(shù)據(jù) sql 數(shù)量只有兩條瞻惋；

優(yōu)化效果（源數(shù)據(jù)相同厦滤，查詢(xún)條件相同，查詢(xún)結(jié)果一致）

• jpa 耗時(shí) 約 4.5s
• mybatis 耗時(shí) 0.27s

:smirk: 日常數(shù)據(jù)的增刪改查 推薦jpa,無(wú)需手動(dòng)進(jìn)行數(shù)據(jù)就持久化歼狼，復(fù)雜統(tǒng)計(jì)業(yè)務(wù) 推薦 mybatis,根據(jù) 需求 設(shè)計(jì) 模型掏导，查詢(xún)需要的數(shù)據(jù)，提示系統(tǒng)響應(yīng)速度羽峰，降低性能消耗

popo先生的博客