We use Spring Data JPA and have successfully configured L2 entity and query caches. What still bothers me though is the fact that Hibernate always opens a JDBC connection before checking the L2 cache. This sometimes causes issues (too many open connections) due to a very busy database.

Is there a way to make Hibernate only open a JDBC connection after a L2 cache miss?

Just to clarify, here are the session metrics:

i.StatisticalLoggingSessionEventListener : Session Metrics {
    1716707 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    0 nanoseconds spent preparing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    236767 nanoseconds spent performing 1 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

So even though there was absolutely no query performed on the database, a JDBC connection was still opened.

CodePudding user response：

Thanks to pointers given by Christian Beikov I've found out that the jdbc connection is necessary for the transaction around the operation.

It was the @Transactional that is enabled for methods in the default Spring Data JPA repository implementation. Overriding one of these methods and leaving the annotation away causes everything to work solely on the cache without any jdbc connection involved.

CodePudding user response：

Although removing @Transactional might appear to solve your issue it actually isn't. You want to delay the acquisition of the connection for this you would need to do some additional configuration

1. Set spring.jpa.open-in-view=false this disables Open Session In View
2. Set spring.datasource.hikari.auto-commit=false, this will optimize Hibernate to not set this (performance of your app)

This should stop Hibernate from actually getting a physical connection to the point when it actually needs to. Even when inside the transaction (as it now not needs to check the connection for auto-commit).

I'm assuming you are using Spring Boot, if not at least set the autoCommit property of your datasource to false should already fix this (if you are on a recent Spring (Boot) version!).

See also this explanation by Vlad Mihalcea (the goto source for JPA/Hibernate related stuff :) ).