Spring CrudRepository findByInventoryIds(List inventoryIdList) - equivalent to IN clause

Question

In Spring CrudRepository, do we have support for "IN clause" for a field? ie something similar to the following?

 findByInventoryIds(List<Long> inventoryIdList)

If such support is not available, what elegant options can be considered? Firing queries for each id may not be optimal.

score 421 · Accepted Answer · edited Jan 26 '23 at 13:39

421

findByInventoryIdIn(List<Long> inventoryIdList) should do the trick.

The HTTP request parameter format would be like so:

Yes ?id=1,2,3
No  ?id=1&id=2&id=3

The complete list of JPA repository keywords can be found in the current documentation listing. It shows that IsIn is equivalent – if you prefer the verb for readability – and that JPA also supports NotIn and IsNotIn.

If inventoryId has the primary key, you can simply use yourRepo.findAll(inventoryIdList).

edited Jan 26 '23 at 13:39

Marco Sulla

15,299
14
65
100

answered Sep 25 '13 at 04:42

Oliver Drotbohm

80,157
18
225
211

Thanks, that was exactly I was looking for. Do they have it documented in CrudRepository page, or discover by reading the code? – Espresso Sep 26 '13 at 14:55
Thanks. That "gem is hidden in the appendix B", rightly so :) – Espresso Sep 27 '13 at 14:54
1

For the method signature: List findByIdIn(List ids); I get the error: Caused by: java.lang.NumberFormatException: For input string: "(1, 2)" – user64141 Feb 14 '16 at 17:04
When accessing this search method via Spring Data REST How would you specify multiple inventoryIds in an HTTP GET request? – Miguel Pereira Apr 29 '16 at 15:54
@OliverGierke following repository method `@Modifying @Query(value = "SELECT * FROM badge WHERE threshold_type=?1 AND threshold2 AND id NOT IN (SELECT id FROM badge_achieved WHERE user_id=?3)", nativeQuery = true) List getAchievableBadgeByThreshold(BadgeThresholdType thresholdType, Integer threshold, Long userId);` is not returning any result. If I run same query with same bounded parameter it's working. Any hint? – Piyush May 08 '17 at 13:58
2

I'm curious does author of the question(or even answer) checked the number of queries that spawns by accepted solution. From the question: "Firing queries for each id may not be optimal.". Accepted code will do query per entity. So if inventoryIdList.size == 100 this code will do 100 selects with possible joins and stuff. – chill appreciator Jun 25 '20 at 00:31
If `inventoryId` has the primary key, you can simply use `yourRepo.findAll(inventoryIds)` – Marco Sulla Jan 26 '23 at 13:38

score 139 · Answer 2 · edited Aug 11 '16 at 20:26

139

For any method in a Spring CrudRepository you should be able to specify the @Query yourself. Something like this should work:

@Query( "select o from MyObject o where inventoryId in :ids" )
List<MyObject> findByInventoryIds(@Param("ids") List<Long> inventoryIdList);

edited Aug 11 '16 at 20:26

Athar

579
5
12

answered Sep 24 '13 at 16:47

digitaljoel

26,265
15
89
115

1

Thanks, this works. Was looking for a "more cleaner" solution i.e without writing the @Query. – Espresso Sep 24 '13 at 19:23
3

Oliver Gierke is the man that would know the answer to this and he has the "more cleaner" solution. You should accept his answer. – digitaljoel Sep 25 '13 at 16:55
2

Great! I used a `Set` as a parameter, worked well. – Muneer Aug 20 '17 at 09:12
what if I want to pass 2 paramemeters to my method one list and other a normal string , will that work? if yes how shall I name my method – Varun Sood Sep 24 '18 at 07:46

score 43 · Answer 3 · answered May 11 '17 at 15:10

Yes, that is supported.

Check the documentation provided here for the supported keywords inside method names.

You can just define the method in the repository interface without using the @Query annotation and writing your custom query. In your case it would be as followed:

List<Inventory> findByIdIn(List<Long> ids);

I assume that you have the Inventory entity and the InventoryRepository interface. The code in your case should look like this:

The Entity

@Entity
public class Inventory implements Serializable {

  private static final long serialVersionUID = 1L;

  private Long id;

  // other fields
  // getters/setters

}

The Repository

@Repository
@Transactional
public interface InventoryRepository extends PagingAndSortingRepository<Inventory, Long> {

  List<Inventory> findByIdIn(List<Long> ids);

}

This works for all interfaces that are extending the **CrudRepository** interface. — Dzinot, May 11 '17 at 15:16
This won't work if the ids size is over 1000 or certain size depending on the DB. How about this? **List findByIdIn(List ids, Pageable pageable);** — Julie, Feb 08 '19 at 20:25

Spring CrudRepository findByInventoryIds(List inventoryIdList) - equivalent to IN clause

3 Answers3

Linked

Related