Hibernate two ManyToOne relations on one Table, the first gets Eager and the second LAZY loaded

Question

I have got the following Entities, an item which can has up to two categories, a primary and a secondary. Both categories are mapped ManyToOne to the category table using a JoinColumnsOrFormulas. The first one gets fetched EAGER as expected, but the second one does not occur in the SQL statement and gets lazy loaded. This lazy loading results in a classical n+1 problem.

This is my item entity with the both category entities which should gets joined:

@Entity
@Table(name = "item", schema = "public", catalog = "stackoverflow_question")
@DynamicUpdate
public class Item extends StackOverflowQuestionEntity {

    @Id
    @Column(name = "id")
    protected Long id;

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

    @ManyToOne
    @JoinColumnsOrFormulas({
            @JoinColumnOrFormula(formula = @JoinFormula(value = "site", referencedColumnName = "site")),
            @JoinColumnOrFormula(formula = @JoinFormula(value = "primary_category_id", referencedColumnName = "category_id"))
    })
    private Category primaryCategory;

    @Column(name = "primary_category_id")
    private Long primaryCategoryId;

    @ManyToOne
    @JoinColumnsOrFormulas({
            @JoinColumnOrFormula(formula = @JoinFormula(value = "site", referencedColumnName = "site")),
            @JoinColumnOrFormula(formula = @JoinFormula(value = "secondary_category_id", referencedColumnName = "category_id"))
    })
    private Category secondaryCategory;

    @Column(name = "secondary_category_id")
    private Long secondaryCategoryId;
}

This is the category entity:

@Entity
@Table(name = "category", schema = "public", catalog = "stackoverflow_question")
public class Category extends StackOverflowQuestionEntity {

    @Column(name = "category_id")
    private Long categoryId;

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

    @Column(name = "site")
    private String site;
}

The resulting query contains only the primary category:

SELECT this_.id AS id1_9_9_,
       this_.inserted AS inserted2_9_9_,
       this_.updated AS updated3_9_9_,
       this_.primary_category_id AS formula174_9_,
       this_.secondary_category_id AS formula176_9_,
       category2_.id AS id1_0_0_,
       category2_.inserted AS inserted2_0_0_,
       category2_.updated AS updated3_0_0_,
       category2_.name AS name7_0_0_
FROM public.item this_
LEFT OUTER JOIN public.category category2_ ON this_.site=category2_.site
AND this_.primary_category_id=category2_.category_id
WHERE True;

Hence the secondary category get joined lazy:

SELECT category0_.id AS id1_0_0_,
       category0_.inserted AS inserted2_0_0_,
       category0_.updated AS updated3_0_0_,
       category0_.name AS name4_0_0_,
       category0_.site AS site5_0_0_
FROM public.category category0_
WHERE category0_.site=?
  AND category0_.category_id=?;

Why is Hibernate joining the secondary category lazy, the annotations seems the be the same.

The hibernate version I am using is 5.0.10.Final.

This is how the base entity looks like:

@MappedSuperclass
abstract public class StackOverflowQuestionEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, insertable = true, updatable = false, nullable = false)
    protected Long id;

    @Type(type="LocalDateTime")
    @Column(name = "created", nullable = false, insertable = true, updatable = false)
    protected LocalDateTime created;

    @Type(type="LocalDateTime")
    @Column(name = "refreshed", nullable = false, insertable = true, updatable = true)
    protected LocalDateTime refreshed;

    @PreUpdate
    protected void onUpdate() {
        refreshed = now();
    }

    @PrePersist
    protected void onCreate() { 
        created = refreshed = now();
    }
}

Here is an example "query", as said I am using hibernate criteria as well as HQL, the problem occurs with both methods.

session
    .createCriteria(Item.class)
    .add(eq("id", id))
    .uniqueResult();

Chopfallen

try to use standard jpa @JoinColumns.. instead of the hibernate one – Carlinecarling

Any specifics in the database model? I second category nullable for example? – Cribriform

Both category ids are nullable in the database model: \d item primary_category_id | bigint secondary_category_id | bigint – Cyclostyle

How do you load Items? – Underbodice

Via a hibernate Criteria. – Cyclostyle

You did not mention if you tried Maciej's suggestion to use proper JPA join column annotations rather than mixing up JPA and Hibernate annotations. It shouldn't matter, but you never know. I would also mention the version of Hibernate you're using. – Cribriform

I am confused with ManyToOne mapping here. Your one instance of Item entity is associated with two instances(one for primary and one for secondary) of Category entity. – Overstep

@HarshYadav I have added the hibernate version to my question. – Cyclostyle

@MaciejKowalski How would the usage of the JPA Annotations would look like? – Cyclostyle

try docs.oracle.com/javaee/7/api/javax/persistence/JoinColumns.html – Carlinecarling

Using the JPA Annotation produces the correct query with both joins. But it leads to another Problem, if the secondary category is not present (it is optional), hibernate tries to lazy load it again. – Cyclostyle

How do you query your Items? Is it just "from Item", or something more involved? Also, please provide the source for your StackOverflowQuestionEntity, and maybe add the results of your research on using JPA annotations, if you have anything definite there yet – Lala

@AlexSavitsky I have added the StackOverflowQuestionEntity base class to the question. I am fetching the items with HQL and Hibernate criteria, currently I am using the criteria for testing. Using the JPA Annotaions produces the correct query with both joins, but in cases where the secondary category is null hibernates tries to lazy fetch it… . – Cyclostyle

@GuidoKrömer what's the code you use for fetch, could you include it please? I think it makes all the difference – Lala

@GuidoKrömer When saying secondary category is not present do you mean that secondary_category_id is null or that it's not null but the entity with such id is not present? – Luralurch

It seems the second select is generated when you use a composite key. Hibernate tries to resolve associations for {site=site, id=null} using TwoPhaseLoad – Luralurch

@AlexSavitsky I have updated my question with an example "query". – Cyclostyle

@KirillSimonov The secondary_category_id is null. – Cyclostyle

Answer 1

With standard JPA annotations it would look like this (updated):

@ManyToOne
@JoinColumns({
    @JoinColumn(name="site", referencedColumnName="site", insertable = false, updatable = false),
    @JoinColumn(name="primary_category_id", referencedColumnName="category_id", insertable = false, updatable = false)
})
private Category primaryCategory;

@ManyToOne
@JoinColumns({
    @JoinColumn(name="site", referencedColumnName="site", insertable = false, updatable = false),
    @JoinColumn(name="secondary_category_id", referencedColumnName="category_id", insertable = false, updatable = false)
})
private Category secondaryCategory;

UPDATE: I found that the second select statement is generated only when you use join by a composite key: Hibernate tries to resolve associations for {site=site, id=null} using TwoPhaseLoad. But if you write

@ManyToOne
@JoinColumn(name="secondary_category_id")
private Category secondaryCategory;

and secondary_category_id is null then the only one select statement will be generated, and the secondaryCategory value will be null. Maybe it will help you somehow. For example, you could add a constraint on site field while building your criteria:

Category c = (Category) session.createCriteria(Category.class)
    .add(Restrictions.eq("id", 1L)) // for example
    // here you define additional restriction on site field
    .createAlias("secondaryCategory", "sc", JoinType.LEFT_OUTER_JOIN, Restrictions.sqlRestriction("this_.site = {alias}.site"))
    .uniqueResult();

Luralurch

He is joining using composed primary keys: site and category_id. – Thailand

@Hugo Thanks for noticing! Indeed, I was Inattentive. Updated my answer – Luralurch

Using the JPA Annotations only moves the Problem, the query gets build right, with both categories joined. Hence the secondary category is optional, it seems hibernate tries a lazy load it if the secondary_category_id is null. – Cyclostyle

It doesn't load it lazily, it loads it eagerly, but in the separate select statement. And it only does it if you use a composite key for join – Luralurch

@Guido Krömer Try to join only by secondary_category_id and you will get one select. And then you can use criteria to add a constraint by site. Its kinda hack, but I really do not know if the other way exists, if only to revise the architecture. – Luralurch

The workaround using an additional constraint works (I check it as HQL query). But I think I'm going to change the db layout and my code so that I have an internal category id and then I can join directly to this id since I don't like this kind of workaround. – Cyclostyle

Answer 2

Try the following solution:

@Entity
@Table(name = "item", schema = "public", catalog = "stackoverflow_question")
  @DynamicUpdate
  public class Item {
    
    @ManyToOne
    @JoinColumn(name="site")
    private Category primaryCategory;
    
    @ManyToOne
    @JoinColumn(name="site")
    private Category secondaryCategory;
  }

  @Entity
  @Table(name = "category", schema = "public", catalog = "stackoverflow_question")
  public class Category {
    
     @OneToMany(targetEntity=Item.class, mappedBy="primaryCategory", cascade=CascadeType.ALL)
      private List<Item> primaryCategoryList;

    @OneToMany(targetEntity=Item.class, mappedBy="secondaryCategory", cascade=CascadeType.ALL)
       private List<Item> secondaryCategoryList;
    
  }

Misogyny

Answer 3

I did a quick test using your classes, and the following query code (using JPA criteria queries rather than native Hibernate)

CriteriaQuery<Item> cq = em.getCriteriaBuilder().createQuery(Item.class);
EntityGraph<Item> entityGraph = em.createEntityGraph(Item.class);
entityGraph.addSubgraph("primaryCategory", Category.class);
entityGraph.addSubgraph("secondaryCategory", Category.class);
List<Item> items = em.createQuery(cq.select(cq.from(Item.class)))
    .setHint("javax.persistence.loadgraph", entityGraph)
    .getResultList();

results in the following SQL being generated (formatted for readability):

select item0_.id as id1_1_0_, 
    category1_.id as id1_0_1_, 
    category2_.id as id1_0_2_, 
    item0_.site as site4_1_0_, 
    item0_.primary_category_id as primary_2_1_0_,
    item0_.secondary_category_id as secondar3_1_0_, 
    category1_.category_id as category2_0_1_, 
    category1_.name as name3_0_1_, 
    category1_.site as site4_0_1_, 
    category2_.category_id as category2_0_2_, 
    category2_.name as name3_0_2_, 
    category2_.site as site4_0_2_ 
from item item0_ 
left outer join category category1_
    on item0_.site=category1_.site
    and item0_.secondary_category_id=category1_.category_id 
left outer join category category2_
    on item0_.site=category2_.site
    and item0_.primary_category_id=category2_.category_id

As you can see, both category tables are being joined in the same SELECT

Lala