Asserting the SQL statement count generated by Hibernate JPA via a Spring 4 MVC example

Before reading this post is strongly recommended to read How to assert the SQL statement count generated by Hibernate by Vlad Mihalcea.

The steps needed to assert the SQL statement count generated by Hibernate are:

1. Configure the DataSource-Proxy (this is needed because it will proxy your data source and can obtain important info such as the bind parameters values and number of executed SQLs).
2. Add the countQuery() when you build the proxy in order to instruct DataSource-Proxy to create a DataSourceQueryCountListener. This provides metrics information per HTTP request such as datasource name, number of database call, total query execution time and number of queries by type: 

@Bean

public DataSource dataSource() {

 SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();

 loggingListener.setQueryLogEntryCreator(new DefaultQueryLogEntryCreator());

 return ProxyDataSourceBuilder

        .create(actualDataSource())

        .name(DATA_SOURCE_PROXY_NAME)

        .listener(loggingListener)

        .countQuery()

        .build();

}

3. Use the QueryCount directly, or even better use the db-util provided by Vlad Mihalcea. Among other things, here we have access to an out-of-the-box automated validator named, SQLStatementCountValidator. This validator comes with the following static assertions: assertSelectCount(), assertInsertCount(), assertUpdateCount() and assertDeleteCount(). Simply place in your POM the following dependency:

<dependency>

 <groupId>com.vladmihalcea</groupId>

 <artifactId>db-util</artifactId>

 <version>0.0.1</version>

</dependency>

4. In order to use this validator simply reset the QueryCount, execute the SQL statements and apply the proper assertions. Most likely, you will do this from an unit test, but just a quick sample we did it as below:

@Override  
@Transactional
public void storeProducts() {
       
 Product p1 = new Product();
 p1.setIdproducts(1);
 p1.setName("TV");
 p1.setCode("001");

 Product p2 = new Product();
 p2.setIdproducts(2);
 p2.setName("Microphone");
 p2.setCode("002");

 Product p3 = new Product();
 p3.setIdproducts(3);
 p3.setName("Phone");
 p3.setCode("003");
        
 SQLStatementCountValidator.reset();
      
 productRepository.save(p1);
 productRepository.save(p2);
 productRepository.save(p3);
     
 assertInsertCount(1);

}

If the number of expected SQLs differs from the number of executed SQLs then the SQLStatementCountValidator will throw an excpetion of type SQLFooCountMismatchException where Foo is one of Select, Insert, Update, Delete depending on the SQL type. For example, if we test with:

assertInsertCount(1);

then the message will be as:

com.vladmihalcea.sql.exception.SQLInsertCountMismatchException:

Expected 1 statements but recorded 0 instead!  

NOTE: This was just a proof of concept being practically a pretty dummy example. In reality, you can use the assertion of the SQL statement count generated by Hibernate to see what happens behind fetching and finding the N+1 issues as the example created by Vlad Mihalcea here.

The complete application based on Hibernate JPA and Spring 4 MVC can be found here. The example should failed because we have inserted three records and asserted for 1.