JPA N+1 iteration and JSON serializer : query are still there

Question

THE PROBLEM

I have the entity Product with a lot of relationship: productor, category and so on.

When I ask for the products'list, fist of all JPA do the normal query:

select t1.a, t1.b,..., t1.idcategory, ... from product t1 limit 50 offset 0

Then, for each product ask for category and the other relationship

select * from category c where c.id = ?

This is very heavy : 50 rows produce 50xrelationship queries (in my case about 8 relationship, so 50+50*8 queries). I need to remove these queries.

FIRST TRY

I use criteria query, so i have added the joins to the query:

Root<T> root  = query.from(Product.class);
Map<Class,Field> foreignRelationship = EntityUtils.listEntity(Product.class);
for(Field f : foreign.values()){
   root.join(f.getName());
}

This code read my entity and produces the correct query:

select t1.* from product t1 join category c on t1.idcategory = c.id ...

The query is builded successfully, but when i call the json serializer (I need to return the object via a REST service), the NxROW queries are executed :(

SECOND TRY I'have tryed with NamedEntityGraph:

@NamedEntityGraphs({
@NamedEntityGraph(name = "graph.Product", 
    attributeNodes = {
        @NamedAttributeNode(value = "producer",
                subgraph = "graph.Product.producer"),
        @NamedAttributeNode(value = "catmer"),
        @NamedAttributeNode(value = "shop"),
        ...
    },
    subgraphs = {
        @NamedSubgraph(
            name = "graph.Product.producer",
            attributeNodes = {
                @NamedAttributeNode(value = "id"),
                @NamedAttributeNode(value = "code")
            }
        ),...
    },
    includeAllAttributes = true
)
})

In my criteria query:

getEntityManager().createQuery(query)
                //.setHint("javax.persistence.fetchgraph",graph);
                .setHint("javax.persistence.loadgraph",graph);

The query has no join and all the per-row queries are done :(

How can I solve this problem? Are there some mistakes in my approach?

PS: I'am using eclipselink

score 0 · Answer 1 · answered Jan 23 '18 at 15:34

I would suggest to try fetch join like:

select p from Person p join fetch p.adresses

or in a criteria query:

EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Person.class);
Root person = criteriaQuery.from(Person.class);
person.fetch(Person_.adresses);
criteriaQuery.select(person);

List resultList = em.createQuery(criteriaQuery).getResultList();

This will result avoid the n+1 problem.

Read more about it: What is the solution for the N+1 issue in hibernate?

I'm using Eclipselink. I've changed root.join with root.fetch but the queries are still there — Daniele Licitra, Jan 23 '18 at 16:50

score 0 · Accepted Answer · answered Feb 09 '18 at 15:29

Problem partially solved with some annotations.

In the product:

public class Product extends EntityBase implements Serializable {

@JoinColumn(name = "idsupp",referencedColumnName = "ID")
@ManyToOne(targetEntity = Fornitori.class,fetch = FetchType.EAGER)
@BatchFetch(BatchFetchType.JOIN)
@JoinFetch(JoinFetchType.OUTER)
private Supplier supplier;

@ManyToOne(cascade=CascadeType.ALL,fetch = FetchType.EAGER)
@JoinColumn(name="infagg_id", referencedColumnName = "ID")
@BatchFetch(BatchFetchType.IN)
private Infos info = new Infos();

BatchFetch and JoinFetch on supplier produces a left outer join clause in the query reducing data query.

Instead for Infos i preferred to use a BatchFetchType.IN. In this way all the record are fetched in one query (where id in ... ) instead of N queriers

JPA N+1 iteration and JSON serializer : query are still there

2 Answers2