Send array of tuple to prepared statement

Question

I want to create PreparedStatement for request like this:

SELECT * FROM table WHERE (a, b) in ( (a1, b1), (a2, b2), (a3, b3) );

How I can send [(a1, b1), (a2, b2), (a3, b3)] to PreparedStatement from Java or create it from pair of array (a1, a2, a3) and (b1, b2, b3)?

Answer 1

Let's assume you have 2 arrays.

int[] tuple_left = int[]{1,2,3,4,5,6};
int[] tuple_right = int[]{1,2,3,4,5,6};

You would then generate the query:

if(tuple_left.length == tuple_right.length && tuple_left.length >= 1){
    String placeholders = "";
    for(int i=0; i < tuple_left.length; i++){
        if(i == 0){
            placeholders += "(?,?)";
        }else{
            placeholders += ",(?,?)";
        }
    }

    String sql = "SELECT * FROM table WHERE (a,b) in ("+placeholders+")";

You would then prepare the query and add the values:

    PreparedStatement ps = ....
    for(int i=0; i < tuple_left.length; i++){
        ps.setInt((2*i)+0,tuple_left[i]);
        ps.setInt((2*i)+1,tuple_right[i]);
    }
    ps.execute();

And ofcourse handle the case where no arguments or different lengths:

}else{
    //Invalid argument count, throw an exception or handle the error otherwise.
}

Edit:

I did some testing with postgresql. When selecting (a,b) from a table, it will return it as type record. This record can not be compared to an array. One way to bypass this restriction is by selecting the values from a subquery. In the case of a 2D array we can do this as subquery:

SELECT 
  arr[i][1],
  arr[i][2] 
FROM 
   (SELECT
     generate_series(1, array_upper(arr, 1)) AS i, 
     arr 
   FROM (SELECT ARRAY[[1,5],[1,10]] arr) t
   ) t

We could do this in java:

Array arr = con.createArrayOf("int", new Object[][]{{1,2},{4,5},{7,8}});

String sql = "SELECT arr[i][1],arr[i][2]"+
             "FROM ("+
             "  SELECT generate_series(1, array_upper(arr, 1)) AS i, arr"+
             "  FROM (SELECT ? AS arr) t"+
             ") t";

PreparedStatement ps = con.prepareStatement(sql);
ps.setArray(1, arr);


//Full prepared statement:
SELECT * FROM table WHERE (a,b) IN 
(SELECT 
  arr[i][1],arr[i][2]
 FROM
   SELECT generate_series(1, array_upper(arr, 1)) AS i, arr
   FROM (SELECT ? AS arr) t
 ) t)

With 2 arrays A and B (representing (a1,a2,a3) and (b1,b2,b3) respectively) we can do the same except select A[row],B[row]. Example code:

Array arr_a = con.createArrayOf("int", new Object[]{1,4,7});
Array arr_b = con.createArrayOf("int", new Object[]{2,5,9});

String sql =     
    "SELECT A[i],B[i]"+
    "FROM ("+
    "  SELECT generate_series(1, array_upper(A, 1)) AS i, A, B"+
    "  FROM (SELECT ? AS A, ? AS B) t"+
    ") t";

PreparedStatement ps = con.prepareStatement(sql);
ps.setArray(1, arr_a);
ps.setArray(2, arr_b);

ps.execute();
ResultSet rs = ps.getResultSet();
while(rs.next()){
        System.out.println("Row: " + rs.getInt(1) + " - " + rs.getInt(2));
}

The output in this case would be:

Row: 1 - 2
Row: 4 - 5
Row: 7 - 9

Answer 2

try this:

select blah from blam where hoot in (?)

Then build the one parameter as follows:

int[] leftArray = {1, 2, 3};
int[] rightArray = {7, 9, 11};
StringBuilder buffer = new StringBuilder();
for (int index = 0; index < leftArray.length; ++index)
{
    if (index > 0)
    {
        buffer.append(',');
    }

    buffer.append('(');
    buffer.append(leftArray[index]);
    buffer.append(',');
    buffer.append(rightArray[index]);
    buffer.append(')');
}

Finally, use the value from the buffer as the parameter to the prepared statement.