o7planning

Fetch data with Spring Data JPA DTO Projections

  1. Entity Classes
  2. Basic example
  3. Open Projections
  4. Nested Projections
  5. Repository method with parameters
  6. Repository method naming convention
  7. Dynamic Projections
When using Spring Data JPA to fetch data, you often use queries with all the properties of an Entity, for example:
package org.o7planning.spring_jpa_projections.repository;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	@Query("SELECT e FROM Employee e " //
			+ " where e.empNo = :empNo")
	public Employee getByEmpNo(String empNo);

}
Each Entity represents a table in the database, it has many properties, each property corresponds to a column in the table. Sometimes you only need to query a few columns instead of all, which increases application performance.
Employee table.
In this article I show you how to use Spring Data JPA DTO Projections to create custom queries, including only the properties you are interested in.
Semantically "Projection" refers to a mapping between a JPA query to the properties of a custom Java DTO.
DTO
The concept of DTO (Data Transfer Object) is explained in a complicated way online, but it is simply a class with properties to hold data, used to transfer data from one place to another. In case of Spring Data JPA, data is transferred from Database to your application.
In Spring Data JPA, you will use an interface to describe a DTO with properties you are interested in. The rest will be done by Spring Data JPA, which will create a proxy DTO class that implements this interface at runtime of the application. The proxy DTO class will contain the actual data.
package org.o7planning.spring_jpa_projections.view;

public interface EmployeeView {

	public String getEmpNo();

	public String getFullName();

	public String getEmail();
}

1. Entity Classes

In this article I will use the two Entity classes below to illustrate in the examples:
Department.java
package org.o7planning.spring_jpa_projections.entity;

import java.io.Serializable;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.SequenceGenerator;
import jakarta.persistence.Table;
import jakarta.persistence.UniqueConstraint;

@Entity
@Table(name = "Department", uniqueConstraints = { //
		@UniqueConstraint(name = "DEPARTMENT_UK", columnNames = { "Dept_No" }), })
public class Department implements Serializable {

	private static final long serialVersionUID = 2091523073676133566L;

	@Id
	@GeneratedValue(generator = "my_seq")
	@SequenceGenerator(name = "my_seq", //
			sequenceName = "main_seq", allocationSize = 1)
	private Long id;

	@Column(name = "Dept_No", length = 32, nullable = false)
	private String deptNo;

	@Column(name = "Name", length = 128, nullable = false)
	private String name;

	// Getters & Setters

}
Employee.java
package org.o7planning.spring_jpa_projections.entity;

import java.io.Serializable;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.ForeignKey;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.SequenceGenerator;
import jakarta.persistence.Table;
import jakarta.persistence.UniqueConstraint;

@Entity
@Table(name = "Employee", uniqueConstraints = { //
		@UniqueConstraint(name = "EMPLOYEE_UK", columnNames = { "Emp_No" }), })
public class Employee implements Serializable {

	private static final long serialVersionUID = 8195147871240380311L;

	@Id
	@GeneratedValue(generator = "my_seq")
	@SequenceGenerator(name = "my_seq", //
			sequenceName = "main_seq", allocationSize = 1)
	private Long id;

	@Column(name = "Emp_No", length = 32, nullable = false)
	private String empNo;

	@Column(name = "First_Name", length = 64, nullable = false)
	private String firstName;

	@Column(name = "Last_Name", length = 64, nullable = false)
	private String lastName;

	@Column(name = "Phone_Number", length = 32, nullable = true)
	private String phoneNumber;

	@Column(name = "Email", length = 64, nullable = false)
	private String email;

	@ManyToOne
	@JoinColumn(name = "Department_Id", nullable = false, //
			foreignKey = @ForeignKey(name = "Dept_Emp_Fk"))
	private Department department;

	// Getters & Setters
}

2. Basic example

The steps to deploy Spring Data JPA Projections are very simple, you only need an interface that describes a DTO with the properties you are interested in. Then write a JPA query to map the columns to the DTO's properties. The rest will be done by Spring Data JPA, which will create a proxy DTO class, that implements this interface at runtime of the application. The proxy DTO class is what actually holds the data.
In this example we create an interface that describes a DTO, with a few properties, corresponding to a few columns of data we are interested in.
EmployeeView.java
package org.o7planning.spring_jpa_projections.view;

public interface EmployeeView {

	public String getEmpNo();

	public String getFullName();

	public String getEmail();
}
Then write a query to map the data columns to the properties of the DTO created in the step above.
EmployeeRepository.java (*)
package org.o7planning.spring_jpa_projections.repository;

import java.util.List;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {
	
	//
	// IMPORTANT: Required: "as empNo", "as fullName", "as email".
	//
	@Query("SELECT e.empNo as empNo, " //
			+ " concat(e.firstName, ' ', e.lastName) as fullName, " //
			+ " e.email as email " //
			+ " FROM Employee e")
	public List<EmployeeView> listEmployeeViews();

	// Other methods ..
}

3. Open Projections

In this example the "fullName" property of the DTO will not appear in the JPA query. But its value is still determined correctly based on the @Value annotation.
EmployeeView2.java
package org.o7planning.spring_jpa_projections.view;

import org.springframework.beans.factory.annotation.Value;

public interface EmployeeView2 {

	public String getEmpNo();

	@Value("#{target.firstName + ' ' + target.lastName}")
	public String getFullName();

	public String getEmail();
}
The JPA query below does not include "fullName", but it provides "firstName" and "lastName" to calculate "fullName" for the DTO.
EmployeeRepository.java (** ex2)
package org.o7planning.spring_jpa_projections.repository;

import java.util.List;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView;
import org.o7planning.spring_jpa_projections.view.EmployeeView2;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> { 

	//
	// IMPORTANT: Required: "as empNo", "as firstName", "as lastName", "as email".
	//
	@Query("SELECT e.empNo as empNo, " //
			+ " e.firstName as firstName, " // (***)
			+ " e.lastName as lastName, " // (***)
			+ " e.email as email " //
			+ " FROM Employee e")
	public List<EmployeeView2> listEmployeeView2s();

	// Other methods ..
}

4. Nested Projections

Next is an example with nested Projections.
EmployeeView3.java
package org.o7planning.spring_jpa_projections.view;

public interface EmployeeView3 {

	public String getEmpNo();

	public String getFullName();

	public String getEmail();

	public DepartmentView3 getDepartment();
}
DepartmentView3.java
package org.o7planning.spring_jpa_projections.view;

import org.springframework.beans.factory.annotation.Value;

public interface DepartmentView3 {

	// Map to "deptNo" property of Department entity.
	// Same property names --> No need @Value
	public String getDeptNo();

	// Map to "name" property of Department entity.
	// Different property names --> NEED @Value
	@Value("#{target.name}")
	public String getDeptName();
}
EmployeeRepository.java (** ex3)
package org.o7planning.spring_jpa_projections.repository;

import java.util.List;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView3;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	//
	// IMPORTANT: Required: "as empNo", "as fullName", "as email", "as department".
	//
	@Query("SELECT e.empNo as empNo, " //
			+ " concat(e.firstName, ' ', e.lastName) as fullName, " //
			+ " e.email as email, " //
			+ " d as department " //
			+ " FROM Employee e " //
			+ " Left join e.department d ")
	public List<EmployeeView3> listEmployeeView3s();

	// Other methods ..
}

5. Repository method with parameters

EmployeeRepository.java (** ex4)
package org.o7planning.spring_jpa_projections.repository;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	//
	// IMPORTANT: Required: "as empNo", "as fullName", "as email".
	//
	@Query("SELECT e.empNo as empNo, " //
			+ " concat(e.firstName, ' ', e.lastName) as fullName, " //
			+ " e.email as email " //
			+ " FROM Employee e " //
			+ " WHERE e.empNo = :empNo")
	public EmployeeView findByEmpNo(String empNo);

	// Other methods ..
}

6. Repository method naming convention

In some cases you may not need to write a JPA query if the Repository method name and its parameters conform to Spring Data JPA conventions. This means you do not need to use the @Query annotation.
For example, we create a method with a name according to the following rule:
  • "find" + "By" + "PropertyName"
  • "find" + "Xxx" + "By" + "PropertyName"
EmployeeRepository.java (** ex5)
package org.o7planning.spring_jpa_projections.repository;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView5;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	//
	// Without @Query annotation. (Ex5)
	// - "find" + "By" + "PropertyName".
	// - "find" + "Xxx" + "By" + "PropertyName".
	//
	public EmployeeView5 findEmployeeView5ByEmpNo(String empNo);

	// Other methods ..
}
Note, the properties of the Interface must also be the same as the properties of the original Entity.
EmployeeView5.java
package org.o7planning.spring_jpa_projections.view;

public interface EmployeeView5 { 
 
	public String getEmpNo();

	public String getFirstName();

	public String getLastName();

	public String getEmail();
}
Another example, without using @Query annotation:
EmployeeRepository.java (** ex6)
package org.o7planning.spring_jpa_projections.repository;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView6;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	//
	// Without @Query annotation. (Ex6)
	// - "find" + "By" + "PropertyName".
	// - "find" + "Xxx" + "By" + "PropertyName".
	//
	public EmployeeView6 findView6ByEmpNo(String empNo);

	// Other methods ..
}
EmployeeView6.java
package org.o7planning.spring_jpa_projections.view;

import org.springframework.beans.factory.annotation.Value;

public interface EmployeeView6 {

	public String getEmpNo();

	@Value("#{target.firstName + ' ' + target.lastName}")
	public String getFullName();

	public String getEmail();
}
In the case of a method without parameters, the naming rule is:
  • "find" + "By"
  • "find" + "Xxx" + "By"
EmployeeRepository.java (** ex7)
package org.o7planning.spring_jpa_projections.repository;

import java.util.List;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.o7planning.spring_jpa_projections.view.EmployeeView6;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	//
	// Without @Query annotation. (Ex7)
	// - "find" + "By"
	// - "find" + "Xxx" + "By"
	//
	public List<EmployeeView6> findView6By();

	// Other methods ..
}
In short, to avoid having to use the @Query annotation you need to follow the naming rules for the Repository method. For me, I like to use @Query to make things clearer and to be able to name methods arbitrarily, even though it takes a bit more coding.
  • "find" + "By" + "PropertyNames" + "Keyword"
  • "find" + "Xxx" + "By" + "PropertyNames" + "Keyword"
Keyword
Method Name
JPA Query
GreaterThan
findByAgeGreaterThan(int age)
Select {properties} from Person e where e.age > :age
LessThan
findByAgeLessThan(int age)
Select {properties} from Person e where e.age < :age
Between
findByAgeBetween(int from, int to)
Select {properties} from Person e where e.age between :from and :to
IsNotNull, NotNull
findByFirstnameNotNull()
Select {properties} from Person e where e.firstname is not null
IsNull, Null
findByFirstnameNull()
Select {properties} from Person e where e.firstname is null
Like
findByFirstnameLike(String name)
Select {properties} from Person e where e.firstname like :name
(No Keyword)
findByFirstname(String name)
Select {properties} from Person e where e.firstname = :name
Not
findByFirstnameNot(String name)
Select {properties} from Person e where e.firstname <> :name
...

7. Dynamic Projections

An Entity can have one or more Projections, depending on your application logic. You might consider using the Repository method with the Class parameter as follows:
EmployeeRepository.java (** ex8)
package org.o7planning.spring_jpa_projections.repository;

import java.util.List;

import org.o7planning.spring_jpa_projections.entity.Employee;
import org.springframework.data.repository.CrudRepository;

public interface EmployeeRepository extends CrudRepository<Employee, Long> {

	// (Ex8)
	// - "find" + "By"
	// - "find" + "Xxx" + "By"
	//
	public <T> List<T> findViewByEmpName(String empNo, Class<T> type);

	// Other methods ..
}

Spring Boot Tutorials

Show More

Spring Boot Tutorials

Show More

Newest Articles

Show More