Thread: PreparedStatement for set membership (The IN operator)
Hello, I need to use a SELECT statement with varying data for set membership. IE. SELECT link_id FROM links WHERE heading_id IN (?, ?, ?) It won't always be the same number of members in the set. I am using an ordinary statement with an executeQuery call but would prefer to use a PreparedStatement. Is this possible? If so how do I set the values in the set? Regards, Daron.
On 05.04.2011 10:17, Daron Ryan wrote: > Hello, > > I need to use a SELECT statement with varying data for set membership. IE. > > SELECT link_id > FROM links > WHERE heading_id IN (?, ?, ?) > > It won't always be the same number of members in the set. I am using an > ordinary statement with an executeQuery call but would prefer to use a > PreparedStatement. Is this possible? If so how do I set the values in > the set? You can do "WHERE heading_id = ANY (?)", and pass an array of the ids for the parameter. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Thanks Heikki. I have tried using the setArray method but I am still running into an error. Exception in thread "main" org.postgresql.util.PSQLException: Unknown type _INTEGER. at org.postgresql.jdbc2.AbstractJdbc2Statement.setArray(AbstractJdbc2Statement.java:2800) at dictionary.test.Main.main(Main.java:85) This is the Array implementation I have created. http://pastebin.com/tkzPRL4A For baseTypeName I have also tried "int" and received the same error referring to type _int instead. This is the code I used to test the implementation calling code http://pastebin.com/T1mvADaF Any idea what I need to do? On 5/04/2011 5:57 PM, Heikki Linnakangas wrote: > On 05.04.2011 10:17, Daron Ryan wrote: >> Hello, >> >> I need to use a SELECT statement with varying data for set >> membership. IE. >> >> SELECT link_id >> FROM links >> WHERE heading_id IN (?, ?, ?) >> >> It won't always be the same number of members in the set. I am using an >> ordinary statement with an executeQuery call but would prefer to use a >> PreparedStatement. Is this possible? If so how do I set the values in >> the set? > > You can do "WHERE heading_id = ANY (?)", and pass an array of the ids > for the parameter. >
hi, try select unnest(ARRAY[?,?,?]) regards Thomas Am 05.04.2011 09:17, schrieb Daron Ryan: > Hello, > > I need to use a SELECT statement with varying data for set membership. > IE. > > SELECT link_id > FROM links > WHERE heading_id IN (?, ?, ?) > > It won't always be the same number of members in the set. I am using > an ordinary statement with an executeQuery call but would prefer to > use a PreparedStatement. Is this possible? If so how do I set the > values in the set? > > Regards, > Daron. >
On 05.04.2011 15:39, Daron Ryan wrote: > Thanks Heikki. I have tried using the setArray method but I am still > running into an error. > > Exception in thread "main" org.postgresql.util.PSQLException: Unknown > type _INTEGER. > at > org.postgresql.jdbc2.AbstractJdbc2Statement.setArray(AbstractJdbc2Statement.java:2800) > > at dictionary.test.Main.main(Main.java:85) > > This is the Array implementation I have created. > http://pastebin.com/tkzPRL4A Starting with JDBC4, you can use conn.createArrayOf() function. No need to create a custom Array class anymore. This is what we have in the test suite: public void testCreateArrayOfInt() throws SQLException { PreparedStatement pstmt = _conn.prepareStatement("SELECT ?::int[]"); Integer in[] = new Integer[3]; in[0] = 0; in[1] = -1; in[2] = 2; pstmt.setArray(1, _conn.createArrayOf("int4", in)); ResultSet rs = pstmt.executeQuery(); assertTrue(rs.next()); Array arr = rs.getArray(1); Integer out[] = (Integer [])arr.getArray(); assertEquals(3, out.length); assertEquals(0, out[0].intValue()); assertEquals(-1, out[1].intValue()); assertEquals(2, out[2].intValue()); } -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On 5/04/2011 10:26 PM, Heikki Linnakangas wrote: > On 05.04.2011 15:39, Daron Ryan wrote: >> Thanks Heikki. I have tried using the setArray method but I am still >> running into an error. >> >> Exception in thread "main" org.postgresql.util.PSQLException: Unknown >> type _INTEGER. >> at >> org.postgresql.jdbc2.AbstractJdbc2Statement.setArray(AbstractJdbc2Statement.java:2800) >> >> >> at dictionary.test.Main.main(Main.java:85) >> >> This is the Array implementation I have created. >> http://pastebin.com/tkzPRL4A > > Starting with JDBC4, you can use conn.createArrayOf() function. No > need to create a custom Array class anymore. This is what we have in > the test suite: > > public void testCreateArrayOfInt() throws SQLException { > PreparedStatement pstmt = _conn.prepareStatement("SELECT > ?::int[]"); > Integer in[] = new Integer[3]; > in[0] = 0; > in[1] = -1; > in[2] = 2; > pstmt.setArray(1, _conn.createArrayOf("int4", in)); > > ResultSet rs = pstmt.executeQuery(); > assertTrue(rs.next()); > Array arr = rs.getArray(1); > Integer out[] = (Integer [])arr.getArray(); > > assertEquals(3, out.length); > assertEquals(0, out[0].intValue()); > assertEquals(-1, out[1].intValue()); > assertEquals(2, out[2].intValue()); > } > Thanks, my code is working now. I changed my baseTypeName to int4 and after a few more fixes my code worked. Then I tried the Connection.createArrayOf method and that worked too.
Thanks to your help before I made ANY work with integers. I now need to work with strings. Any idea where I can find a list of the type names used by postgres for the Connection.createArray method?
On 6/04/2011 8:58 PM, Daron Ryan wrote: > Thanks to your help before I made ANY work with integers. I now need > to work with strings. Any idea where I can find a list of the > type names used by postgres for the Connection.createArray method? Just found it at http://db.apache.org/ojb/docu/guides/jdbc-types.html. Sorry.