"where in" query

Is it possible to run a query selecting values that exist in a provided list? e.g. something like:

SELECT u FROM User m WHERE u.username IN ('test_user','anotheruser')

I've tried various combinations of this including passing the list as a named parameter but without any joy (error below). The only similar examples I can find in the docs involve a join query - but my list isn't in the database - and the MEMBER OF operator doesn't seem to do what I want (although I may be using it incorrectly!

Any advice would be much appreciated and apologies in advance if I've done something silly or missed something simple :-)

The error I'm seeing is:

Caused by: com.objectdb.o.UserException: Unexpected query token 'IN'
at com.objectdb.o.MSG.d(MSG.java:61)
at com.objectdb.o.TKN.J(TKN.java:755)
at com.objectdb.o.TKI.s(TKI.java:229)
at com.objectdb.o.QPR.o(QPR.java:158)
at com.objectdb.o.QRC.<init>(QRC.java:122)
at com.objectdb.o.QRM.UR(QRM.java:242)
at com.objectdb.o.MST.UR(MST.java:884)
at com.objectdb.o.WRA.UR(WRA.java:286)
at com.objectdb.o.WSM.UR(WSM.java:113)
at com.objectdb.o.STC.r(STC.java:421)
at com.objectdb.o.SHN.ah(SHN.java:468)
at com.objectdb.o.SHN.J(SHN.java:146)
at com.objectdb.o.HND.run(HND.java:133)
at java.lang.Thread.run(Thread.java:619)

The IN operator is currently not supported and will be implemented with subqueries (since its main use is in subqueries).

The following code demonstrates how to use MEMBER OF with parameter for this purpose:

package com.objectdb.forum;

import java.util.*;

import javax.persistence.*;


public final class T351
{
    public static void main(String[] args)
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("$objectdb/db/test.odb");
        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();

        Query query = em.createQuery(
            "SELECT e.name FROM MyEntity e WHERE e.name MEMBER OF :names");
        query.setParameter("names", Arrays.asList("a", "c"));
        List resultList = query.getResultList();
        System.out.println(resultList);

        em.close();
        emf.close();
    }
   
    @Entity
    public static final class MyEntity {
        private String name;
        public MyEntity(String name) {
            this.name = name;
        }
    }
}

Anyone know why this result returns an empty set?

List<Prez> results;
TypedQuery<Prez> prezs = em.createQuery("SELECT p FROM Prez p WHERE p.id MEMBER OF :ids", 
                                              Prez.class);
prezs.setParameter("ids", Arrays.asList(1,3));
results = prezs.getResultList();

(Prez entities with id 1,3 do exist)

Thanks.

I tried to build a test case and the result was an exception rather than an empty result set.

Version 2.3.2 fixes the exception - please try it.

If you still get an empty set with the new version - try to modify the following test case (that works well with version 2.2.3) to demonstrate the problem:

import java.util.*;
import javax.persistence.*;

public final class T351
{
    public static void main(String[] args)
    {
        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory(
                "objectdb:$objectdb/db/test.tmp;drop");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
        for (int i = 0; i < 5; i++) {
            em.persist(new Prez());
        }
        em.getTransaction().commit();

        TypedQuery<Prez> prezs = em.createQuery(
            "SELECT p FROM Prez p WHERE p.id MEMBER OF :ids", Prez.class);
        prezs.setParameter("ids", Arrays.asList(1L,3L));
        List results = prezs.getResultList();
        System.out.println("Result Size: " + results.size());

        em.close();
        emf.close();
    }
   
    @Entity
    public static final class Prez {
        @Id @GeneratedValue long id;
    }
}

Thanks.

That would be fine if upgrading didn't break my application. Keeps throwing exceptions relating to using a non-closed factory manager.

Got around it by using the long form of the query.

Might have to start a new topic when I try another upgrade.