We're using Sybase Adaptive Server 12.5, and have development, testing and production databases on this server. I want to set up an application that will use any of these databases on an ad-hoc basis, and didn't see anything documented. I'm looking for any advice anyone can offer. The same tables exist in every database, and the mapping documents are all the same.

I saw that a SessionFactory can be associated with one and only one database, is this correct? If so, then if my application can use any of the three databases, I'll need to instantiate three SessionFactories, one for each database, and know which session factory to do sessionFactory.openSession() with? Is this the right approach?

Assuming all of the above is correct, what's the correct way to associate a SessionFactory with a particular database, chosen when the SessionFactory is instantiated? I've only gotten it to work when the connection.url in hibernate.cfg.xml contains the name of the database.

Sorry for the newbie questions, but I didn't see anything documented that describes what I want to do. Thanks for any advice.

Hibernate version:

3.0.5

Mapping documents:

I think my question has more to do with configuration, so here's my hibernate.cfg.xml:

<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="show_sql">false</property>
<property name="connection.driver_class">com.sybase.jdbc2.jdbc.SybDriver</property>
<property name="connection.url">jdbc:sybase:Tds:*my url*:4100/dev</property>
<property name="dialect">org.hibernate.dialect.SybaseDialect</property>
<property name="connection.username">*my user id*</property>
<property name="connection.password">*my password*</property>

<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">300</property>
<property name="c3p0.max_statements">50</property>
<property name="c3p0.idle_test_period">3000</property>
</session-factory>
</hibernate-configuration>

Note that "dev" in the connection URL is the name of the development database.

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

N/A -- I think I'm concerned with the code that comes prior to opening a session.

Full stack trace of any exception that occurs:

N/A

Name and version of the database you are using:

Sybase ASE 12.5.1.

The generated SQL (show_sql=true):

N/A

Debug level Hibernate log excerpt:

N/A

It is possible to programmatically tweak Hibernate configuration before creating session factory. For example

configuration = new Configuration();
configuration
.configure( getConfigurationName() ).
setProperty( "jndi name for data source" , Productionname);
sessionFactoryProduction = configuration.buildSessionFactory();


setProperty( "jndi name for data source" , trest_name);
sessionFactoryTest = configuration.buildSessionFactory();


etc.

Thanks for the reply. I'm not using JNDI -- my cfg.xml file uses the "connection.url" property. I tried doing something like you mentioned (remove that property from the cfg.xml file, set it programmatically), but Hibernate always crashed when loading it (it crashed on the C3P0 configuration).

I got around the problem by having three complete hibernate.cfg.xml files, with the only difference being the database specified in the connection.url property. I then wrote a version of the HibernateUtils class (from the reference documentation) that maintains a HashMap of SessionFactory objects, one per database, with the key being the database name and the value being the SessionFactory. I changed getSessionFactory() to getSessionFactory(String dbName), and this method now looks up the correct SessionFactory when given the database name. If the SessionFactory isn't in the HashMap, it instantiates a new one and does configure(cfgXmlFileName), then adds it to the HashMap.

This all works, but it's pretty ugly.

You can set "connection.url" but you have to prefix it with magic word "hibernate" like this:

setProperty("hibernate.connection.url","jdbc:postgresql://localhost/hbtest")

:)