Spring Data JPA Specific Columns using JPQL and DTO Projections

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

Selecting specific columns (or a subset of columns) in Spring Data JPA can be achieved using multiple ways, but one of the most common approaches is using JPQL in combination with DTO projections. In this guide, I'll demonstrate how to select specific columns using this approach.

1. Setting up the project

Make sure you have the required dependencies in your pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

2. Define the Entity

For this example, let's use a Person entity:

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String firstName;
    private String lastName;
    private Date dateOfBirth;
    // getters, setters, etc.
}

3. Create a DTO Projection

This DTO will represent the subset of columns we wish to retrieve. For instance, if we only want the firstName and lastName:

public class PersonNameDto {
    private final String firstName;
    private final String lastName;

    public PersonNameDto(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }

    // getters
}

Note the final keyword for fields and the constructor to initialize these fields. 

4. Create the Repository

Define a custom query using JPQL in the repository:

public interface PersonRepository extends JpaRepository<Person, Long> {

    @Query("SELECT new com.yourpackage.PersonNameDto(p.firstName, p.lastName) FROM Person p")
    List<PersonNameDto> findAllNames();
}

The above query selects only the firstName and lastName from the Person entity and maps them to the PersonNameDto. 

5. Use the Repository in a Service

Now, you can retrieve the specific columns in your service layer:

@Service
public class PersonService {

    @Autowired
    private PersonRepository personRepository;

    public List<PersonNameDto> getPersonNames() {
        return personRepository.findAllNames();
    }
}

6. Test the Service

You can create a test to ensure the specific columns are being retrieved:

@SpringBootTest
public class PersonServiceTest {

    @Autowired
    private PersonService personService;

    @Test
    public void testFetchNames() {
        List<PersonNameDto> names = personService.getPersonNames();
        for (PersonNameDto name : names) {
            assertNotNull(name.getFirstName());
            assertNotNull(name.getLastName());
        }
    }
}

Notes

This approach uses DTO projections to map specific columns, providing a type-safe way of retrieving partial entities. 

This is useful for performance, especially when the full entity contains many columns or large content (like blobs). It's also possible to achieve similar functionality using native SQL queries, the JPA Criteria API, or other third-party libraries like Querydsl. 

 Always ensure that the DTO's constructor parameters match the order of columns in your custom query to ensure the correct mapping.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

🆕 High-Demand 80–90% OFF

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

🆕 High-Demand 80–90% OFF

ChatGPT + Generative AI + Prompt Engineering for Beginners

🚀 Trending Now 80–90% OFF

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Real-Time REST APIs with Spring Boot - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Microservices with Spring Boot and Spring Cloud

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Java Full-Stack Developer Course with Spring Boot and React JS

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

🌟 Top Rated 80–90% OFF

Testing Spring Boot Application with JUnit and Mockito

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot Thymeleaf Real-Time Web Application - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Master Spring Data JPA with Hibernate

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot + Apache Kafka Course - The Practical Guide

🎓 Student Favorite 80–90% OFF
Available in Udemy for Business

Java Testing: Mastering JUnit 5 Framework

🆕 New 80–90% OFF

Reactive Programming in Java: Spring WebFlux and Testing

🌟 Top Rated 80–90% OFF

Spring Boot + RabbitMQ Course - The Practical Guide

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Functional Programming in Java (Includes Java Collections)

🎓 Student Favorite 80–90% OFF

ChatGPT for Java Developers: Boost Your Productivity with AI

🚀 Trending Now 80–90% OFF