Introduction

In this article, you are going to learn how to automatically detect the N+1 query problem when using JPA and Hibernate using the Hypersistence Utils open-source project.

With Hibernate, you manage entity state transitions, which are then translated to SQL statements. The number of generated SQL statements is affected by the current fetching strategy, Criteria queries, or Collection mappings, and you might not always get what you expected. Ignoring SQL statements is risky, and it may eventually put a heavy toll on the overall application performance.

I’m a strong advocate of peer review, but that’s not “sine qua non” for detecting bad Hibernate usage. Subtle changes may affect the SQL statement count and pass unnoticed through the reviewing process. Not least, when it comes to “guessing” the JPA SQL statements, I feel like I can use any extra help. I’m for as much automation as possible, and that’s why I came up with a mechanism for enforcing the SQL statement count expectations.

First, we need a way to intercept all executed SQL statements. I researched this topic, and I was lucky to find this great datasource-proxy library.

Adding an automated validator

This safeguard is meant to run only during the testing phase, so I’ll add it to the Integration Testing Spring context exclusively. I’ve already talked about Spring bean aliasing, and this is the right time to make use of it.

@Bean
public DataSource dataSource(DataSource originalDataSource) {
    ChainListener listener = new ChainListener();
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    loggingListener.setQueryLogEntryCreator(new InlineQueryLogEntryCreator());
    listener.addListener(loggingListener);
    listener.addListener(new DataSourceQueryCountListener());
    return ProxyDataSourceBuilder
        .create(originalDataSource)
        .name("DS-Proxy")
        .listener(listener)
        .build();
}

The InlineQueryLogEntryCreator, as well as all the examples in this article, can be found in my High-Performance Java Persistence repository.

The new proxy data source decorates the already existing data source, intercepting all executed SQL statements. This library can log all SQL statements along with the actual parameter values, unlike the default Hibernate logging, which only prints a placeholder instead.

This is what the validator looks like:

public class SQLStatementCountValidator {

    private SQLStatementCountValidator() {}

    public static void reset() {
        QueryCountHolder.clear();
    }

    /**
     * Assert select statement count
     *
     * @param expectedCount expected select statement count
     */
    public static void assertSelectCount(int expectedCount) {
        StatementType.SELECT.validate(expectedCount);
    }

    /**
     * Assert insert statement count
     *
     * @param expectedCount expected insert statement count
     */
    public static void assertInsertCount(int expectedCount) {
        StatementType.INSERT.validate(expectedCount);
    }

    /**
     * Assert update statement count
     *
     * @param expectedCount expected update statement count
     */
    public static void assertUpdateCount(int expectedCount) {
        StatementType.UPDATE.validate(expectedCount);
    }

    /**
     * Assert delete statement count
     *
     * @param expectedCount expected delete statement count
     */
    public static void assertDeleteCount(int expectedCount) {
        StatementType.DELETE.validate(expectedCount);
    }

    /**
     * Assert the total statement count
     *
     * @param expectedCount expected total statement count
     */
    public static void assertTotalCount(int expectedCount) {
        StatementType.TOTAL.validate(expectedCount);
    }
}

This utility is part of my Hypersistence Utils project, and since it’s already available in Maven Central Repository, you can easily use it by just adding this dependency to your pom.xml:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-55</artifactId>
    <version>${hypersistence-utils.version}</version>
</dependency>

Let’s write a test for detecting the infamous N+1 select query problem.

For this, we will write two service methods, one of them being affected by the aforementioned issue:

@Override
@Transactional
public List<WarehouseProductInfo> findAllWithNPlusOne() {
    List<WarehouseProductInfo> warehouseProductInfos = entityManager.createQuery("""
        select wpi 
        from WarehouseProductInfo wpi
        """, WarehouseProductInfo.class)
    .getResultList();
	
    navigateWarehouseProductInfos(warehouseProductInfos);
	
    return warehouseProductInfos;
}

@Override
@Transactional
public List<WarehouseProductInfo> findAllWithFetch() {
    List<WarehouseProductInfo> warehouseProductInfos = entityManager.createQuery("""
        select wpi
        from WarehouseProductInfo wpi
        join fetch wpi.product p
        join fetch p.company
        """, WarehouseProductInfo.class)
    .getResultList();
	
    navigateWarehouseProductInfos(warehouseProductInfos);
    return warehouseProductInfos;
}

private void navigateWarehouseProductInfos(
    List<WarehouseProductInfo> warehouseProductInfos) {
    for(WarehouseProductInfo warehouseProductInfo : warehouseProductInfos) {
        warehouseProductInfo.getProduct();
    }
}

The unit test is rather simple to write since it follows the same coding style as any other JUnit assert mechanism.

try {
    SQLStatementCountValidator.reset();
    warehouseProductInfoService.findAllWithNPlusOne();
    assertSelectCount(1);
} catch (SQLStatementCountMismatchException e) {
    assertEquals(3, e.getRecorded());
}

SQLStatementCountValidator.reset();
warehouseProductInfoService.findAllWithFetch();
assertSelectCount(1);

Our validator works for all SQL statement types, so let’s check how many SQL INSERTs are being executed by the following service method:

@Override
@Transactional
public WarehouseProductInfo newWarehouseProductInfo() {

    LOGGER.info("newWarehouseProductInfo");

    Company company = entityManager.createQuery("from Company", Company.class)
    .getResultList()
    .get(0);

    Product product3 = new Product("phoneCode");
    product3.setName("Phone");
    product3.setCompany(company);

    WarehouseProductInfo warehouseProductInfo3 = new WarehouseProductInfo();
    warehouseProductInfo3.setQuantity(19);
    product3.addWarehouse(warehouseProductInfo3);

    entityManager.persist(product3);
    return warehouseProductInfo3;
}

And the validator looks like:

SQLStatementCountValidator.reset();

warehouseProductInfoService.newWarehouseProductInfo();

assertSelectCount(1);
assertInsertCount(2);

Let’s check the test logs to convince ourselves of its effectiveness:

-- newWarehouseProductInfo

SELECT 
	c.id as id1_6_, 
	c.name as name2_6_ 
FROM 
	Company c

INSERT INTO WarehouseProductInfo 
	(id, quantity) 
VALUES 
	(default, 19)
	
INSERT INTO Product 
	(id, code, company_id, importer_id, name, version) 
VALUES 
	(default, 'phoneCode', 1, -5, 'Phone', 0)

I'm running an online workshop on the 11th of October about High-Performance SQL.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Code reviewing is a fine technique, but it’s not enough for a large-scale development project. That’s why automatic checking is of paramount importance. Once the test is written, you are assured that no future change can break your assumptions.

Insert details about how the information is going to be processed

DOWNLOAD NOW