Hibernate mapping one class to multiple databases

At work our data is state specific and for each state we have a lot of data. It was decided to create a separate database for each state in MySQL long before I joined the company and now we have a lot of legacy infrastucutre that depends on it. In Oracle I would have solved it by creating a partitioned table in which you can partition the data based on the value of a field (yet still have it be one table). I believe partitioned tables are a feature of MySQL cluster of which I hope to learn more about in the future.

In moving the company from Perl to Java one of my challenges has been creating the OR mapping layer. I chose Hibernate and therefor wanted the ability to use Spring's Hibernate DAO support for the generic unchecked data exceptions. I also needed to have one class map to multiple databases depending on which state the data was specific for. Rather than make the project specific to our domain though I figured this is a general problem that other companies using open-source databases have solved in the same way. Especially when their data gets too large for one database and can be easily partitioned. In our case we have a main shared database and then state specific databases to store all state specific data (which is the bulk of our data).

To solve the OR mapping issue I created a generic maven / hibernate / xdoclet / dbunit kickstart project with a generic BaseDAOHibernate layer based on Appfuse as well as my own BaseDAOPartitionHibernate to easily create DAO's for partitioned tables. I still use Spring's Hibernate DAO support, but I had to add a new HibernateDaoPartitionSupport class based on Spring's HibernateDaoSupport class.

It's not very polished yet but if you're trying to use hibernate with identical schemas across multiple databases hopefully my partition-dao-kickstart-0.1.tgz project can help you get on the right track.

This entry was posted in Java. Bookmark the permalink.

One Response to Hibernate mapping one class to multiple databases

  1. Marten says:

    Hi,

    We currently have a problem which is very similair to yours. When an object (record) has a certain state it needs to be moved to another database. I was looking for solutions and yours seems promising.

    Our company firewall is blocking the download of .tgz files for some weird reason (every other fileformat is legal). So can you send me your code in a .zip/.tar.gz file that would be great.

Leave a Reply

Your email address will not be published. Required fields are marked *