How to transfer data from one database to another with Hibernate?

Question

I have an application A with a domain-model which is mapped to a database using Hibernate. I have another application B that uses exactly the same domain-model-classes as A and adds some additional classes.

My goal is to read data from database A in application B and transfer that data into the database of B (to make a copy of it). In addition, some the domain-classes of B have associations (OneToOne) to domain-classes of A (but in the database of B, of course).

What's the best strategy to accomplish this? I thought of two session factories and using Session.replicate() (how does that work?). Or should I better introduce an additional mapping layer between these two domain-models for loose coupling?

Answer 1

Tried other tools and had problems. Here's my home-rolled solution. Might need some cleaning up, but the meat of it is there.

import java.io.Serializable;
import java.util.List;
import java.util.logging.Logger;

import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.Setter;

import org.hibernate.Session;
import org.hibernate.Transaction;

import ca.digitalrapids.lang.GeneralException;
import ca.digitalrapids.mediamanager.server.dao.hibernate.GenericDAOHibernate;
import ca.digitalrapids.mediamanager.server.dao.hibernate.GenericDAOHibernate.GenericDAOHibernateFactory;
import ca.digitalrapids.persist.dao.DAOOptions;
import ca.digitalrapids.persist.hibernate.HibernateUtil2;

import com.google.common.collect.ImmutableMultimap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Sets;

@RequiredArgsConstructor
public class DataMigrator
{
    private static final Logger logger = Logger
        .getLogger(DataMigrator.class.getName());
    private final HibernateUtil2 sourceHibernateUtil2;
    private final HibernateUtil2 destHibernateUtil2;
    private final ImmutableSet<Class<?>> beanClassesToMigrate;
    @Setter @Getter
    private Integer copyBatchSize = 10;
    @Setter
    private GenericDAOHibernateFactory sourceDaoFactory = 
        new GenericDAOHibernate.GenericDAOHibernateFactoryImpl();
    @Setter
    private GenericDAOHibernateFactory destDaoFactory = 
        new GenericDAOHibernate.GenericDAOHibernateFactoryImpl();
    private final ImmutableMultimap<Class<?>, Class<?>> entityDependencies;

    public void run() throws GeneralException
    {
        migrateData(sourceHibernateUtil2.getSession(), 
            destHibernateUtil2.getSession());
    }

    private void migrateData(Session sourceSession, Session destSession) 
        throws GeneralException
    {
        logger.info("\nMigrating data from old HSQLDB database.\n");

        Transaction destTransaction = null;
        try
        {
            destTransaction = destSession.beginTransaction();
            migrateBeans(sourceSession, destSession, beanClassesToMigrate,
                entityDependencies);
            destTransaction.commit();
        } catch (Throwable e) {
            if ( destTransaction != null )
                destTransaction.rollback();
            throw e;
        }

        logger.info("\nData migration complete!\n");
    }



    private void migrateBeans(Session sourceSession, Session destSession,
        ImmutableSet<Class<?>> beanClasses, ImmutableMultimap<Class<?>, Class<?>> deps)
    {
        if ( beanClasses.isEmpty() ) return;
        Class<?> head = beanClasses.iterator().next();
        ImmutableSet<Class<?>> tail = 
            Sets.difference(beanClasses, ImmutableSet.of(head)).immutableCopy();
        ImmutableSet<Class<?>> childrenOfHead = getChildren(head, tail, deps);
        migrateBeans(sourceSession, destSession, childrenOfHead, deps);
        migrateBean(sourceSession, destSession, head);
        migrateBeans(sourceSession, destSession, 
            Sets.difference(tail, childrenOfHead).immutableCopy(), deps);
    }

    private ImmutableSet<Class<?>> getChildren(Class<?> parent,
        ImmutableSet<Class<?>> possibleChildren, 
        ImmutableMultimap<Class<?>, Class<?>> deps)
    {
        ImmutableSet<Class<?>> parentDeps = ImmutableSet.copyOf(deps.get(parent));
        return Sets.intersection(possibleChildren, parentDeps).immutableCopy();
    }

    private void migrateBean(Session sourceSession, Session destSession,
        Class<?> beanClass)
    {
        GenericDAOHibernate<?, Serializable> sourceDao = 
            sourceDaoFactory.get(beanClass, sourceSession);
        logger.info("Migrating "+sourceDao.countAll()+" of "+beanClass);

        DAOOptions options = new DAOOptions();
        options.setMaxResults(copyBatchSize);
        List<?> sourceBeans;
        int firstResult = 0;
        int sourceBeansSize;
        do { 
            options.setFirstResult(firstResult);
            sourceBeans = sourceDao.findAll(options);
            sourceBeansSize = sourceBeans.size();
            @SuppressWarnings("unchecked")
            GenericDAOHibernate<Object, Serializable> destDao = 
                (GenericDAOHibernate<Object, Serializable>) 
                destDaoFactory.get(beanClass, destSession);
            for (Object sourceBean : sourceBeans)
            {
                destDao.save(sourceBean);
            }
            firstResult += copyBatchSize;
            sourceSession.clear();/* prevent memory problems */
        } while ( sourceBeansSize >= copyBatchSize );
    }
}

Answer 2

Like others have pointed out, I think we need to know exactly what it is you're trying to accomplish. If you're doing a one time migration, there are better tools out there than Hibernate to do ETL (Extract, Transform, Load).

If you really insist on doing this in Hibernate (this applies to you also, Daniel), I'd do something like:

1. Open session to database A.
2. Read all entities of the type you're trying to copy (make sure lazy loading is disabled)
3. Open session to database B.
4. Save or update the entities.

I'd do this in a separate tool, rather than in application A or B.

On the other hand, if this is part of the functionality of your applications (e.g., application A is the admin console to the data, while application B consumes the data), you may want to do things a little differently. It's hard to say without knowing what exactly you're looking for.

Finally, something to look into (I don't think this is what you're looking for, but maybe it'll help you look at your problem in a different way) is Hibernate Shards (http://shards.hibernate.org/).

Answer 3

I've done this before to transfer data between two different database types (in my case DB2 and MS SQL Server). What I did was to create two separate session factories, and give both of them the same list of mapping files. Then I simply read records from one, and saved them to the other.

Of course, this assumed that both data sources were identical.

Answer 4

What is the purpose of the copying? Is that part of your application flow or logic? or just straight data copying?

If it is just for the sake of copying data over, there is no need to use hibernate. There are plenty of tools for it.