Announcement Announcement Module
No announcement yet.
@Query "countQuery" skip @Param argument - 1.1.0.RELEASE Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • @Query "countQuery" skip @Param argument - 1.1.0.RELEASE

    I work right now with Spring Data JPA, and find its really cool.
    I have not the common problem to solve, and I'm trying to solve it with Spring Data JPA if possible. I know several solutions (and they work) how to accomplish this with just custom implementation for my @Repository, but I still have the belief that maybe this can be done without custom implementation.

    So the problem I have that I need to make SELECT query, but ordering should be done based on some business calculation (and pagination is used, so it is not possible to sort the data directly in Java [would be good], and apply business logic there). So I use PL/SQL stored function to make the calculation and I use this function in the ORDER BY clause (function depends on the input @Param-eter).

    I found that @Query annotation supports countQuery attribute where custom count query can be configured. So my approach I try to make work now is to have the method defined something like this:

    @Query(value = "from MyTable myTable where prop1 = :prop1 and prop2 = :prop2 order by myfunction(prop3, :prop3)",
    countQuery = "select count(myTable) from MyTable myTable where prop1 = :prop1 and prop2 = :prop2")
    public Page<Something> findSomething(@Param("prop1") String prop1, @Param("prop2") String prop2, @Param("prop3") String prop3, Pageable pageable);
    I could use just @Query("main SELECT query"), but I don't want to execute myfunction during count query (it is quite expensive), so I would like to use almost the same count query as the main query except of ORDER BY clause.
    The problem I have is that Hibernate (the JPA provider I use) complains that it cann't find "prop3" parameter in my countQuery (and that's true, of course, because it is used only in the main SELECT query).

    So, finally the question - Is it possible to skip @Param argument for the countQuery? And just an assumption that probably this can be possible, otherwise what are the use cases for the countQuery attribute usage?