How to query a subselect with sorting and pageable in R2dbcRepository

Question

I am using R2dbcRepository and I think it does not support native queries. @Query(nativeQuery is giving an error.

I cannot seem to find a solution for the query without going native since I have join tables, subselect query, and Pageable.

I cannot get all the results from Registration and filter it out since I have Pageable (unless it is achievable in flatMaps?)

Does anyone have an idea on how to turn this query into reactive?

This is my query:

import org.springframework.data.r2dbc.repository.Query;

@Query(nativeQuery = true, value =
    "select R.ID, R.REGISTRATION_DATE, R.NAME, U.FULL_NAME, R.STATUS, R.LAST_MODIFIED_DATE, "
        + " (SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) AS LAST_MODIFIED_BY "
        + " FROM REGISTRATION R "
        + " INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID "
        + " WHERE U.SUBMITTED_REGISTRATION = TRUE "
        + " AND LOWER(U.FULL_NAME) LIKE like concat('%', LOWER(:fullName), '%') "
        + " order by :sortColumn :sortDirection limit :pageSize offset :pageNumber")
List<Object[]> findAllByAppUsers(@Param(value = "fullName") String fullName,
                                 @Param(value = "sortColumn") String sortColumn,
                                 @Param(value = "sortDirection") String sortDirection,
                                 @Param(value = "pageSize") int pageSize,
                                 @Param(value = "pageNumber") int pageNumber);

(1) If I remove the @Query(nativeQuery=true) and just use @Query("select...") and return a Flux<Object[]> findAllByAppUsers(...)

nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [42000] [42000] Syntax error in SQL statement "SELECT R.ID, R.REGISTRATION_DATE, R.NAME, U. FULL_NAME AS FULL_NAME, R.STATUS, R.LAST_MODIFIED_DATE,  (SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) AS LAST_MODIFIED_BY  FROM REGISTRATION R  INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID  WHERE U.SUBMITTED_REGISTRATION = TRUE  ORDER BY $1 $[*]2 LIMIT $3 OFFSET $4";

(2) If I remove the parameters, I get this error:

org.springframework.data.mapping.MappingException: Couldn't find PersistentEntity for type class java.lang.Object

(3) If I do @Query("select new com.my.dto.RegistrationDTO () ...")

nested exception is io.r2dbc.spi.R2dbcBadGrammarException: [42000] [42000] Syntax error in SQL statement "SELECT NEW COM.[*]MY.DTO.REGISTRATIONDTO (R.ID, R.NAME, U.FULL_NAME AS FULL_NAME,  (SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) AS LAST_MODIFIED_BY)  FROM REGISTRATION R  INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID  WHERE U.SUBMITTED_REGISTRATION = TRUE";

The SQL is working fine in postgreSQL:

SELECT R.ID, R.REGISTRATION_DATE, R.NAME, U.FULL_NAME AS FULL_NAME, R.STATUS, R.LAST_MODIFIED_DATE,
(SELECT ASSESSOR.FULL_NAME FROM JHI_USER ASSESSOR WHERE ASSESSOR.USERNAME = R.LAST_MODIFIED_BY) as LAST_MODIFIED_BY
FROM REGISTRATION R
INNER JOIN JHI_USER U ON R.ID = U.REGISTRATION_ID  
WHERE U.SUBMITTED_REGISTRATION = true
AND LOWER(U.FULL_NAME) LIKE LOWER('%test%')
ORDER BY full_name, last_modified_by ASC LIMIT 10 offset 0

Please add the error stacktrace – Vladimir Shefer May 09 '21 at 13:55 — Vladimir Shefer, May 09 '21 at 13:55
updated to include stacktrace – sophie May 09 '21 at 16:35 — sophie, May 09 '21 at 16:35

score 0 · Answer 1 · answered May 09 '21 at 15:06

According to the Javadoc of the Query annotation:

Annotation to provide SQL statements that will get used for executing the method.

A Query wraps a SQL statement, not some other language like HQL/JPQL. I don't think there are any "non-native" queries supported by R2dbcRepository, yet.

How to query a subselect with sorting and pageable in R2dbcRepository

1 Answers1