[FIXED] Spring Boot + Hibernate Web Application: How to globally intercept native and managed entity queries?

Issue

Is it possible to place a hook at the low level of the database connection or data source and execute a query just before any other query is executed?

Hello.

I'd like to be able to intercept all database queries in hibernate and inject execute one simple query before the original query gets sent to the database: SET @SomeSessionVariable = 123346;.

Since hibernate uses a lifecycle for its managed entities, I find it a challenge to achieve what I need.

In my spring boot application, I'd like to audit data changes using triggers. I want to be able to associate a change with the currently logged in user in the spring application and the only way to do that is to send the id of that user to the database along with any queries, but just before the query gets executed so that the trigger can see the variable in the current connection session.

I'm using MySQL and it seems like there's no built in way of doing this. Postgresql does seem to support sending client contexts to the DBMS.

The application is quite huge to refactor to manually send the id.

Do you know any other place I could place a global hook in hibernate configuration to be able to intercept both native and managed queries?

Solution

If you're using spring-security in your application to authenticate users, you can utilize spring-data-jpa auditing system for your needs.

For example, with a Product entity it can look like this:

@Entity
@EntityListeners(AuditingEntityListener.class)
public class Product {
    
    // id and other fields
    
    @Column(name = "created_by")
    @CreatedBy
    private String createdBy;

    @Column(name = "modified_by")
    @LastModifiedBy
    private String modifiedBy;
    
    // getters, setters, etc.
}

Also you should put @EnableJpaAuditing annotation on any of configuration classes.

By doing this you will tell spring-data to automatically insert created_by and modified_by fields into the DB relation on save or update operations, using the name of the principal that is stored in SecurityContext's Authentication object and who called the save or update.

You can change this default behavior by implementing AuditorAware<T> interface

public class CustomAuditorAware implements AuditorAware<String> {
    @Override
    public Optional<String> getCurrentAuditor() {
        // retrieve name, id or anything else from your SecurityContext
    }
}

Then you just need to register this AuditorAware as a bean and point your application to it:

@EnableJpaAuditing(auditorAwareRef = "auditor")
@Configuration
public class ApplicationConfig {
    @Bean
    AuditorAware<String> auditor() {
        return new CustomAuditorAware();
    }
}

Note, that this auditing mechanism works only with entities, so it won't work with native queries. Also there are other ways to implement entity auditing - take a look at this article at Baeldung

If you want to modify native sql queries before they are executed you can utilize Hibernate's StatementInspector:

public class CustomStatementInspector implements StatementInspector {
    @Override
    public String inspect(String sql) {
        // check if query is modifying and change sql query 
    }
}

Then you should let Spring know you want to use this inspector, and there's an easy way to do this in spring-boot:

@Configuration
public class ApplicationConfig {
    @Bean
    public HibernatePropertiesCustomizer hibernateCustomizer() {
        return (properties) -> properties.put(AvailableSettings.STATEMENT_INSPECTOR, new CustomStatementInspector());
    }
}

Other ways of configuring StatementInspector can be found here: How I can configure StatementInspector in Hibernate?

Answered By - AndrewThomas
Answer Checked By - Willingham (JavaFixing Volunteer)