662 words

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

#1
2011-05-10 21:39

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

chris
chris's picture
Joined on 2011-04-29
User Post #12
#2
2011-05-10 23:09

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #260
#3
2011-05-11 05:46

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

chris
chris's picture
Joined on 2011-04-29
User Post #13
#4
2011-05-11 23:55

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #262
#5
2011-05-12 08:29

great, thanks

chris
chris's picture
Joined on 2011-04-29
User Post #14
#6
2011-05-13 14:55

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!

FastModel
FastModel's picture
Joined on 2011-02-07
User Post #15
#7
2011-05-14 21:01

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.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #268

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel