jpa多表联查动态_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 返回对象

* @return PageImpl

*/

@Override

public Page queryPageable(String querySql, String orderSql, Map paramMap, Pageable pageable, Class 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 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 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 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 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);

}

}