英文:
ResultSet empty when using createArrayOf in Java with PostgreSQL
问题
我正在尝试使用一个 id 数组来查询 product 表。以下是该方法的片段：
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM product WHERE id IN (?)");
System.out.println(ids /*ArrayList<Integer>*/); //打印 [3]
Array array = connection.createArrayOf("INTEGER", ids.toArray());
// Array array = connection.createArrayOf("INTEGER", new Integer[]{1, 2, 3}); //<-----也尝试过这个
statement.setArray(1, array);
ResultSet results = statement.executeQuery();
while (results.next()) {
System.out.println("不会打印这个");
Product product = new Product(0);
product.setId(results.getInt("id"));
products.add(product);
}
return products;
表 product 包含具有 id 1、2 和 3 的 3 行。products 返回为 null。有任何想法为什么会这样吗？
编辑
根据 9.23.1 节
> 右侧是标量表达式的括号列表
例如 (1,2,3)
所以，我想问题变成了：如何从我的 ArrayList 中获取这个标量表达式列表？
英文:
I'm trying to query product table using an array of ids. Here's a fragment of the method:
PreparedStatement statement = connection
.prepareStatement("SELECT * FROM product WHERE id IN (?)");
System.out.println(ids /*ArrayList<Integer>*/); //prints [3]
Array array = connection.createArrayOf("INTEGER", ids.toArray());
// Array array = connection.createArrayOf("INTEGER", new Integer[]{1, 2, 3}); //<-----tried this too
statement.setArray(1, array);
ResultSet results = statement.executeQuery();
while (results.next()) {
System.out.println("does not print this");
Product product = new Product(0);
product.setId(results.getInt("id"));
products.add(product);
}
return products;
Table product contains 3 rows with ids 1, 2 and 3. products returns null. Any idea why?
Thanks
EDIT
According to section 9.23.1
> The right-hand side is a parenthesized list of scalar expressions
example (1,2,3)
So, I think question turns into: how to get that list of scalar expressions from my ArrayList?
答案1
得分: 1
在查询的WHERE子句中检查一个元素是否在数组中，您可以使用ANY。在您的情况下：
SELECT * FROM product WHERE id = ANY(?)
英文:
To check in the WHERE clause of a query, whether an element is in an array you can use ANY. In your case:
SELECT * FROM product WHERE id = ANY(?)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论