how to query by properties of onetomany relations? (Error 990)

the following code fails

em.createQuery("select f from Foo f where f.bars.name = ?1", Foo.class).setParameter(1, barName)

stacktrace:

[ObjectDB 2.2.4] Unexpected exception (Error 990)
  Generated by Java HotSpot(TM) 64-Bit Server VM 1.6.0_23 (on Windows 7 6.1).
Please report this error on http://www.objectdb.com/database/issue/new
com.objectdb.o.InternalException: java.lang.ClassCastException: com.objectdb.o.CLT cannot be cast to com.objectdb.o.UTY
java.lang.ClassCastException: com.objectdb.o.CLT cannot be cast to com.objectdb.o.UTY
 at com.objectdb.o.QNF.B(QNF.java:878)
 at com.objectdb.o.QNF.y(QNF.java:745)
 at com.objectdb.o.QNF.j(QNF.java:246)
 at com.objectdb.o.QNF.p(QNF.java:480)
 at com.objectdb.o.QNF.j(QNF.java:179)
 at com.objectdb.o.QNF.i(QNF.java:126)
 at com.objectdb.o.QRC.y(QRC.java:287)
 at com.objectdb.o.QRC.v(QRC.java:206)
 at com.objectdb.o.QRC.u(QRC.java:163)
 at com.objectdb.o.QRM.UR(QRM.java:242)
 at com.objectdb.o.MST.UR(MST.java:878)
 at com.objectdb.o.WRA.UR(WRA.java:286)
 at com.objectdb.o.WSM.UR(WSM.java:113)
 at com.objectdb.o.QRR.g(QRR.java:220)
 at com.objectdb.o.QRR.b(QRR.java:143)
 at com.objectdb.jpa.JpaQuery.getSingleResult(JpaQuery.java:610)

f.bars is:

@OneToMany
private Set<Bar> bars;

bars.name is just a String

in my test database i have one Foo entity with one Bar relation

thanks in advanced

Sorry about the error message - it should be replaced by a more descriptive error message in future versions.

However, this query is invalid. You cannot navigate through collections in JPQL directly. You have to use JOIN to define a variable that will iterate over the collection elements.

This specific query should be rewritten as follows:

select f from Foo f join f.bars b where b.name = ?1

More details are provided on the FROM page of the ObjectDB manual.

i just woke up after i a short night and remembered sql joins :)

in my last projects i have been using mongodb and such a query was possible for embedded objects.

i adapted my query and it works like expected.

thanks for the hint

Support of direct navigation from collections (with no JOIN) was just added in build 2.2.4_02. The following program demonstrates how it works:

public final class Test {

    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
        Foo foo = new Foo();
        foo.bars.add(new Bar("aaa"));
        em.persist(foo);
        em.getTransaction().commit();

        Query query = em.createQuery(
            "select count(f) from Foo f where f.bars.name = :name");
        query.setParameter("name", "aaa");
        System.out.println("Count: " + query.getSingleResult());

        em.close();
        emf.close();
    }
   
    @Entity
    public static final class Foo {
        @OneToMany(cascade=CascadeType.PERSIST)
        private Set<Bar> bars = new HashSet<Bar>();
    }

    @Entity
    public static final class Bar {
        private String name;
        Bar(String name) {
            this.name = name;
        }
    }   
}

This is of course an extension to the standard JPQL. The following 2 queries are now equivalent:

select count(f) from Foo f where f.bars.name = :name

select count(f) from Foo f join f.bars b where b.name = :name

The second form is a bit longer but it is JPA portable.

ObjectDB can enforce using standard JPQL by setting the following query hint:

em.setProperty("objectdb.query-language", "JPQL");

The default query language is ODBQL, which is a union of JPQL and JDOQL + ObjectDB extensions. When the query language is set to JPQL using a hint the following exception is thrown on attempt to use the new navigation from collection syntax:

Navigation from 'java.util.Set<test.Test$Bar>' through 'name' is invalid (error 763)
 at com.objectdb.jpa.JpaQuery.getSingleResult(JpaQuery.java:620)

The query hint is set in the code above for one EntityManager but it can also be set in persitencece.xml and in other scopes. More information is provided on the Query Setting and Tuning page.

great, thanks

I'd like to know more about this. It looks very useful.

1. Are there any restrictions on the depth of nested collections?

ie. "SELECT count(f) FROM Foo f WHERE f.bars.morebars.name = :name"

2. Can this also work if the objects in the collection are marked @Embedded instead of @Entity?

Thanks!

Regarding the new syntax - there is no restriction on the depth of nested collections and embedded collections are also supported.

But please notice that this is only a syntactic sugar. Every navigation through collection is replaced internally by defining an implicit JOIN variable. Particularly the following query:

SELECT f FROM Foo f
WHERE f.bars.name = :n AND f.bars.value = :v

is equivalent to the following valid JPQL query:

SELECT f FROM Foo f JOIN f.bars b1 JOIN f.bars b2
WHERE b1.name = :n AND b2.value = :v

and not:

SELECT f FROM Foo f JOIN f.bars b
WHERE b.name = :n AND b.value = :v

i.e. every occurrence of a collection navigation defines a separate JOIN variable, so this should be taken into consideration (since the meaning of the above JPQL queries is different).

The other questions regarding collections of embedded objects are unrelated to the new syntax, so they are moved to a new thread.