JPA原生查询中列表参数与IN子句的正确用法

在JPA原生查询中处理列表参数的挑战

在使用spring data jpa进行开发时，我们经常需要执行原生sql查询来满足特定的业务需求，例如利用数据库特有的函数或优化查询性能。一个常见的场景是在where子句中使用in操作符，并传入一个字符串列表作为参数。然而，如果不正确地配置参数绑定，可能会遇到org.hibernate.queryexception: named parameter not bound这样的错误，即使相同的查询在数据库客户端（如dbeaver）中可以正常执行。

这个问题通常发生在尝试将一个List类型的参数绑定到原生查询的IN子句时。例如，以下是一个可能导致错误的查询示例：

// 错误示例：@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 getPersonalRecipesByIngredient(@Param(value = "ingredient") List ingredientFilter);

在这个例子中，查询字符串中的命名参数是:ingredientFilter，但@Param注解却将其绑定到了名为"ingredient"的参数。这种不匹配是导致Named parameter not bound错误的核心原因。即使查询中使用了citext进行不区分大小写的搜索，参数绑定机制的根本问题依然存在。

正确绑定列表参数到IN子句

解决上述问题的关键在于确保@Param注解的value属性与原生SQL查询中定义的命名参数完全一致。Hibernate/JPA能够智能地将一个List类型的参数展开为IN子句所需的多个参数。

以下是修正后的代码示例，展示了如何正确地绑定列表参数：

Sora

Sora是OpenAI发布的一种文生视频AI大模型，可以根据文本指令创建现实和富有想象力的场景。

下载

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;

// 假设PersonalRecipesEntity和IngredientsEntity是对应的JPA实体
// 假设PersonalRecipesEntity包含name, type, comments, instructions, rating等字段
// 假设IngredientsEntity包含name, quantity等字段，并有一个recipe_name字段关联PersonalRecipesEntity

public interface PersonalRecipesRepository extends JpaRepository {

    /**
     * 根据食材名称列表查询个人食谱。
     * 使用原生SQL查询，并通过citext进行不区分大小写的食材名称匹配。
     *
     * @param ingredientFilter 包含要查询的食材名称的列表。
     * @return 匹配到的个人食谱实体列表。
     */
    @Query(value = "select pr.name, pr.type, pr.comments, pr.instructions, pr.rating, i.name, i.quantity " +
            "from personal_recipes pr " +
            "inner join ingredients i on pr.name = i.recipe_name " +
            "where (i.name::citext in (:ingredientFilter))", nativeQuery = true)
    List getPersonalRecipesByIngredient(@Param("ingredientFilter") List ingredientFilter);

    /**
     * 另一个通用示例：根据日期范围和ID列表查询Pqrs实体。
     *
     * @param fechaInicial 查询起始日期。
     * @param fechaFinal 查询结束日期。
     * @param radicados 包含要查询的radicado（ID）列表。
     * @return 匹配到的Pqrs实体列表。
     */
    @Query(value = "select q.* from sde.pqrs q where q.fecha_radicado between :fechaInicial and :fechaFinal and q.radicado in (:radicados)", nativeQuery = true)
    List consultaRadicadoDeVisita(@Param("fechaInicial") java.sql.Timestamp fechaInicial,
                                        @Param("fechaFinal") java.sql.Timestamp fechaFinal,
                                        @Param("radicados") List radicados);
}

在上述修正后的getPersonalRecipesByIngredient方法中，@Param("ingredientFilter")与查询字符串中的:ingredientFilter完全匹配，从而解决了参数绑定问题。

注意事项与最佳实践

1. 参数名称一致性： 这是最关键的一点。@Param注解的value属性必须与原生SQL查询中使用的命名参数（例如:paramName）完全一致。
2. nativeQuery = true： 确保在@Query注解中设置nativeQuery = true，以指示JPA执行原生SQL查询。
3. 列表类型支持： JPA和Hibernate对IN子句中的List类型参数有良好的支持，它们会自动将列表展开为逗号分隔的多个参数。
4. 数据类型匹配： 确保传入列表中的元素类型与数据库列的类型兼容。如果需要类型转换（如citext），应在SQL查询中明确指定，如ingredients.name::citext。
5. 查询可读性： 对于复杂的原生查询，可以考虑使用多行字符串连接，或者将查询定义在外部文件中，以提高代码的可读性和维护性。
6. 安全性： 使用命名参数是防止SQL注入的推荐做法。避免直接拼接字符串来构建查询，尤其是在处理用户输入时。

总结

在JPA原生查询中使用List类型参数配合IN子句是一个常见的需求。解决Named parameter not bound错误的核心在于确保@Param注解的名称与原生SQL查询中的命名参数精确匹配。遵循这些简单的规则和最佳实践，可以帮助开发者更顺畅地在Spring Data JPA项目中执行复杂且高效的原生查询。