Обсуждение: IN clause with PreparedStatement

Hello,

I want to submit a query like

SELECT *
FROM table1
WHERE id IN (1, 2, 3);

whereas the list 1, 2, 3 must be created dynamically. The goal is to
avoid n queries for single objects instead of one query. I can
concatenate the statement and use Statement. But I can't figure out, how
to use PreperedStatement. Can you help me?

Thank you, Ingmar

On Wed, 18 Jul 2007, Ingmar Lötzsch wrote:

> SELECT *
> FROM table1
> WHERE id IN (1, 2, 3);
>
> whereas the list 1, 2, 3 must be created dynamically. The goal is to
> avoid n queries for single objects instead of one query. I can
> concatenate the statement and use Statement. But I can't figure out, how
> to use PreperedStatement. Can you help me?
>

This identical thread concluded with a perl example that is relevant.

http://archives.postgresql.org/pgsql-jdbc/2007-06/msg00015.php

Kris Jurka

>> SELECT *
>> FROM table1
>> WHERE id IN (1, 2, 3);

> This identical thread concluded with a perl example that is relevant.
>
> http://archives.postgresql.org/pgsql-jdbc/2007-06/msg00015.php

Thank you very much.

They suggest two approaches

1.
int[] num = new int[]{1,2,3};
StringBuffer sb = new StringBuffer();
sb.append("SELECT * FROM items WHERE id IN(").append(generateCSV("?",
num.length)).append(")");
for (int i = 0; i < num.length; i++) {
    ps.setInt(i, num[i]);
}
ps.executeQuery();

2.
$sth = $dbh->prepare("select * from foo where bar =ANY(?::int[])")
$sth->execute('{' . join(@array, ',') . '}');

What do you think about combining the ANY key word with the
PreparedStatement the following way?

class IntArray

package test;

import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;

public class IntArray implements Array
{
    private int[] array;

    public IntArray(int[] array)
    {
        if (array == null)
        {
            throw new IllegalArgumentException("parameter array should not be null");
        }
        this.array = array;
    }

    public Object getArray() throws SQLException
    {
        return null;
    }

    public Object getArray(Map map) throws SQLException
    {
        return null;
    }

    public Object getArray(long index, int count) throws SQLException
    {
        return null;
    }

    public Object getArray(long index, int count, Map map) throws SQLException
    {
        return null;
    }

    public int getBaseType() throws SQLException
    {
        return Types.INTEGER;
    }

    /**
     * This method is called by driver ver. 8 but not by ver. 7.
     */
    public String getBaseTypeName() throws SQLException
    {
        return "int4";
    }

    public ResultSet getResultSet() throws SQLException
    {
        return null;
    }

    public ResultSet getResultSet(long index, int count) throws SQLException
    {
        return null;
    }

    public ResultSet getResultSet(long index, int count, Map map) throws
SQLException
    {
        return null;
    }

    public ResultSet getResultSet(Map map) throws SQLException
    {
        return null;
    }

    /**
     * This method is called by both drivers ver. 8 and 7.
     */
    public String toString()
    {
        String result = "{";
        for (int i = 0; i < this.array.length; ++i)
        {
            if (i > 0)
            {
                result += ",";
            }
            result += this.array[i];
        }
        result += "}";
        return result;
    }
}

class ArrayTest

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ArrayTest
{
    public static void main(String[] args)
    {
        int[] array = new int[]{1, 2, 3};
        IntArray intArray = new IntArray(array);
        try
        {
            Class.forName("org.postgresql.Driver");
            String url = "jdbc:postgresql://localhost/test";
            Connection con = DriverManager.getConnection(url, "postgres", "admin");
            String sql = "SELECT *\n" +
                "FROM table1\n" +
                "WHERE id = ANY (?::int[]);";
            PreparedStatement pstmt = con.prepareStatement(sql);
            pstmt.setArray(1, intArray);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next())
            {
                int id = rs.getInt("id");
                String str1 = rs.getString("str1");
                System.out.println(id + ", " + str1);
            }
            rs.close();
            pstmt.close();
            con.close();
        }
        catch (Exception e)
        {
            e.printStackTrace(System.out);
        }
    }
}

This works with postgresql-8.1-404.jdbc3.jar and pg74.216.jdbc3.jar. I
don't know how to implement the getArray() and getResultSet() methods.
Both drivers only call the toString() method and the driver version 8
additionaly calls getBaseTypeName().

Thank you