Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?

Question

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?

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.

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.

Pang · Accepted Answer · 2024-06-25 01:46:53Z

17

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

edited Jun 25, 2024 at 1:46

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Apr 8, 2014 at 11:19

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rafael Redrado Over a year ago

Thank you so much! That's what I was looking for. I've used Set<Long> because my Node Java class declares the identifier as a long. I've edited the question to include the solution. I'm using nativeQuery because I need to use the agregation function MAX with an INNER JOIN and I don't know if it's possible to do it without the nativeQuery option. Thanks again!

Collectives™ on Stack Overflow

Is it possible to use an Array object as a parameter in Spring Repository @Query annotation?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related