Hooking into start of JDBC transaction

Question

I have a Spring Boot webapp connected to a Postgres 9.6 database.
I use Spring's JdbcTemplate for executing SQL statements. Each table in my database has triggers for INSERT, CREATE and DELETE statments. These triggers copy the affected rows into a history table.

I want the triggers to also save the application user ID of the user who made the change.

According to https://stackoverflow.com/a/13172964/2591231 I can achieve my goal by having the application insert the current user id into a temporary table at the start of each transaction and having the triggers read from the temporary table.
A similar method, mentioned in several other places, is executing: SET LOCAL application_name = "my_application_user", then reading application_name inside the triggers. Again, this has to be done at the start of each transaction.

I'm looking for way, which is orthogonal to business code (I don't want each DAO to explicitly set user ID), to hook into the start of each transaction in order to run a specific SQL statement before any other statement in the same transaction.

I need this to work for both implicit transactions (single invocations of JdbcTemplate) and transactions demarcated declaratively with Spring's @Transactional annotation.

Answer 1

First of all, JdbcTemplate does not provide transaction support out-of-the-box (see here). So, in order to intercept all @Transaction annotated code AND every call to JdbcTemplate, this could be done at DataSource level, as commented earlier by Serge Bogatyrev.

I have a Spring Web project where I tested this approach. I defined a replacement DataSource @Bean called MyDataSource that extends BasicDataSource, and overwrites its getConnection() method so that it creates the temp table and insert the user_id before returning the connection.

It worked for @Transaction calls and pure JdbcTemplate calls.

If you want to strictly tie this temp table update at the start of each transaction, do this same strategy for defining the PlatformTransactionManager @Bean. You only need to overwrite the doBegin() method. And don't forget to annotate with @Transaction all methods calling JdbcTemplate.

PS1: Make sure to call DROP TABLE IF EXISTS temp_table_name prior creating the temp table, in order to replace the DISCARD ALL on connection returning to pool, as mentioned here.

PS2: This whole solution of creating a temp table doesn't smell well. I wouldn't implement it myself. I would prefer to take a deep breath and add created_by and updated_by columns to all my tables.

Answer 2

You can take advantage of Spring AOP for setting up the user. The aspect will make a call to the database to set up the application user.

In my example, a stored procedure is used to set up the application user responsible for creating, modifying, or deleting a record . You can customize it according to your requirements. Here is the example aspect which retrieves the user from the HTTP request and then makes a call to the stored procedure,

@Component
@Aspect
@Slf4j
public class SetUserAspect {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public SetUserAspect(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Before("execution(* com.basaki.example.service.BookService.*(..) )")
    public void setUser(JoinPoint jp) {
        log.info("In class: " + jp.getSignature().getDeclaringTypeName() +
                " - before method: " + jp.getSignature().getName());

        HttpServletRequest request =
                ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes()).getRequest();
        if (request != null) {
            String user = request.getHeader("user");
            if (user != null) {
                log.info("Setting user " + user);
                SimpleJdbcCall
                        jdbcCall = new SimpleJdbcCall(jdbcTemplate)
                        .withSchemaName("example_book_schema")
                        .withFunctionName("set_user");
                SqlParameterSource
                        in =
                        new MapSqlParameterSource().addValue("audit_user",
                                user);
                jdbcCall.executeFunction(String.class, in);
            }
        }
    }
}

All the CRUD operations are performed in BookService, essentially a DAO.

Here is the stored procedure used for setting up the user,

CREATE OR REPLACE FUNCTION example_book_schema.set_user(
    audit_user TEXT
) RETURNS BOOLEAN STABLE LANGUAGE SQL AS $$
    SELECT set_config('book.audit_user', audit_user, true);
    SELECT TRUE;

Restricting Pointcuts to Only Transactional Methods

You can restrict the points cuts to only transactional methods in BookService by adding an additional clause in the Before advice.

@Before("execution(* com.basaki.example.service.BookService.*(..) ) " +
            "&& @annotation(annotation)")
    public void setUser(final JoinPoint jp, final Transactional annotation) {
    ...
}

Answer 3

You can use @EntityListeners to listen change of entity in application context, then collect whatever information (entity value, authentication user, etc...) and then insert to your history table. Example you can follow here: http://www.baeldung.com/database-auditing-jpa

Answer 4

You can create view, add user id column, and use your triggers to deal with updates. So that is yet another way to code it at DB side. That way you are supposed to pass it every time, so no other changes are needed.

Going to Java/Spring side.

A bit oldish style: TransactionTemplate - that way you have full control, but your dao needs more code, since transaction management needs to be done there.

Other option is to create proxy on org.springframework.jdbc.datasource.DataSourceTransactionManager and do your job at doBegin, then your proxy needs to be passed to transaction manager. And that is the way to go for me.