I am using spring-data-jpa (2.1.7.RELEASE) with hibernate (5.6.9.Final) as ORM implementation.

I am creating Pageable object in service layer and passing it to repo layer -

Service Layer Code:

Pageable pg = PageRequest.of(4, 2);
Page<Tuple> packagesList = packageRepository.getPackageByAccountIdAndPackageId(accountId, packageId, pg);   

Repository Layer Code:

public interface PackageRepository extends JpaRepository<Packages, PackageId> { ...

and

@Query(value = "SELECT p.package_id FROM packages p WHERE p.account_id=:accountId AND (CASE WHEN :packageId IS NULL THEN TRUE WHEN  p.package_id LIKE :packageId THEN TRUE ELSE FALSE END)",
            countQuery = "SELECT count(*) FROM packages", nativeQuery = true)
Page<Tuple> getPackageByAccountIdAndPackageId(String accountId, String packageId, Pageable pg);

In the generated SQL - the limit restriction is getting added but no binding values are getting generated. Below should clarify that -

Query and Binding Params

Hibernate: 
    SELECT
        p.package_id 
    FROM
        packages p 
    WHERE
        p.account_id=? 
        AND (
            CASE 
                WHEN ? IS NULL THEN TRUE 
                WHEN  p.package_id LIKE ? THEN TRUE 
                ELSE FALSE 
            END
        ) limit ?, ?
2022-10-15 18:43:46.285 TRACE 68021 --- [nio-8095-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [40955353-d2c3-4377-8c95-6b403b7570ef]
2022-10-15 18:43:46.286 TRACE 68021 --- [nio-8095-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [%2]
2022-10-15 18:43:46.286 TRACE 68021 --- [nio-8095-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [%2]
2022-10-15 18:43:46.328 TRACE 68021 --- [nio-8095-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.orm.jpa.EntityManagerHolder@41baa13] for key [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean@7317fb] bound to thread [http-nio-8095-exec-1]
2022-10-15 18:43:46.329 TRACE 68021 --- [nio-8095-exec-1] .s.t.s.TransactionSynchronizationManager : Retrieved value [org.springframework.orm.jpa.EntityManagerHolder@41baa13] for key [org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean@7317fb] bound to thread [http-nio-8095-exec-1]

Why binding parameters 4 and 5 corresponding to offset and limit are not getting generated even though the query is expecting them.

Note: The query is succeeding and I am not getting any exception.
Note: plz ignore the count query. It is intentionally incorrect. I added it to see if count query makes any difference. But it did not.
Q2 - Is count query mandatory for Pageable to work?

CodePudding user response：

JPA handles limit and offset separately from the rest of the query.

What Spring Data does for you is:

1. Create a query object from your SQL statement.
2. Bind parameters to it (excluding the pageable)
3. Call special methods for limit and offset

So these values get some special treatment in you JPA implementation and I guess this is why they don't get printed with normal bind parameters.

Also I seem to remember that some database doesn't allow setting these as bind parameters at all. Which might actually be the original reason for the special treatment.