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();

Answer 1

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;
    
  }

Answer 2

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

Answer 3

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();