Hi,

I'm looking for a way to alter JDBC connections before they are used by hibernate/entity manager. Specifically I want to perform few "alter session" commands to set up the NLS environment.
(AFAIK I can't do this with the connection string.)

Thanks in advance,
Genady

Depending on when those ALTER statements have to be issued, you might be able to get away with doing this in an Interceptor:

http://www.hibernate.org/hib_docs/v3/ap ... eptor.html

Otherwise I think you'll have to write a ConnectionProvider (you can probably extend DataSourceConnectionProvider). You could touch the Connection before it's handed off to Session.

-Chris

There are two ways to go about it.
1) Subclass the datasource. It's little tricky to do if you want to do it in efficient manner by executing alter session on per physical connection instead of logical connection.
2) Make use of Oracle logon_trigger. This will work very well if your parameter is a constant or computed using db queries. Please consult your DBA before doing it. There are few things to remember,
i) Any error raise by logon_trigger doesn't stop DBAs from logging on (Oracle 8.1.6 and above have this feature). So be careful if you are using a database version from museum.
ii) They are not executed for internal or SYSDBA/SYSOPER.

Thanks everybody!

Chris - Can I implement your suggestion in some way that does not use hibernate APIs?

Genady

Neither interface (ConnectionProvider or Interceptor) is standardized in JPA. However in either case, the bulk of your code is isolated because the implementation you provide is one class and it is specified in configuration; your code never knows about it. This is acceptable in most cases to folks who don't want Hibernate API's in their app.

-Chris

I've tried the ConnectionProvider solution. I've subclassed DatasourceConnectionProvider, added
the hibernate.connection.datasource and hibernate.connection.provider_class properties to the xmls and
now I can issue an "alter session" statements whenever a new connection is returned.

This solution is nice since it reuses the datasource defined elsewhere. The downside is that I'd rather issue these statements on each new physical connection. Subclassing DriverManagerConnectionProvider seems like a good option, but I'm not sure how it will interact with the datasource resource we have.

How can I subclass a standard datasource and tell jboss/hibernate to use it (before resorting to the logon scripts solution).

Thank you all!
Genady

The opening of the physical connection is out of Hibernate's hands because of the DataSource abstraction. You might consider manually constructing a connection pool using Apache DBCP or the vendor of your choice. It's not so hard with BasicDataSource:

http://jakarta.apache.org/commons/dbcp/ ... ource.html

-Chris