Spring Data JPA自定义原生分页查询参数绑定异常处理指南

在使用spring data jpa的`@query`注解结合`pageable`进行自定义原生查询时，若主查询包含参数而`countquery`不包含，可能会遇到`illegalargumentexception`。这是因为spring data jpa在执行`countquery`时，会尝试绑定主查询的所有参数。解决方案是在`countquery`中添加一个不影响逻辑的虚拟参数引用，使其能够成功接收并忽略传入的参数，从而避免异常。

Spring Data JPA自定义原生分页查询中的参数绑定异常

Spring Data JPA极大地简化了数据访问层的开发，特别是在处理分页查询时，通过Pageable接口提供了强大的支持。然而，当开发者需要使用自定义的原生SQL查询，并且这些查询涉及参数和分页时，可能会遇到一个常见的IllegalArgumentException。本教程将深入探讨这一问题的原因，并提供一个简洁有效的解决方案。

问题描述

考虑一个场景，我们需要根据某个地理位置点（point）计算距离，并对结果进行分页和排序。以下是一个典型的Spring Data JPA Repository方法定义，它使用了一个自定义的原生查询：

public interface AdvertSearchRepository extends JpaRepository {

    @Query(value = "SELECT *, ST_Distance_Sphere(ST_GeomFromText(?1, 4326), location) AS distance FROM Advert_Search ORDER BY distance ",
            countQuery = "SELECT count(*) FROM Advert_Search",
            nativeQuery = true)
    Page findAllSortByDistance(String point, Pageable pageable);
}

尽管主查询(value)能够正确执行并返回分页数据，但应用程序却抛出了IllegalArgumentException，错误信息类似于：Could not locate ordinal parameter [1], expecting one of []。这表明在某个查询执行阶段，Spring Data JPA尝试绑定一个参数，但查询本身并没有声明或预期这个参数。

异常根源分析

这个异常的根本原因在于Spring Data JPA（底层通过Hibernate）处理分页查询的机制。当一个Pageable参数被传递给Repository方法时，Spring Data JPA不仅会执行主查询（用于获取当前页的数据），还会执行一个独立的countQuery（用于获取总记录数，以便计算总页数）。

问题出在countQuery上：

• 主查询 (value)：SELECT *, ST_Distance_Sphere(ST_GeomFromText(?1, 4326), location) AS distance FROM Advert_Search ORDER BY distance 明确使用了参数 ?1 (即 point 变量)。
• 计数查询 (countQuery)：SELECT count(*) FROM Advert_Search 没有 使用任何参数。

Spring Data JPA在执行countQuery时，会尝试将主查询的所有参数（在这里是point）绑定到countQuery上。然而，由于countQuery本身并没有定义任何参数位（例如?1），Hibernate在尝试绑定第一个序数参数时就会失败，从而抛出IllegalArgumentException。尽管countQuery在逻辑上并不需要point参数来计算总数，但这种参数绑定机制的期望是主查询和计数查询的参数列表应该保持一致。

MagickPen

在线AI英语写作助手，像魔术师一样在几秒钟内写出任何东西。

下载

解决方案：引入虚拟参数

解决此问题的关键是修改countQuery，使其在语法上能够接收主查询的参数，即使它在逻辑上并不使用这些参数。我们可以通过添加一个“虚拟”的WHERE子句来实现这一点，该子句永远为真，并且引用了传入的参数。

修改后的Repository方法如下：

public interface AdvertSearchRepository extends JpaRepository {

    @Query(value = "SELECT *, ST_Distance_Sphere(ST_GeomFromText(?1, 4326), location) AS distance FROM Advert_Search ORDER BY distance ",
            countQuery = "SELECT count(*) FROM Advert_Search WHERE (?1 IS NULL OR ?1 IS NOT NULL)", // 关键修改
            nativeQuery = true)
    Page findAllSortByDistance(String point, Pageable pageable);
}

在这个修改中，我们在countQuery中添加了 WHERE (?1 IS NULL OR ?1 IS NOT NULL)。

• ?1：引用了主查询中的第一个参数 point。
• (?1 IS NULL OR ?1 IS NOT NULL)：这是一个恒为真的条件。无论 point 参数的值是什么（null或非null），这个条件都会评估为真，因此不会影响计数查询的实际结果。

通过这种方式，countQuery现在在语法上声明了它接受一个参数 ?1。当Spring Data JPA尝试绑定point参数时，它会成功地将其绑定到countQuery的?1位置，从而避免了IllegalArgumentException。

注意事项与最佳实践

1. 参数索引一致性：如果你的主查询有多个参数，例如 ?1, ?2 等，你需要确保countQuery中也包含对这些参数的虚拟引用。例如，如果主查询有 ?1 和 ?2，那么countQuery可能需要 WHERE (?1 IS NULL OR ?1 IS NOT NULL) AND (?2 IS NULL OR ?2 IS NOT NULL)。
2. 命名参数：如果你倾向于使用命名参数（例如 :point），则countQuery也应使用相应的命名参数引用，例如 WHERE (:point IS NULL OR :point IS NOT NULL)。
3. 复杂countQuery：在某些更复杂的场景中，countQuery可能确实需要不同的参数集，或者其逻辑与主查询差异很大。此时，简单地添加虚拟参数可能不够。在这种情况下，你可以考虑：
   • 手动实现分页逻辑，即不使用Pageable直接返回List，然后手动构造Page对象。
   • 如果countQuery可以独立于主查询的参数工作，但仍然导致问题，可以尝试使用@Query(value = "...", countQuery = "...", countProjection = "...")，并确保countProjection返回一个单一的数字列，且countQuery本身不带参数。但通常，虚拟参数方法更简单直接。
4. 数据库方言：本例中使用了MySQL的地理空间函数，并指定了MySQL56SpatialDialect。虚拟参数的语法（IS NULL OR IS NOT NULL）是标准SQL，因此在大多数数据库方言中都适用。

总结

当在Spring Data JPA中使用@Query注解进行自定义原生分页查询时，如果主查询带有参数而countQuery没有，会导致IllegalArgumentException。这是由于Spring Data JPA在执行计数查询时，会尝试绑定主查询的所有参数。通过在countQuery中添加一个不影响逻辑的虚拟WHERE子句（例如 WHERE (?1 IS NULL OR ?1 IS NOT NULL)），我们可以使countQuery在语法上接受这些参数，从而成功绕过异常，确保分页功能正常运行。这种方法简单有效，是处理此类问题的常用实践。