Loading multiple objects using jpa with "in clause"

[Tex]

Hi,

I'm using play 1.2.3,

how can I load multiple objects using JPA with "in clause" ?

An example: having a simple model /app/models/User.java

    package models;

    import javax.persistence.Entity;

    import play.db.jpa.Model;

    @Entity

    public class User extends Model {

        public String name;

        public User(String name) {

            this.name = name;

        }

    }

and this test data:

User first = new User("first").save();

User second = new User("second").save();

User third = new User("third").save();

...

I want to load the first and the third user (NOTICE: the 1st and 3rd user is only an example, in production the number of user/id loaded may vary) using "in clause", so I've been tried:

            List<Item> itemObjs = null;

            itemObjs = Item.find("id in (?)", "1,3").fetch(); // exception Parameter value [1,3] was not matching type [java.lang.Long]

            itemObjs = Item.find("id in (?)", "(1,3)").fetch(); // exception Parameter value [1,3] was not matching type [java.lang.Long]

            itemObjs = Item.find("id in ?", "(1,3)").fetch(); // exception Parameter value [1,3] was not matching type [java.lang.Long]

            itemObjs = Item.find("id in ?", "1,3").fetch(); // exception Parameter value [1,3] was not matching type [java.lang.Long]

            itemObjs = Item.find("id in (:ids)", "1,3").fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in (:ids)", "(1,3)").fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in :ids", "(1,3)").fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in :ids", "1,3").fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            Long[] ids = new Long[] {1l,3l};

            itemObjs = Item.find("id in (?)", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in ?", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in :ids", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in (:ids)", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

and 

            List<Long> ids = new ArrayList<Long>();

            ids.add(1l);

            ids.add(3l);

            itemObjs = Item.find("id in (?)", ids).fetch(); // exception Error while executing query from models.User where id in (?): java.util.ArrayList cannot be cast to java.lang.Long

            itemObjs = Item.find("id in ?", ids).fetch(); // exception Error while executing query from models.User where id in (?): java.util.ArrayList cannot be cast to java.lang.Long

            itemObjs = Item.find("id in :ids", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

            itemObjs = Item.find("id in (:ids)", ids).fetch(); // exception org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

none of the above methods work, why ? 

I receive always an exception (see comments on every call)...

Is there a simple way to load a given number of records using "in clause" or must I do a loop that loads every single object into a list ?

Many thanks in advance...

[Vijay Kiran]

Try this:

List<Long> ids  = new ArrayList<Long>();

ids.add(1L);

ids.add(2L);

final List<User> users = User.find("id in (?1)", ids).fetch();

for (User user : users) {

System.out.println(user.code);

}

./Vijay

[Tex]

That works !

Many thanks !

BTW: where do you learn that in JPA you must use a positional parameter for "in clause" ?

[Vijay Kiran]

The syntax for the use of the comparison operator [NOT] IN in a conditional expression is as follows:

  in_expression ::= state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)

  in_item ::= literal | input_parameter

http://download.oracle.com/docs/cd/E14571_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_pos_params :-)

[Vijay Kiran]

Sorry for the incomplete answer to your question:

If you use JPQL directly - as you can see from the grammar above, you can use either positional or named parameters. So the following works as expected:

List<Long> ids = new ArrayList<Long>();

ids.add(1L);

ids.add(2L);

final List<User> users = JPA.em().createQuery("from User where id in :ids").setParameter("ids", ids).getResultList();

for (User user : users) {

   System.out.println(user.code);

}

I guess Play Model's JPQL generation for bind is not creating the correct JPQL. 

See here : https://github.com/playframework/play/blob/master/framework/src/play/db/jpa/JPQL.java#L113

HTH,

Vijay

[Tex]

Many thanks Vijay, I'll open a bug ASAP...