Spring Boot JPA native query for enum values

Question

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.

Answer 1

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