postgresql 是否可以在 Spring Repository @Query 注释中使用 Array 对象作为参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22935278/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?
提问by Rafael Redrado
Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?
是否可以在 Spring Repository @Query 注释中使用 Array 对象作为参数？
I'm trying to retrieve all rows in a table whose column node is present in an String array. Is it possible to do it at a time using the @Query annotation in Spring repository?
我正在尝试检索列节点存在于字符串数组中的表中的所有行。是否可以使用 Spring 存储库中的 @Query 注释一次完成？
Here is my Location Entity:
这是我的位置实体：
@Entity
@Table(name = "LOCATIONS")
public class Location extends Measurement{
private String latitude;
private String nsIndicator;
private String longitude;
private String ewIndicator;
@ManyToOne
@JoinColumn(name="node")
private Node node;
}
Where node references the Node class, and it is mapped in the database as a BIGINT.
其中 node 引用 Node 类，它在数据库中作为 BIGINT 映射。
I have a repository like this:
我有一个这样的存储库：
public interface LocationRepository extends CrudRepository<Location, Long>{
@Query(value=
"SELECT l1.node, l1.id, l1.latitude, l1.longitude " +
"FROM LOCATIONS l1 WHERE l1.node IN (:ids)",
nativeQuery=true)
List<Location> findMeasureByIds(@Param("ids") String[] ids);
}
There you can see the query that I'm trying to execute, but it's not working. I don't know if it's possible to use an array there, or parameters must be just Strings and/or Integers, I couldn't find it anywhere.
在那里您可以看到我正在尝试执行的查询，但它不起作用。我不知道是否可以在那里使用数组，或者参数必须只是字符串和/或整数，我在任何地方都找不到。
I've tried several combinations like using a simple String with the right format or a long array.. but nothing has worked so far.
我尝试了几种组合，例如使用具有正确格式的简单字符串或长数组..但到目前为止没有任何效果。
Thanks in advance.
提前致谢。
SOLUTION:
解决方案：
@Query(value="SELECT * FROM LOCATIONS l1 " +
"INNER JOIN (SELECT node, MAX(id) AS id FROM LOCATIONS GROUP BY node) l2 " +
"ON l1.node = l2.node AND l1.id = l2.id " +
"WHERE l1.node IN :ids", nativeQuery=true)
List<Location> findLastLocationByIds(@Param("ids") Set<Long> ids);
I've added more functionality to the query because I needed to retrieve the last row inserted for each node identifier. So there's the MAX function and the INNER JOIN to do that work.
我向查询添加了更多功能，因为我需要检索为每个节点标识符插入的最后一行。所以有 MAX 函数和 INNER JOIN 来完成这项工作。
回答by JB Nizet
Use a collection instead of an array (Set<String>), and make sure it's not empty (otherwise the query will be invalid.
使用集合而不是数组 ( Set<String>)，并确保它不为空（否则查询将无效。
Also, there's no reason to use a native query for that, and you shouldn't have parentheses around the parameter:
此外，没有理由为此使用本机查询，并且您不应该在参数周围使用括号：
@Query("SELECT l1 FROM Location l1 WHERE l1.node.id IN :ids")
List<Location> findLocationsByNodeIds(@Param("ids") Set<String> ids);
