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.

Have you tried List<Meal> findMealByType(String mealType); ? — Sudhir Ojha
– Sudhir Ojha, Commented Dec 24, 2019 at 11:08
you use spring data jpa, why not findByMealType(MealType mealType); nothing to add. — chillworld
– chillworld, Commented Dec 24, 2019 at 11:13

Simon Martinelli · Accepted Answer · 2019-12-24 11:09:53Z

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, 2019 at 11:09

Simon Martinelli

36.6k6 gold badges62 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cleaner Over a year ago

you actually can: :#{#mealType.name()} see stackoverflow.com/questions/44460394/…

aayoustic Over a year ago

Do you have any reference doc for this answer?

tharanga-dinesh · Accepted Answer · 2019-12-24 11:25:46Z

-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, 2019 at 11:25

answered Dec 24, 2019 at 11:12

tharanga-dinesh

5677 silver badges26 bronze badges

2 Comments

tharanga-dinesh Over a year ago

"m.mealType = :mealType" ? @chillworld

Arun Vinoth PrecogTechnologies Over a year ago

Add some context by explaining how your answer solve the problem in question, instead of posting code-only answer.

Collectives™ on Stack Overflow

Spring Boot JPA native query for enum values

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related