EclipseLink / JPA: How to programmatically get the number of SQL queries that have been performed

Question

I'm using JPA, by way of EclipseLink. In my unit tests, I'd like to test how many SQL queries were performed during an operation. That way, if a later modification causes the query count to explode (if lazy loading is triggered, for instance), the unit test will flag it as potentially needing optimization.

I'm striking out in finding the correct API to do this. A pure-JPA solution would be ideal, but I'm fine with using EclipseLink-specific APIs in my unit tests. I looked at the EclipseLink profiler, but it doesn't seem to give me a way to count the number of SQL queries.

Thanks in advance for the help!

Answer 1

I didn't find a proper tool for such validation and created my own. It is called sniffy and available under MIT license.

You can assert the number of generated queries like shown below:

// Integrate Sniffy to your test using @Rule annotation and a QueryCounter field
@Rule
public final QueryCounter queryCounter = new QueryCounter();

// Now just add @Expectation or @Expectations annotations to define number of queries allowed for given method
@Test
@Expectation(1)
public void testJUnitIntegration() throws SQLException {
    // Just add sniffer: in front of your JDBC connection URL in order to enable sniffer
    final Connection connection = DriverManager.getConnection("sniffer:jdbc:h2:mem:", "sa", "sa");
    // Do not make any changes in your code - just add the @Rule QueryCounter and put annotations on your test method
    connection.createStatement().execute("SELECT 1 FROM DUAL");
}

More information about integration with JUnit available in project wiki

Answer 2

Most databases have built-in statistics, you might consider using those.

E.g. MySQL has SHOW STATUS LIKE 'Queries' command which dumps total amount of queries run.