背景：公司技术框架为 spring boot +jpa，jpa 可以处理正常的 业务，但是对于复杂的统计业务（尤其涉及对个聚合的复杂业务），jpa 过于消耗性能,所以研究在 spring boot 项目中，jpa+mybatis 组合的可能性
jpa && mybatis
- jpa（Java Persistence API）：大大简化数据访问层代码的编码，无需手动维护数据的持久化，是面向对象的
- mybatis：支持定制化 SQL、存储过程以及高级映射，是面向关系的
实战
分页查询用户职位信息 业务需求如下图：
需求效果
入参查询条件涉及聚合：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,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:
- 根据复杂统计业务建模，在数据库 层面 只查询有用数据
- 降低 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 数量只有两条；
优化效果（源数据相同，查询条件相同，查询结果一致）
- jpa 耗时 约 4.5s
- mybatis 耗时 0.27s
:smirk: 日常数据的增删改查 推荐jpa,无需手动进行数据就持久化，复杂统计业务 推荐 mybatis,根据 需求 设计 模型，查询需要的数据，提示系统响应速度，降低性能消耗
