Read SQL statements in Hibernate

Question

I am having problems with hibernation because it strikes the database too much. I am exploring some extraction strategies. I want to write some functional unit tests, as my application develops, I can see how many SQL expressions are being called.

I want to know if I can count how many SQL statements are being called. Currently, I set show-sql to true, and then count the SQL queries in the console. I want to do this in code, if possible. Is it possible to calculate how much hibernate SQL hits DB with code?

thanks

EDIT

After @Aleksander Blomskøld answers ....

My test case

StatisticsService statisticsService = new StatisticsService(); Session session = entityManager.unwrap(Session.class); statisticsService.setSessionFactory(session.getSessionFactory()); statisticsService.setStatisticsEnabled(true); List<MyType> r= entityManager.createQuery("from MyType", MyType.class).getResultList(); System.out.println(statisticsService.getQueryExecutionCount()); System.out.println(statisticsService.getQueries()[0]); 

The request execution score is set to 1, and if I look at the request, it says that it is "from MyType"

However, in the sql statements that are in the log, I see that there are SQL queries to retrieve MyType and many related classes. Therefore, in fact, I want to know all the SQL that gets into the database due to a call from MyType.

Is that what I need more clearly? Or am I just abusing StatisticService

thanks

Answer 1

Enable statistics in sleep mode and use the statistics service. Be sure to set hibernate.generate_statistics = true when setting up a factory session. Then you can either access the statistics via JMX, or programmatically:

 //Enable statistics StatisticsService statisticsService = new StatisticsService(); statisticsService.setSessionFactory(sessionFactory); statisticsService.setStatisticsEnabled(true); // Do queries... //... ///Print out stats: System.out.println(statisticsService.getQueryExecutionCount()); 

Answer 2

You can try some JDBC shell / proxy tools.

This looks promising for your task: JDBC Spy .

Functions

• log execution and iteration time of all SQL statements
• identify statements executed several times
• depth stack trace for all listed operators
• provides statistics for all connections, SQL statements, results
• provides a result set size
• provides an API to retrieve all statistical information
• list of all statements that are currently being executed
• list of all statements that were executed but were not closed
• notifies (for example, through tracing) if the execution time of an instruction exceeds a custom threshold
• notifies if you forgot to close the result set or statement before closing the connection.
• supports different loggers (log4j, java logging, slf, ...)
• extensible by user listeners

But there are many more, for example log4dbc , jdbc-trace-wrapper , etc.