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.