4

Fetching recursive associations with JPA and Hibernate

 7 months ago
source link: https://vladmihalcea.com/recursive-associations-jpa-hibernate/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Fetching recursive associations with JPA and Hibernate

Last modified: Jan 26, 2024

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

Introduction

In this article, we are going to see how to fetch recursive associations when using JPA and Hibernate.

Recursive table relationships are built using self-referencing Foreign Key columns so that a record in a table can reference another record in the very same table, therefore allowing us to represent hierarchical structures using the relational model.

Domain Model

Let’s consider we have the following book and category tables in our database:

Book and Category tables

The category table has a parent_id column that has a Foreign Key constraint referencing the id column in the very same table.

The category has a one-to-many relationship with the book table since a category record could be referenced by many book rows.

The Category JPA entity maps the category table like this:

@Entity
@Table(name = "category")
public class Category {
@Id
@GeneratedValue
private Short id;
@Column(length = 25)
private String name;
@ManyToOne(fetch = FetchType.LAZY)
private Category parent;
}

And the book table is mapped to the Book entity as follows:

@Entity
@Table(name = "book")
public class Book {
@Id
@GeneratedValue
private Long id;
@Column(length = 50)
private String title;
@Column(columnDefinition = "numeric(13)")
@NaturalId
private long isbn;
@ManyToOne(fetch = FetchType.LAZY)
private Category category;
}

Now, let’s create a hierarchy of category records, each new table row being the child of the previously created category record:

Category java = new Category()
.setName("Java");
Category jpa = new Category()
.setName("JPA")
.setParent(java);
Category hibernate = new Category()
.setName("Hibernate")
.setParent(jpa);
Category hibernate6 = new Category()
.setName("Hibernate 6")
.setParent(hibernate);
⠀
entityManager.persist(java);
entityManager.persist(jpa);
entityManager.persist(hibernate);
entityManager.persist(hibernate6);

Now, we will also create two book records, one referencing the last category in our hierarchy and the other referencing the first category:

entityManager.persist(
new Book()
.setTitle("High-Performance Java Persistence")
.setIsbn(9789730228236L)
.setCategory(hibernate6)
);
entityManager.persist(
new Book()
.setTitle("Effective Java")
.setIsbn(9780134685991L)
.setCategory(java)
);

Fetching recursive associations with JPA and Hibernate can be done in two ways:

  • we can use the JOIN FETCH clause in a JPQL query or
  • we can use the Recursive CTE directive with a native SQL query

Next, we are going to explore both options and see what are their pros and cons.

Fetching recursive associations with JPQL

If we want to fetch the Book along with its associated Category hierarchy, we can use the JOIN FETCH directive of the JPQL query, as illustrated by the following example:

Book hpjp = doInJPA(entityManager -> {
return entityManager.createQuery("""
select b
from Book b
left join fetch b.category c1
left join fetch c1.parent c2
left join fetch c2.parent c3
left join fetch c3.parent c4
where b.isbn = :isbn
""", Book.class)
.setParameter("isbn", 9789730228236L)
.getSingleResult();
});
Category hpjpCategory = hpjp.getCategory();
⠀
assertEquals(
"Hibernate 6",
hpjpCategory.getName()
);
assertEquals(
"Hibernate",
hpjpCategory.getParent().getName()
);
assertEquals(
"JPA",
hpjpCategory.getParent().getParent().getName()
);
assertEquals(
"Java",
hpjpCategory.getParent().getParent().getParent().getName()
);

When running the above JPQL query, Hibernate generates the following SQL query:

SELECT
b.id, b.isbn, b.title,
c.id, c.name,
p1.id, p1.name,
p2.id, p2.name,
p3.id, p3.name, p3.parent_id
FROM
book b
LEFT JOIN
category c ON c.id = b.category_id
LEFT JOIN
category p1 ON p1.id = c.parent_id
LEFT JOIN
category p2 ON p2.id = p1.parent_id
LEFT JOIN
category p3 ON p3.id = p2.parent_id
WHERE
b.isbn = 9789730228236

When fetching the second book record, we would need a single JOIN FETCH:

Book effectiveJava = doInJPA(entityManager -> {
return entityManager.createQuery("""
select b
from Book b
left join fetch b.category c
where b.isbn = :isbn
""", Book.class)
.setParameter("isbn", 9780134685991L)
.getSingleResult();
});
assertEquals("Java", effectiveJava.getCategory().getName());

Therefore, this solution works only if we know up-front how many levels the hierarchy of the category records has.

If the number of levels is dynamic, this approach will fall short.

Fetching recursive associations with native SQL

Another approach to fetching the book and the associated category hierarchy is to use a Recursive CTE and LATERAL JOIN query, as illustrated by the following example:

BookDTO hpjp = doInJPA(entityManager ->
(BookDTO) entityManager.createNativeQuery("""
SELECT
b.id AS "b.id", b.title AS "b.title",
b.isbn AS "b.isbn", b.category_id AS "b.category_id",
c.id AS "c.id", c.name AS "c.name", c.parent_id AS "c.parent_id"
FROM book b,
LATERAL (
WITH RECURSIVE book_category_hierarchy AS (
SELECT
category.id AS id,
category.name AS name,
category.parent_id AS parent_id
FROM category
WHERE category.id = b.category_id
⠀
UNION ALL
⠀
SELECT
category.id AS id,
category.name AS name,
category.parent_id AS parent_id
FROM category category
JOIN book_category_hierarchy bch ON bch.parent_id = category.id
)
SELECT *
FROM book_category_hierarchy
) c
WHERE isbn = :isbn
""",
"BookCategory"
)
.setParameter("isbn", 9789730228236L)
.unwrap(NativeQuery.class)
.setTupleTransformer(new BookDTOTupleTransformer())
.setResultListTransformer(DistinctListTransformer.INSTANCE)
.getSingleResult());
CategoryDTO hpjpCategory = hpjp.getCategory();
assertEquals(
"Hibernate 6",
hpjpCategory.getName()
);
assertEquals(
"Hibernate",
hpjpCategory.getParent().getName()
);
assertEquals(
"JPA",
hpjpCategory.getParent().getParent().getName()
);
assertEquals(
"Java",
hpjpCategory.getParent().getParent().getParent().getName()
);

The SQL query result set is mapped to a BookDTO and CategoryDTO tuple using the following BookCategory @SqlResultSetMapping:

@SqlResultSetMapping(
name = "BookCategory",
classes = {
@ConstructorResult(
targetClass = BookDTO.class,
columns = {
@ColumnResult(name = "b.id"),
@ColumnResult(name = "b.title"),
@ColumnResult(name = "b.isbn"),
@ColumnResult(name = "b.category_id")
}
),
@ConstructorResult(
targetClass = CategoryDTO.class,
columns = {
@ColumnResult(name = "c.id"),
@ColumnResult(name = "c.name"),
@ColumnResult(name = "c.parent_id")
}
)
}
)

And the BookDTO and CategoryDTO classes look as follows:

BookDTO and CategoryDTO

To transform the BookDTO and CategoryDTO flat result set into a hierarchical structure, we are using the BookDTOTupleTransformer:

public class BookDTOTupleTransformer
implements TupleTransformer<BookDTO> {
private BookDTO book;
@Override
public BookDTO transformTuple(
Object[] tuple,
String[] aliases){
CategoryDTO category = (CategoryDTO) tuple[1];
⠀
if(book == null) {
book = (BookDTO) tuple[0];
book.setCategory(category);
} else {
CategoryDTO childCategory = book.getCategory().findByParentId(
category.getId()
);
if (childCategory != null) {
childCategory.setParent(category);
}
}
return book;
}
}

The BookDTOTupleTransformer takes the default Object[] projection containing the BookDTO and CategoryDTO objects and returns the BookDTO object with the category hierarchy in place.

The findByParentId and setParent calls allow us to build the parent Object reference since the SQL result set only provides the value of the parentId column value.

For more details about the Hibernate TupleTransformer, check out this article.

In the end, we are using the DistinctListTransformer.INSTANCE from the Hypersistence Utils project to remove the duplicated BookDTO object references returned by the BookDTOTupleTransformer since, for each parent CategoryDTO we process, we return the same BookDTO object reference.

That’s it!

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

HPJP_h200.jpg

HPJP_Video_Vertical_h200.jpg

HPSQL_Video_Course_h200.jpg

Conclusion

Fetching recursive associations with JPA and Hibernate can be done using both JPQL and native SQL queries.

The JPQL approach is suitable when we know the number of levels in our hierarchy and we plan on updating the entities that we are fetching.

On the other hand, the native SQL approach is more efficient since the levels are fetched dynamically by the Recursive CTE expression.

Transactions and Concurrency Control eBook

2 Comments on “Fetching recursive associations with JPA and Hibernate”

  1. Hello Vlad! It would be awesome if you could add option 3 “HQL CTE query” to your blog post!

    • While HQL added support for CTE, I don’t think the LATERAL JOIN part is supported yet. The test case can be found here. Try to add a HQL example, and if it works, send me a Pull Request to check it as well.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Comment *

Before posting the comment, please take the time to read the FAQ page

Name *

Email *

Website

Notify me of follow-up comments by email.

This site uses Akismet to reduce spam. Learn how your comment data is processed.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK