java - 枚举值的 Spring Boot JPA native 查询

Question

我正在尝试编写 native 查询以从基于 EnumType 实体的表中进行搜索。这个 ENUM MealType 是@Table Meal 的一部分。

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

现在，我的查询是:

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

但是当我对其运行测试时，我不断收到 org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet;嵌套异常是 org.hibernate.exception.GenericJDBCException: 无法提取 ResultSet

除此之外，我还尝试使用 MealType 作为参数重写查询:

  @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:无法准备语句； SQL [select * from meal m where m.meal_type in ? ];嵌套异常是 org.hibernate.exception.SQLGrammarException: 无法准备语句

我预计其他地方会出现一些问题，但使用基于 ID 的搜索的相同自定义查询工作正常。

Answer 1

您不能使用枚举和 SQL。您必须将参数作为字符串传递:

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