JPQL Create new object using a query with dynamic order by and limit

Question

I am able to create my custom DTO by using this JPQL query:

@Query("SELECT new com.mycompany.dto.UserDetailsDTO(vu.id, ru.active, ru.firstname, ru.lastname, ru.username, vu.logins, ru.email, COUNT(li.creator)) "
            + "FROM User vu inner join RemoteUser ru on vu.remoteUser = ru.username "
            + "inner join Item li on li.creator = vu.id "
            + "group by li.creator, ru.active, ru.firstname, ru.lastname, ru.username, vu.logins, vu.id, ru.email")
List<UserDetailsDTO> getAllUsers();

Now I want to add order by, ASC/DESC, limit, offset to the above query to get the result based on dynamic params something like this:

@Query("SELECT new com.mycompany.dto.UserDetailsDTO(vu.id, ru.active, ru.firstname, ru.lastname, ru.username, vu.logins, ru.email, COUNT(li.creator)) "
            + "FROM User vu inner join RemoteUser ru on vu.remoteUser = ru.username "
            + "inner join Item li on li.creator=vu.id "
            + "group by li.creator, ru.active, ru.firstname, ru.lastname, ru.username, vu.logins, vu.id, ru.email "
            + "order by = :orderBy :orderDir and offset = :pageNo and limit = :pageSize")
    List<UserDetailsDTO> getAllUsers(@Param("pageNo") int pageNo,
                                     @Param("pageSize") int pageSize,
                                     @Param("orderDir") String orderDir,
                                     @Param("orderBy") String orderBy);

But it is not working and the error is:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: = near line 1, column 427

Already tried with passing pageable as a param:

Page<UserDetailsDTO> getAllUsers(Pageable pageable)

and preparing page request like:

PageRequest pageRequest = PageRequest.of(pageNo, pageSize, Sort.by(Sort.Direction.valueOf(orderDir), orderFieldUid));

Also, I tried with

+ "order by ?4 ?3 and offset ?1 and limit ?2")

Is there any way to add dynamic params as order by and offset to it? I want similar to something like this in JPQL.

Answer 1

Finally, I fixed it by using Page<UserDetailsDTO> getAllUsers(Pageable pageable) No need to pass params in the query. Hibernate will take the params internally from pageable