How can I set the schema name used by hibernate entities at query time?

Question

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?

Answer 1

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.

Answer 2

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.