Connecting to multiple databases using a single MBean

Amrit · **Joined:** Thu Aug 12, 2004 10:02 am **Posts:** 8

Hibernate version:
Hibernate 2.1.4
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:
Informix 9.4
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

My application needs to connect to a minimum of 4 databases of the same schema. My requirement is to update all the 4 databases in a single transaction using the same Hibernate sar file which is deployed in Jboss. Currently the SessionFactory is associated with a single Informix datasource which is specified in the Jboss-service.xml as shown below.

<?xml version="1.0" encoding="UTF-8" ?>

<server>
<mbean code="net.sf.hibernate.jmx.HibernateService" name="jboss.jca:service=HibernateFactory,
name=HibernateFactory">
<depends>jboss.jca:service=RARDeployer</depends>
<depends>jboss.jca:service=LocalTxCM,name=InformixDS</depends>



<attribute name="MapResources">

com/krcl/rap/common/hibernate/krcl.hbm.xml

</attribute>
<attribute name="JndiName">java:/hibernate/HibernateFactory</attribute>
<attribute name="Datasource">java:/InformixDS</attribute>
<attribute name="Dialect">net.sf.hibernate.dialect.InformixDialect</attribute>
<attribute name="TransactionStrategy">net.sf.hibernate.transaction.JTATransactionFactory</attribute>
<attribute name="TransactionManagerLookupStrategy">net.sf.hibernate.transaction.JBossTransactionManagerLookup</attribute>
<attribute name="UseOuterJoin">false</attribute>
<attribute name="ShowSql">true</attribute>
<attribute name="CacheProvider">net.sf.hibernate.cache.HashtableCacheProvider</attribute>
#net.sf.ehcache.hibernate.Provider
<attribute name="UserTransactionName">UserTransaction</attribute>
</mbean>
</server>

steve · **Posted:** Wed Sep 15, 2004 3:49 pm

A single MBean manages exactly one SessionFactory. A single SessionFactory manages exactly one database. If you need to interact with multiple databases, a SessionFactory must be defined for each (or use db-specific features like Oracle's DB_LINK).

To have all these operations come under the auspices of a single transaction, use XA DataSources (one for each database). For each XA DataSource, setup an MBean mapping in the jboss-service.xml for your SAR specifying the DataSource's jndi name. Then use the TransactionManager to ensure transaction consistency between the 4 (or however many) DataSources.

Amrit · **Joined:** Thu Aug 12, 2004 10:02 am **Posts:** 8

Hi Mr Steve,

Thanks for your immediate reply.
You have quoted that "A single MBean manages exactly one SessionFactory."
So according to your opinion for connecting to 4 databases, i need to create and deploy 4 SAR files. Since 4 databases are having the same DATABASE SCHEMA, should they can share the same hbm.xml files? and can i manage this with only one SAR file to reduce Jboss' deployment time.
Is there any way to specify these thing in a single Jboss-service.xml file?

steve · **Posted:** Fri Sep 17, 2004 10:05 am

Quote:

So according to your opinion for connecting to 4 databases, i need to create and deploy 4 SAR files

No, I never said that. Certainly you will need to deploy 4 MBeans (one for each SessionFactory); how you accomplish that is up to you.

Quote:

Since 4 databases are having the same DATABASE SCHEMA, should they can share the same hbm.xml files

I must be missing something, as I don't understand why you would need 4 different SessionFactories hitting the same database, the same schema, with the same mappings.

Amrit · **Joined:** Thu Aug 12, 2004 10:02 am **Posts:** 8

Hi steve

I am using 4 session factories for connecting to 4 database servers which are having the exactly same database schema. This is one of my requirement. Can i use the same persistent object with different sessions for updating two databases in the same transaction?

octopus · **Posted:** Fri Sep 17, 2004 1:43 pm

Amrit wrote:

Hi steve

I am using 4 session factories for connecting to 4 database servers which are having the exactly same database schema. This is one of my requirement. Can i use the same persistent object with different sessions for updating two databases in the same transaction?

Sure you can. Do something like this:

Code:

start XA transaction

while(sessionIterator.hasNext()){
   Session session = (Session)sessionIterator.next();

   session.update(myEntity);
   session.flush();
}

flush XA transaction

Amrit · **Joined:** Thu Aug 12, 2004 10:02 am **Posts:** 8

My application deals with a legacy Informix database. So most of the tables are having composite keys. I have generated the mapping files and hibernate objects using middlegen. I have removed all the middlegen generated primary key(PK) classes for tables having composite keys and included them in the same model class itself.

My requirement is to get all the composite column names (identofiers) and all the other attributes separately. I couldn't find any method in ClassMetadata to retrieve the columns.

Please give me a right solution for this. Thanks in advance.