I would like to use ONE session factory, but change the default schema used to automatically prefix tables on a PER-SESSION basis.

Effectively, this would be like doing
ALTER SESSION SET CURRENT_SCHEMA=<some-schema>
on Oracle.

The reason for this desired functionality is that the system I am using requires that the same database tables exist in multiple schemas of a single DB instance. I only want one set of mapping files (unprefixed, no schema prefix), that can be used by one session factory, but add the prefixes on a per-session basis based on a schema selected by the user through the application's UI.

Is it possible to do this? I'm having no luck with the API or google. The best I can do is to create one hibernate session factory per schema. The problem is that I can not use such a mechanism within Spring.

TIA,

-- Stephen

I think this is not even the problem of hibernate itself. It would be simpler if your connection pool served a properly setup connection (with default schema set).

If you'd like to try this approach there is a silmilar solution in spring for a connection pool that serves connections that are authenticated with different credentials. This approach uses ThreadLocals to store username/password.

see http://www.springframework.org/docs/api/org/springframework/jdbc/datasource/UserCredentialsDataSourceAdapter.html
especially:

http://www.springframework.org/docs/api/org/springframework/jdbc/datasource/UserCredentialsDataSourceAdapter.html#setCredentialsForCurrentThread(java.lang.String,%20java.lang.String)

you could implement similar adapter that would call a target datasource and than set a proper schema using a simple sql statement.

did that help?