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?

Trawick

Do you get any error? After run snippet with nativeQuery, what do you get? – Farnham

Possible duplicate of Problems with making a query when using Enum in entity – Provitamin

@Farnham I don`t have any exception, just empty list – Trawick

Using String type of status parameter into method with nativeQuery instead of enum ApplicationStatus is good solution for me. – Podite

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 – Childress

Answer 1

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()}

Misfeasance

Hi, thanks for your response, but how to handle situation when passing status as null ? – Seaward

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 – Misfeasance

To manage the null values, you need to add "?" : app.status=:#{#status?.name()} – Skaggs

Answer 2

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()]}

Orelu

If you don't mind, Could you explain the syntax as to how spEL works for collection.? – Micron

@ArunGowda it's called collection projection docs.spring.io/spring-framework/docs/current/reference/html/… . So out of collection of enums we create collection of strings – Orelu

Answer 3

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.

Announcement

Answer 4

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

Abampere

Answer 5

You could convert to string before passing the params.

Brockbrocken

As i write above "P.S I can pass String into method instead of ApplicationStatus but maybe there are another option?" – Trawick

Answer 6

With Hibernate 5, I was using the following:

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);
}

But this doesn't work anymore with Hibernate 6 (tested with 6.2.9, maybe due to https://hibernate.atlassian.net/browse/HHH-17006), so now I'm using the following (the enum is passed as String to the native query, and the enum->name conversion is done in a default method; requires Java 8):

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

    default List<Application> find(@Param("status") ApplicationStatus status) {
        return find(status != null ? status.name() : null);
    }
}

Skaggs

FYI: I have tried on v6.4.4 hibernate, the top method works. – Bookstall

Answer 7

Just use setOf(ENUMVALUE1.name, ENUMVALUE2.name)

Filibeg

Answer 8

how about this?

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

Siegfried

In my question I use simplified example, my real code way more complicated and I need to use 'nativeQuery'. – Trawick