2
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)
)
Vlada
  • 559
  • 2
  • 11
  • 27

1 Answers1

4
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.

ymajoros
  • 2,454
  • 3
  • 34
  • 60
Laures
  • 5,389
  • 11
  • 50
  • 76
  • I get error 'Cannot resolve method between(javax.persistence.criteria.Path, java.util.Date, java.util.Date)'. – Vlada Apr 11 '11 at 14:05
  • 1
    @Vlada [> Predicate between(Expression extends Y> v, Y x, Y y)](http://download.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html#between%28javax.persistence.criteria.Expression,%20Y,%20Y%29) – Laures Apr 12 '11 at 06:30
  • Yes, I know: javax.persistence.criteria.CriteriaBuilder but I get this error at Predicate pValid1, pValid2, pValid3 and I don't know why. Did you try to compile your example? – Vlada Apr 12 '11 at 08:03
  • 3
    @Vlada looked more closely. either use use the static metadata feature or you use the generic classification of root.get `root.get("...` – Laures Apr 12 '11 at 10:11
  • Thank you, now I can compile it. (I use root.get("...) – Vlada Apr 12 '11 at 11:31