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

Your database probably has better ways to get heuristics on this than "counting SQLs". I think you mean "counting SQL statements" btw. — Daniel Kaplan
– Daniel Kaplan, Commented Jun 13, 2013 at 20:08

Aleksander Blomskøld · Accepted Answer · 2013-06-13 20:14:16Z

8

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());

answered Jun 13, 2013 at 20:14

Aleksander Blomskøld

18.6k10 gold badges79 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RNJ Over a year ago

Hi @Aleksander, Im not sure this is quite what I want. I have updated the question...it's almost there so thanks

Edwin Dalorzo · Accepted Answer · 2013-06-13 20:20:14Z

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.

Collectives™ on Stack Overflow

Count SQL statements in Hibernate

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related