Spring JPA selecting specific columns

java

jpa

spring-data

query-optimization

byNikita Barsukov·Jan 21, 2025

For Spring JPA queries targeting specific columns, use projections through interfaces or DTOs. Projections involve declaring methods matching the column names, whereas DTOs encapsulate those columns in objects.

A projection sample:

public interface UserInfo {
  String getUsername(); // Hannibal Lecter's username, maybe?
  String getEmail(); // an email address...nothing else to say here.
}

Use it in a repository like this:

List<UserInfo> findUsersByActiveIsTrue();

DTO sample:

public class UserDTO {
  private String username; // Dracula maybe???
  private String email; // his bat-mail!

  // Constructors and getters
}

// Use in the repository with JPQL
@Query("SELECT new com.example.UserDTO(u.username, u.email) FROM User u WHERE u.active = true")
List<UserDTO> findActiveUsers();

Projections are much lighter, but DTOs give more control. Choose prudently.

A practical guide to selecting specific columns

Let's examine some common methods and approaches to improve your efficiency with Spring Data JPA - after all, who doesn't want to make their life easier?

Custom JPQL queries for the kill

Use the @Query annotation to roll out your custom JPQL queries.

@Query("SELECT u.username, u.email FROM User u")
List<Object[]> fetchUsernamesAndEmails();

Ready for the rockstar move? Use referential constructor expression in your JPQL query.

@Query("SELECT new com.example.UserDetails(u.username, u.email, u.status) FROM User u")
List<UserDetails> fetchAllUserDetails();

The UserDetails here is a DTO that takes the matching fields from JPQL.

Throw the native SQL punch

Sometimes, good old plain SQL queries are all you need. For that, here comes @Query annotation, twinned with a native SQL query.

@Query(value = "SELECT username, email FROM users WHERE active = 1", nativeQuery = true)
List<Object[]> fetchActiveUserNamesAndEmails();

Remember, with native SQL, it's like taking a test in a foreign language - you might have to manually map the results to your DTOs or custom objects.

DTOs: Only carry what you 'actually' need

Entities are like that friend who overstays their welcome; often loaded with more data than you require. DTOs are the courteous guests - carrying only the data that you need, they ensure your application runs efficiently.

Custom repository methods: 'Cause you're special

Incorporate your queries in custom repository methods - so you can call them whenever, without the fuss of writing the full query every time.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
  @Query("SELECT u.username FROM User u WHERE u.status = :status")
  List<String> findAllUsernamesByStatus(@Param("status") String status);
}

Trust me, your service layer and your nerves would prefer this!

Native SQL necessity assessment

If JPA or JPQL can get your job done, why bring in native SQL and manual mapping into the picture? It's always about making your life easy, ain't it?

The edge cases, the tricks, the traps

The Criteria API gladiators

When the Criteria Builder comes into the fray, you're dealing with SQL construction in a type-safe manner.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<UserDTO> query = cb.createQuery(UserDTO.class);
Root<User> root = query.from(User.class);

query.multiselect(root.get("username"), root.get("email")); // only the username and email - no nonsense!
TypedQuery<UserDTO> typedQuery = entityManager.createQuery(query);
List<UserDTO> results = typedQuery.getResultList(); // Voila!

This really shines when your queries have parameters that change faster than your mood swings.

Manual mapping? Not today, buddy!

While nativeQuery seldom comes without the manual mapping blues, projections and JPQL are the good samaritans who do the manual work for you. Result? Lesser boilerplate code and fewer chances of errors.

Grouping and aggregation riddles

Bringing in GROUP BY clauses or aggregated data? Make sure your projections or DTOs are ready for the results! You might need additional DTO fields or creative property methods in your projections.

explain-codes / Java / Spring JPA selecting specific columns

Linked

Spring JPA @Query with LIKE



What Java ORM do you prefer, and why?



Jpa: How to convert a native query result set to POJO class collection



How to fetch FetchType.LAZY associations with JPA and Hibernate in a Spring Controller



Does Spring Data JPA have any way to count entities using method name resolving?

