Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach
CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach [message #682170] Fri, 10 June 2011 10:48 Go to next message
gwf is currently offline 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 #683414 is a reply to message #682170] Mon, 13 June 2011 13:29 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I think you need to use an outerjoin for this.

Join<Thing, Set<Thing.Type>> typeJoin = root.join("typeSet", JoinType.LEFT);

You could also define the subquery yourself instead of using the isEmpty API.



James : Wiki : Book : Blog : Twitter
(no subject) [message #683439 is a reply to message #682170] Mon, 13 June 2011 13:29 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
I think you need to use an outerjoin for this.

Join<Thing, Set<Thing.Type>> typeJoin = root.join("typeSet", JoinType.LEFT);

You could also define the subquery yourself instead of using the isEmpty API.


--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Re: CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach [message #683645 is a reply to message #682170] Tue, 14 June 2011 03:13 Go to previous messageGo to next message
gwf is currently offline 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 04:50]

Report message to a moderator

Re: CriteriaBuilder.isEmpty on an ElementCollection vs. JPQL approach [message #683825 is a reply to message #683645] Tue, 14 June 2011 09:30 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You should not be getting a null-pointer or stack overflow, please log a bug for these and include as much detail as possible.


James : Wiki : Book : Blog : Twitter
Previous Topic:Catching Internal Exceptions
Next Topic:Composite Key and IN Operator
Goto Forum:
  


Current Time: Mon Jul 28 20:40:39 EDT 2014

Powered by FUDForum. Page generated in 0.02773 seconds