CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach [message #682170] |
Fri, 10 June 2011 14:48 |
gwf Messages: 2 Registered: June 2011 |
Junior Member |
|
|
I'm trying to do a simple query using the JPA2 criteria API on the following class(es):
// a lot of imports
@Entity
public class Thing {
enum Type { FIRST, SECOND, THIRD };
@SequenceGenerator(name = "Thing_SeqGen", sequenceName = "Thing_Id_Seq", initialValue = 1000)
@Id
@GeneratedValue(generator = "Thing_SeqGen")
private int id;
private String name = "name";
@Enumerated(EnumType.STRING)
@ElementCollection(targetClass = Thing.Type.class)
@CollectionTable(name = "TYPES", joinColumns = { @JoinColumn(referencedColumnName = "ID", name = "TYPE_ID") })
private Set<Thing.Type> typeSet = new HashSet<Thing.Type>();
public static void main(final String[] args) {
new Thing().start();
}
public void start() {
final Thing firstThing = new Thing();
firstThing.setName("First one");
firstThing.setTypeSet(EnumSet.of(Thing.Type.FIRST));
final Thing firstAndSecondThing = new Thing();
firstAndSecondThing.setName("Test2");
firstAndSecondThing.setTypeSet(EnumSet.of(Thing.Type.FIRST, Thing.Type.SECOND));
final Thing bareThing = new Thing();
bareThing.setName("Test3");
final EntityManagerFactory emf = Persistence.createEntityManagerFactory("sandbox");
final EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
em.persist(firstThing);
em.persist(firstAndSecondThing);
em.persist(bareThing);
em.getTransaction().commit();
em.getTransaction().begin();
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Thing> c = cb.createQuery(Thing.class);
final Root<Thing> root = c.from(Thing.class);
final Join<Thing, Set<Thing.Type>> typeJoin = root.join("typeSet");
c.select(root).distinct(true).where(cb.isEmpty(typeJoin));
final List<Thing> results = em.createQuery(c).getResultList();
em.getTransaction().commit();
}
// getter/setter methods omitted
}
What I want to query: Find all things which has no typeset.
The JPQL which does the job is:
select t from Thing t where t.typeSet is empty
The JPQL query returns one result which is expected.
The criteria query returns no results.
The SQL the CriteriaBuilder created:
SELECT DISTINCT t0.ID, t0.NAME FROM THING t0, TYPES t1 WHERE (((SELECT COUNT(t2.ID) FROM THING t2 WHERE (t1.TYPE_ID = t0.ID)) = 0) **AND (t1.TYPE_ID = t0.ID)**)
The last theta-join (marked **) kills it all. And I have no idea why the table THING is specified twice (THING to, THING t1).
Obviously I'm doing wrong. But I have no clue what's the fault.
Any hint and/or help is highly appreciated.
|
|
|
|
|
Re: CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach [message #683645 is a reply to message #682170] |
Tue, 14 June 2011 07:13 |
gwf Messages: 2 Registered: June 2011 |
Junior Member |
|
|
Thanks for your reply James!
Actually I started with an outer join. But with EclipseLink 2.1 I got an NPE, with 2.2 a StackOverflowError.
I would like your second idea of a workaround if I knew there's a known issue with isEmpty. Otherwise I would keep this idea as a last resort.
Addendum:
For those who are interested in the subquery solution:
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Thing> c = cb.createQuery(Thing.class);
final Root<Thing> root = c.from(Thing.class);
final Join<Thing, Set<Thing.Type>> typeJoin = root.join("typeSet");
final Subquery<Long> subQuery = c.subquery(Long.class);
final Root<Thing> thingy = subQuery.from(Thing.class);
final Predicate predicate = cb.equal(thingy, typeJoin);
subQuery.select(cb.count(thingy)).where(predicate);
c.select(root).where(cb.equal(subQuery, 0));
[Updated on: Tue, 14 June 2011 08:50] Report message to a moderator
|
|
|
|
Powered by
FUDForum. Page generated in 0.03374 seconds