Search code examples
jpacriteriabuilderhuman-readable

JPA criteria builder - in human readable language

I understand the advantages of using a JPA criteria builder above the Java Persistence Query Language.

Is there an easy way to explain how to build up this kind of queries? I need a more human readable explanation to build up my queries, this to have a kind of intuitive approach to query my database.

Example:

SQL:

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

Critera Builder with MetaModel:

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();

This example was based on another question: Complex queries with JPA criteria builder

Solution

  • I don't think there's an more clean way to write that kind of query following the standards and not using an hand wrote JPQL query, anyway out of the standard the are many query builders like: query dsl or Torpedo query or Object Query that allow to write query in a more clean way if that can help :)