457 words

IN operator syntax

#1
2013-03-20 15:30

Hi,

what is the correct syntax for IN operator when list of literals ia used instead of parameter, e.g.

SELECT FROM Info i WHERE i.STATUS IN (1,4) ?

(Suppose status is int.)

I always get this exception:

SELECT FROM Info i WHERE i.status IN (1 ==> , <==  4)
javax.persistence.PersistenceException
Unexpected query token ',' (closing ')' is missing) (error 752)

 

Query with only one item in list works fine:

SELECT FROM Info i WHERE i.STATUS IN (1)

The same behavior is when using MEMBER OF operator.

Thanks.

Marta

 

 

package test;
 
import java.util.*;
import javax.persistence.*;
 
public final class MyTestCase {
 
    public static void main(String[] args)  {
        EntityManagerFactory emf =
                Persistence.createEntityManagerFactory(
                        "objectdb:$objectdb/db/test.tmp;drop");
        EntityManager em = emf.createEntityManager();
 
        em.getTransaction().begin();
        MyEntity e = new MyEntity(1);
        em.persist(e);
        em.getTransaction().commit();
 
        Query query = em.createQuery("SELECT FROM MyEntity e WHERE e.num IN (4,5)");
        List resultList = query.getResultList();
        System.out.println(resultList);
 
        em.close();
        emf.close();
    }
 
    @Entity
    public static class MyEntity {
        private int num;
        MyEntity(int num) {
            this.num=num;
        }
        @Override
        public String toString() {
            return ""+num;
        }
    }
}
Maruta
Maruta's picture
Joined on 2013-03-20
User Post #1
#2
2013-03-20 16:06

Your query is fine, but list literal is one of the few JPA query elements that are not supported by ObjectDB yet.

You can replace the list with a collection parameter, i.e. replace:

    Query query = em.createQuery("SELECT FROM MyEntity e WHERE e.num IN (4,5)");

with:

    Query query = em.createQuery("SELECT FROM MyEntity e WHERE e.num IN :list");
    query.setParameter("list", Arrays.asList(4, 5));
ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,460
#3
2013-03-20 16:36

Are you planning to support literal list in near future?

There is no workaround for this issue?

I am unfortunately limited by strict IF, so I cannot use the alternative with parameters, as the IF accepts just String:(

Thank you.

Marta

Maruta
Maruta's picture
Joined on 2013-03-20
User Post #2
#4
2013-03-21 00:54

Build 2.4.7_07 includes an initial attempt to support literal lists in IN expressions.

Please try it.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,461
#5
2013-03-21 08:07

Thank you very much.

It is working fine for me.

Marta

Maruta
Maruta's picture
Joined on 2013-03-20
User Post #3

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