JPA原生查询IN子句中List参数绑定错误解析与实践

理解JPA原生查询中的参数绑定

在使用spring data jpa进行数据访问时，我们经常需要执行复杂的sql查询，此时原生查询（nativequery = true）就显得尤为重要。当查询中包含动态参数，特别是集合类型（如list）需要用于in子句时，参数的正确绑定是关键。hibernate作为jpa的默认实现，在处理命名参数时有严格的匹配要求。

常见问题：Named parameter not bound

许多开发者在初次尝试将List传入原生查询的IN子句时，会遇到org.hibernate.QueryException: Named parameter not bound : yourParameterName的错误。这个错误通常意味着Hibernate无法在SQL查询字符串中找到与@Param注解中指定名称相对应的参数占位符。

考虑以下一个尝试查询个人食谱中包含特定食材列表的示例：

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;

public interface PersonalRecipesRepository extends JpaRepository {

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

在上述代码中，尽管SQL查询在DBeaver等数据库客户端中可以正常运行，但在Spring Boot应用程序中执行时，却抛出了Named parameter not bound : ingredientFilter的异常。

问题分析

仔细观察上述代码，问题的根源在于@Param注解中value属性的值与SQL查询字符串中使用的参数占位符名称不一致：

• SQL查询中的占位符是 :ingredientFilter。
• @Param注解中指定的参数名为 value = "ingredient"。

Hibernate在解析查询时，会尝试将@Param("ingredient")绑定到名为ingredient的占位符。然而，SQL查询中并没有:ingredient这个占位符，而是:ingredientFilter。因此，Hibernate找不到对应的绑定，从而报告Named parameter not bound : ingredientFilter，因为它发现ingredientFilter这个占位符没有被任何@Param注解绑定。

值得注意的是，查询中使用的ingredients.name::citext是PostgreSQL特有的语法，用于将字段转换为citext类型以实现不区分大小写的比较。这与参数绑定问题无关，但表明原生查询可以充分利用数据库的特定功能。

解决方案

解决此问题的方法非常直接：确保@Param注解中value属性的值与SQL查询字符串中使用的参数占位符名称完全一致。

SEEK.ai

AI驱动的智能数据解决方案，询问您的任何数据并立即获得答案

下载

将@Param(value = "ingredient")修改为@Param(value = "ingredientFilter")即可。

以下是修正后的代码示例：

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;

public interface PersonalRecipesRepository extends JpaRepository {

    @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 = "ingredientFilter") List ingredientFilter);
}

通过这个简单的修改，Hibernate就能正确地将传入的List ingredientFilter参数绑定到SQL查询中的:ingredientFilter占位符，从而解决Named parameter not bound错误。

另一个工作示例

为了进一步巩固理解，可以参考另一个成功将List传递给IN子句的原生查询示例：

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

public interface PqrsRepository extends JpaRepository {

    @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") Timestamp fechaInicial,
                                        @Param("fechaFinal") Timestamp fechaFinal,
                                        @Param(value = "radicados") List radicados);
}

在这个示例中，@Param(value = "radicados")与SQL查询中的:radicados完美匹配，因此查询能够正常执行。同时，它也展示了如何结合其他类型的参数（如Timestamp）与List类型参数一起使用。

注意事项与最佳实践

1. 参数名严格匹配：这是解决Named parameter not bound错误的核心。无论是原生查询还是JPQL查询，@Param注解中的名称必须与查询字符串中的命名参数占位符（例如:paramName）完全一致。
2. List参数与IN子句：当使用List类型参数时，Hibernate会自动将其展开为IN子句所需的多个值（例如('value1', 'value2', 'value3')），无需手动拼接字符串。
3. nativeQuery = true：明确声明查询为原生SQL查询，以便JPA/Hibernate知道如何解析和执行它。
4. 数据库特定语法：原生查询允许使用数据库特定的函数和语法（如PostgreSQL的::citext），这在JPQL中通常是无法实现的。
5. 安全性：使用命名参数（如:ingredientFilter）是防止SQL注入的推荐做法，因为它会自动处理参数值的转义。避免手动拼接SQL字符串。
6. 可读性：对于复杂的原生查询，可以考虑将SQL语句定义为常量或外部文件，以提高代码的可读性和维护性。

总结

Named parameter not bound错误在JPA原生查询中是一个常见的陷阱，尤其是在处理List类型参数时。通过确保@Param注解中的参数名称与SQL查询字符串中的命名参数占位符完全匹配，可以轻松解决这一问题。遵循上述最佳实践，不仅能有效避免此类错误，还能编写出更安全、可维护且功能强大的JPA数据访问层代码。