Force Hibernate to use own connection

Question

In our application we use Oracle and have some audit-relevant tables.

Every data changing action on them (but only on them!) must be accompanied by some audit entries in another tables. This is done by setting connection properties, so triggers can automatically update them. Please refrain from discussion if this is a good way or not. It is defined like this by the customer and we have to stay with it.

Unfortunately those properties are user-dependent, so we cannot set them generally for every connection, but only for user who is doing update. Also, those properties changes during the day / even sometimes for every request, but unfortunately those properties are not changeable in Oracle after connection is established. Usage of user pool is therefore neither possible.

Our current idea is not to use Hibernate connection pool here, but create own connections. After operation for those tables is requested by user, the backend creates a new connection, fills properties as required, executes user updates closes the connection.

Current code is:

Properties props = new Properties();
...setproperties...
Connection con = DriverManager.getConnection(connectionString, props);
PreparedStatement stmt = con.prepareStatement(userSQL);
con.executeUpdate();
... clean up and close connection

However, we can only execute now regular (prepared) statements, but cannot use Hibernate Repository mechanics anymore, what is not nice.

Is there any way to provide this connection to Hibernate, so we can force a repository.save() exactly on this particular connection we created? However, this connection cannot stay in Hibernate Pool for the further, it must be immediately again closed. So we would need a mechanism like

create a connection
hibernate uses it for repository update
connection is forcibly closed.

Is it somehow possible?

Yahor Barkouski · Accepted Answer · 2023-07-12 13:41:15Z

0

You need to provide a custom connection to Hibernate's session as well:

Session session = sessionFactory.openSession();
session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        // access to the connection here
        try (PreparedStatement stmt = connection.prepareStatement(userSQL)) {
            // use the statement
            stmt.executeUpdate();
            // set other properties and do whatever you want
        }
    }
});
session.close();

But please note that this way bypasses Hibernate's caching and object management mechanisms, so should only be used for specific use cases where you really need a fine-grained control

answered Jul 12, 2023 at 13:41

Yahor Barkouski

1,6311 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Force Hibernate to use own connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related