Reduce number of queries for JPQL POJO containing an entity

Question

Entity relation: Transaction(@ManyToOne - eager by default) -> Account

String sql = "SELECT new com.test.Pojo(t.account, SUM(t.value)) FROM Transaction t GROUP BY t.account";
List list = entityManager.createQuery(sql).getResultList();

By default JPA using Hibernate implementation will generate 1 + n queries. The n queries are for lazy loading of the account entities.

How can I make this query eager and load everything with a single query? The sql equivalent would be something like

SELECT account.*, SUM(t.value) FROM transactions JOIN accounts on transactions.account_id = accounts.id GROUP BY account.id

, a syntax that works well on PostgreSQL. From my findings Hibernate is generating a query that justifies the lazy loading.

SELECT account.id, SUM(t.value) FROM transactions JOIN accounts on transactions.account_id = accounts.id GROUP BY account.id

Answer 1

Try marking the @ManyToOne field as lazy:

@ManyToOne(fetch = FetchType.LAZY)
private Account account;

And change your query using a JOIN FETCH of the account field to generate only one query with all you need, like this:

String sql = "SELECT new com.test.Pojo(acc, SUM(t.value)) "
    + "FROM Transaction t JOIN FETCH t.account acc GROUP BY acc";

UPDATE:

Sorry, you're right, the fetch attribute of @ManyToOne is not required because in Hibernate that is the default value. The JOIN FETCH isn't working, it's causing a QueryException: "Query specified join fetching, but the owner of the fetched association was not present".

I have tried with some other approaches, the most simple one that avoids doing n + 1 queries is to remove the creation of the Pojo object from your query and process the result list, manually creating the objects:

String hql = "SELECT acc, SUM(t.value)"
  + " FROM " + Transaction.class.getName() +  " t"
  + " JOIN t.account acc"
  + " GROUP BY acc";

Query query = getEntityManager().createQuery(hql);
List<Pojo> pojoList = new ArrayList<>();
List<Object[]> list = query.getResultList();

for (Object[] result : list)
    pojoList.add(new Pojo((Account)result[0], (BigDecimal)result[1]));