SPRING - Count the number of queries made in a request

Question

I have a simple requisition GET for example, to search a product for your ID:

@Autowired
    private ProdutoRepository produtos;

    @GetMapping
    public Optional<Produto> listarProduto(){

        return produtos.findById(1L);   

    }

Is there any property that would allow me to see the number of queries made in a request?

I searched for properties I could put in my file application.properties and I just found some:

spring.jpa.show-sql=true

spring.jpa.properties.hibernate.format_sql=true

among other properties that did not help me in the case. I am beginner in the subject and I am studying on, I also tried to see if Postman (where I test the requests) would have some configuration that would help me in that sense, but without success.

Answer 1

Using Spring Boot you can enable the following property as described in documentation:

spring.jpa.properties.hibernate.generate_statistics=true

With this at the end of each session will be printed on the console an output similar to that:

12:19:30,318 INFO [org.hibernate.engine.internal.StatisticalLoggingSessionEventListener] – Session Metrics {
    24229 nanoseconds spent acquiring 1 JDBC connections;
    19394 nanoseconds spent releasing 1 JDBC connections;
    1064216 nanoseconds spent preparing 9 JDBC statements;
    1698842 nanoseconds spent executing 9 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    14999942 nanoseconds spent executing 1 flushes (flushing a total of 12 entities and 12 collections);
    64915 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

With this you can verify that they were executed n statements, in my example were 9 statements (queries), as well as the time spent for operations.

Either way, programmatically you can implement an Interceptor to log in. A SessionFactory has an interface to recover statistics:

Statistics estatisticas = sessionFactory.getStatistics();
System.out.println(estatisticas.getQueryExecutionCount());

There are several other options for you to log in, you can check on this documentation

Answer 2

Unfortunately, as far as I know, the Spring and Hibernate framework offers no function to count the queries being executed.

It would be possible to turn on the Loggin query, but every executed query would be logged there and it would be too much for a log file, causing problems. This is because at a low level where we are not seeing, it may be (there are cases) that dozens of queries are being executed and we do not know. I believe that’s the reason you want to know how to see the number of queries being executed.

Fortunately, there’s a legal way to do that. You can implement in your program a Hibernate Interceptor that can count the number of queries for each request in a Spring MVC application that uses Hibernate and JPA.

It would be a lot of text and a lot of code for me to put here, so I’ll leave the link where you can see how to implement this service. The article is English, so if you have problems with the language, I recommend using google translator or the direct Chrome translator function to better understand. I hope it helps.

Here is the article: http://knes1.github.io/blog/2015/2015-07-08-counting-queries-per-request-with-hibernate-and-spring.html