How to rewrite this query using JPA Criteria Query?

jpa criteriaquery
Vlada picture Vlada · Apr 8, 2011 · Viewed 10.9k times · Source 
public class Entity {
private boolean suspended;

private String code;

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
@DateTimeFormat(style = "S-")
private DateTime validFrom;

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime")
@DateTimeFormat(style = "S-")
private DateTime validTill;
}

Meaning of query: "Is the code unique in given time validity?"

How to rewrite this query:

select count(o) from Entity o 
where o <> :this and o.code = :code and (
  (o.validFrom between :validFrom and :validTill and not o.suspended) or
  (o.validTill between :validFrom and :validTill and not o.suspended) or 
  (:validFrom between o.validFrom and o.validTill and not o.suspended)
)

Answer

Laures picture Laures · Apr 8, 2011 
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Entity> cQuery = builder.createQuery(Entity.class);
Root<Entity> root = cQuery.from(Entity.class);

Predicate pValid1 = builder.between(root.get("ValidFrom"), validFrom, validTill);
Predicate pValid2 = builder.between(root.get("ValidTill"), validFrom, validTill);
Predicate pValid3 = builder.and(builder.lessThan(root.get("validTill"), balidFrom),builder.greaterThan(root.get("ValidFrom",validfrom)));

Predicate pSuspend = builder.not(root.get("Suspended"),true);

ExecutableQuery qFinal = em.createQuery(cQuery.select(root).where(builder.and(pSuspend,builder.or(pValid1,pvalid2,pvalid3))))

It should work this way. It should be possible to adapt the metadata object, although I didn't do that yet.

Related questions

JPA Query selecting only specific columns without using Criteria Query?

Is it possible to select, say, only properties A and B from an object using a JPA query without using criteria queries? To select all properties I'd just do something like: SELECT i FROM ObjectName i WHERE i.id = 10 But …

Read More
JPA Criteria query group by uses only the id

This is a sample entity: public class Account{ @Id Long id Double remaining; @ManyToOne AccountType type } public class AccountType{ @Id Long id; String name; } Now i create a criteria query with Join as follwing : CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder(); CriteriaQuery criteriaQuery = …

Read More
Condition left join in CriteriaQuery

Hi everybody I am trying to do this in CriteriaQuery, I was searching so long but I can't found anything to do it, someone can help me? SELECT b.name FROM Empl a LEFT OUTER JOIN Deplo b ON (a.…

Read More