CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
Vlad Demyan
Vlad Demyan

Reputation: 363

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.

Upvotes: 4

Views: 11542

Answers (2)

tharanga-dinesh
tharanga-dinesh

Reputation: 567

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

Upvotes: -3

Simon Martinelli
Simon Martinelli

Reputation: 36133

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

Upvotes: 8

Related Questions