Обсуждение: 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. >> > >