Blog

Spring Data JPA: How To Intercept SQL Query

  • Post last modified:February 9, 2024
  • Reading time:4 mins read

Intercepting SQL queries and writing custom logic is easy and powerful in Spring Data JPA/Hibernate

Subscribe to Java/Spring Newsletter for java and spring boot related blogs.

Introduction

  • Spring Data JPA is an Object Relational Mapping framework that works as middleware between Java applications and databases.
  • As part of this interaction, one of the core features is to convert entity CRUD operations into SQL and execute it against the database.
  • Now before executing these queries, we may have requirements to audit, debug, log, and potentially modify these queries before executing against the database.
  • In this article, we will learn how to intercept SQL queries and operate over them.

StatementInspector

  • StatementInspector is an interface that is provided under hibernate in order to inspect and modify SQL queries.
  • In order to intercept SQL query and operate on it we need to implement StatementInspector.
  • StatementInspector provides an inspect method that we need to override and custom logic.

public interface StatementInspector extends Serializable {
    String inspect(String var1);
}

We can add custom logic inside the inspect method and return SQL.

public class CustomStatementInspector implements StatementInspector {
    @Override
    public String inspect(String sql) {
          // add custom logic for sql before execution against SQL database.
      return sql;
    }
}

Some Use Cases

DeIdentify & Log SQL Query

  • The simplest use case would be to log the SQL query before executing it. We can capture this SQL query and send the logs to third-party services like Splunk or Logstash.
  • Also, we may have to inspect SQL query against any security attack such as injection, so we can add that logic here.
  • Additionally while logging SQL queries we may have to see if our SQL logs contain any PII/PCI information that may be prohibited to send third-party systems. 

If you want to know how to protect PII data with Java check here

  • In that case, we can add that logic here as well.
public class CustomStatementInspector implements StatementInspector {
    @Override
    public String inspect(String sql) {
     // Security check
     // Deidentify logs    
     // Push SQL Query to Splunk or Logstash
     return sql;
    }
}

Writing Unit Tests

  • We can write unit tests around the logic to an asset if the logic executes n number of queries.
  • This is useful when we want to be mindful of increasing the number of queries in the code hot path. 
  • We are adding all the inspected queries to the array list.
public class CustomStatementInspector implements StatementInspector {
    public static List<String> allExecutedQueries = new ArrayList<>();

    @Override
    public String inspect(String sql) {
        allExecutedQueries.add(sql);
        return sql;
    }
}

Logic class is a simple findAll call to the database. findAll returns a single SQL query.

public List<Customer> getAllCustomer(){
    return customerRepository.findAll();
}
  • In our test, we execute the getAllCustomer method and assert if that code path only returns one query.
  • In the future, if the number of queries increases this unit test will break and we can provide a custom message to the author about the reason and action.
@Test
void testGetAllCustomer(){
   customerLogic.getAllCustomer();
   assertEquals(
     CustomStatementInspector.allExecutedQueries.size(),
     1,
     "Number of queries in code path has changed"
   );
}

Conclusion

  • In this article, we learned about StatementInspector which provides support to intercept SQL queries before executing to the database.
  • This feature provides the possibility to write custom interceptors to modify SQL queries or perform a task that needs to be executed before running an SQL query against the database.

Before You Leave

Tags: , , , ,

Leave a Reply