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?

Solution 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.

Solution 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.

Comments

[Chris R]

Do you think your solution could be adapted to work when multiple schemas are accessed in the course of a transaction? Obviously it could work if there were two schemas -- one fixed, and the other dependent on the situation. I'll be contacting you, however. Thanks!

[paulmurray]

Well, if your setup is this weird, another way to do it is to create oracle table aliases for each of the user names that you log on as. It's a lot of management, of course. Or you can combine the solutions = use CURRENT_SCHEMA and also have schemas with table aliases pointng to other schemas.

[Robert Simmons]

One of the perils of the internet I suppose. I've updated the links as best I can.