The term projection means wrapping/projecting the data into the object. There are three types of widely projection used in the Spring Data JPA.
In the Spring Data JPA bydefault scaler projection is enabled. In this tutorial, we will learn how to use the DTO projection in the Spring Boot application using the Spring Data JPA.
1. Scaler projection
2. Interface projection
3. DTO projection
DTO projection in the entity
We can use DTO projection on an entity to map the selected parameter to the entity class in the spring data JPA.
Let's suppose we have a table called books with the following columns, and we want to select only selected columns such as title, price, and unit_sold.
In such scenario, we can use the DTO projection on an entity to map the selected paramter into the entity; although you can use the findAll() method of the JPARepository interface, which maps the column into the entity, that could not be the best practice if you are required to select only the selected column.
On the following table, We will use the DTO projection.
create table books(
id bigint(20) not null auto_increment primary key,
title varchar(255) not null,
price float(3,2) not null,
unit_sold bigint(200),
author_id bigint not null,
FOREIGN KEY (author_id) REFERENCES author(id)
); First Create the Entity
public class Books
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
private String title;
private BigDecimal price;
private long unitSold;
@ManyToOne()
@JoinColumn(name = "author_id", referencedColumnName = "id")
private Author author;
//Getter & Setter
} Constructor using parameters
Once we have completed creating the entity, we need to create the constructor with only the required parameters that we wish to fetch from the table. In our case, we like to fetch the title, price, and unit_sold.
In the Books Entity, Create the constructor using the desire parameter just like in the below code.
public Books(String title, BigDecimal price, long unitSold)
{
this.title = title;
this.price = price;
this.unitSold = unitSold;
} JPA Query for Entity DTO projection
Let's create a JPA select query that will map the table output into the Entity. We will be using the Books constructor in the JPA query, which we created in the earlier step.
@Query("select new Books(title, price, unitSold) from Books")
List<Books> findAllBooks(); You can observe that new Books(title, price, unitSold) is nothing but the constructor that we created in the previous step. This constructor will map the select query output from the table into the entity class.
Test Entity DTO Projection
@Test
void findAllBooksTest()
{
List<Books> allBooks = booksRepository.findAllBooks();
System.out.println(allBooks);
}Hibernate will execute the below query internally to replace the DTO projection JPA query with the SQL query.
Hibernate:
select
b1_0.title,
b1_0.price,
b1_0.unit_sold
from
books b1_0 Below is the output of the findAllBooks() method. As you can see, we found values of the title, price, and unit_sold columns only, and id and author_is were null because we didn't fetch those parameters in our DTO projection query in the repository.
Books list are :
[Books{id=0, title='Harry Potter', price=1.1, unitSold=2000, author=null}] DTO Projection on join query
Till now, we have learnt how to use DTO projection in the one entity itself, Let's now take a glimpse how we can leverage the DTO projection technique in the join query as well.
Let's create two tables such as books and the author, and we will use an inner join query to fetch some selected parameter and will map those parameters into the DTO.
create table books(
id bigint(20) not null auto_increment primary key,
title varchar(255) not null,
price float(3,2) not null,
unit_sold bigint(200),
author_id bigint not null,
FOREIGN KEY (author_id) REFERENCES author(id)
); create table author(
id bigint not null auto_increment primary key,
name varchar(255) not null
); Let's create one DTO with the elements which we desire to fetch with the constructor.
public class BooksWithAuthorDTO
{
private String bookTitle;
private long bookPrice;
private BigDecimal bookUnitSold;
private String authorName;
//Getter and Setter
public BooksWithAuthorDTO(String bookTitle, long bookPrice, BigDecimal bookUnitSold, String authorName)
{
this.bookTitle = bookTitle;
this.bookPrice = bookPrice;
this.bookUnitSold = bookUnitSold;
this.authorName = authorName;
}
}Before we begin, First we need to learn about two annotations which are @NamedNativeQuery and @SqlResultSetMapping
@NamedNativeQuery annotation
The @NamedNativeQuery annotation is the entity level annotation and its use to create the name SQL query in the JPQL and as well as the native query.
The name should be the unique across the persistence unit because this name will be used in the JPA repository method to fetch the data.
@NamedNativeQueries(
@NamedNativeQuery(name = "Books.findBooksWithAuthor",
query = "select b.title as bookTitle, b.price as bookPrice, " +
"b.unit_sold as bookUnitSold, a.name as authorName " +
"from books b left join author a on b.author_id = a.id;",
resultSetMapping = "Mapping.BooksWithAuthorMapping"
)
) Make sure you use an alias name the same as the DTO class parameter name.
@SqlResultSetMapping annotation
The @SqlResultSetMapping annotation is also the entity level annotation. As the name of this annotation, this annotation is used to map the SQL query output into the DTO class using the constructs and instruction the parameter type to parse the correct data type into the DTO.
@SqlResultSetMapping(name = "Mapping.BooksWithAuthorMapping",
classes = @ConstructorResult(targetClass = BooksWithAuthorDTO.class,
columns = {
@ColumnResult(name = "bookTitle"),
@ColumnResult(name = "bookPrice", type = Long.class),
@ColumnResult(name = "bookUnitSold", type = BigDecimal.class),
@ColumnResult(name = "authorName")
})
) @ConstructorResult annotaion
The @ConstructorResult annotation will specify the DTO class in which output of the SQL query should mapped, This annotation accepts two parameters such as targetClass and columns
In the targetClass parameter we need to declare the DTO class, and under the columns we need to instruct how to use contractions to map the SQL output into the parameter using the @ColumnResult
If you have thoroughly monitored the @ColumnResult, It's anticipate the name and the type parameter, in the name we have to declare the DTO class parameter and under the type we need to render the data type of those parameters.
String data type is bydefualt hence, we don't need to explicitly declare that but for the other data type we required the appropriate data type to mapped to the fields.
JPA Rpository method
Now, Create a method in the Books Repository with the name which we used in the @NamedNativeQueries. In our case, it's findBooksWithAuthor().
@Query(nativeQuery = true)
List<BooksWithAuthorDTO> findBooksWithAuthor(); Test DTO projection with join query
@Test
void findBooksWithAuthorTest()
{
List<BooksWithAuthorDTO> booksWithAuthor = booksRepository.findBooksWithAuthor();
System.out.println("Books with author are : ");
System.out.println(booksWithAuthor);
} Hibernate will execute the below query internally to replace the DTO projection JPA query with the SQL query.
Hibernate:
select
b.title as bookTitle,
b.price as bookPrice,
b.unit_sold as bookUnitSold,
a.name as authorName
from
books b
left join
author a
on b.author_id = a.id; Output of the above query which has been mapped to the DTO.
Books with author are :
[BooksWithAuthorDTO{bookTitle='Harry Potter', bookPrice=1, bookUnitSold=2000, authorName='J.K. Rowling'}] Conclusion
Using the DTO projection in the join query is very handy and it's leverage the advantages over the scalar projection because in the scalar projection we explicitly need to convert the Object data type into the airport data type before we need to consume it.
DTO projection enhances the code's readability and provides more flexible or easily modifiable code.
Keep learning and keep growing.
