(JPQL?) find with IN ArrayList

[kheraud]

Hello again !

I have crashed my head against the wall for hours with this issue.
Is it possible to use "find" passing an ArrayList of int ?

int[] myArray = new int[] { 1, 2 };
List<Exponent> expoList = Exponent.find("select exp from Exponent exp
where exp.id IN ?", myArray).fetch();

I can't find any way to make it work.

Do you have some hints ?

Thanks a lot !

kheraud

[Pascal Voitot Dev]

I don't have the immediate solution but it might give you other ideas...
one part of the interesting code for you is here apparently:
in JPQL class:

public String createFindByQuery(String entityName, String entityClass, String query, Object... params) {
        if (query == null || query.trim().length() == 0) {
            return "from " + entityName;
        }
        if (query.matches("^by[A-Z].*$")) {
            return "from " + entityName + " where " + findByToJPQL(query);
        }
        if (query.trim().toLowerCase().startsWith("select ")) {
            return query;
        }
        if (query.trim().toLowerCase().startsWith("from ")) {
            return query;
        }
        if (query.trim().toLowerCase().startsWith("order by ")) {
            return "from " + entityName + " " + query;
        }
        if (query.trim().indexOf(" ") == -1 && query.trim().indexOf("=") == -1 && params != null && params.length == 1) {
            query += " = ?1";
        }
        if (query.trim().indexOf(" ") == -1 && query.trim().indexOf("=") == -1 && params == null) {
            query += " = null";
        }
        return "from " + entityName + " where " + query;
    }

First of all, it seems your function awaits an object array of params. But your own array is 1 param so your array itself should be put in an object array...
int[][] myArray = new int[1][] ;
myArray[0][]=new int[] { 1, 2 };
and you try...

If it doesn't work, other ideas:
The IN is not explicitly managed anywhere in this class but I looked quickly.

You could try to build as a param a String "(1, 2)" (the SQL representation of the list)
Object[] params = new Object[1];
params[0] = "(1,2)";
Exponent.find("select exp from Exponent exp
where exp.id IN ?", params).fetch();

maybe you could try:
Exponent.find("exp.id IN ?", myArray).fetch();

and the horrible clutch:

Exponent.find("select exp from Exponent exp

where exp.id IN "+JOIN_YOU_ARRAY_AS_A_SQL_LIST, myArray).fetch();

regards
Pascal

--
You received this message because you are subscribed to the Google Groups "play-framework" group.
To post to this group, send email to play-fr...@googlegroups.com.
To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.

[Karim Heraud]

Solved with a :

String[] myArray = new String[] { "1", "2" };
 List<Exponent> expoList = Exponent.find("select exp from Exponent exp where exp.id in ("+this.implode(myArray)+")").fetch();

The following code fires errors :

String[] myArray = new String[] { "1", "2" };
List<Exponent> expoList = Exponent.find("select exp from Exponent exp where exp.id in (?)", this.implode(myArray)).fetch();

It tries to interpretate "?" after a "in" statement. I haven't found a way to just replace "?" with the given string except with my bad coded solution.

For information Pascal, all the other solution you gave failed... Thanks for you usefull help

kheraud

2011/4/22 Pascal Voitot Dev <pascal.v...@gmail.com>

--
K. Heraud

[Sascha Hanke]

Try Exponent.find("id in (:ids)").bind("ids", new Long[]{1L,
2L}).fetch();

On 22 Apr., 20:10, Karim Heraud <kher...@gmail.com> wrote:
> Solved with a :
>
> String[] myArray = new String[] { "1", "2" };
>  List<Exponent> expoList = Exponent.find("select exp from Exponent exp where
> exp.id in ("+this.implode(myArray)+")").fetch();
>
> The following code fires errors :
>
> String[] myArray = new String[] { "1", "2" };
> List<Exponent> expoList = Exponent.find("select exp from Exponent exp where
> exp.id in (?)", this.implode(myArray)).fetch();
>
> It tries to interpretate "?" after a "in" statement. I haven't found a way
> to just replace "?" with the given string except with my bad coded solution.
>
> For information Pascal, all the other solution you gave failed... Thanks for
> you usefull help
>
> kheraud
>

> 2011/4/22 Pascal Voitot Dev <pascal.voitot....@gmail.com>

> > where <http://exp.id/>exp.id IN ?", params).fetch();

>
> > maybe you could try:
> > Exponent.find("exp.id IN ?", myArray).fetch();
>
> > and the horrible clutch:
>
> > Exponent.find("select exp from Exponent exp
> > where exp.id IN "+JOIN_YOU_ARRAY_AS_A_SQL_LIST, myArray).fetch();
>
> > regards
> > Pascal
>

[Pascal Voitot Dev]

On Fri, Apr 22, 2011 at 8:10 PM, Karim Heraud <khe...@gmail.com> wrote:

Solved with a :

String[] myArray = new String[] { "1", "2" };
 List<Exponent> expoList = Exponent.find("select exp from Exponent exp where exp.id in ("+this.implode(myArray)+")").fetch();

this one corresponds to the last cludge I proposed but it is generally not advised because it is the kind of code driving to SQL injection attacks ;)
 

The following code fires errors :

String[] myArray = new String[] { "1", "2" };
List<Exponent> expoList = Exponent.find("select exp from Exponent exp where exp.id in (?)", this.implode(myArray)).fetch();

It tries to interpretate "?" after a "in" statement. I haven't found a way to just replace "?" with the given string except with my bad coded solution.

For information Pascal, all the other solution you gave failed... Thanks for you usefull help

that was just ideas ;)
 

[Karim Heraud]

Thanks for your help Sascha and Pascal !
Sascha's solution is, I think, the most appropriate to my case* :
List<Integer> idExs;
Exponent.find("id in (:ids)").bind("ids", idExs).fetch();

(*) As, in my case, idExs is transmitted by user through a form, I create the List<Integer> directly in the method handling the POST request :
routes : POST       /testbackend     SynchroBackend.getAll

public class SynchroBackend extends Controller {
    public static void getAll(List<Integer> idExs) {
        Exponent.find("id in (:ids)").bind("ids", idExs).fetch();
        [...]
    }
}

This way, if the user tries to pass something that can't be casted to Integer, there is no issue. I tested to pass idExs:["s1", "jlj", "6"] and I got :

• With public static void getAll(Integer[] idExs) --> (null, null, 6) // Require further treatments to avoid errors / exceptions
  
• With public static void getAll(int[] idExs) --> (0,0, 6) // I do not want id "0" to be fetched !!
  
• With public static void getAll(List<Integer> idExs) --> (6)

Not a big news but it could be usefull for someone else :).

Thanks again

Kheraud