Hi,
I've searched the forum but did not find any posts that could help me . Let me describe my problem:
- Hibernate 2.8
- An Oracle database (9iR2) with 30 schemas. For each schema a different oracle user is used . An oracle user can only use his schema.

At login time the web application finds the schema that the logged user must use. This schema will be used for all his CRUD operations.

How can this be done ? Should I use 30 hibernate.cfg.xml files ?(I doubt that ...but I have to ask).

I have tried to overwrite the hibernate.default_schema property like this

Configuration cfg = new Configuration().configure();
cfg.setProperty("hibernate.default_schema","SCHEMA_NAME");

but this doesn't seems to work. Is there a way to do this ?

Thanks

Ovidiu

This isn't correct - oracle schema and user are same things for oracle
Oracle user can use objects from another schema/user (you have to grant permission, but it is possible)

for example, user a access to table T from user b with
select * from b.t

you can create synonym for t for user a and then you needn't prefix t.b

User a can update,delete, insert (or create objects) in another schema/user if she/he have permissions

regards

In your question, if "oracle user" means "Oracle database user", and "logged user" means "application system user", then my understand of your question is:



Hibernate uses JDBC connections, but it is not required for Hibernate to create JDBC connections by himself.

For example, one can first get a JDBC connection with some user/password, then tell Hibernate to create a Session on that JDBC connection.

(And, the mapping does not care those lower-level things.)

In your question, if "oracle user" means "Oracle database user", and "logged user" means "application system user", then my understand of your question is:


In an application, for each logged application user, the application code decides to use which existed Oracle database user to connect to Oracle.
But how to tell Hibernate to use a specific Oracle database user?


Hibernate uses JDBC connections, but it is not required for Hibernate to create JDBC connections by himself.

For example, one can first get a JDBC connection with some user/password, then tell Hibernate to create a Session on that JDBC connection.


(And, the mapping does not care those lower-level things.)

I know that Hibernate can use an existing JDBC Connection.
This means that I'll need 30 connection pools - for each oracle database user a connection pool - from which I'll extract a JDBC connection that will be used by Hibernate.
Is this correct ?

Thanks,
Ovidiu

PS:
oracle user = oracle database user
logged user = web application user

Or is it possible to reuse a connection with different user/password ?

Hello,
you can use one connection for all users/schemas - you have schema parameter in all jdbc calls
connection user have to have permission for another user/schema (you can choose dba user)
regards

I have found a way to do it : using the c3p0 connecttion pooling.
I'm setting a datasource in Tomcat that uses c3p0 connection pooling, and then when I must decide which schema to use I'll obtain a connection using getConnection() for the default oracle database user or getConnection(oracleusername, password) for using another schema.
It's very important to use c3p0 since dbcp does not implement javax.sql.DataSource getConnection(String user,String password) method.