IN operator syntax

#1

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;
        }
    }
}


#2

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)
#3

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

#4

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)
#5

Thank you very much.

It is working fine for me.

Marta


Post Reply

To post a reply and/or subscribe to update notifications - please login