Hibernate version:3.0.5

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: PostgreSQL 7.4.7

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Here is my issue. I have one database which is composed of n schema (each customer got one schema). Each time we have a new customer, we had a new schema to the database.
All schemas got the same DML (same table definition and all the other stuff).

Depending on which client connect to the web application, I want to connect to the appropriate schema. (equivalent to postgres SQL command : SET search_path TO customer1).

I want to be able to add new customer dinamically to the application, so no repackaging.

I want to share the same connection pool for all the customers.

I had a look at the documentation and the forums and did not find much informations. I tried to execute the "SET search_path TO customer1" directly into hibernate but it did not work using Query or SQLQuery.

Am I trying to do something which should not be done? Is there a way to execute this set command into hibernate?

http://forum.hibernate.org/viewtopic.ph ... ple+schema
http://forum.hibernate.org/viewtopic.ph ... ple+schema


Thanks for your help.
Damien

session.connection().createStatement("SET search_path TO customer1").execute();

thanks a lot for your answer.
here is what I have done.
The first request using JDBC retrieve the right informations but retrieving the information with hibernate did retrieve the information from the 'public' schema instead of the 'customer1' schema.
I guess that is probably due to something I misunderstand in the way hibernate works.
Why is hibernate still requesting the public schema? Does it has something to do with the Transaction?

Session session = null;
Transaction tx = null;
Configuration configuration = new Configuration();
SessionFactory sessionfactory = configuration.configure()
.buildSessionFactory();
session = sessionfactory.openSession();
Connection connection = session.connection();
tx = session.beginTransaction();
connection.prepareStatement("SET search_path TO public").execute();
PreparedStatement stnt = connection
.prepareStatement("select * from okorganisme;");
ResultSet rs = stnt.executeQuery();
while (rs.next()) {
logger.debug("result : " + rs.getString(1));
}
Criteria crit2 = session.createCriteria(Okorganisme.class);
List varorganisme = crit2.list();
for (Iterator it = varorganisme.iterator(); it.hasNext();) {
Okorganisme okorganisme = (Okorganisme) it.next();
logger.debug("okvarproperty id ["
+ okorganisme.getIdorganisme() + "] idokrs["
+ okorganisme.getIdokrs() + "]");
}

Hibernate uses public shema because you set search_path to public in your code.

"connection.prepareStatement("SET search_path TO public").execute();"

Why do you use set to public shema and expect result from different shema ?

you are right, my fault. I made a mistake copying my code.
It was a second test I made to test the result of the JDBC select.
Sorry for this.

The right code was indeed :
connection.prepareStatement("SET search_path TO customer1").execute();

here are the resulting logs:

...
result : sdfqsdfsd
...
okorganisme id [essai2] idokrs[essai2]
okorganisme id [essai] idokrs[essai]

In the database,

I indeed got one record in okorganisme in the 'customer1' schema and 2 records in the 'public' schema.

here is something new I found into hibernate logs :

19:04:34,218 DEBUG SQL:324 - select this_.idorganisme as idorgani1_0_, this_.idokrs as idokrs20_0_ from public.okorganisme this_

Apparently, hibernate forces the request to the public schema. Does someone know if this can be disabled?

for people interested by this problem, I guess I found my error.

i did replace :
<class name="com.ok.docflow.database.Okorganisme" table="okorganisme" schema="public" lazy="false">

by :
<class name="com.ok.docflow.database.Okorganisme" table="okorganisme" lazy="false">

and now the generated hibernate SQL looks like this :

select this_.idorganisme as idorgani1_0_, this_.idokrs as idokrs20_0_ from okorganisme this_

which does not include any more schema.
I did not read carefully enough the xml definition of hibernate files and as I generated them directly from the database, I did not notice it included a schema specific field.

So now I am able to question the right schema into my database. I will do more tests to verify I can switch from one schema to another.
I am wondering if the hibernate cache system will work correctly with this tweak done on the connection. It is maybe caching data based on primary key.

Create session factory per shema to isolate factory level caches or do not use global cache. It is related to factory level cache only, session cache will work without problems, just do not reuse session (open a new session before to change shema)

balakuis, thanks for answering again.

I guess you are right but I would like to avoid creating one session factory per client as I guess it will create n connection pools. As I might have hundreds of customer, I would prefer avoid having 200 or 300 connections to the databse.
As I am new to hibernate, I might misunderstand how connection pooling is working.
disabling the cache system would be an acceptable issue for me. I did a test but I guess I only disabled the second level cache.
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.cache.use_query_cache">false</property>
I guess the good answer is RTFM as I will probably find another cache to disable.

And doing so, my unit test is not working.
I connect to schema customer1
select using JDBC
select using hibernate
commit transaction
open new transaction
connect to schema public
select using JDBC
select using hibernate

the first 2 selects are ok. the second JDBC select is ok but the second hibernate select return the list from schema customer1 instead of schema public.

in the hibernate logs, I got :
select this_.idorganisme as idorgani1_0_, this_.idokrs as idokrs20_0_ from okorganisme this_
select this_.idorganisme as idorgani1_0_, this_.idokrs as idokrs20_0_ from okorganisme this_

that is why I guess a cache is acting here.

I even try with closing session and reopening one new.

here is my test :

Log logger = new TestLogger();
logger.debug("Executing Hibernate Test");
Session session = null;
Transaction tx = null;
try {
SessionFactory sessionfactory = new Configuration().configure().buildSessionFactory();
session = sessionfactory.openSession();
Connection connection = session.connection();
tx = session.beginTransaction();
connection.prepareStatement("SET LOCAL search_path TO customer1").execute();
PreparedStatement stnt = connection.prepareStatement("select * from okorganisme;");
ResultSet rs = stnt.executeQuery();
while (rs.next()) {
logger.debug("JDBC okorganisme id [" + rs.getString(1) + "] idokrs[" + rs.getString(2) + "]");
}
connection.prepareStatement("SET LOCAL search_path TO customer1").execute();
stnt = connection.prepareStatement("SHOW search_path");
rs = stnt.executeQuery();
while (rs.next()) {
logger.debug("SHOW search_path [" + rs.getString(1) + "]");
}
Criteria crit2 = session.createCriteria(Okorganisme.class);
for (Iterator it = crit2.list().iterator(); it.hasNext();) {
Okorganisme okorganisme = (Okorganisme) it.next();
logger.debug("HIBE okorganisme id [" + okorganisme.getIdorganisme() + "] idokrs["
+ okorganisme.getIdokrs() + "]");
}
tx.commit();
session.close();
session = null;
session = sessionfactory.openSession();
connection = session.connection();
tx = session.beginTransaction();
connection.prepareStatement("SET LOCAL search_path TO public").execute();
stnt = connection.prepareStatement("select * from okorganisme;");
rs = stnt.executeQuery();
while (rs.next()) {
logger.debug("JDBC okorganisme id [" + rs.getString(1) + "] idokrs[" + rs.getString(2) + "]");
}
connection.prepareStatement("SET LOCAL search_path TO customer1").execute();
stnt = connection.prepareStatement("SHOW search_path");
rs = stnt.executeQuery();
while (rs.next()) {
logger.debug("SHOW search_path [" + rs.getString(1) + "]");
}
crit2 = session.createCriteria(Okorganisme.class);
for (Iterator it = crit2.list().iterator(); it.hasNext();) {
Okorganisme okorganisme = (Okorganisme) it.next();
logger.debug("HIBE okorganisme id [" + okorganisme.getIdorganisme() + "] idokrs["
+ okorganisme.getIdokrs() + "]");
}
tx.commit();
} catch (Throwable ex) {
logger.debug("Throwable : " + ex.getMessage());
tx.rollback();
assertTrue(ex.getMessage(), false);
}
session.close();
assertTrue(true);

sorry for this post I found the answer. the code is wrong as I was setting customer1 before hibernate select.
so it is working fine in fact.
thanks balukias