Spring Boot中如何用@Query注解从DATA JPA获取指定列（含WHERE条件）

Absolutely! You can totally pull this off with Spring Data JPA's @Query annotation — and there are even flexible ways to handle both fixed and dynamic WHERE clauses while fetching only specific columns. Let me walk you through the practical implementations:

If your WHERE clauses are static (you know exactly which conditions to apply every time), you can directly write JPQL or native SQL in @Query, and use projections to return only the columns you need.

Option 1: Return a Custom DTO #

First, define a DTO class to hold your target columns (make sure the constructor matches the order of columns in your query):

public class UserProfileDTO {
    private String fullName;
    private String email;

    public UserProfileDTO(String fullName, String email) {
        this.fullName = fullName;
        this.email = email;
    }

    // Getters for the fields
    public String getFullName() { return fullName; }
    public String getEmail() { return email; }
}

Then add the query to your JpaRepository:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT new com.yourpackage.dto.UserProfileDTO(u.fullName, u.email) " +
           "FROM User u WHERE u.age > :minAge AND u.accountStatus = :status")
    List<UserProfileDTO> findUserProfilesByConditions(
        @Param("minAge") Integer minAge,
        @Param("status") String accountStatus
    );
}

Option 2: Use Interface Projection (Simpler) #

Instead of a DTO, define a projection interface with getters matching your target columns:

public interface UserProfileProjection {
    String getFullName();
    String getEmail();
}

Then update your repository query to map results to this interface:

@Query("SELECT u.fullName AS fullName, u.email AS email " +
       "FROM User u WHERE u.age > :minAge AND u.accountStatus = :status")
List<UserProfileProjection> findUserProfilesByConditions(
    @Param("minAge") Integer minAge,
    @Param("status") String accountStatus
);

If your WHERE clauses are variable (users might pass 1, 2, or more optional conditions), @Query alone can't handle full dynamism, but you can pair it with Spring Data features for flexibility:

Option 1: Combine @Query with SpEL (Simple Dynamic Scenarios) #

For small numbers of optional conditions, use SpEL to conditionally include clauses when parameters are not null:

@Query("SELECT u.fullName, u.email FROM User u " +
       "WHERE (:minAge IS NULL OR u.age > :minAge) " +
       "AND (:status IS NULL OR u.accountStatus = :status) " +
       "AND (:nameKeyword IS NULL OR u.fullName LIKE %:nameKeyword%)")
List<UserProfileProjection> findUserProfilesByDynamicConditions(
    @Param("minAge") Integer minAge,
    @Param("status") String accountStatus,
    @Param("nameKeyword") String nameKeyword
);

This logic ignores any condition where the corresponding parameter is null, effectively building a dynamic WHERE clause.

Option 2: Use Specification (For Complex Dynamic Queries) #

For more complex dynamic logic, implement Specification to build conditions programmatically, then pair it with projections:

First, create reusable specification builders:

public class UserSpecs {
    public static Specification<User> ageGreaterThan(Integer minAge) {
        return (root, query, cb) -> 
            minAge != null ? cb.greaterThan(root.get("age"), minAge) : null;
    }

    public static Specification<User> accountStatusEquals(String status) {
        return (root, query, cb) -> 
            status != null ? cb.equal(root.get("accountStatus"), status) : null;
    }

    public static Specification<User> nameContains(String keyword) {
        return (root, query, cb) -> 
            keyword != null ? cb.like(root.get("fullName"), "%" + keyword + "%") : null;
    }
}

Update your repository to inherit JpaSpecificationExecutor:

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

Then use it in your service to assemble dynamic queries and fetch projections:

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;

    public List<UserProfileProjection> getFilteredUserProfiles(
        Integer minAge, String status, String nameKeyword
    ) {
        Specification<User> specs = Specification.where(UserSpecs.ageGreaterThan(minAge))
                .and(UserSpecs.accountStatusEquals(status))
                .and(UserSpecs.nameContains(nameKeyword));

        return userRepository.findAll(specs, UserProfileProjection.class);
    }
}

If you need to run raw SQL (for complex queries that JPQL can't handle), add nativeQuery = true to your @Query annotation:

@Query(value = "SELECT full_name, email FROM users WHERE age > :minAge AND account_status = :status",
       nativeQuery = true)
List<UserProfileProjection> findUserProfilesByNativeQuery(
    @Param("minAge") Integer minAge,
    @Param("status") String accountStatus
);

内容的提问来源于stack exchange，提问作者DHANANJAI TIWARI