The best way to fetch SQL query projections with JPA

Learn what the best way you can fetch SQL query projections when using JPA.

In this video, you are going to learn:

1️⃣ How the default Object[] query projection works
2️⃣ How to use the JPA Tuple for query projections
3️⃣ How to fetch DTO projections using JPA
4️⃣ How to use Java Records with JPA query projections

[kerbar]

Hello, Mr Vlad Thanks so munch for this amazing video, thanks to you I have improved performance and my applications are faster

[maiers]

Thank you for all the information. Do you have some more information when to use which approach?

[niladrisekharnath]

Thanks for the most of the approaches demonstration but can you please tell me something for the result set we know what would be the value that we are selecting and we keep one DTO for the resultList for the values that would be coming to us, but what if we don't know what are the values that we are selecting(like we know that the fields are part of some Entity class) but we don't know what are the values that would be requested for us like sometimes five values are requested and sometimes 10 values so what should we do then ? Obviously we cannot create a ResultDTO for the values for every combination, then please suggest how to create a DTO for the resultSet for the values that would do something like when five fields are selected we set the five values and rest of the fields we set as null ? What would be the efficient way to do this ?

[java_tar]

hello there thanks for this amazing video i wanted to ask can i have a projection with postDTO and in it a list of PostCmmecntsDto and if so what is the jpql query to return will be like ?

[Максим-нкп]

Thank you for video! I have a question, can i create DTO projection if my entity have nested collections?

[shpativata]

Is anyway on the DTO projections to get a list, example on the DTO on the cunstroctor is (Long postId, List<Comments> commentList), how would u do it on the jpql with dto projections how do u map this, because on the large dataset i dont want to go with the for loops to set the values on the dto(using native query), so this problem called N+1, can u make a video please for this or reply on my comment. Thank you a lot!

[jkickic]

These approaches are much better than handling Object[] data but still look kind of complicated.
Why can't I just supply a random object with fields annotaded such that the framework code can figure out how to assign results to the fields.
Like (annotations made up, example simplified):

@MappableResult
public class PostDTO {

@ResultMapping(name = "id")
Long id;
@ResultMapping(name = "title")
String title;
}

Hibernate already does this with entities and @Column annotations, why not use a similar thing here?
Would this break some core principles of the framework?