Why does placing an entity in the POJO class using "select new" in JPA cause an N + 1 problem?

user_2187007 0
Viewed 382

I have the following entity:

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "simple_entity")
public class SimpleEntity {

    @Id
    private Long id;

    @Column(name = "text")
    private String text;

}

I want to get some entities with additional column from the database. To do this I created a simple Pair class.

@Getter
@Setter
@AllArgsConstructor
public class Pair<First, Second> {

    private First first;
    private Second second;

}

Then I prepared a query in JPQL that creates the expected result.

@Repository
public interface SimpleEntityRepository extends JpaRepository<SimpleEntity, Long> {

    @Query("SELECT new com.example.demo.Pair(m, false) FROM SimpleEntity m")
    List<Pair<SimpleEntity, Boolean>> getRecords();

}

The query returns correct results, but there is a problem with additional queries.

wtf

Therefore, I have some questions:

  1. Why does JPA work this way?
  2. Is there any way to get this data in one query in JPQL?
  3. How should I get the entities along with some additional data (I've seen the solution with returning Object[] but it doesn't look nice)?
jpa hibernate spring java
1 Answers

Not a complete answer, but I believe it will add some info to the discussion:

Result Transformer

As you marked your question as hibernate, you can use a hibernate result transformer (deprecated in 5.2, but 6.0 is still in Alpha):

List<Pair<SimpleEntity, Boolean>> resultList = entityManager.createQuery(
"select m as first, false as second from SimpleEntity m")
        .unwrap(org.hibernate.query.Query.class)
        .setResultTransformer(Transformers.aliasToBean(Pair.class))
        .getResultList();

This assumes Pair has no-arg constructor and produces:

select
    simpleenti0_.id as col_0_0_,
    0 as col_1_0_,
    simpleenti0_.id as id1_18_,
    simpleenti0_.text as text2_18_ 
from
    simple_entity simpleenti0_

Proper DTO

Use a class that can be constructed via a list of fields, not entire entity. The query will behave nicely when you specify list of selected fields, not entire entity

public class SimpleEntityDTO {

    private Long id;
    private String text;
    private Boolean second;

    public SimpleEntityDTO() {
    }

    public SimpleEntityDTO(Long id, String text, Boolean second) {
        this.id = id;
        this.text = text;
        this.second = second;
    }

    // getters and setters
}

and modify your query:

@Query("SELECT new com.example.demo.SimpleEntityDTO(m.id, m.text, false) FROM SimpleEntity m")
List<SimpleEntityDTO> getRecordsExtended();

This produces:

select
    simpleenti0_.id as col_0_0_,
    simpleenti0_.text as col_1_0_,
    0 as col_2_0_ 
from
    simple_entity simpleenti0_

General remarks

I believe that one of the reasons of using DTOs over entities in projections is that they are not managed. In your case, where an entity is a part of a DTO, the entity IS managed:

@Transactional
public void changeValueInFirstRecord() {
    List<Pair<SimpleEntity, Boolean>> all = simpleEntityRepository.getRecords();
    SimpleEntity firstEntity = all.get(0).getFirst();
    boolean managed = entityManager.contains(firstEntity);
    System.out.println("managed: " + managed);  // true
    firstEntity.setText("new value");           // firstEntity is updated
}

The same is true in case of a result transformer:

@Transactional
public void changeValueInFirstEntityViaTransformer() {
    List<Pair<SimpleEntity, Boolean>> all = entityManager.createQuery(
            "select m as first, false as second from SimpleEntity m")
            .unwrap(org.hibernate.query.Query.class)
            .setResultTransformer(Transformers.aliasToBean(Pair.class))
            .getResultList();
    SimpleEntity firstEntity = all.get(0).getFirst();
    boolean managed = entityManager.contains(firstEntity);
    System.out.println("managed: " + managed);  // true
    firstEntity.setText("new_value");           // firstEntity is updated
}
user_15708540
Related