java - 将元组数组发送到准备好的语句

Question

我想为这样的请求创建PreparedStatement:

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

如何从 Java 发送 [(a1, b1), (a2, b2), (a3, b3)] 到 PreparedStatement 或从一对创建它数组(a1，a2，a3)和(b1，b2，b3)？

Answer 1

假设您有 2 个数组。

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

然后您将生成查询:

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+")";

然后您将准备查询并添加值:

    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();

当然还要处理没有参数或不同长度的情况:

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

编辑:

我用 postgresql 做了一些测试。当从表中选择(a，b)时，它将作为记录类型返回。该记录无法与数组进行比较。绕过此限制的一种方法是从子查询中选择值。对于二维数组，我们可以将其作为子查询来执行:

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

我们可以在 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)

对于 2 个数组 A 和 B(分别代表 (a1,a2,a3) 和 (b1,b2,b3))，我们可以做同样的事情，除了 select A[row],B[row]。示例代码:

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));
}

这种情况下的输出将是:

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