2

I've been trying to figure out how to take an input of a list of enums for my sql query. This is what it looks like:

@Query(value = "Select * from employees where city in :cities", nativeQuery = true)
List<Employee> findByCities(@Param("cities") List<City> cities);

I understand that for simple queries we can rely on the JPA Criteria API but I want to know if I can actually do it this way instead. Because if so, i can create more complicated queries (such as joins with another table) if I could have this flexibility of specifying the list.

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
SansMango
  • 21
  • 1
  • 3
  • `@Query` is not in "JPA". It is in "Spring Data JPA" (!= JPA API). That aside you are using SQL there nothing else, so the question should be directed at JDBC really – Neil Stockton Aug 11 '17 at 07:23

1 Answers1

4

Yes spring-data-jpa's @Query can take a list of enums.

This is my repository method

@Query("Select u from User u where u.userType in :types")
List<User> findByType(@Param("types") List<UserType> types);

This is my repository call

userRepository.findByType(Arrays.asList(AppConstant.UserType.PRINCIPLE)))

And this is the query logs

SELECT user0_.id           AS id1_12_, 
       user0_.date_created AS date_created2_12_, 
       ...
       ...
FROM   users user0_ 
WHERE  user0_.user_type IN ( ? )

Hope this helps.

PS: I tested this in my local machine with positive results.

Update 1

The same doesn't work with nativeQuery=true. I tested it on my local system and it doesn't seem to be working with native queries. However with JPQL it works fine as mentioned in the above answer.

May be this answer will help.

Abdullah Khan
  • 12,010
  • 6
  • 65
  • 78