Hibernate bug using Oracle?

Question

Hello,

I've got the problem, that I use a property in the persistence.xml which forces Hibernate to look only for tables in the given schema.

<property name="hibernate.default_schema" value="FOO"/>

Because we are using now 4 different schemas the actual solution is to generate 4 war files with a modified persistence.xml.

That not very elegant.

Does anybody know, how I can configure the schema with a property or by manipulation the JDBC connection string?

I'm using Oracle 10g, 10_2_3 Patch.

Thanks a lot.

Answer 1

You could create four different users on the oracle database for the four different applications, the JDBC connection would include the user.

The for the user, you can create synonyms and permissions for the tables.

E.g.

create or replace synonym USER1.tablename FOR SCHEMA1.tablename;  
create or replace synonym USER2.tablename FOR SCHEMA1.tablename;  
create or replace synonym USER3.tablename FOR SCHEMA2.tablename;  

And when you are accessing the tables from hibernate, just leave the schema off. When logged in as USER1, it'll use SCHEMA1, etc.

That way you don't have to create four different WAR files with four different persistence.xml files. Just deploy the app four times with different jdbc connections.

Hope that helps.

Answer 2

See this - https://www.hibernate.org/429.html

Answer 3

If you don't want to generate four different WARs then put this property in a hibernate.properties file and put that file on the class path (but outside the webapp) for each webapp.

Answer 4

I created a method called deduceSchema that I run when I'm setting up the SessionFactory. It opens a jdbc connection using the data source (because you don't have a Hibernate session yet) and queries "select user from dual" to get the logged in user. This will be accurate if the user you log in as also owns the tables. If not, I use a jndi environment variable to override.

Once I have the schema, I modify the Hibernate configuration to set it for each table although this is only necessary if the logged in user is different than the schema:

for (Iterator iter = configuration.getTableMappings(); iter.hasNext();) {
  Table table = (Table) iter.next();
  table.setSchema(schema);
}