是否可以在Spring Repository @Query注释中使用Array对象作为参数? [英] Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?
问题描述
是否可以在Spring Repository @Query注释中使用Array对象作为参数?
Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?
我正在尝试检索其列节点为表的所有行存在于String数组中。可以使用Spring存储库中的@Query注释一次执行该操作吗?
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?
这是我的位置实体:
@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;
}
其中节点引用Node类,并且它在数据库中映射为a BIGINT。
Where node references the Node class, and it is mapped in the database as a 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.
ve尝试了几种组合,例如使用具有正确格式或长数组的简单String。.但是到目前为止,仍然没有任何效果。
I've tried several combinations like using a simple String with the right format or a long array.. but nothing has worked so far.
先谢谢了。
解决方案:
@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);
我为查询添加了更多功能,因为我需要检索为每个节点插入的最后一行标识符。这样就有了MAX函数和INNER JOIN来完成这项工作。
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.
推荐答案
使用集合而不是数组( Set< String>
),并确保它不为空(否则查询将无效。)
Use a collection instead of an array (Set<String>
), and make sure it's not empty (otherwise the query will be invalid.
此外,没有理由可以使用本机查询,并且在参数周围不应包含括号:
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);
这篇关于是否可以在Spring Repository @Query注释中使用Array对象作为参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!