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.

you use spring data jpa, why not `findByMealType(MealType mealType);` nothing to add. — chillworld, Dec 24 '19 at 11:13

score 8 · Accepted Answer · answered Dec 24 '19 at 11:09

8

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

answered Dec 24 '19 at 11:09

Simon Martinelli

34,053
5
48
82

4

you actually can: :#{#mealType.name()} see https://stackoverflow.com/questions/44460394/can-i-use-enum-parameter-into-jparepository-nativequery – cleaner Aug 28 '20 at 09:16
Do you have any reference doc for this answer? – aayoustic Nov 15 '22 at 17:26

tharanga-dinesh · Answer 2 · 2019-12-24T11:25:46.713

-3

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

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

edited Dec 24 '19 at 11:25

answered Dec 24 '19 at 11:12

tharanga-dinesh

537
6
26

"m.mealType = :mealType" ? @chillworld – tharanga-dinesh Dec 24 '19 at 11:17
Add some context by explaining how your answer solve the problem in question, instead of posting code-only answer. – Arun Vinoth-Precog Tech - MVP Dec 24 '19 at 17:24

Spring Boot JPA native query for enum values

2 Answers2