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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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...6/msg00015.php

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

>> 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...6/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 = "jdbcostgresql://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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend