Can I use enum parameter into JpaRepository nativeQuery?

Question

Entity looks like this:

@Getter
@Setter
@Entity
public class Application {
@Id
private Long id;
@Enumerated(EnumType.STRING)
private ApplicationStatus status;
}

Code works this way:

public interface ApplicationRepository extends JpaRepository<Application, Long> {
@Query("SELECT app FROM #{#entityName} AS app WHERE app.status LIKE :status")
List<Application> find(@Param("status") ApplicationStatus status);

But the same snippet with nativeQuery - doesn't:

@Query(value = "SELECT app.* FROM application AS app WHERE app.status LIKE :status", nativeQuery = true)
List<Application> findNative(@Param("status") ApplicationStatus status);
}

And I don`t have any exception, just empty list.

How can I fix this? Is it possible to use enum with nativeQuery?

P.S I can pass String into method instead of ApplicationStatus but maybe there are another option?

Do you get any error? After run snippet with nativeQuery, what do you get? — the_bluescreen, Jun 09 '17 at 14:35
Possible duplicate of [Problems with making a query when using Enum in entity](https://stackoverflow.com/questions/8217144/problems-with-making-a-query-when-using-enum-in-entity) — Yaroslav Boichuk, Jun 09 '17 at 14:42
Using `String` type of `status` parameter into method with nativeQuery instead of enum `ApplicationStatus` is good solution for me. — Dmitry Stolbov, Nov 21 '17 at 07:06
Passing the value as a String instead is a workaround, but it would be good to know if there is a better solution. It appears to work as expected if a JPA query, but returns empty results if a native query — Kevin Hooke, Oct 12 '18 at 20:37

score 26 · Answer 1 · answered Jan 21 '20 at 11:56

26

Following similar question with similar requirement and one of the answers pointing to Spring Expression Language (SpEL) you could use:

public interface ApplicationRepository extends JpaRepository<Application, Long> {
    @Query(nativeQuery = true, value = "SELECT app FROM #{#entityName} AS app WHERE app.status=:#{#status.name()}")
    List<Application> find(@Param("status") ApplicationStatus status);
}

Above important part is app.status=:#{#status.name()}

answered Jan 21 '20 at 11:56

Ivar

4,350
2
27
29

1

Hi, thanks for your response, but how to handle situation when passing status as null ? – Vladimir Titov Feb 25 '22 at 08:04
I would suggest checking it within the curly braces of an expression `#{ ... }` e.g. `#status != null && ...` but I am not sure if expression engine can handle it – Ivar Feb 25 '22 at 13:19
To manage the null values, you need to add "?" : `app.status=:#{#status?.name()}` – Julien Kronegg Mar 20 '23 at 13:07

score 21 · Answer 2 · edited Aug 25 '21 at 14:44

21

To extend on @Aivaras answer: If you want to use a list of statuses, the SpEL expression is slightly different - you need to do a projection:

public interface ApplicationRepository extends JpaRepository<Application, Long> {
    @Query(nativeQuery = true, value = "SELECT app FROM #{#entityName} AS app WHERE app.status in :#{#statuses.![name()]}")
    List<Application> find(@Param("statuses") List<ApplicationStatus> statuses);
}

Note the change of expression to

#{#statuses.![name()]}

edited Aug 25 '21 at 14:44

Aserre

4,916
5
33
56

answered Nov 29 '20 at 11:02

Alexey

2,388
1
16
32

If you don't mind, Could you explain the syntax as to how spEL works for collection.? – Arun Gowda Apr 01 '21 at 10:06
1

@ArunGowda it's called collection projection https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#expressions-collection-projection . So out of collection of enums we create collection of strings – Alexey Apr 02 '21 at 12:38

score 4 · Answer 3 · edited Mar 20 '23 at 13:06

4

I solved this issue using #{#paramName?.name()}

public interface ItemRepository extends JpaRepository<Item, Long> {
    @Query(value = "select * from items where type = :#{#type?.name()}", nativeQuery = true)
    List<Item> findByType(@Param("type") EnumType type);
}
 
public enum EnumType { NORMAL, LARGE };

Note: the "?" manages the case when the type parameter is null.

edited Mar 20 '23 at 13:06

Julien Kronegg

4,968
1
47
60

answered May 31 '21 at 11:15

Ousama

2,176
3
21
26

score 3 · Answer 4 · answered Apr 07 '21 at 04:44

After a few days with this error, I was able to find the solution.

I did a lot of research and tested in many ways receiving as a parameter @Param("environment") environment: Environment:

 :#{#environment.TESTING}
 :#{#environment}
 :#{#environment.name()}
 CAST(:#{#environment.name()} as environment)

Solution in Kotlin

The key is in the query. You must to transform the value of the parameter to a String using .name() (or receive a String as a parameter) and cast that value of type String to the specific Enum that is needed. Because passing an object of type Enum directly in the query does not work.

Assuming the Enum in your database is defined as environment.

@Query(
        value = "SELECT some_routine(CAST(:#{#environmentNamedParam.name()} as environment))",
        nativeQuery = true
)
fun yourFunction(
        @Param("environmentNamedParam") environmentParam: Environment
) : Boolean

Differentiate between:

environmentNamedParam
environmentParam
environment

#spring #jpa #postgresql #kotlin

score 0 · Answer 5 · answered Jul 18 '17 at 08:00

0

You could convert to string before passing the params.

answered Jul 18 '17 at 08:00

johnlemon

20,761
42
119
178

5

As i write above "P.S I can pass String into method instead of ApplicationStatus but maybe there are another option?" – Tetiana Serediuk Jul 19 '17 at 09:01

score -3 · Answer 6 · answered Jun 09 '17 at 17:49

-3

how about this?

public interface ApplicationRepository extends JpaRepository<Application, Long> {
List<Application> findByStatus(ApplicationStatus status);

answered Jun 09 '17 at 17:49

David M.

77
1
13

3

In my question I use simplified example, my real code way more complicated and I need to use 'nativeQuery'. – Tetiana Serediuk Jun 12 '17 at 07:51

Can I use enum parameter into JpaRepository nativeQuery?

6 Answers6

Solution in Kotlin

Linked