-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: HibernateUtil needed for multiple databases
PostPosted: Thu Sep 30, 2010 4:58 pm 
Beginner
Beginner

Joined: Tue Aug 03, 2010 4:32 pm
Posts: 22
Greetings,

I've built a standalone Java/Hibernate/MySQL app where all the tables live in one database witin one instance of MySQL on one physical server. I patterned the app after the canonical caveat emptor app ie HibernateUtil, GenericHibernateDAO, DAOs for all my persistance classes etc. I use annotations in my persistant classes.

Now I must also pull data from additional tables, some living in different databases within the same instance of MySQL on the same server, others in different databases on different instances of MySQL on different physical servers.

I'm aware that each databases needs its own SessionFactory.
I'd like to use the pattern where HibernateUtil has been adjusted to use a HashMap<String, SessionFactory> , googling turns up dozens of variations of this.

But what changes need to be made to the rest of the app?

If database Olaf contains tables Animals, Cars and Trees
and
database Sven contains tables Plants, Planes and Flowers

then I would need an Olaf.cfg.xml and Sven.cfg.xml referencing
their respective tables, and adjust hibernate.cfg.xml to
reference the two .cfg.xml , is that correct?

I think GenericHibernateDAO also needs an adjustment
from:
Code:
    protected Session getSession() {
        if (session == null)
            session = HibernateUtil.getSessionFactory().getCurrentSession();
        return session;
    }

to
Code:
    protected Session[] getSession() {
        Session[] session_arry = new Session[2];
        if (session == null)
//          session = HibernateUtil.getSessionFactory().getCurrentSession();
            session_arry[0] = HibernateUtil.currentSession("olaf");
            session_arry[1] = HibernateUtil.currentSession("sven");
        return session_arry;
    }


or something.

Sure would've been nice if the JPH book had an example of this common need.

Would anyone point me to a complete, working example that'd be great! But all help and suggestions are most welcome.

TIA,

Still-learning Steve


Top
 Profile  
 
 Post subject: Re: HibernateUtil needed for multiple databases
PostPosted: Wed Oct 06, 2010 12:44 am 
Beginner
Beginner

Joined: Tue Aug 03, 2010 4:32 pm
Posts: 22
I've got my HibernateUtil changed around to create a SessionFactory for two connections corresponding to two files:
olaf.cfg.xml
sven.cfg.xml

and stores the two factories in a hashmap<String, SessionFactory> structure.

I've updated my main() to create two separate Sessions:
Session session_a = HibernateUtil.currentSession("olaf");
Session session_b = HibernateUtil.currentSession("sven");
Transaction tx_a = session_a.beginTransaction();
Transaction tx_b = session_b.beginTransaction();

But now I need to know how to modify my HibernateDAOFactory to work with more than one database. Or must I instead use an Olaf_HibernateDAOFactory and Sven_HibernateDAOFactory?

Still-learning Steve


Top
 Profile  
 
 Post subject: Re: HibernateUtil needed for multiple databases
PostPosted: Wed Oct 06, 2010 5:21 am 
Newbie

Joined: Tue Feb 02, 2010 10:37 am
Posts: 13
In case of two different datasources, transactions has to be started using transaction manager instead of hibernate sessions.
Take a look at deployment of JTA transaction manager with Hibernate to get an idea of XA transactions.


Top
 Profile  
 
 Post subject: Re: HibernateUtil needed for multiple databases
PostPosted: Wed Oct 13, 2010 4:31 am 
Beginner
Beginner

Joined: Tue Aug 03, 2010 4:32 pm
Posts: 22
Er, no, I didn't have to tinker with any JTA stuff.

I figured it out after a great deal of googling, translating from Russian and Chinese.
You'd think someone would have a solution posted years ago, oh well.

Assume you have two databases, Olaf and Sven. they can be live in the same instance of a database server or be in different instances on different physical machines.

I set up my project in Eclipse, with packages of "urproject.dao", "urproject.dao.hibernate", "urproject.model" and "urproject.persistance".
All my mapping files go in "urproject.model" regardless of which database the corresponding tables reside in. So, Car.java maps to table Cars, etc, as expected.
I use annotations and DAOs so expect to make some adjustments if you do not.

Olaf contains tables:
-- Animals
-- Cars
-- Trees

Sven contains tables:
-- Plants
-- Planes
-- Flowers

Step 1
You'll need to create ONE cfg.xml file PER DATABASE, with the connection parameters pointing to the proper places. These files go in the directory where the
defalt hibernate.cfg.xml used to reside - I deleted mine.
so,
olaf.cfg.xml
Code:
<?xml version='1.0' encoding='utf-8'?>
<!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="hibernate.connection.username">trickydicknixon</property>
    <property name="connection.password">xxxxxx</property>
    <property name="connection.url">jdbc:mysql://localhost:3306/olaf</property>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.autocommit">false</property>
    <property name="current_session_context_class">thread</property>

    <property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
   
    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">600</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">400</property>
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.hbm2ddl.auto">update</property>

    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

    <!--<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
    <property name="hibernate.cache.use_second_level_cache">true</property>
    <property name="hibernate.cache.use_query_cache">true</property> -->
   
    <mapping class="urproject.model.Animal"/>
    <mapping class="urproject.model.Car"/>
    <mapping class="urproject.model.Tree"/>

  </session-factory>
</hibernate-configuration


sven.cfg.xml
Code:
<?xml version='1.0' encoding='utf-8'?>
<!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="hibernate.connection.username">lyndonbjohnson</property>
    <property name="connection.password">yyyyyyyy</property>
    <property name="connection.url">jdbc:mysql://localhost:3306/sven</property>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.autocommit">false</property>
    <property name="current_session_context_class">thread</property>

    <property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
   
    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">600</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.idle_test_period">400</property>
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.format_sql">true</property>
    <property name="hibernate.hbm2ddl.auto">update</property>

    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

    <!--<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
    <property name="hibernate.cache.use_second_level_cache">true</property>
    <property name="hibernate.cache.use_query_cache">true</property> -->
   
    <mapping class="urproject.model.Plant"/>
    <mapping class="urproject.model.Plane"/>
    <mapping class="urproject.model.Flower"/>

  </session-factory>
</hibernate-configuration


You'll have to set up the username/password/connection stuff to suit your needs.

Step 2
In each of your dao.hibernate files you'll need to make adjustments to all your getSession statements to tell them which session to grab:

For example, in CarDAOHibernate.java you'd change

public List<Car> getAll()
{
List<Car> results = null;
String sqlcmd;

sqlcmd = " FROM Car ";
/* Query hqlQuery = getSession().createQuery( sqlcmd); <-- old */
Query hqlQuery = getSession("olaf").createQuery( sqlcmd); // <-- new
results = hqlQuery.list();

return results;
}

Step 3
Upgrade your GenericHibernateDAO.java by replacing the original getSession method
with the following:
Code:
/*   original method
    protected Session getSession() {
        if( session == null)
            session = HibernateUtil.getSessionFactory().getCurrentSession();
        return session;
    }
*/ 
// new methods   
    protected Session getSession() {
        if( session == null)
            session = HibernateUtil.currentSession();
        return session;
    }
   
    protected Session getSession( String keyy) {
       if( session == null)
          session = HibernateUtil.currentSession( keyy);
       return session;
    }



Step 4
Replace your HibernateUtil.java file with something like this:
Code:
package urproject.persistence;

import org.hibernate.*;
import org.hibernate.cfg.*;
import org.apache.commons.logging.*;

import javax.naming.*;
import java.io.*;
import java.util.*;
import java.net.URL;

public class HibernateUtil
{
    private static final boolean usingAnnotations = true;
    private static Log log = LogFactory.getLog(HibernateUtil.class);
    private static HashMap<String, SessionFactory> sessionFactoryMap = new HashMap<String, SessionFactory>();
    public static final ThreadLocal sessionMapsThreadLocal = new ThreadLocal();

    static {
        try {
            String fileName;
            SessionFactory sf;
            // Create the SessionFactory objects from *.cfg.xml
            ArrayList<String> cfglist = getHibernateCfgFiles("/");
            for( String keyy : cfglist) {
                fileName = keyy + ".cfg.xml";
                if( usingAnnotations) {
                    sf = new AnnotationConfiguration().configure(fileName).buildSessionFactory();
                } else {
                    sf = new Configuration().configure(fileName).buildSessionFactory();                   
                }
                sessionFactoryMap.put(keyy, sf);
            }
        } catch (Exception ex) {
            ex.printStackTrace(System.out);
            log.error("Initial SessionFactory creation failed.", ex);
            System.out.println("Initial SessionFactories creation failed.");
            throw new ExceptionInInitializerError(ex);
   
        } // end of the try - catch block
    }
   
    public static Session currentSession() throws HibernateException
    {
        return currentSession("");
    }

    public static Session currentSession( String keyy) throws HibernateException
    {
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();

        if( sessionMaps == null) {
            sessionMaps = new HashMap();
            sessionMapsThreadLocal.set( sessionMaps);
        }

        // Open a new Session, if this Thread has none yet
        Session s = (Session) sessionMaps.get(keyy);
        if( s == null) {
            s = ((SessionFactory) sessionFactoryMap.get(keyy)).openSession();
            sessionMaps.put(keyy, s);
        }

        return s;
    }

    public static ArrayList<String> getHibernateCfgFiles( String path)
    {
        ArrayList<String> ret_arry = new ArrayList<String>();
        String fileName, keyy;
        String[] str_arry;

        URL url = Configuration.class.getResource( path);
        File file = new File( url.getFile());
        if( file.isDirectory()) {
            for( File f : file.listFiles()) {
                if( f.isFile()) {
                    fileName = f.getName();
                    if( fileName.endsWith(".cfg.xml")) {
                        str_arry = fileName.split("\\.");
                        keyy = str_arry[0];
                        ret_arry.add( keyy);
                    }
                }
            }
        }
     
        return ret_arry;
       
    }

    public static void closeSession()
    {
        log.debug("closing a single session");
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();
        sessionMapsThreadLocal.set(null);
        if( sessionMaps != null) {
            Session session = sessionMaps.get("");
            if( session != null && session.isOpen())
                session.close();
        }
    }

    public static void closeSessions() throws HibernateException
    {
        log.debug("closing sessions");
        HashMap<String, Session> sessionMaps = (HashMap<String, Session>) sessionMapsThreadLocal.get();
        sessionMapsThreadLocal.set(null);
        if( sessionMaps != null) {
            for( Session session : sessionMaps.values()) {
                if( session.isOpen())
                    session.close();
            };
        }
    }
   
    /**
     * Closes the current SessionFactory and releases all resources.
     * <p>
     * The only other method that can be called on HibernateUtil
     * after this one is rebuildSessionFactory(Configuration).
     */
    public static void shutdown()
    {
        log.debug("Shutting down Hibernate");
        closeSessions();
        // Close caches and connection pools
        if( sessionFactoryMap != null) {
            for( SessionFactory sf : sessionFactoryMap.values()) {
                 sf.close();
            }
        }
        sessionFactoryMap = null;  // Clear static variables
    }

}



YMMV. No doubt someone wiser than I can improve upon the above code, but this works for me.

CASE CLOSED

Thank for reading,

Still-learning Steve


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.