[FIXED] select new (JPQL Constructor Expression) in jpa/hibernate causes "lazy" loading for each row

Issue

Recently I found no obvious behaviour when using 'select new/constructor expression' in jpa/hibernate. It uses kind of lazy loading for each entity in each row in result set what is not efficient.

Test example

@Value
public class PojoTuple {
    Entity1 e1;
    Entity2 e2;
}

@Entity
@Table(name = "entity1", schema = DEFAULT_DATABASE_SCHEMA)
@NoArgsConstructor(access = PROTECTED)
public class Entity1 {

    @Id
    @Column(name = "id", nullable = false)
    private String id;

    @Column(name = "field1", nullable = false)
    private String field1;
}

@Entity
@Table(name = "entity2", schema = DEFAULT_DATABASE_SCHEMA)
@NoArgsConstructor(access = PROTECTED)
public class Entity2 {

    @Id
    @Column(name = "id", nullable = false)
    private String id;

    @Column(name = "fkentity1", nullable = false)
    private String entity1Id;

    @Column(name = "field2", nullable = false)
    private String field2;
}

create table entity1
(
    id     varchar2(22 char) not null primary key,
    field1 varchar2(50 char) not null
);

create table entity2
(
    id        varchar2(22 char) not null primary key,
    fkentity1 varchar2(22 char) not null,
    field2    varchar2(50 char) not null

);

insert into entity1 (id, field1) values ('10', 'anyvalue1');
insert into entity1 (id, field1) values ('11', 'anyvalue2');

insert into entity2 (id, fkentity1, field2) VALUES ('20', '10', 'anyvalue3');
insert into entity2 (id, fkentity1, field2) VALUES ('21', '11', 'anyvalue4');

First case

we issue a query using select new technique:

Query query = entityManager.createQuery("select new my.package.PojoTuple(e1, e2) " +
                                        "from Entity1 e1 " +
                                        "join Entity2 e2 on e1.id=e2.entity1Id ");
query.getResultList();

This issues one query to fetch only ids of e1 and e2 and then more queries to fetch e1, e2 by id one by one for each row in result set:

Query:["select entity1x0_.id as col_0_0_, entity2x1_.id as col_1_0_ from schema.entity1 entity1x0_ inner join schema.entity2 entity2x1_ on (entity1x0_.id=entity2x1_.fkentity1)"]

Query:["select entity1x0_.id as id1_1_0_, entity1x0_.field1 as field2_1_0_ from schema.entity1 entity1x0_ where entity1x0_.id=?"] Params:[(10)]

Query:["select entity2x0_.id as id1_2_0_, entity2x0_.fkentity1 as fkentity2_2_0_, entity2x0_.field2 as field3_2_0_ from schema.entity2 entity2x0_ where entity2x0_.id=?"] Params:[(20)]

Query:["select entity1x0_.id as id1_1_0_, entity1x0_.field1 as field2_1_0_ from schema.entity1 entity1x0_ where entity1x0_.id=?"] Params:[(11)]

Query:["select entity2x0_.id as id1_2_0_, entity2x0_.fkentity1 as fkentity2_2_0_, entity2x0_.field2 as field3_2_0_ from schema.entity2 entity2x0_ where entity2x0_.id=?"] Params:[(21)]

Second case

Whereas rewriting sample from above to:

Query query = entityManager.createQuery("select e1, e2 " +
                                        "from Entity1 e1 " +
                                        "join Entity2 e2 on e1.id=e2.entity1Id ");

query.getResultList();

Issues exactly one query to database with all required fields selected:

Query:["select entity1x0_.id as id1_1_0_, entity2x1_.id as id1_2_1_, entity1x0_.field1 as field2_1_0_, entity2x1_.fkentity1 as fkentity2_2_1_, entity2x1_.field2 as field3_2_1_ from schema.entity1 entity1x0_ inner join schema.entity2 entity2x1_ on (entity1x0_.id=entity2x1_.fkentity1)"] Params:[()]

Question

From my perspective there is no big difference how these two queries should be performed. First case issues many queries that I do not expect what is highly inefficient. Second case works as expected issuing exactly one query to database. Is this a bug, suboptimal solution or some hidden feature that I can not see?

Environment hibernate-core: 5.6.9.Final

Solution

So I finally found partial explanation from the most authoritative source of knowledge about hibernate I know - Vlad Mihalcea: Paragraph: Returning an entity in a DTO projection

However, there might be use cases when you want to select an entity inside your DTO projection. (...)

When you execute a JPQL query like this one:

List<PersonAndCountryDTO> personAndAddressDTOs = entityManager.createQuery(
"select new " +
"   com.vladmihalcea.book.hpjp.hibernate.query.dto.PersonAndCountryDTO(" +
"       p, " +
"       c.name" +
"   ) " +
"from Person p " +
"join Country c on p.locale = c.locale " +
"order by p.id", PersonAndCountryDTO.class) .getResultList();

Hibernate generates the following SQL queries:

SELECT p.id AS col_0_0_,
   c.name AS col_1_0_ FROM   Person p INNER JOIN
   Country c ON
   ( p.locale = c.locale ) ORDER BY
   p.id   

SELECT p.id AS id1_1_0_,
   p.locale AS locale2_1_0_,
   p.name AS name3_1_0_ FROM   Person p WHERE  p.id = 3   

SELECT p.id AS id1_1_0_,
   p.locale AS locale2_1_0_,
   p.name AS name3_1_0_ FROM   Person p WHERE  p.id = 4

The Hibernate 5.2 implementation of the DTO projection cannot materialize the DTO projection from the ResultSet without executing a secondary query. However, this is very bad to performance since it can lead to N+1 query issues.

This HQL limitation has been discussed, and Hibernate 6.0 new SQM parser might address this issue, so stay tuned!

So to summarize:

1. Behaviour I asked about is known to hibernate developers and there is a hope it will be fixed.
2. As for now one has to know that extracting complete, managed entities with constructor expression is completely fine as a design, but with hibernate 5.x can lead to non optimal solution due to many queries issued by hibernate

Answered By - mkobylarz
Answer Checked By - Clifford M. (JavaFixing Volunteer)