Count SQL statements in Hibernate

Question

I am having some peformance issues with hibernate as it is hitting the database too many times. I am investigating some fetch strategies. I want to write some functional unit tests so as my application evolves I can see how many SQL statements are being called.

I want to know if I can count how many SQL statements are being called. At the moment I am setting show-sql to true and then counting the SQLs in the console. I want to do this in code if possible. Is it possible to count how many SQLs hibernate is hitting the DB with in code?

Thanks

EDIT

After @Aleksander Blomskøld reply....

My test case is

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 query execution count is given as 1 and if I look at the query it says that it is "from MyType"

However in the sql statements that are in the log I can see that there are SQL statements to retrieve MyType and a lot of its related classes. So in fact I want to know all the SQLs that are hitting the DB because of the "from MyType" call.

Is what I require clearer? Or am I just misusing the StatisticService

Thanks

Answer 1

Enable statistics in Hibernate and use the statistics service. Make sure to set hibernate.generate_statistics=true when you configure your session factory. You could then either access the statistics via JMX or programatically:

//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 might try some JDBC wrapper/proxy toolkits out there.

This one looks promising for your task: JDBC Spy.

Features

• log the execution and the iteration time of all SQL statements
• identify statements that are executed multiple times
• the stack trace with configurable depth for all listed statements
• provides statistics for all connections, SQL statements, resultsets
• provides the size of the resultset
• provides an API to retrieve all statistical information
• list all statements that are currently being executed
• list all statements that have been executed, but have not been closed
• notifies (e.g. via trace) if a statement's execution time exceeds a configurable threshold
• notifies if you forgot to close a resultset, or a statement before the connection is closed
• supports different loggers (log4j, java logging, slf, ...)
• extendable by custom listeners

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