CODEWITHSUNDEEP
javapostgresqlspring-bootspring-data-jpa
app
app

Reputation: 783

spring jpa query where clause with enum type matching

I'm using the spring-data-jpa for object relational mapping with spring boot and postgreSql, and wrote custom method to get the distinct value of task_id column when matching to where condition 

@Query(value = "select distinct task_id from schema_task_test.test_table where type =:type", nativeQuery = true)
public List<Integer> findDistinctTasks(@Param("type") String type);

But in database type is of Enum type so I'm getting execption

Error

[ERROR] 2019-03-19 16:33:45,006 http-nio-8080-exec-1 org.hibernate.engine.jdbc.spi.SqlExceptionHelper - {} - ERROR: operator does not exist: schema_task_test.type_enum = character varying

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

Upvotes: 0

Views: 3337

Answers (1)

minibi
minibi

Reputation: 50

In a native query you have to cast explicit to the database enum type.

@Query(value = "select distinct task_id from schema_task_test.test_table where type = cast(:type as type)", nativeQuery = true)

You also should use the @Enumerated in the entity.

When you want to save the values as string you can use @Enumerated(EnumType.STRING)

Upvotes: 1

Related Questions