Jpql Create New Object in Select Statement - Avoid or Embrace

JPQL Create new Object In Select Statement - avoid or embrace?

Don't avoid it, the SELECT NEW is there because there are perfectly valid use cases for it as reminded in the §10.2.7.2. JPQL Constructor Expressions in the SELECT Clause of the EJB 3.0 JPA Specification:

A constructor may be used in the
SELECT list to return one or more Java
instances. The specified class is not
required to be an entity or to be
mapped to the database. The
constructor name must be fully
qualified.

If an entity class name is specified
in the SELECT NEW clause, the
resulting entity instances are in the
new state.

SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count)
FROM Customer c JOIN c.orders o
WHERE o.count > 100

In short, use the SELECT NEW when you don't want to retrieve a full entity or a full graph of objects in a type safe way (as opposed to an Object[]). Whether you map the result of a query in an entity class or a non mapped class will depend on your select. A typical example would be a list screen (where you might not want all the details).

In other words, don't use it everywhere but don't forbid its use (few things are only black or white).

SELECT NEW in JPQL

Found the problem.
The constructor accepted (String, int) when it needed (String, long).

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

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

JPQL request with NEW That takes too much time

the reason is there were too much data to collect. When I run the following request

select co.num from Object1 co LEFT JOIN co.items item LEFT JOIN item.dim dim LEFT JOIN item.mat mat LEFT JOIN item.pro pro
           where co.ins between '2018-12-26 01:00:00' and '2019-06-26 01:00:00' 
               or co.mod between '2018-12-26 01:00:00' and '2019-06-26 01:00:00'.

I have 105 000 results. So the server lacks memory to render the other objects and create CustomObject

How to deal with Object type returned by JPQL query?

That possibly returns a List<Object[]>. The message of the ClassCastException will have the exact type. In case I'm correct:

for (Object[] items : result) {
    Masatosan m1 = (Masatosan) items[0];
    Masatosan m2 = (Masatosan) items[1];
}

To quote the docs

When multiple select_expressions are used in the SELECT clause, the result of the query is of type Object[], and the elements in this result correspond in order to the order of their specification in the SELECT clause

JPQL SELECT not retrieving the updated data

Try
@Modifying(clearAutomatically = true)
To clear the cache and force a new select against the database in the next queries

And maybe you should use flushAutomatically = true to flush entities before your query

---

Related Topics