Our application uses Hibernate for ORM, and stores data in several schemas, accessing them with a user whose grants are customized for the application.
The schema names are determined at runtime based on data; it's not feasible to include their names in the entity mapping documents. This means that I need a way to tell Hibernate to use a specific schema name when performing lookups. Is there a way to do this?
Here's a page that lists some ways you can manage multiple schemas in Hibernate. I'd probably go with implementing your own connection provider. You'll probably want to disable caching as well.
We ran into this problem at work. I fixed it, as Robert suggests, by creating a connection provider (ie, an implementation of DataSource), called ""OracleSchemaRemappingDataSource" and using spring to do the plumbing.
Basically, this datasource implements getConnection(). The implementation of that method works by getting a connection from some other data source by spring injection, which it assumes to be an oracle connection, and then executing
ALTER SESSION SET CURRENT_SCHEMA = 'someotherschema'
and them passing that connection back.
All of the hibernate config is careful to use names without specifying schemas for them.
Also: with this, you don't want to disable caching - allow hibernate to manage connections as normal, as we are not doing any magic within the app such as using different connections on a per-user basis.
Works great. The app is here. If you want the code, contact me at work, but there's not much to it more than what I have just described.