如何在JPQL中引用参数属性，避免DTO参数扁平化？

在控制器中，我可以通过@ParameterObject获取到整洁的DTO：

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

但随后我必须将其扁平化才能用于仓库方法。真的必须这么做吗？

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

是否可以在JPQL查询中直接引用参数对象的属性？或是有其他方式避免在仓库方法参数列表中罗列所有DTO属性？

方案1：JPQL中直接引用DTO属性（Spring Data JPA SpEL支持） #

Spring Data JPA允许通过SpEL表达式直接访问参数对象的属性，无需扁平化参数。只需将DTO作为仓库方法参数，用@Param标注后，在JPQL中通过:#{#dto.property}格式引用属性即可。

修改仓库方法：

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

服务层无需再扁平化，直接传递DTO：

@Override
public List<UserResponseDto> find(FindUserRequestDto userRequestDto, Pageable pageable) {
    List<User> users = repository.find(userRequestDto, pageable);
    return users.stream()
            .map(userMapper::toResponseDto)
            .toList();
}

方案2：使用Specification动态构建查询 #

如果查询条件可能动态变化，用Specification可以灵活基于DTO属性构建查询，同时避免硬编码JPQL。

首先，让仓库继承JpaSpecificationExecutor<User>：

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

编写Specification类：

public class UserSpecifications {
    public static Specification<User> withFilter(FindUserRequestDto filter) {
        return (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();

            if (filter.getDateOfBirth() != null) {
                predicates.add(criteriaBuilder.greaterThan(root.get("dateOfBirth"), filter.getDateOfBirth()));
            }
            if (filter.getName() != null) {
                predicates.add(criteriaBuilder.like(root.get("name"), filter.getName() + "%"));
            }
            if (filter.getEmail() != null) {
                Subquery<EmailData> emailSubquery = query.subquery(EmailData.class);
                Root<EmailData> emailRoot = emailSubquery.from(EmailData.class);
                emailSubquery.select(emailRoot)
                        .where(criteriaBuilder.equal(emailRoot.get("user"), root),
                                criteriaBuilder.equal(emailRoot.get("email"), filter.getEmail()));
                predicates.add(criteriaBuilder.exists(emailSubquery));
            }
            if (filter.getPhone() != null) {
                Subquery<PhoneData> phoneSubquery = query.subquery(PhoneData.class);
                Root<PhoneData> phoneRoot = phoneSubquery.from(PhoneData.class);
                phoneSubquery.select(phoneRoot)
                        .where(criteriaBuilder.equal(phoneRoot.get("user"), root),
                                criteriaBuilder.equal(phoneRoot.get("phone"), filter.getPhone()));
                predicates.add(criteriaBuilder.exists(phoneSubquery));
            }

            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        };
    }
}

服务层调用方式：

@Override
public List<UserResponseDto> find(FindUserRequestDto userRequestDto, Pageable pageable) {
    Specification<User> spec = UserSpecifications.withFilter(userRequestDto);
    List<User> users = repository.findAll(spec, pageable).getContent();
    return users.stream()
            .map(userMapper::toResponseDto)
            .toList();
}

方案3：使用Querydsl实现类型安全的动态查询 #

如果项目引入Querydsl，可以用类型安全的方式构建查询，代码更简洁易维护。

首先，仓库继承QuerydslPredicateExecutor<User>：

public interface UserRepository extends JpaRepository<User, Long>, QuerydslPredicateExecutor<User> {
}

基于DTO构建Predicate：

public class UserQueryPredicates {
    public static BooleanExpression withFilter(FindUserRequestDto filter) {
        QUser user = QUser.user;
        BooleanExpression predicate = BooleanExpression.TRUE;

        if (filter.getDateOfBirth() != null) {
            predicate = predicate.and(user.dateOfBirth.gt(filter.getDateOfBirth()));
        }
        if (filter.getName() != null) {
            predicate = predicate.and(user.name.like(filter.getName() + "%"));
        }
        if (filter.getEmail() != null) {
            QEmailData emailData = QEmailData.emailData;
            predicate = predicate.and(JPAExpressions.selectOne()
                    .from(emailData)
                    .where(emailData.user.eq(user).and(emailData.email.eq(filter.getEmail())))
                    .exists());
        }
        if (filter.getPhone() != null) {
            QPhoneData phoneData = QPhoneData.phoneData;
            predicate = predicate.and(JPAExpressions.selectOne()
                    .from(phoneData)
                    .where(phoneData.user.eq(user).and(phoneData.phone.eq(filter.getPhone())))
                    .exists());
        }

        return predicate;
    }
}

服务层调用：

@Override
public List<UserResponseDto> find(FindUserRequestDto userRequestDto, Pageable pageable) {
    BooleanExpression predicate = UserQueryPredicates.withFilter(userRequestDto);
    List<User> users = repository.findAll(predicate, pageable).getContent();
    return users.stream()
            .map(userMapper::toResponseDto)
            .toList();
}

内容的提问来源于stack exchange，提问作者Sergey Zolotarev