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)?
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);