310 words

JPQL IN Operator

#1
2011-09-08 14:54

How can I use the standard JPQL IN operator with ObjectDB?

This JPQL query works fine with EclipseLink / postgres but throws an exception in objectDB:

SELECT DISTINCT dv.creationtime
FROM DataValue dv  
WHERE dv.capabilityId.capabilityId  ==> IN <==  :capabilities  
AND dv.creationtime >= :earliest   
AND dv.creationtime <= :latest   
ORDER BY dv.creationtime ASC

[ObjectDB 2.2.9] javax.persistence.PersistenceException
Unexpected query token 'in' (error 752)

freddieFishCake
freddieFishCake's picture
Joined on 2011-08-30
User Post #6
#2
2011-09-08 16:36

The IN operator is currently not supported but you can use MEMBER OF instead.

See more details and an example in this forum thread.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #597
#3
2011-09-08 17:16

thanks for suggestion, I modified the queries to read e.g. as follows:

@NamedQuery(name = "DataValue.findDistinctCreationtimesWithLimitDesc",
    query = " select distinct dv.creationtime from DataValue dv"
    + "   where dv.capabilityId.capabilityId member of :capabilities"
    + "   and dv.creationtime >= :earliest"
    + "   and dv.creationtime <= :latest "
    + "   order by dv.creationtime DESC"),

this seems to work in ObjectDB, but fails with EclipseLink .. I really need a portable solution, would you class that as an EclipseLink bug?

Caused by: Exception [EclipseLink-8025] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query [DataValue.findDistinctCreationtimesWithLimitDesc:  select distinct dv.creationtime from DataValue dv   where dv.capabilityId.capabilityId member of :capabilities   and dv.creationtime >= :earliest   and dv.creationtime <= :latest    order by dv.creationtime DESC], line 1, column 98: unexpected token [:capabilities].

 

freddieFishCake
freddieFishCake's picture
Joined on 2011-08-30
User Post #7
#4
2011-09-08 17:38

This is not an EclipseLink bug since JPA implementations are not required to support a parameter on the right side of a MEMBER OF operator (just collection navigation paths).

I will check if a quick fix to support the in operator is possible.

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

A new build (2.2.9_08) adds support of the IN operator (currently as a synonym of MEMBER OF).

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #599
#6
2011-09-08 21:14

wow, awesome support, many thanks I will give this a spin ASAP ..

freddieFishCake
freddieFishCake's picture
Joined on 2011-08-30
User Post #8
#7
2011-09-08 21:14

great news, 2.2.9_08 now passes all the jUnit tests for my db-lib module,

all static metadata loaded from xml files, so now I can get down to some serious bench marking and volume testing .. thanks again for great support guys ;-)

 

 

freddieFishCake
freddieFishCake's picture
Joined on 2011-08-30
User Post #9

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