【Frame】jpa+mybatis 组合数据持久化

Popo's Blog 6/17/2019 Jpa Mybatis MySql

背景：公司技术框架为 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());
          });
      }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

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>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

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>-->

1
2
3
4

增加批量查询 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>

1
2
3
4
5

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

优化效果（源数据相同，查询条件相同，查询结果一致）

jpa 耗时约 4.5s
mybatis 耗时 0.27s

😏 日常数据的增删改查推荐jpa,无需手动进行数据就持久化，复杂统计业务推荐 mybatis,根据需求设计模型，查询需要的数据，提示系统响应速度，降低性能消耗

popo先生的博客

【Frame】jpa+mybatis 组合 数据持久化

# jpa && mybatis

# 实战

# jpa 实现代码

# mybatis 实现代码

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