Using Pagination & Sorting in Spring Boot 4 with CrudRepository & PagingAndSortingRepository on JDBC

The issue is because of the count query which is generated for the pageable object by Spring.

See the javadoc for countQuery

String countQuery

Defines a special count query that shall be used for pagination queries to look up the total number of elements for a page. If none is configured we will derive the count query from the original query or countProjection() query if any.

In your case, it is using the original query (including the sort) to perform the count which results in an invalid query.

My advice would be to add a custom count query which excludes the sort and everything should work as expected.

    @Query(
            value = "FROM Demo d WHERE d.owner = :owner",
            countQuery = "SELECT count(1) FROM Demo d WHERE d.owner = :owner"
    )
    Page<Demo> findByOwner(@Param("owner") String owner, Pageable pageable);

In Spring Boot 4.0 with Spring Data JDBC 2024.0, Pageable.getSortOr(...) started behaving differently than in Spring Boot 3.2. The sort provided by getSortOr() is now applied to both the main query and the COUNT query used for pagination.

Because of this, a query like:

SELECT COUNT(*) 
FROM demo 
WHERE owner = ? 
ORDER BY amount ASC;

is generated when no sort is provided by the client. H2 does not allow ORDER BY in a count query unless the column is also in a GROUP BY clause, which leads to the error:

Column "DEMO.AMOUNT" must be in the GROUP BY list

Using pageable.getSort() works fine because it only returns user-provided sorting and leaves the COUNT query unsorted, so H2 doesn’t complain.

The problem with combining pagination and sorting in your controller comes from recreating the Pageable:

PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort())

Spring MVC already constructs a correct Pageable object. Rebuilding it can interfere with default values and fallback sorting, which causes issues in Spring Boot 4.

The simplest solution is to pass the pageable directly to the repository:

@GetMapping
public ResponseEntity<List<Demo>> readDemos(Pageable pageable, Principal principal) {
    Page<Demo> page = demoRepository.findByOwner(principal.getName(), pageable);
    return ResponseEntity.ok(page.getContent());
}

Also, in your test, there’s a small typo in the URL:

/demo?page=0&size=1sort=amount,desc

It should be:

/demo?page=0&size=1&sort=amount,desc

Without the &, Spring treats it as a single parameter and the sort is ignored.

With this approach, pagination and sorting work together correctly, and the H2 error is avoided.

It's not clear what error you're encountering. A clear error message from your logs would help. In the meantime, I tried this quickly and it works:

is used page PageableDefault (it makes code little clear)

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Data;

@Data
@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    private String name;
    private String ownerId;
    private Double price;
}

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;

public interface ProductRepository extends CrudRepository<Product, Long> {
    Page<Product> findByOwnerId(String owner, Pageable pageable);

    String id(Long id);
}

@RestController
@AllArgsConstructor
public class ProductController {

    private final ProductRepository productRepository;

    @GetMapping("/product")
    private ResponseEntity<Page<Product>> readDemos(@RequestParam("ownerId") String ownerId,
                                                    @PageableDefault(
                                                            page = 0,
                                                            size = 5, sort = {"price"},
                                                            direction = Sort.Direction.DESC) Pageable pageable) {
        Page<Product> page = productRepository.findByOwnerId(ownerId, pageable);

        return ResponseEntity.ok(page);
    }
}

@SpringBootTest
@AutoConfigureMockMvc
@Import(TestcontainersConfiguration.class)
class ProductControllerIntegrationTest {

    @Autowired
    private MockMvc mockMvc;

    @Autowired
    private ProductRepository productRepository;

    @BeforeEach
    void setUp() {
        productRepository.deleteAll();
        // Add 10 products for the same ownerId with ascending price
        IntStream.rangeClosed(1, 10).forEach(i -> {
            Product p = new Product();
            p.setName("Product " + i);
            p.setOwnerId("owner1");
            p.setPrice((double) (i * 10));
            productRepository.save(p);
        });
    }

    @Test
    void testPaginationAndSortingByPriceDesc() throws Exception {
        mockMvc.perform(get("/product")
                        .param("ownerId", "owner1")
                        .param("page", "0")
                        .param("size", "3")
                        .param("sort", "price,desc")
                        .accept(MediaType.APPLICATION_JSON))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.content.length()").value(3))
                .andExpect(jsonPath("$.content[0].price").value(100.0))
                .andExpect(jsonPath("$.content[1].price").value(90.0));
    }
}