Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.2

I am trying to use hibernate in an application to connect to multiple data sources. One db is DB2 that houses most of the basic employee info companywide, the other is an Oracle db that houses specific performance appraisal info for the application.
is it possible to connect to both data sources in the same App using hibernate. i.e is it possible to have 2 separate SessionFactories configged for 2 separate dbs.
The major problem that i am running into is when i try to use a custom config file like oracle.properties (or oracle.cfg.xml).

It seems that Hibernate doesnt like this. I have tried a hundred different variations of code, trying both properties files and xml files and nothing seems to work.

her is an example of something that i have been trying. If anyone could give me an idea or example of something that will work, it would be greatly appreciated.

Properties props = new Properties();
props.load(new FileInputStream("oracle.properties"));
Configuration config = new Configuration().setProperties(props);
config.addClass(Associate.class);
SessionFactory sessionFactory = config.buildSessionFactory();
Session session = sessionFactory.openSession();
try{
Query query = session.getNamedQuery "com.ae.common.getAssociateById");
query.setString("employeeId", employeeId);
associates = query.list();
} catch (Exception e) {
e.printStackTrace();
} finally {
session.close();
}

and my oracle.properties file is
hibernate.dialect=org.hibernate.dialect.OracleDialect
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.connection.url=jdbc:oracle:thin:@localhost:1521:apprdev
hibernate.connection.username=user
hibernate.connection.password=password

and the Associate.hmb.xml file is
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

<class name="com.ae.common.Associate" table="ASSOCIATE">
<meta attribute="class-description">
Represents an associate in the appraisal system
</meta>

<id
name="id"
type="int"
column="EMPLOYEE_ID">
<meta attribute="scope-set">protected</meta>
<generator class="assigned"/>
</id>

<property
name="fname"
type="string"
not-null="true"
column="FNAME"
length="50" />

<property
name="lname"
type="string"
not-null="true"
column="LNAME"
length="50" />

</class>

<query name="com.ae.common.getAssociateById">
<![CDATA[
from com.ae.common.Associate as associate where associate.employeeId = :employeeId
]]>
</query>

</hibernate-mapping>

and the exception i get is
org.hibernate.MappingException: Named query not known: com.ae.common.getAssociateById
at org.hibernate.impl.AbstractSessionImpl.getNamedQuery(AbstractSessionImpl.java:70)
at org.hibernate.impl.SessionImpl.getNamedQuery(SessionImpl.java:1253)
at com.ae.db.dao.AssociateDAOImpl.getAssociate(AssociateDAOImpl.java:47)
at com.ManualTest.main(ManualTest.java:30)
java.lang.NullPointerException
at com.ManualTest.main(ManualTest.java:30)

AssociateDAOImpl.java #47 - Query query = session.getNamedQuery("com.ae.common.getAssociateById");

when I rename oracle.properties to hibernate.properties it work perfectly fine.

Thanks,

Aaron

Yes, you could create two different session factories for two different data sources. By the way, your mapping is referencing old dtd. It could be a problem, so, try to fix it.

Kind Regards

I fixed the DTD mapping. But I am still getting the same error. It is almost like it cant find the Associate.hbm.xml file because i keep getting the error that it cant find the named query.
Again. change back to using oracle.properties file renamed to hibernate.properties and and just use the default config it works fine.
like this.
Configuration config = new Configuration();
config.addClass(Associate.class);
SessionFactory sessionFactory = config.buildSessionFactory();
Session session = sessionFactory.openSession();

but when i try to explicitely set the properties (even to hibernate.properties) it gives me that same error about not being able to fine the named query
Properties props = new Properties();
props.load(new FileInputStream("hibernate.properties"));
Configuration config = new Configuration();
config.addClass(Associate.class);
SessionFactory sessionFactory = config.buildSessionFactory();
Session session = sessionFactory.openSession();

and the same problem when i try to explicitely set it to oracle.properties.

Do you have an example of explicitely setting the properties to a file other than using the default properties file that actually works?
Thanks

I have three datasources defined in my application w/o any issues. I use a very generic "hibernate.properties" that contains configuration parameters that apply to all datasources. Then, I created a .cfg.xml for each datasource that contains the db specific properties. Build a session factory for each datasource and then use the appropriate session factory to get a session.


This is roughly how I've done it although the example is from a stand-alone application.

Good luck,
Curtis ...

How generic of a hibernate.properties file??
what are you putting in the prop file that would pertain to all DBs??

hibernate.dialect
hibernate.connection.driver_class
hibernate.connection.url
hibernate.connection.username
hibernate.connection.password

are all different between the 2 dbs.

i guess more specifically, would you be able to give me an example of your hibernate.properties file and one of your XXXX.cfg.xml files?
THanks,

A

well i think i finally got it, At least for one DB thru a custom config file.

i have a hibernate.properties file
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=30
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.order_updates=true
hibernate.generate_statistics=false
hibernate.use_sql_comments=false
hibernate.connection.autocommit=true
##################

Then i have an oracle.cfg.xml file, and it contains the mapping resource
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>

<!-- Settings for a oracle DEV database. -->
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@localhost:1521:databasename</property>
<property name="connection.username">user</property>
<property name="connection.password">password</property>

<!-- Hibernate XML mapping files -->
<mapping resource="com/ae/common/Associate.hbm.xml"/>

</session-factory>
</hibernate-configuration>
##################

of course i still have the Associate.hbm.xml mapping file with my associations and my named Queries
##################

then in my code i do the following...

Configuration config = new Configuration().configure("oracle.cfg.xml");
SessionFactory sessionFactory = config.buildSessionFactory();
System.out.println("sessionFactory = " + sessionFactory);
Session session = sessionFactory.openSession();

try{
Query query = session.getNamedQuery("com.ae.common.getAssociateBySsnOrEmail");
query.setString("ssn", ssnOrEmail);
query.setString("email", ssnOrEmail);
associates = query.list();
}cathc(Exception e) {
e.printStackTrace();
}

THIS ACTUALLY builds the session successfullly and finds the named query and the mapped class object (in this case Associate) and returns a successful result.

NOW all i have to do it try and see if I can connect to more than on Db using this same approach.

I can't really give you my files as they are quite large. What you've got looks pretty close.

Curtis ...