Обсуждение: Using SELECT IN with prepared statement

Greetings!

Sorry if you answer this before, I couldn't find it.

I want to do something like:

PreparedStatement ps = connection.prepareStatement("select * from users
where userid in ?");
ps.setSomething(anIntArray);

Is this possible? How?

Thanks.
Tiago A.

Hi,

You need to write: select * from users where userid = ANY ?

See
http://www.postgresql.org/docs/8.3/interactive/functions-comparisons.html

Tiago Alves wrote:
> Greetings!
>
> Sorry if you answer this before, I couldn't find it.
>
> I want to do something like:
>
> PreparedStatement ps = connection.prepareStatement("select * from
> users where userid in ?");
> ps.setSomething(anIntArray);
>
> Is this possible? How?
>
> Thanks.
> Tiago A.
>

Hello,

you can use another syntax

WHERE userid = ANY (?)

and call PreparedStatement.setArray(). That means, you have to provide
an instance of java.sql.Array as parameter. For this purpose you can
implement this interface (It's not too difficult, I've done this.) or
use Connection.createArrayOff(). The last is available dependent on the
JDBC-Library you use. I have not tried it yet.

One advantage of ANY instead of IN is, that you can pass an empty array
without causing a SQL syntax error.

Ingmar

Tiago Alves schrieb:
> Greetings!
>
> Sorry if you answer this before, I couldn't find it.
>
> I want to do something like:
>
> PreparedStatement ps = connection.prepareStatement("select * from users
> where userid in ?");
> ps.setSomething(anIntArray);
>
> Is this possible? How?
>
> Thanks.
> Tiago A.
>

Hello Alexander,

 > You need to write: select * from users where userid = ANY ?

I have not found another way to use an array. You can execute the
following statements:

SELECT 1 = ANY (ARRAY[1, 2]);
SELECT 1 = ANY ('{3, 2}');
SELECT 1 = ANY ('{}');
SELECT 1 IN (1, 2);

You can't execute

SELECT 1 IN (ARRAY[1, 2]);

because IN expects "a parenthesized list of scalar expressions".

See
http://www.postgresql.org/docs/8.3/interactive/functions-comparisons.html
too.

Of course you can concatenate the values like

String idlist = "1, 2";
String sql = "SELECT" + ... + "WHERE id IN (" + idlist + ")";

and execute the statement. But there is no parameter.

Ingmar

Alexander Panzhin schrieb:
> Hi,
>
> You need to write: select * from users where userid = ANY ?
>
> See
> http://www.postgresql.org/docs/8.3/interactive/functions-comparisons.html
>
> Tiago Alves wrote:
>> Greetings!
>>
>> Sorry if you answer this before, I couldn't find it.
>>
>> I want to do something like:
>>
>> PreparedStatement ps = connection.prepareStatement("select * from
>> users where userid in ?");
>> ps.setSomething(anIntArray);
>>
>> Is this possible? How?
>>
>> Thanks.
>> Tiago A.
>>
>

Ingmar Lötzsch <iloetzsch 'at' asci-systemhaus.de> writes:

> Of course you can concatenate the values like
>
> String idlist = "1, 2";
> String sql = "SELECT" + ... + "WHERE id IN (" + idlist + ")";
>
> and execute the statement. But there is no parameter.

...which is not too good, as the driver escapes/sanitizes input
much better than us (e.g. using parameters is *good*). Hence
personally, from JDBC, I like this workaround:

SELECT * FROM pg_language WHERE lanname = ANY( string_to_array(?, ',') );

See:

http://zarb.org/~gc/html/doc-misc.html#2008-08-21

--
Guillaume Cottenceau

Guillaume Cottenceau schrieb:
> Ingmar Lötzsch <iloetzsch 'at' asci-systemhaus.de> writes:
>
>> Of course you can concatenate the values like
>>
>> String idlist = "1, 2";
>> String sql = "SELECT" + ... + "WHERE id IN (" + idlist + ")";
>>
>> and execute the statement. But there is no parameter.
>
> ...which is not too good, as the driver escapes/sanitizes input
> much better than us (e.g. using parameters is *good*). Hence
> personally, from JDBC, I like this workaround:
>
> SELECT * FROM pg_language WHERE lanname = ANY( string_to_array(?, ',') );

Yes, I tried this too. You have to implement the conversion
Collection<Integer> -> String. Why not inside an Array?

And if you are using int/bigint for identifiers, there is no need to
escape or sanitize these strings.

> See:
>
> http://zarb.org/~gc/html/doc-misc.html#2008-08-21

Thank you for all your help.

I ended implementing the interface ava.sql.Array. I just implemented the
functions getBaseType,  getBaseTypeName (returning "int4"), getArray and
toString (returning a neatly formated string like "{1,2,3}").

I think that the connection.createArrayOf didn't work because it's not
implemented in the apache common-dbcp, the library I'm using.

Again, thanks!
Tiago A.

Ingmar Lötzsch escreveu:
> Hello,
>
> you can use another syntax
>
> WHERE userid = ANY (?)
>
> and call PreparedStatement.setArray(). That means, you have to provide
> an instance of java.sql.Array as parameter. For this purpose you can
> implement this interface (It's not too difficult, I've done this.) or
> use Connection.createArrayOff(). The last is available dependent on
> the JDBC-Library you use. I have not tried it yet.
>
> One advantage of ANY instead of IN is, that you can pass an empty
> array without causing a SQL syntax error.
>
> Ingmar
>
> Tiago Alves schrieb:
>> Greetings!
>>
>> Sorry if you answer this before, I couldn't find it.
>>
>> I want to do something like:
>>
>> PreparedStatement ps = connection.prepareStatement("select * from
>> users where userid in ?");
>> ps.setSomething(anIntArray);
>>
>> Is this possible? How?
>>
>> Thanks.
>> Tiago A.
>>
>
>