Spring Boot Hibernate run query when connection/session is created

Question

I have Spring Boot application with implemented RESTControllers. In the RESTController I specified /clients/ endpoint (PUT method) to update client resource.

In my database (PostgreSQL) I have implemented several triggers on the client table to record history of the changes to the client_history table. The problem is that I need to insert author (user_id) of the changes to the client_history table.

This question (https://stackoverflow.com/a/13172964) led me to use GUC inside my trigger function.
select current_setting('custom.application_user_id'))

Now I need to execute sql query set custom.application_user_id = {user_id}; to set user_id when Hibernate opens new session. I have tried to use Hibernate Interceptors to do this, but this solution does not work.

Could someone give me an example how to subscribe to the event when session is created?

P.S. Also I have silly question: Does every HTTP requests received by the application creates (obtains from connection pool) new connection?

So the use case is to have auditing informations? If so, maybe you could use jpa-auditing, hibernate-envers or similiar. "Does every HTTP requests received by the application creates (obtains from connection pool) new connection?" Every transaction get's a connection from a pool and it get's released at the end. — judomu, Sep 30 '19 at 16:58
@stripfire thanks for your reply. Yes, it is done mostly for auditing. I prefer to keep this functionality on the database size because it reduces Hibernate roundtrips. And also it allows to log changes in case someone tries to update data directly in the database. — Konstantin Chsherbakov, Sep 30 '19 at 18:19

score 2 · Answer 1 · answered Feb 20 '20 at 19:46

After research, I found this trick. For me i have two datasource in my project (ds1, ds2)

spring:
  datasource:
    ds1:
      url: jdbc:oracle:thin:${DATABASE_USERNAME:E4A5}/${DATABASE_PASSWORD:EA5}@${DATABASE_HOSTNAME:local4host}:${DATABASE_PORT:49161}:${DATABASE_SERVICE:EE}
      driver-class-name: oracle.jdbc.OracleDriver
    ds2:
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
      url: jdbc:sqlserver://${DATABASE_EF_HOSTNAME:localhost};databaseName=${DATABASE_EF_NAME:test}
      username: ${DATABASE_EF_USERNAME:test}
      password: ${DATABASE_EF_PASSWORD:test}

And to run query when connection/session is created in ds1 I did this in my class annotated with @Configuration:

@Primary
@Bean
@ConfigurationProperties("spring.datasource.ds1")
public DataSourceProperties ds1DataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("spring.datasource.ds1.configuration")
public DataSource ds1DataSource() {
    HikariDataSource hikariDataSource = ds1DataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
    hikariDataSource.setConnectionInitSql("ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY' " +
            "NLS_SORT='Binary' " +
            "NLS_NUMERIC_CHARACTERS='.,' " +
            "NLS_LANGUAGE='FRENCH'");
    return hikariDataSource;
}

I googled for an hour trying to figure out how to run some sql after the connection and was so frustrated after getting good results until finding this answer. Your solution is simple and solid, many thanks. — shaune, Aug 12 '22 at 23:38

Spring Boot Hibernate run query when connection/session is created

1 Answers1