Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Learn › JPA › Web applications and performance

Querying directly into a DTO

What is the difference between querying entities and copying them, and querying directly into a DTO?

To show only the name and age of each member on a screen, querying entities reads every column of the member and then copies only the values you need into a DTO. With new in JPQL, you can pick only the values you need and receive them directly as a DTO.

Checked against real execution · Twelve combinations were run on Hibernate 7.4.5 and H2, and the number of SELECT statements, the number of columns, the number of managed entities, and the number of UPDATE statements were checked

Concepts in this lab

DTO
This is short for Data Transfer Object, a simple object made to carry values between layers or to a screen. Unlike an entity, it has no @Entity and is not connected to a table, so the persistence context does not manage it, and changing its values does not change the DB. If you put only the values the screen needs into it, the shape of the response does not change along with the structure of the entity, and you can also prevent values that must not be sent out from slipping out. You can query an entity and then copy its values into a DTO, or you can have the query pick only the values you need and receive them directly as a DTO.
JPQL constructor expression
This is the syntax in which you write new package.Class(value, value) in the select clause of JPQL to receive the query result directly as objects of a class that is not an entity. The SELECT clause of the SQL contains only the columns that correspond to the values passed to the constructor, and the constructor of that class is called for each row. The result is not an entity, so the persistence context does not manage it, and it is not a target of dirty checking. The JPA standard requires the class name to be written including the package, and the class must have a constructor whose number and types of parameters match the values you pass.
Persistence context
The logical space in JPA that holds and manages entities. Entities that you load or save are kept here, and JPA tracks the changes made to them and applies those changes to the database when the transaction ends. It usually shares its lifecycle with one transaction (in a web request with Open Session in View (OSIV) turned on, it lasts until the request ends). Features such as the first-level cache, dirty checking, and transactional write-behind work on top of it.
Dirty checking
The feature that automatically runs UPDATE statements for changed entities. When the values of an entity managed by the persistence context change, JPA compares the entity with the state it first read at flush time (usually just before the commit), and it runs an UPDATE statement for each entity whose values differ. By default, Hibernate puts all columns in the UPDATE statement, not only the changed columns. To include only the changed columns, use @DynamicUpdate. So if you only change the values of an entity that you loaded, the change is applied to the database without calling a separate save method.
N+1 selects problem
The problem where, after 1 query loads a list, 1 more query runs for each of the N results to load its associated data, so N more queries run in total. As the data grows, the number of queries grows in proportion and performance drops sharply. To solve it, load the associated data together in a single query (fetch join) or load several items in one batch (batch fetching).

Try these

Running with the default settings

  1. em.createQuery("select m from Member m"): The whole Member entity is queried. The SELECT clause has all 6 columns of the member (id, name, age, email, city, team_id). The 4 members that were read are made into entity objects and kept in the persistence context, and their initial values are saved as snapshots. The team uses lazy fetching, so it has not been read yet.
  2. Copying member 1 into a DTO: At the moment m.getTeam().getName() gets the team name, a separate SELECT is sent for team 1, which has not been read yet (lazy fetching). The team that was read is also managed by the persistence context.
  3. Copying member 2 into a DTO: Team 1 of this member was read earlier and is in the persistence context, so no query is sent.
  4. Copying member 3 into a DTO: At the moment m.getTeam().getName() gets the team name, a separate SELECT is sent for team 2, which has not been read yet (lazy fetching). The team that was read is also managed by the persistence context.
  5. Copying member 4 into a DTO: Team 2 of this member was read earlier and is in the persistence context, so no query is sent.
  6. End of the method, commit: Just before the commit, the 6 managed entities are compared with their snapshots. Nothing differs, so there is no SQL to send. Even for a screen that only displays data, the work of making snapshots and comparing them still happens.

Result of this run

Code

@Entity
class Member {
    @Id Long id;
    String name;
    int age;
    String email;
    String city;
    @ManyToOne(fetch = FetchType.LAZY)
    Team team;             // Team has id, name, and region
}

// A class that holds only the values to send to the screen. It is not an entity (the package is dtoquery)
public class MemberDto {
    private String name;
    private int age;
    private String teamName;
    public MemberDto(String name, int age, String teamName) {
        this.name = name; this.age = age; this.teamName = teamName;
    }
    public void setName(String name) { this.name = name; }
}

@Transactional
public List<MemberDto> list() {
    List<Member> members = em.createQuery(
        "select m from Member m", Member.class)
        .getResultList();

    List<MemberDto> dtos = new ArrayList<>();
    for (Member m : members) {
        dtos.add(new MemberDto(m.getName(), m.getAge(), m.getTeam().getName()));
    }
    return dtos;
}   // commit when the method ends

Assumptions of this model

Twelve combinations were actually run on the PC used to write this lab, with Hibernate 7.4.5 and an H2 database, using code with the same structure as the screen, and the number of SELECT statements sent and their target tables, the number of columns in the first SELECT, the number of entities managed by the persistence context, the number of UPDATE statements sent at commit, and the name left in the DB were checked against the screen. All the code was assumed to run inside one transaction (one persistence context). The SQL on the screen is shortened to be easier to read, and the actual shape of the statements and the order of the columns differ depending on the Hibernate version. The fetch join reads 8 columns, not 9, because Hibernate 7.4.5 did not read the member's team_id separately, and this may differ depending on the version. The number of extra queries that read the teams equals the number of different teams that had not been read yet (2 in this example), and this is the value when no batch size is set. The JPA standard requires the class name after new to include the package name. When I ran it on Hibernate 7.4.5, passing MemberDto.class as the result type to createQuery made it work even without the package name, and writing only the class name without a result type made it fail to find the class and throw an exception (SemanticException). Leaving out the package name is not behavior that the standard guarantees. The feature of Spring Data JPA that lets you receive only the values you need through an interface or a record (projections) is not covered in this lab.

Previous lab: Stock decrement and locks

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these