Spring Data JPA 连表动态条件查询

多表查询在spring data jpa中有两种实现方式，第一种是利用hibernate的级联查询来实现（使用较为复杂，查询不够灵活），第二种是使用原生sql查询。

JPA原生SQL连表查询

@Repository
public class SqlQueryRepository implements BaseQueryRepository {
    private static final String COUNT_REPLACEMENT_TEMPLATE = "select count(%s) $5$6$7";
    /**
     * 匹配第三组，换成count(*)，目前只用到simple
     */
    private static final String SIMPLE_COUNT_VALUE = "$3*";
    /**
     * 复杂查询，count(主对象)
     */
    private static final String COMPLEX_COUNT_VALUE = "$3$6";

    private static final Pattern COUNT_MATCH;
    private static final String IDENTIFIER = "[\\p{Alnum}._$]+";
    private static final String IDENTIFIER_GROUP = String.format("(%s)", IDENTIFIER);

    @PersistenceContext
    private EntityManager entityManager;

    // (select\s+((distinct )?(.+?)?)\s+)?(from\s+[\p{Alnum}._$]+(?:\s+as)?\s+)([\p{Alnum}._$]+)(.*)
    static {
        StringBuilder builder = new StringBuilder();
        // from as starting delimiter
        builder.append("(?<=from)");
        // at least one space separating
        builder.append("(?: )+");
        // Entity name, can be qualified (any
        builder.append(IDENTIFIER_GROUP);
        // exclude possible "as" keyword
        builder.append("(?: as)*");
        // at least one space separating
        builder.append("(?: )+");
        // the actual alias
        builder.append("(\\w*)");

        builder = new StringBuilder();
        builder.append("(select\\s+((distinct )?(.+?)?)\\s+)?(from\\s+");
        builder.append(IDENTIFIER);
        builder.append("(?:\\s+as)?\\s+)");
        builder.append(IDENTIFIER_GROUP);
        builder.append("(.*)");

        COUNT_MATCH = compile(builder.toString(), CASE_INSENSITIVE);
    }


    /**
     * 封装原生sql分页查询，自动生成countSql
     *
     * @param pageable 分页参数
     * @param querySql 查询sql，不包含排序
     * @param orderSql 排序sql
     * @param paramMap 参数列表
     * @param clazz    返回对象class
     * @param <T>      返回对象
     * @return PageImpl
     */
    @Override
    public <T> Page<T> queryPageable(String querySql, String orderSql, Map<String, Object> paramMap, Pageable pageable, Class<T> clazz) {
        String countSql = createCountQuery(querySql);
        Query countQuery = (Query)this.entityManager.createNativeQuery(countSql);
        Query query = (Query)this.entityManager.createNativeQuery(querySql + orderSql);

        // 设置参数
        if (paramMap != null && paramMap.size() > 0) {
            for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
                countQuery.setParameter(entry.getKey(), entry.getValue());
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }

        BigInteger totalCount = (BigInteger) countQuery.getSingleResult();

        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        // 不使用hibernate转bean，存在数据类型问题
        //query.setResultTransformer(Transformers.aliasToBean(clazz));
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<T> resultList = JSON.parseArray(JSON.toJSONString(query.getResultList(), SerializerFeature.WriteMapNullValue), clazz);

        return new PageImpl<>(resultList, pageable, totalCount.longValue());
    }


    /**
     * 根据查询sql自动生成countSql，正则匹配
     *
     * @param sql 查询sql
     * @return countSql
     */
    public String createCountQuery(String sql) {
        Matcher matcher = COUNT_MATCH.matcher(sql);
        return matcher.replaceFirst(String.format(COUNT_REPLACEMENT_TEMPLATE, SIMPLE_COUNT_VALUE));
    }

}

使用示例

@Repository
public class UserInfoDaoImpl implements UserInfoNativeDao {
    @Autowired
    private BaseQueryRepository baseQueryRepository;


    @Override
    public Page<UserInfo> getUserInfoPageable(String userId, QueryObj queryObj, PageRequest pageable) {
        // 拼接查询sql
        StringBuilder selectSql = new StringBuilder();
        selectSql.append("SELECT rc.xxx xxx, rc.yyy yyy ");
        selectSql.append("FROM table_a rs,table_b rc  ");
        selectSql.append("WHERE  rs.user_id = :userId ");

        HashMap<String, Object> paramMap = new HashMap<>(16);
        paramMap.put("userId", userId);

        StringBuilder whereSql = new StringBuilder();
        // 企业名称模糊筛选
        if (StringUtils.isNotBlank(queryObj.getCompanyName())) {
            whereSql.append(" AND rs.company_name like :companyName ");
            paramMap.put("companyName", "%" + queryObj.getCompanyName() + "%");
        }

        // 风险类型筛选 in
        if (!CollectionUtils.isEmpty(queryObj.getRiskTypes())) {
            whereSql.append(" AND rc.risk_type in :riskType ");
            paramMap.put("riskType",  queryObj.getRiskTypes());
        }

        // 添加排序
        String orderSql = " ORDER BY xxx desc, yyy desc ";
        String querySql = selectSql.append(whereSql).toString();

        return baseQueryRepository.queryPageable(querySql, orderSql, paramMap, pageable, UserInfo.class);
    }
}