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();
You can have multiple one-to-many associations, as long as only one is EAGER. But, even if you can use Set instead of List to bypass this MultipleBagFetchException , it's not a good thing to do because you'll end up with a Cartesian Product.
In JPA a ManyToOne relationship is specified through the @ManyToOne annotation or the <many-to-one> element. A @ManyToOne annotation is typically accompanied by a @JoinColumn annotation. The @JoinColumn annotation specifies how the relationship should be mapped to (expressed in) the database.
The @JoinColumn annotation is used to specify the join/linking column with the main table. Here, the join column is employee_id and project_id is the inverse join column since Project is on the inverse side of the relationship.
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();
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 primaryCategory;
}
@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;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With