Spring Data JPA + Hibernate + Postgresql LTree. Is it possible to load whole subtree by given node with one query?

Question

I'm using Spring Data JPA + Postgresql and I have entity "Category" which implements Postgresql LTree type and looks like this :

@Id
@SequenceGenerator(name = "categories_id_seq", sequenceName =  "categories_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "categories_id_seq")
@Column(name = "id", updatable = false)
private Integer id;

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

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

@Column(name = "trail")
@Type(type = "ltree")
private String trail;

@Column(name = "layer")
private Integer layer;

@Column(name = "parent_id")
private Integer parentId;

@ManyToOne()
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "parent_id", insertable = false, updatable = false)
private Category parent;

@OneToMany
@Fetch(FetchMode.JOIN)
@BatchSize(size = 20)
@JoinColumn(name = "parent_id", insertable = false, updatable = false)
private Set<Category> childrens;

My problem is when i want to load whole subtree from given node to "leaf" nodes Hibernate issues 2 additional selects for each pair of parent and childrens.

How can i change this Hibernate behavior?

score 0 · Answer 1 · answered Apr 05 '16 at 12:23

If you want to minimize the Hibernate overhead why don't you define your custom @NamedNativeQuery in the entity or directly use createNativeQuery(sqlString, Category.class)? ORM are useful but they also not very effective with complex queries/table structures.

Spring Data JPA + Hibernate + Postgresql LTree. Is it possible to load whole subtree by given node with one query?

1 Answers1