javaspring-data

Referencing parameter attributes in JPQL


I get a nice DTO in my controller.

// UserController
    @GetMapping
    public ResponseEntity<List<UserResponseDto>> findUsers(@ParameterObject FindUserRequestDto userRequestDto,
                                                           @ParameterObject Pageable pageable) {
        List<UserResponseDto> userResponseDtos = userService.find(userRequestDto, pageable);
        return ResponseEntity.ok(userResponseDtos);
    }

But then I have to flatten it to use in my repository method. Or do I?

// UserService implementation
    @Override
    public List<UserResponseDto> find(FindUserRequestDto userRequestDto, Pageable pageable) {
        // flattening
        List<User> users = repository.find(
                userRequestDto.getDateOfBirth(),
                userRequestDto.getPhone(),
                userRequestDto.getName(),
                userRequestDto.getEmail(),
                pageable);
        List<UserResponseDto> userDtos = users.stream()
                .map(userMapper::toResponseDto)
                .toList();
        return userDtos;
    }
    @Query("""
            SELECT u
            FROM User u
            WHERE (CAST(:dateOfBirth AS DATE) IS NULL OR u.dateOfBirth > CAST(:dateOfBirth AS DATE))
            AND (:name IS NULL OR u.name LIKE CONCAT(:name, '%'))
            AND (:email IS NULL OR EXISTS (
                SELECT 1 FROM EmailData ed WHERE ed.user = u AND ed.email = :email
            ))
            AND (:phone IS NULL OR EXISTS (
                SELECT 1 FROM PhoneData pd WHERE pd.user = u AND pd.phone = :phone
            ))
            """)
    List<User> find(@Param("dateOfBirth") LocalDate dateOfBirth,
                    @Param("phone") String phone,
                    @Param("name") String name,
                    @Param("email") String email,
                    Pageable pageable);

Is there a way to somehow reference parameter attributes inside a JPQL query? Or otherwise avoid listing all DTO attributes in the parameter list (making it long)?


Solution

  • You can use this syntax

    @Query("""
                SELECT u
                FROM User u
                WHERE (CAST(:#{#dto.getDateOfBirth()} AS DATE) IS NULL OR u.dateOfBirth > CAST(:#{#dto.getDateOfBirth()} AS DATE))
                AND (:#{#dto.getName()} IS NULL OR u.name LIKE CONCAT(:#{dto.getName()}, '%'))
                AND (:#{#dto.getEmail()} IS NULL OR EXISTS (
                    SELECT 1 FROM EmailData ed WHERE ed.user = u AND ed.email = :#{#dto.getEmail()}
                ))
                AND (:#{#dto.getPhone()} IS NULL OR EXISTS (
                    SELECT 1 FROM PhoneData pd WHERE pd.user = u AND pd.phone = :#{#dto.getPhone()}
                ))
                """)
        List<User> find(UserRequestDTO dto,
                        Pageable pageable);