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.