1070 words

IN expression in a Criteria Query

#1
2013-04-23 14:23

Hello,

I am trying to migrate my Spring project from Hibernate to ObjectDB. On ObjectDB page, it is written that ObjectDB is 100% compatible with JPA. However, I have problem with this JPQL query:

SELECT e FROM Employee e WHERE e.present = false AND NOT EXISTS
(SELECT c FROM Computer c WHERE c.employeeEntity = e)
ORDER BY e.name

with Hibernate, there was no problem with this query, but ObjectDB throws exception:

com.objectdb.o.UserException: Unexpected query token 'EXISTS'

Second problem is with Criteria Language. This code:

In<Employee> inExpression = cb.in(root.get(Computer_.employeeEntity));
for (Long id : emplIds) {
    Employee e = new Employee(id);
    inExpression = inExpression.value(e);
}
list.add(inExpression);

was working with Hibernate, with ObjectDB it is throwing:

com.objectdb.o.UserException: Unexpected query token ':l2'

Does anybody have any idea how to solve these problems?

Milan

mimac
mimac's picture
Joined on 2013-04-23
User Post #1
#2
2013-04-23 22:17

> On ObjectDB page, it is written that ObjectDB is 100% compatible with JPA.

Where did you see that statement? Actually there are several JPA features that are currently not supported by ObjectDB. They are listed in the issue tracking system, and unfortunately subqueries is one of them.

Regarding the criteria query, it is unclear where exactly the exception is thrown. It may be an ObjectDB issue or limitation, or a non standard JPA Hibernate extension. What happens if you replace Computer_.employeeEntity with "employeeEntity"? Currently you cannot generate Computer_ with ObjectDB, and maybe the class that Hibernate generates doesn't work with ObjectDB.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,509
#3
2013-04-24 12:32

I couldn't find the statement any more, I probbably saw it on some unreliable page, sorry about that.

I was generating metamodel by myself. I replaced it with "employeeEntity", but the result is the same. Here is complete stack trace:

com.objectdb.o.UserException: Unexpected query token ':l2'
com.objectdb.o.MSG.d(MSG.java:61)
com.objectdb.o.TKN.J(TKN.java:765)
com.objectdb.o.QNF.B(QNF.java:894)
com.objectdb.o.QNF.I(QNF.java:1294)
com.objectdb.o.QNF.k(QNF.java:315)
com.objectdb.o.QNF.H(QNF.java:1270)
com.objectdb.o.QNF.k(QNF.java:210)
com.objectdb.o.QNF.t(QNF.java:611)
com.objectdb.o.QNF.t(QNF.java:605)
com.objectdb.o.QNF.k(QNF.java:218)
com.objectdb.o.QNF.j(QNF.java:135)
com.objectdb.o.QRC.z(QRC.java:321)
com.objectdb.o.QRC.v(QRC.java:212)
com.objectdb.o.QRC.u(QRC.java:166)
com.objectdb.o.QRM.U6(QRM.java:250)
com.objectdb.o.MST.U6(MST.java:933)
com.objectdb.o.WRA.U6(WRA.java:291)
com.objectdb.o.WSM.U6(WSM.java:113)
com.objectdb.o.STC.r(STC.java:449)
com.objectdb.o.SHN.aj(SHN.java:489)
com.objectdb.o.SHN.K(SHN.java:156)
com.objectdb.o.HND.run(HND.java:132)
java.lang.Thread.run(Unknown Source)

I also found another issue with this query:

SELECT a FROM Account a GROUP BY a.code HAVING COUNT(a) < 2

It throws following exception:

com.objectdb.o.UserException: Invalid result expression 'a' for an aggregate query
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.QNF.l(QNF.java:338)
at com.objectdb.o.QNF.n(QNF.java:399)
at com.objectdb.o.QNF.k(QNF.java:170)
at com.objectdb.o.QRC.G(QRC.java:608)
at com.objectdb.o.QRC.F(QRC.java:580)
at com.objectdb.o.QRC.E(QRC.java:529)
at com.objectdb.o.QRC.v(QRC.java:214)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U6(QRM.java:250)
at com.objectdb.o.MST.U6(MST.java:933)
at com.objectdb.o.WRA.U6(WRA.java:291)
at com.objectdb.o.WSM.U6(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:449)
at com.objectdb.o.SHN.aj(SHN.java:489)
at com.objectdb.o.SHN.K(SHN.java:156)
at com.objectdb.o.HND.run(HND.java:132)
at java.lang.Thread.run(Unknown Source)
mimac
mimac's picture
Joined on 2013-04-23
User Post #2
#4
2013-04-25 08:50

Regarding the criteria query issue, please provide the complete query string (you should be able to see it in the log, if you enable logging as explained in this forum thread, or by converting the criteria query into a string with toString).

Regarding the GROUP BY query:

SELECT a FROM Account a GROUP BY a.code HAVING COUNT(a) < 2

This is an invalid query. In a grouping query you can only select expressions of the groups, as stated in the manual:

The SELECT clause can only use properties of the groups, which include:

  • The properties that are used for grouping (each group has unique value combination).
  • Aggregate calculations (count, sum, avg, max, min) that are carried out on all the objects (or the object tuples) in the group.
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,510
#5
2013-04-29 11:20

Bellow is the XML for criteria query from logs:

<token name="QUERY" code="67" postion="0:0">
    <token name="SELECT" code="132" postion="0:6">
        <token name="DISTINCT" code="94" postion="7:15" />
        <token name="COUNT" code="88" postion="16:21">
            <token name="(" code="14" postion="21:22">
                <token name="$1" code="1" postion="22:24" />
                <token name=")" code="15" postion="24:25" />
            </token>
        </token>
    </token>
    <token name="FROM" code="103" postion="26:30">
        <token name="$1" code="1" postion="46:48">
            <token name="Computer" code="1" postion="31:45" />
        </token>
    </token>
    <token name="WHERE" code="150" postion="49:54">
        <token name="IN" code="106" postion="99:101">
            <token name="." code="6" postion="83:84">
                <token name="$1" code="1" postion="81:83" />
                <token name="employeeEntity" code="1" postion="84:98" />
            </token>
            <token name="(" code="14" postion="102:103">
                <token name=":" code="7" postion="103:104">
                    <token name="l2" code="1" postion="104:106" />
                </token>
                <token name=":" code="7" postion="107:108">
                    <token name="l3" code="1" postion="108:110" />
                </token>
                <token name=":" code="7" postion="111:112">
                    <token name="l4" code="1" postion="112:114" />
                </token>
                <token name=")" code="15" postion="114:115" />
            </token>
        </token>
    </token>
</token>
mimac
mimac's picture
Joined on 2013-04-23
User Post #3
#6
2013-04-29 12:02

I just found out intereting thing. This query:

SELECT a FROM Account a WHERE a.code IN (:codes) ORDER BY a.code

throws exception Unexpected query token ':l2'. When I remove the brackets:

SELECT a FROM Account a WHERE a.code IN :codes ORDER BY a.code

it works.

I think it should be valid with brackets, Hibernate has no problem with it. Can it be somehow connected to the problem with IN in criteria query above?

mimac
mimac's picture
Joined on 2013-04-23
User Post #4
#7
2013-04-29 20:51

Good catch. Both forms (with and without parenthesis) should be supported, and apparently this is a regression in version 2.5.0, as a result of adding support of IN (literal-list) expressions.

Please try build 2.5.0_02 that should fix it, and check whether or not the criteria query problem is solved.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,512
#8
2013-04-30 13:08

I have just tryed 2.5.0_02, but the problem with criteria query is still there. Log shows that this query:

<queryRequest query="SELECT DISTINCT COUNT($1) FROM Computer $1 WHERE (($1.workplace.id=:l1) AND $1.employeeEntity.id IN (:l2,:l3,:l4))" args="{l1->5,l2->37,l3->5,l4->38}" transactionId="-1" />

throws this exception:

com.objectdb.o.UserException: Unexpected query token ':l3'
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.TKN.J(TKN.java:765)
at com.objectdb.o.QNF.B(QNF.java:904)
at com.objectdb.o.QNF.I(QNF.java:1295)
at com.objectdb.o.QNF.k(QNF.java:315)
at com.objectdb.o.QNF.H(QNF.java:1271)
at com.objectdb.o.QNF.k(QNF.java:210)
at com.objectdb.o.QNF.t(QNF.java:611)
at com.objectdb.o.QNF.t(QNF.java:605)
at com.objectdb.o.QNF.k(QNF.java:218)
at com.objectdb.o.QNF.j(QNF.java:135)
at com.objectdb.o.QRC.z(QRC.java:321)
at com.objectdb.o.QRC.v(QRC.java:212)
at com.objectdb.o.QRC.u(QRC.java:166)
at com.objectdb.o.QRM.U6(QRM.java:250)
at com.objectdb.o.MST.U6(MST.java:933)
at com.objectdb.o.WRA.U6(WRA.java:293)
at com.objectdb.o.WSM.U6(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:450)
at com.objectdb.o.SHN.aj(SHN.java:489)
at com.objectdb.o.SHN.K(SHN.java:156)
at com.objectdb.o.HND.run(HND.java:132)
at java.lang.Thread.run(Unknown Source)
mimac
mimac's picture
Joined on 2013-04-23
User Post #5
#9
2013-04-30 14:25

OK. Apparently the problem is clear now, and we are working on a quick fix.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,515
#10
2013-05-02 02:34

Please try build 2.5.0_04, which seems to fix the criteria query issue.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,520
#11
2013-10-02 10:29

Hi,

I am seeing the same issue of "IN (:bindVar)" generates a syntax error in version 2.5.3, but "IN :bindVar"  - without the parenthesis works ok. Looks like a regression ... ?

Thanks

andrewv
andrewv's picture
Joined on 2013-09-03
User Post #6
#12
2013-10-04 01:28

A test that was written for the original report, still works with 2.5.3:

package test;
 
import java.util.*;
 
import javax.persistence.*;
import javax.persistence.criteria.*;
 
 
@Entity
public class F602 {
 
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop"
            );
 
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.persist(new MyEntity("a"));
        em.persist(new MyEntity("b"));
        em.persist(new MyEntity("c"));
        em.getTransaction().commit();
        em.close();
 
        em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<MyEntity> q = cb.createQuery(MyEntity.class);
        Root<MyEntity> root = q.from(MyEntity.class);
        Path<String> value = root.get("str");
        Predicate predicate = value.in("a", "c", "e");
        q.where(predicate);
        q.select(root);
        TypedQuery<MyEntity> query = em.createQuery(q);
        List<MyEntity> results = query.getResultList();
        System.out.println(results);
        em.close();
 
        emf.close();
    }
 
    @Entity
    public static class MyEntity {
        String str;
        public MyEntity() {}
        public MyEntity(String str) { this.str = str; }
        @Override
        public String toString() { return str; }
    }
}

Please demonstrate the issue that you have with a test case, or change this example to show it.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,705
#13
2013-10-04 03:41

Hi,

The test case you have posted uses the CriteriaBuilder API. The problem I was getting, was a syntax error when the "IN (:bindVar)" was used within a JPQL query string, passed to EntityManager.createQuery() in version 2.5.3.

Thanks

andrewv
andrewv's picture
Joined on 2013-09-03
User Post #7
#14
2013-10-04 07:58

The original post was on criteria queries, but it is the same with JPQL, since criteria queries are converted into JPQL query strings. Anyway, here is a revised test with a JPQL query string, that still works:

package test;
 
import java.util.*;
 
import javax.persistence.*;
import javax.persistence.criteria.*;
 
 
@Entity
public class F602 {
 
    public static void main(String[] args) {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop"
            );
 
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        em.persist(new MyEntity("a"));
        em.persist(new MyEntity("b"));
        em.persist(new MyEntity("c"));
        em.getTransaction().commit();
        em.close();
 
        em = emf.createEntityManager();
        Query query =
            em.createQuery("SELECT e FROM MyEntity e WHERE e.str IN (:bindVar)");
        query.setParameter("bindVar", "a");
        List<MyEntity> results = query.getResultList();
        System.out.println(results);
        em.close();
 
        emf.close();
    }
 
    @Entity
    public static class MyEntity {
        String str;
        public MyEntity() {}
        public MyEntity(String str) { this.str = str; }
        @Override
        public String toString() { return str; }
    }
}
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,708

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