JPA原生查询用citext时IN子句传字符串列表报错

1. 修复参数绑定不匹配问题 #

你遇到的Named parameter not bound : ingredientFilter错误，核心原因是参数名称不匹配：

• SQL查询里用的参数是:ingredientFilter
• 但@Param注解指定的参数名是"ingredient"

两种修改方式二选一：

方式一：同步@Param的参数名 #

@Query(value = "select personal_recipes.name, personal_recipes.type, personal_recipes.comments, " +
        "personal_recipes.instructions, personal_recipes.rating, ingredients.name, ingredients.quantity " +
        "from personal_recipes " +
        "inner join ingredients on personal_recipes.name = ingredients.recipe_name " +
        "where (ingredients.name::citext in (:ingredientFilter))" , nativeQuery = true)
List<PersonalRecipesEntity> getPersonalRecipesByIngredient(@Param(value = "ingredientFilter") List<String> ingredientFilter);

方式二：同步SQL中的参数名 #

@Query(value = "select personal_recipes.name, personal_recipes.type, personal_recipes.comments, " +
        "personal_recipes.instructions, personal_recipes.rating, ingredients.name, ingredients.quantity " +
        "from personal_recipes " +
        "inner join ingredients on personal_recipes.name = ingredients.recipe_name " +
        "where (ingredients.name::citext in (:ingredient))" , nativeQuery = true)
List<PersonalRecipesEntity> getPersonalRecipesByIngredient(@Param(value = "ingredient") List<String> ingredientFilter);

2. 修复返回结果与实体类匹配问题 #

当前查询返回的是两张表的字段，但方法返回的是List<PersonalRecipesEntity>，如果实体类仅对应personal_recipes表，会出现字段不匹配的问题，可通过以下方式解决：

方式一：用DTO接收查询结果 #

定义一个DTO类来封装查询返回的所有字段：

// 自定义DTO类
public class RecipeIngredientDTO {
    private String recipeName;
    private String type;
    private String comments;
    private String instructions;
    private Integer rating;
    private String ingredientName;
    private String quantity;

    // 全参构造函数
    public RecipeIngredientDTO(String recipeName, String type, String comments, String instructions, Integer rating, String ingredientName, String quantity) {
        this.recipeName = recipeName;
        this.type = type;
        this.comments = comments;
        this.instructions = instructions;
        this.rating = rating;
        this.ingredientName = ingredientName;
        this.quantity = quantity;
    }

    // 按需添加getter方法
}

// 修改Repository方法的返回类型
@Query(value = "select personal_recipes.name, personal_recipes.type, personal_recipes.comments, " +
        "personal_recipes.instructions, personal_recipes.rating, ingredients.name, ingredients.quantity " +
        "from personal_recipes " +
        "inner join ingredients on personal_recipes.name = ingredients.recipe_name " +
        "where (ingredients.name::citext in (:ingredientFilter))" , nativeQuery = true)
List<RecipeIngredientDTO> getPersonalRecipesByIngredient(@Param(value = "ingredientFilter") List<String> ingredientFilter);

方式二：使用@SqlResultSetMapping映射结果 #

在PersonalRecipesEntity上定义结果集映射，让Hibernate知道如何映射多表字段：

@Entity
@SqlResultSetMapping(
    name = "RecipeIngredientMapping",
    classes = @ConstructorResult(
        targetClass = PersonalRecipesEntity.class,
        columns = {
            @ColumnResult(name = "name"),
            @ColumnResult(name = "type"),
            @ColumnResult(name = "comments"),
            @ColumnResult(name = "instructions"),
            @ColumnResult(name = "rating"),
            @ColumnResult(name = "ingredient_name"),
            @ColumnResult(name = "quantity")
        }
    )
)
public class PersonalRecipesEntity {
    // 实体类原有字段，需添加对应上述列的构造函数
    public PersonalRecipesEntity(String name, String type, String comments, String instructions, Integer rating, String ingredientName, String quantity) {
        // 给实体类字段赋值
        this.name = name;
        this.type = type;
        this.comments = comments;
        this.instructions = instructions;
        this.rating = rating;
        // 注意：如果实体类没有ingredientName和quantity字段，需添加对应字段
        this.ingredientName = ingredientName;
        this.quantity = quantity;
    }

    // 其他构造函数、字段、getter/setter
}

// 修改查询语句，给字段加别名并指定映射名
@Query(value = "select personal_recipes.name, personal_recipes.type, personal_recipes.comments, " +
        "personal_recipes.instructions, personal_recipes.rating, ingredients.name as ingredient_name, ingredients.quantity " +
        "from personal_recipes " +
        "inner join ingredients on personal_recipes.name = ingredients.recipe_name " +
        "where (ingredients.name::citext in (:ingredientFilter))" , 
        nativeQuery = true,
        resultSetMapping = "RecipeIngredientMapping")
List<PersonalRecipesEntity> getPersonalRecipesByIngredient(@Param(value = "ingredientFilter") List<String> ingredientFilter);

3. 确认citext扩展已启用 #

确保你的PostgreSQL数据库已经安装citext扩展，若未安装，执行以下SQL启用：

CREATE EXTENSION IF NOT EXISTS citext;

内容的提问来源于stack exchange，提问作者778899