Re: IN clause with PreparedStatement
От | Ingmar Lötzsch |
---|---|
Тема | Re: IN clause with PreparedStatement |
Дата | |
Msg-id | 469F372F.2050602@asci-systemhaus.de обсуждение исходный текст |
Ответ на | Re: IN clause with PreparedStatement (Kris Jurka <books@ejurka.com>) |
Список | pgsql-jdbc |
>> 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
В списке pgsql-jdbc по дате отправления:
Следующее
От: Dave CramerДата:
Сообщение: Re: Using bytea with ResultSet.getBytes("..."). Problem. Help!!