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