JPA exists查询，使用Specification或原生Sql两种方式

JPA的Specification方式查询单表是十分方便的，但是如果需要使用多表连接查询时，项目里没有找到可供参考的样本。

需求：包含多查询条件（条件为空时不参与查询），查询语句包含了exists从表，且需要分页的典型页面查询场景。

在数据库中编写好SQL语句，样例如下

select * from resolve_assign t1 --- 主表
  where t1.assign_status = 2 and not exists
  (select t2.id from budget_adjust t2 where t2.publish_state = 0 and t1.id = t2.resolve_assign_id )  --- 从表

使用JPA的Specification的单表查询，在这里仅查询主表作为基础

Pageable pageable = PageRequest.of(pageNum, pageSize);
Page<ResolveAssign> page = hibernateDao.findAll(getQuery(condition), pageable);

// 拼接Specification
private Specification<ResolveAssign> getQuery(ResolveAssignVO condition) {
     return (Specification<ResolveAssign>) (root, query, criteriaBuilder) -> {
        Predicate predicate = criteriaBuilder.conjunction();
        
        if(!StringUtils.isEmpty(condition.getName())){
            predicate.getExpressions().add(criteriaBuilder.like(roo.get("name"), "%"+condition.getName()+"%"));
        // 查询条件等等
        }

        Order sort = criteriaBuilder.asc(root.get("sort"));
        return query.orderBy(sort).where(predicate).getRestriction();
    }
}

当这种简单的单表查询不满足要求后，需要有新的解决方案。

方案一：使用原生sql

优点：对于经常操作数据库或从mybatis项目经验的开发者来说，直接使用sql比较直观

缺点：查询出的结果key是数据库命名（往往由下划线区分单词名，如project_name）而实体类需要驼峰命名，并且如果项目前端一直使用驼峰字段取值，不作转换会导致取不到值

// 由于项目里没有采用过这种方式，因此这里是临时写法，需要整合的时候需要进行封装处理
StringBuilder sql = new StringBuilder("select t1.* from resolve_assign t1");
StringBuilder sqlCount = new StringBuilder("select count(t1.id) from resolve_assign t1");

StringBuilder where = new StringBuilder();
where.append("where t1.assign_status = 2 and not exists ");
where.append("(select t2.id from budget_adjust t2 where t2.publish_state = 0 and t1.id = t2.resolve_assign_id ) ");

if(StringUtils.isNotEmpty(condition.getName())) {
    where.append(" and t1.name like :name");
}
// 其他查询条件省略

Query query = entityManager.createNativeQuery(sql.append(where).toString());
Query queryCount = entityManager.createNativeQuery(sqlCount.append(where).toString());
if(StringUtils.isNotEmpty(condition.getName())) {
    query.setParameter("name", "%"+conditon.getName()+"%");
}

query.setFirstResult((pageNum - 1) * pageSize);
query.setMaxResults(pageSize);

List<Map> list = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList();
// 这里查出来的是key为数组库字段名，即带下划线的形式，需要转换为驼峰
List<Map> camelList = (List<Map>)list.stream.map(MapUtil::toCamelCaseMap).collect(Collectors.toList());
Long count = ((BigInteger)queryCount.getSingleResult()).longValue();

方案二：改造现有的多表查询Specification

优点：不需要编写sql，也不需要进行key驼峰转换，无须使用map转对象或调整前端接收值的格式调整，更重要的是符合代码规范

缺点：对于更复杂的sql语句来说难度较高，且不如sql直观

Pageable pageable = PageRequest.of(pageNum, pageSize);
Page<ResolveAssign> page = hibernateDao.findAll(getQuery(condition), pageable);

// 拼接Specification
private Specification<ResolveAssign> getQuery(ResolveAssignVO condition) {
     return (Specification<ResolveAssign>) (root, query, criteriaBuilder) -> {
        Predicate predicate = criteriaBuilder.conjunction();
        
        if(!StringUtils.isEmpty(condition.getName())){
            predicate.getExpressions().add(criteriaBuilder.like(roo.get("name"), "%"+condition.getName()+"%"));
        // 查询条件等等
        }

        // 开始子查询
        Subquery<BudgetAdjust> subQuery = query.subquery(BudgetAdjust.class);
        Root<BudgetAdjust> subRoot = subQuery.from(BudgetAdjust.class);
        
        // 从表查询条件
        Predicate sub_state = criteriaBuilder.equal(subRoot.get("publishState"), 0);
        // 从表.resolve_assign_id = 主表.id
        Predicate sub_equals_main = criteriaBuilder.equals(root.get("id"), subRoot.get("resolveAssignId"));
        subQuery.where(sub_state, sub_equals_main);
        
        // 从表查询的字段，这个必须定义
        subQuery.select(subRoot.get("resolveAssignId"));
        
        // not exists
        Predicate notExists = criteriaBuilder.not(criteriaBuilder.exists(subQuery));
        // 主表查询添加上这个not exists子查询
        predicate.getExpressions().add(notExists);

        Order sort = criteriaBuilder.asc(root.get("sort"));
        query.orderBy(sort).where(predicate);
        return query.getRestriction();
    }
}

如果查询sql不是可变的，可以在Dao层定义时，使用@Query注解

@Query(nativeQuery=true, value="select * from resolve_assign where id = ?1")
public List<Map> findById(String id);