[SOLVED] Spring Boot JPA native query for enum values

Spring Boot JPA native query for enum values

I am trying to write a native query to search from a table based on EnumType entity. This ENUM MealType is a part of @Table Meal.

@Column(name = "meal_type")
@Enumerated(EnumType.STRING)
private MealType mealType;

Now, my query is:

@Repository
public interface MealRepository extends JpaRepository<Meal, Long> {
@Query(value ="select * from meal m where m.meal_type = ?1", nativeQuery = true)
    List<Meal> findMealByType(MealType mealType);
}

But when I run a test on it, I keep getting org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Apart from that, I have also tried to re-write the query with MealType as a parameter:

@Query(value ="select * from meal m where m.meal_type in :meal_type ", nativeQuery = true)
List<Meal> findMealByType(@Param("meal_type") MealType mealType);

but it caused a different kind of error

InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select * from meal m where m.meal_type in ? ]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

I would expect that there is some problem somewhere else, but the same customized query with search based on ID works fine.

Solution

You cannot use enums and SQL. You have to pass the parameter as String:

@Repository
public interface MealRepository extends JpaRepository<Meal, Long> {

    @Query(value ="select * from meal m where m.meal_type = ?1", nativeQuery = true)
    List<Meal> findMealByType(String mealType);