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 389 · Accepted Answer · edited Feb 15 '17 at 15:41

389

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.

edited Feb 15 '17 at 15:41

nietaki

8,227
2
43
54

answered Sep 25 '13 at 04:42

Oliver Drotbohm

75,857
17
214
207

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
1

It's actually listed in the [reference documentation](http://docs.spring.io/spring-data/mongodb/docs/current/reference/htmlsingle/#repository-query-keywords). – Oliver Drotbohm Sep 27 '13 at 14:09
Thanks. That "gem is hidden in the appendix B", rightly so :) – Espresso Sep 27 '13 at 14:54
11

[Reference docs](http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repository-query-keywords) URL changed – Mayjak Sep 08 '14 at 09:16
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
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

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

127

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

559
5
12

answered Sep 24 '13 at 16:47

digitaljoel

25,818
15
86
115

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. – BlueBird 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 – user3614936 Sep 24 '18 at 07:46

score 35 · 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