使用JPA Criteria构建器进行复杂查询

问题描述

有人可以建议我如何使用JPA Criteria构建器API构建以下查询吗？

Can someone suggest me how to build up the following query using JPA Criteria builder API?

SELECT id,status,created_at from transactions where status='1'
   and currency='USD' and appId='123' order by id

如果我能找到一个基于使用元模型类或任何其他方式作为 Map< String，String> 给出的参数动态创建的解决方案，那会更好。

It's better if I can find a solution which creates dynamically based on the parameters given as a Map<String,String> using metamodel classes or any other way.

推荐答案

就像这样（没有元模型）：

It's like this (without metamodel):

Map<String, Object> params = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Transaction> r = cq.from(Transaction.class);

Predicate p= cb.conjunction();
for (Map.Entry<String, Object> param: params.entrySet())
    p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));

cq.multiselect(r.get("id"), r.get("status"), r.get("created_at"))
    .where(p)
    .orderBy(cb.asc(r.get("id")));

List<Tuple> result = em.createQuery(cq).getResultList();

或者使用元模型（类型安全，但有点罗嗦）：

Or with metamodel (typesafe, but a bit wordy):

Map<SingularAttribute<Transaction, ?>, Object> params = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Transaction> r = cq.from(Transaction.class);

Predicate p= cb.conjunction();
for (Map.Entry<SingularAttribute<Transaction, ?>, Object> param: params.entrySet())
    p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));

cq.multiselect(r.get(Transaction_.id), r.get(Transaction_.status),
          r.get(Transaction_.created_at))
    .where(p)
    .orderBy(cb.asc(r.get(Transaction_.id)));

List<Tuple> result = em.createQuery(cq).getResultList();

这篇关于使用JPA Criteria构建器进行复杂查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！