CODEWITHSUNDEEP
springspring-data-jpaspring-data
Daniel Morales
Daniel Morales

Reputation: 43

Spring Data JPA - How do i get a list of specific rows by ids?

I'm trying to get specific rows from the table by ids, but what I have below is not working.

@Query(value = "SELECT * FROM row r where r.row_id = :row_ids", nativeQuery = true)
    List<Object> temp(@Param("albumsIds") String row_ids);

row_ids is all the ids separated by an "or" - id:1 or id:2 or id:3

I'm just trying to do select * from row r where r.row_id = id:1, or r.row_id = id:2, or r.row_id = id:3

Does anyone have an idea what the problem is, or is there a better way to do it?

Upvotes: 1

Views: 15630

Answers (1)

CodeTheorist
CodeTheorist

Reputation: 1672

Simply use:

List<Object> findByIdIn(Collection<Integer> ids);

That will automatically setup the derived query from the query method name itself.

Alternatively, if you want to provide the query programmatically, then the query should be:

@Query(value = "SELECT * FROM row R WHERE R.row_id IN :ids", nativeQuery = true)
List<Object> temp(@Param("ids") Collection<Integer> ids);

MySQL lets use use the IN keyword in queries, which let's us provide a CSV for the IDs to parse and returns any records which ID is in the CSV. This obviously can be used on any data, just for this purpose we'll use the IDs as an example.

Using the method above should minimize the risk of SQL injection significantly, as the Java Collection type casting shouldn't allow a user to provide any values that can cause issues.

Upvotes: 3

Related Questions