We want to run some custom SQL on each connection before Hibernate uses the connection.

The SQL only needs to be run once for each connection, it doesn't matter if the connection is involved in lots of transactions (ie. used in a connection pool), as long as the SQL got executed before the first transaction was executed.

Currently, I've subclassed the ProxoolConnectionProvider and overridden the getConnection() method to execute the SQL we need executed before returning the new connection.

There are two problems with this approach:
1. The sql ends up being executed on the same connection multiple times, it's not thta big a deal since the SQL is just setting up the environment and doesn't take too long to execute, but it does take time and it clutters up the SQL log.

2. This one's not that big a deal, just annoying - It only works as long as we are using Proxool for our connection pool, if we decide to switch to a difference connection pool, we'll have to write another custom ConnectionProvider.

Is there any better way to do this? What we're doing now is functional, it works, I'm just wondering if there's a different way to do this?

The version of Hibernate 2.1 beta 6.

I think this is the best way