Postgresql query for this situation?

Question

I am using the following statement

select * 
from table 
where column1 in(groups)

Where "groups" is a String array of size n. If i use it as it is, It wont get executed so can anyone suggest exact query to perform this?
EDIT 1
If I use the following code

try{
   System.out.println("before execute query");
  ps1.setArray(1,conn.createArrayOf("text",gs));
  ps1.setArray(2,conn.createArrayOf("text",gs));
  System.out.println("after execute query");
  }
  catch(Exception e)
  {
   System.out.println("hrer----"+e);
  }

First,It prints "before execute query" and then it gives the following exception

javax.servlet.ServletException:servlet execution threw an exception

*NOTE : * It does not print "hrer-----" in catch(Exception e) block

score 3 · Answer 1 · edited May 23 '17 at 10:29

3

This should work:

PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = any(?)");

String[] usernames = {"admin", "guest"};
stmt.setArray(1, conn.createArrayOf("varchar", usernames));

Credit goes to Boris's answer at https://stackoverflow.com/a/10240302

edited May 23 '17 at 10:29

Community

1
1

answered Jun 14 '12 at 11:12

sayap

6,169
2
36
40

Oy, clever! This would only work on postgres, but in OPs use-case that might be okay. – beerbajay Jun 14 '12 at 11:15
1

@beerbajay Why does this works only with postgresql? Any specific reason? – suraj Jun 14 '12 at 11:31
@sayap : When i use the above query, It doesnt show any error but when I execute the following line "stmt.setArray(1, conn.createArrayOf("varchar", usernames));" gives the following exception "javax.servlet.servletexception:servlet execution threw an exception". I am not able to catch this exception even in catch(Exception e) block – suraj Jun 14 '12 at 11:45
@beerbajay : why do you say it works only with postgresql? which part here is postgresql specific? – Ashwin Jun 14 '12 at 14:48
@Ashwin I was slightly wrong; it will probably also work on oracle. The `createArrayOf`, at least according to the [JDBC tutorial](docs.oracle.com/javase/tutorial/jdbc/basics/array.html), does not work on MySQL. – beerbajay Jun 14 '12 at 15:06

beerbajay · Answer 2 · 2012-06-14T15:00:49.613

2

select * from table where column1 in (?, ?)

except that you have n question marks.

StringBuilder q = new StringBuilder("select * from table where column1 in (");
for(int i=0; i<groups.length; i++) {
    q.append("?");
    if(i != groups.length - 1) {
        q.append(",");
    }
}
q.append(")");
PreparedStatement query = con.prepareStatement(q.toString());
for(int i=1; i<=groups.length; i++) {
    query.setString(i, groups[i-1]);
}
ResultSet rs = query.getResultSet();

edited Jun 14 '12 at 15:00

answered Jun 14 '12 at 10:13

beerbajay

19,652
6
58
75

Oh! Well, unfortunately you have to give a `?` for each parameter of a `PreparedStatement`. You could probably use `StringUtils` or similar to generate and join the `?`s, which would make the code look better. Or you could switch to JPA, which allows you to pass collections to queries, even native queries (although there is a limit of 1000 objects per collection). – beerbajay Jun 14 '12 at 10:39
@beerbajay Is there any inbuilt function for this in java? – suraj Jun 14 '12 at 10:46
@suraj No, JDBC is designed to be pretty simple, so it doesn't provide a lot of convenience methods. Each parameter must be specified (e.g. with a `?` in a prepared statement). This works fine for most queries, but with `IN()` queries, it gets messy. The number of `?`s must match the size of the collection, so you have to build the query dynamically. As I mentioned previously, if you use JPA or hibernate, you can pass collections to these. – beerbajay Jun 14 '12 at 11:09
@suraj Another alternative is to use [Spring's JDBC support](http://static.springsource.org/spring/docs/3.1.x/spring-framework-reference/html/jdbc.html#jdbc-in-clause) which lets you pass a `List` to (for example) [`NamedParameterJdbcTemplate`](http://static.springsource.org/spring/docs/3.1.x/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) in the same form you'd use for JPA (`where column1 IN(:paramName)`). – beerbajay Jun 14 '12 at 11:13
It should be `query.setString(i, groups[i - 1]);` – JB Nizet Jun 14 '12 at 14:21

Postgresql query for this situation?

2 Answers2