How can I dynamically select the database schema used? I know I can
hardcode the schema name into the mapping configuration. Furthermore, if I use plain SQL, I can use the dot-notation (e.g., select * from myschema.mytable ...). However, what I need is to do the latter with the hibernate-mapped data objects, e.g.:

"SELECT p FROM myschema.com.mycom.myapp.data.Person p"

But it does not work; it says "unknown mapping myschema.com.mycom.myapp.data.Person", so it assumes the myschema is part of the package name.

The reason why I need this kind of functionality is because the same query is supposed to perform a JOIN between two tables in different schemas. An example could be:

"SELECT p FROM MYSCHEMA.com.mycom.myapp.data.Person p
WHERE p.id IN (SELECT o.ID FROM OTHERSCHEMA.com.mycom.myapp.data.Person o)"

or equivalent. You get the idea. How to accomplish this?

Thank you.

If class A belongs to schema ASCHEMA and class B belongs to ANOTHERSCHEMA you can specify these in the schema attribute of the class tag of classes A and B. Thereafter whenever you fire queries on A or B it will appropriately add the schema name to the generated query.

Is this what you were looking for.

If you need to change the schema on the fly have a look at the ClassMappings property on the Configuration object. This list holds a object-representation of the information read from the config files. You can manipulate this, including the schema attribute, right after the configuration has been read but before you build your session factory. Once you session factory has been build, changes made to the mapping meta data have no effect!

We change the schema on the fly like this in order to accomodate different environments/contexts. Databases are distributed differently on DEV, QA and PROD for example. We are also able to point to test database instances instead of the real thing by using this approach. It is a nice alternative to having to maintain multiple versions of the mapping files I think.

If you move on in this direction beware that you'll need to change :

Hibernate.Mapping.Property.Value.Table.Schema and also Hibernate.Mapping.Property.Value.CollectionTable.Schema.

/Chris