5 Common Hibernate Mistakes That Cause Dozens of Unexpected Queries
Take your skills to the next level!
The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.
If there is one thing that’s often criticized about JPA and Hibernate, it’s that it sometimes execute more queries than you expected. And I’m not talking about 1 or 2 additional queries. I’m talking about dozens or even hundreds of them. The worst thing about it is that you don’t recognize that problem if you don’t configure your logging correctly or activate Hibernate’s statistics component.
But while this is a problem that often occurs with JPA and Hibernate, you shouldn’t solely blame the framework for it. In most situations, these queries are caused by small mistakes which you could have easily prevented. So, it’s up to you and me, the developer, to avoid these mistakes and use JPA and Hibernate efficiently.
Let me show you the 5 most common mistakes that cause Hibernate to execute dozens or even hundreds of queries and how you can avoid them.
1. Not Initializing Lazily Fetched Associations
The initialization of lazily fetched associations is one of the most common reasons for Hibernate to generate additional queries. If you have been using Hibernate for a while, you probably experienced this problem yourself. It’s often called the n+1 select issue. Whenever you access an uninitialized, lazily fetched association, Hibernate executes a query that loads all elements of this association from the database.
The following code snippet shows a typical example that forces Hibernate to trigger an additional query to get the associated Book entities for each Author entity.
List<Author> authors = em.createQuery("SELECT a FROM Author a", Author.class).getResultList();
for (Author a : authors) {
log.info(a.getFirstName() + " " + a.getLastName() + " wrote " + a.getBooks().size() + " books.");
}
When you run this code and activate the logging of SQL statements, you can see that Hibernate executes an additional query for each Author to fetch the associated Book entities.
19:18:57,148 DEBUG [org.hibernate.SQL] -
select
author0_.id as id1_0_,
author0_.firstName as firstNam2_0_,
author0_.lastName as lastName3_0_,
author0_.version as version4_0_
from
Author author0_
19:18:57,233 DEBUG [org.hibernate.SQL] -
select
books0_.authorId as authorId2_2_0_,
books0_.bookId as bookId1_2_0_,
book1_.id as id1_1_1_,
book1_.publisherid as publishe5_1_1_,
book1_.publishingDate as publishi2_1_1_,
book1_.title as title3_1_1_,
book1_.version as version4_1_1_,
publisher2_.id as id1_3_2_,
publisher2_.name as name2_3_2_,
publisher2_.version as version3_3_2_
from
BookAuthor books0_
inner join
Book book1_
on books0_.bookId=book1_.id
left outer join
Publisher publisher2_
on book1_.publisherid=publisher2_.id
where
books0_.authorId=?
19:18:57,264 INFO [org.thoughts.on.java.model.TestJoinFetch] - Thorben Janssen wrote 1 books.
19:18:57,264 DEBUG [org.hibernate.SQL] -
select
books0_.authorId as authorId2_2_0_,
books0_.bookId as bookId1_2_0_,
book1_.id as id1_1_1_,
book1_.publisherid as publishe5_1_1_,
book1_.publishingDate as publishi2_1_1_,
book1_.title as title3_1_1_,
book1_.version as version4_1_1_,
publisher2_.id as id1_3_2_,
publisher2_.name as name2_3_2_,
publisher2_.version as version3_3_2_
from
BookAuthor books0_
inner join
Book book1_
on books0_.bookId=book1_.id
left outer join
Publisher publisher2_
on book1_.publisherid=publisher2_.id
where
books0_.authorId=?
19:18:57,267 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 2 wrote 0 books.
19:18:57,267 DEBUG [org.hibernate.SQL] -
select
books0_.authorId as authorId2_2_0_,
books0_.bookId as bookId1_2_0_,
book1_.id as id1_1_1_,
book1_.publisherid as publishe5_1_1_,
book1_.publishingDate as publishi2_1_1_,
book1_.title as title3_1_1_,
book1_.version as version4_1_1_,
publisher2_.id as id1_3_2_,
publisher2_.name as name2_3_2_,
publisher2_.version as version3_3_2_
from
BookAuthor books0_
inner join
Book book1_
on books0_.bookId=book1_.id
left outer join
Publisher publisher2_
on book1_.publisherid=publisher2_.id
where
books0_.authorId=?
19:18:57,269 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 3 wrote 0 books.
19:18:57,270 DEBUG [org.hibernate.SQL] -
select
books0_.authorId as authorId2_2_0_,
books0_.bookId as bookId1_2_0_,
book1_.id as id1_1_1_,
book1_.publisherid as publishe5_1_1_,
book1_.publishingDate as publishi2_1_1_,
book1_.title as title3_1_1_,
book1_.version as version4_1_1_,
publisher2_.id as id1_3_2_,
publisher2_.name as name2_3_2_,
publisher2_.version as version3_3_2_
from
BookAuthor books0_
inner join
Book book1_
on books0_.bookId=book1_.id
left outer join
Publisher publisher2_
on book1_.publisherid=publisher2_.id
where
books0_.authorId=?
19:18:57,273 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 4 wrote 0 books.
If you’re only fetching a few Author entities, this is not a huge issue. You might not even recognize it because Hibernate and your database process the additional queries in a few milliseconds. But that changes dramatically if the Book entity becomes more complex or if you select several hundred Author entities.
Initialize all Required Associations
You can easily avoid this problem by initializing the associations you want to use while you retrieve an entity from the database. JPA and Hibernate offer several options to do that. The easiest one is a JOIN FETCH or LEFT JOIN FETCH expression which you can use within your JPQL or Criteria Query. You can use it in the same way as a simple JOIN expression. It tells Hibernate not only to join the association within the query but also to fetch all elements of the association.
Let’s improve the previous example and fetch the associated Book entities with a LEFT JOIN FETCH clause. As you can see in the code snippet, the change is relatively simple. You just need to add the LEFT JOIN FETCH expression to the FROM clause.
List<Author> authors = em.createQuery("SELECT a FROM Author a LEFT JOIN FETCH a.books", Author.class).getResultList();
for (Author a : authors) {
log.info(a.getFirstName() + " " + a.getLastName() + " wrote " + a.getBooks().size() + " books.");
}
This small change in your JPQL query has a huge impact on the generated SQL statements. Hibernate now executes only 1 query instead of multiple ones and it also changed the SELECT clause of the query to include all columns mapped by the Book entity.
19:21:12,254 DEBUG [org.hibernate.SQL] -
select
author0_.id as id1_0_0_,
book2_.id as id1_1_1_,
author0_.firstName as firstNam2_0_0_,
author0_.lastName as lastName3_0_0_,
author0_.version as version4_0_0_,
book2_.publisherid as publishe5_1_1_,
book2_.publishingDate as publishi2_1_1_,
book2_.title as title3_1_1_,
book2_.version as version4_1_1_,
books1_.authorId as authorId2_2_0__,
books1_.bookId as bookId1_2_0__
from
Author author0_
left outer join
BookAuthor books1_
on author0_.id=books1_.authorId
left outer join
Book book2_
on books1_.bookId=book2_.id
19:21:12,409 INFO [org.thoughts.on.java.model.TestJoinFetch] - Thorben Janssen wrote 1 books.
19:21:12,409 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 2 wrote 0 books.
19:21:12,409 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 3 wrote 0 books.
19:21:12,409 INFO [org.thoughts.on.java.model.TestJoinFetch] - Author 4 wrote 0 books.
2. Using the FetchType.EAGER
Not only the initialization of lazily fetched associations can cause lots of unexpected queries. That’s also the case if you use FetchType.EAGER. It forces Hibernate to initialize the association as soon as it loads the entity. So, it doesn’t even matter if you use the association or not. Hibernate will fetch it anyways. That makes it one of the most common performance pitfalls.
And before you tell me that you never declare any eager fetching in your application, please check your to-one associations. Unfortunately, JPA defines eager fetching as the default behavior for these associations.
OK, so how do you avoid these problems?
Use FetchType.LAZY for all Associations
You should use FetchType.LAZY for all of your associations. It’s the default for all to-many associations, so you don’t need to declare it for them explicitly. But you need to do that for all to-one association. You can specify the FetchType with the fetch attribute of the @OneToOne or @ManyToOne association.
@Entity
public class Review {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "book_id")
private Book book;
...
}
3. Removing Child Entities with CascadeType.Remove
Cascading provides a very comfortable option to remove all child entities when you delete their parent entity. In the example of this article, you could, for example, use it to remove all Reviews of a Book, when you delete the Book entity.
Unfortunately, the removal of the associated entities isn’t very efficient and can create serious side effects. I, therefore, recommend to NOT use CascadeType.Remove in your application. Let’s take a look at it anyways so that you know how it works and what you should do instead.
The only thing you need to do to activate the cascading of remove operations is to set the cascade attribute on the @OneToMany association to CascadeType.Remove.
@Entity
public class Book {
@OneToMany(mappedBy = "book", cascade = CascadeType.REMOVE)
private List<Review> reviews = new ArrayList<Review>();
...
}
When you now remove a Book entity, Hibernate cascades the operation to the associated Review entities.
Book b = em.find(Book.class, 2L); em.remove(b);
19:35:50,733 DEBUG [org.hibernate.SQL] - select book0_.id as id1_0_0_, book0_.title as title2_0_0_, book0_.version as version3_0_0_ from Book book0_ where book0_.id=? 19:35:50,781 DEBUG [org.hibernate.SQL] - select reviews0_.fk_book as fk_book3_1_0_, reviews0_.id as id1_1_0_, reviews0_.id as id1_1_1_, reviews0_.fk_book as fk_book3_1_1_, reviews0_.comment as comment2_1_1_ from Review reviews0_ where reviews0_.fk_book=? 19:35:50,823 DEBUG [org.hibernate.SQL] - delete from Review where id=? 19:35:50,826 DEBUG [org.hibernate.SQL] - delete from Review where id=? 19:35:50,828 DEBUG [org.hibernate.SQL] - delete from Review where id=? 19:35:50,829 DEBUG [org.hibernate.SQL] - delete from Review where id=? 19:35:50,830 DEBUG [org.hibernate.SQL] - delete from Review where id=? 19:35:50,831 DEBUG [org.hibernate.SQL] - delete from Book where id=? and version=?
Do you see all the DELETE statements in the log?
Yes, that’s right. Hibernate loads all associated entities and removes each of them with its own SQL DELETE statement. It needs to do that because it transitions the lifecycle state of each entity from managed to removed. That provides the advantage that all lifecycle events get triggered and caches are updated.
Remove Child Entities With a Bulk Operation
If you don’t use any lifecycle callbacks or EntityListeners and don’t use any frameworks that use them, e.g., Hibernate Envers, you can use a JPQL, Criteria or native SQL query to remove all child entities with one bulk operation. This is more efficient but doesn’t trigger any lifecycle events.
The following code shows a JPQL query that removes all Review entities associated with a given Book entity. As you can see, the syntax is pretty similar to SQL.
em.flush();
em.clear();
Book b = em.find(Book.class, 2L);
Query q = em.createQuery("DELETE FROM Review r WHERE r.book.id = :bid");
q.setParameter("bid", 2L);
q.executeUpdate();
em.remove(b);
19:33:56,966 DEBUG [org.hibernate.SQL] - select book0_.id as id1_0_0_, book0_.title as title2_0_0_, book0_.version as version3_0_0_ from Book book0_ where book0_.id=? 19:33:57,503 DEBUG [org.hibernate.SQL] - delete from Review where fk_book=? 19:33:57,510 DEBUG [org.hibernate.SQL] - select reviews0_.fk_book as fk_book3_1_0_, reviews0_.id as id1_1_0_, reviews0_.id as id1_1_1_, reviews0_.fk_book as fk_book3_1_1_, reviews0_.comment as comment2_1_1_ from Review reviews0_ where reviews0_.fk_book=? 19:33:57,541 DEBUG [org.hibernate.SQL] - delete from Book where id=? and version=?
Hibernate doesn’t know which entities this operation removes. So, it can’t remove any of the deleted entities from the persistence context.
You either need to be sure that you didn’t fetch any of the removed entities before you executed the bulk operation or you need to call the flush() and clear() methods on the EntityManager before you execute the JPQL query. This tells Hibernate to write all pending changes to the database and to detach all entities from the persistence context before you execute the remove operation. So, you can be sure that your persistence context doesn’t contain any outdated entities.
4. Modeling Many-to-Many Associations as a List
Another common but not that well-known mistake that causes Hibernate to execute lots of addition SQL statements is using the wrong data type to model many-to-many associations. If you map the association to an attribute of type java.util.List, Hibernate deletes all existing association records and then inserts a new one for each managed association whenever you add an element to or remove one from the association.
Let’s take a look at a simple example.
I modeled a many-to-many association between my Author and Book entity. I mapped this association to the List<Author> authors attribute on the Book entity.
@Entity
public class Book {
@ManyToMany
@JoinTable(name = "book_author",
joinColumns = { @JoinColumn(name = "fk_book") },
inverseJoinColumns = { @JoinColumn(name = "fk_author") })
private List<Author> authors = new ArrayList<Author>();
...
}
When I now add or remove a new Author to the association, Hibernate deletes all records from the book_author relationship table which are associated with the Book before it inserts a new record for each managed association.
b = em.find(Book.class, 1L);
Author a = new Author();
a.setFirstName("Thorben");
a.setLastName("Janssen");
a.getBooks().add(b);
em.persist(a);
b.getAuthors().add(a);
19:08:26,875 DEBUG [org.hibernate.SQL] - select book0_.id as id1_1_0_, book0_.title as title2_1_0_, book0_.version as version3_1_0_ from Book book0_ where book0_.id=?
19:08:26,877 DEBUG [org.hibernate.SQL] - select nextval ('hibernate_sequence')
19:08:26,879 DEBUG [org.hibernate.SQL] - select authors0_.fk_book as fk_book1_2_0_, authors0_.fk_author as fk_autho2_2_0_, author1_.id as id1_0_1_, author1_.firstName as firstNam2_0_1_, author1_.lastName as lastName3_0_1_, author1_.version as version4_0_1_ from book_author authors0_ inner join Author author1_ on authors0_.fk_author=author1_.id where authors0_.fk_book=?
19:08:26,883 DEBUG [org.hibernate.SQL] - insert into Author (firstName, lastName, version, id) values (?, ?, ?, ?)
19:08:26,885 DEBUG [org.hibernate.SQL] - update Book set title=?, version=? where id=? and version=?
19:08:26,887 DEBUG [org.hibernate.SQL] - delete from book_author where fk_book=?
19:08:26,888 DEBUG [org.hibernate.SQL] - insert into book_author (fk_book, fk_author) values (?, ?)
19:08:26,891 DEBUG [org.hibernate.SQL] - insert into book_author (fk_book, fk_author) values (?, ?)
19:08:26,892 DEBUG [org.hibernate.SQL] - insert into book_author (fk_book, fk_author) values (?, ?)
Use a Set instead of a List
That’s obviously not the most efficient approach and probably not what you expected. You can easily fix that by changing the data type from List<Author> to Set<Author>.
@Entity
public class Book {
@ManyToMany
@JoinTable(name = "book_author",
joinColumns = { @JoinColumn(name = "fk_book") },
inverseJoinColumns = { @JoinColumn(name = "fk_author") })
private Set<Author> authors = new HashSet<Author>();
...
}
When you now execute the same code as before, Hibernate doesn’t remove the existing records in the book_author table and just inserts the new one.
@19:12:39,727 DEBUG [org.hibernate.SQL] - select book0_.id as id1_1_0_, book0_.title as title2_1_0_, book0_.version as version3_1_0_ from Book book0_ where book0_.id=?
19:12:39,730 DEBUG [org.hibernate.SQL] - select nextval ('hibernate_sequence')
19:12:39,731 DEBUG [org.hibernate.SQL] - select authors0_.fk_book as fk_book1_2_0_, authors0_.fk_author as fk_autho2_2_0_, author1_.id as id1_0_1_, author1_.firstName as firstNam2_0_1_, author1_.lastName as lastName3_0_1_, author1_.version as version4_0_1_ from book_author authors0_ inner join Author author1_ on authors0_.fk_author=author1_.id where authors0_.fk_book=?
19:12:39,737 DEBUG [org.hibernate.SQL] - insert into Author (firstName, lastName, version, id) values (?, ?, ?, ?)
19:12:39,740 DEBUG [org.hibernate.SQL] - update Book set title=?, version=? where id=? and version=?
19:12:39,745 DEBUG [org.hibernate.SQL] - insert into book_author (fk_book, fk_author) values (?, ?)
5. Updating or Removing Entities One by One
The last mistake I want to talk about in this article slows down use cases that update or remove multiple database records in the same table. With SQL, you would create 1 SQL statement that updates or removes all affected records. That would also be the most efficient approach with JPA and Hibernate.
But that’s not what Hibernate does if you update or remove multiple entities. Hibernate creates an update or remove statement for each entity. So, if you want to remove 100 entities, Hibernate creates and executes 100 SQL DELETE statements.
TypedQuery<Author> query = em.createQuery("SELECT a FROM Author a WHERE id IN (:ids)", Author.class);
query.setParameter("ids", ids);
List<Author> authors = query.getResultList();
for (Author a : authors) {
em.remove(a);
}
18:38:04,708 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id=?
and version=?
18:38:04,709 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id=?
and version=?
18:38:04,710 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id=?
and version=?
18:38:04,711 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id=?
and version=?
18:38:04,713 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id=?
and version=?
... (100 SQL statements in total...)
Use a Bulk Update or Remove Operation
Executing that many SQL statements is obviously not a very efficient approach. It’s much better to implement a bulk operation as a native, JPQL or Criteria query.
This is the same approach as I showed you as the solution to mistake 3. You first need to flush and clear your persistence context before you execute a query that removes all entities that fulfill the defined criteria.
em.flush();
em.clear();
// Remove all entities referenced in the List ids variable
Query query = em.createQuery("DELETE Author a WHERE id IN (:ids)");
query.setParameter("ids", ids);
query.executeUpdate();
18:34:15,911 DEBUG [org.hibernate.SQL] -
delete
from
Author
where
id in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
As you can see in the log messages, Hibernate now removes the entities with a single statement instead of multiple ones.
Summary
As you’ve seen, the most common mistakes are not only easy to make, they are also very easy to avoid:
- When you’re defining your associations, you should prefer FetchType.LAZY and map many-to-many associations to a java.util.Set.
- If your use case uses a lazily fetched association, you should initialize it within the query that loads the entity, e.g., with a JOIN FETCH expression.
- Cascading and updating or removing multiple entities require more SQL statements than you might expect. It’s often better to implement a bulk operation as a native, JPQL or Criteria query.
By following these recommendations, you will avoid the most common mistakes that cause Hibernate to execute lots of unexpected queries. If you want to learn more about Hibernate performance optimizations, you should join the waiting list of my Hibernate Performance Tuning Online Training. I will reopen it to enroll a new class soon.
This is not only related to Hibernate, any JPA implementation is affected one way or another by these changes. But does changed from List to Set really help fixing performance issues? As I have heard java.util.ArrayList is super-fast.
Hi Roland,
Yes, the change from a List to a Set helps to fix performance issues. The Set doesn’t contain enough entries to be significantly slower than a List. And, what’s more important, you avoid unnecessary database queries.
Regards,
Thorben
Excellent post. I’m experiencing many of these issues as well..
The section “Use a Set instead of a List” saved my day. You are god!
Awesome. Happy to hear that it helped you 🙂
Hello Thorben,
Thanks again for your really valuable tips!!
As our company forces us to use EclipsLink I always have to somehow “translate” your proposals.
Now for the first time I found out that a mistake does not appears in EclipseLink: “4. Modeling Many-to-Many Associations as a List”
All the best
Stephan
Hi Stephan,
Yes, that’s one of the few things that EclipseLink does better.
Regards,
Thorben