-->
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.  [ 6 posts ] 
Author Message
 Post subject: Tackling existing multi-database MySQL layout with hibernate
PostPosted: Sun Jan 23, 2005 10:41 pm 
Newbie

Joined: Sun Jan 09, 2005 2:30 am
Posts: 7
At my company we have 50+ MySQL databases on one server that have the same schema for the purpose of partitioning data (due to the size of each database). The decision goes back before my time and of course there is a tremendous amount of spaghetti infrastructure (web, batch processing, cron scripts, reporting, etc...) relying upon this arrangement. The data is state specific and the schemas are identical from state to state.

In moving our company from Perl to Java one of the main challenges for me has been sorting out our approach to the ORM layer. I'm using Hibernate on the unique databases that have unique tables. However, I'm struggling to find an elegant way to work with Hibernate to have one class map to tables in 50+ different databases and have come up with the following possibilities (that I still need to test) listed in order of my perceived level of simplicity and I'm looking for feedback based on other people's experience:

1. Use Hibernate and have Spring maintain the 50+ hibernate session factories and the 50+ datasources. I would then have Spring dependency inject a class that allows the DAO to fetch the appropriate hibernate session factory. I'm not sure but there may be caching issues to deal with here.
2. Use iBatis for these multi-database tables and have Spring maintain 1 database connection and use variable substitution as part of the SQL map statements: select * from #databasename#.table. This approach seems simple and promising on the surface. Downside is programmers will need to know two ORM packages and it takes a little longer to write a DAO/POJO combo in iBatis than Hibernate (at least for me).
3. Use JDBC and have Spring maintain 1 database connection. Then I would use select * from databasename.tablename and then manually do the ResultSet to POJO mapping.
4. Put the company on hold and have engineering focus for a few months on refactoring the 50 databases down to one that support partitioning and refactor the legacy web, batch processing, and reporting systems. This one I feel is a little too costly and risky since I can't think of a way to take an iterative approach due to the number of systems involved.

If you have ideas or have dealt with the same issue please chime in!


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 24, 2005 11:40 am 
Hibernate Team
Hibernate Team

Joined: Sun Sep 14, 2003 3:54 am
Posts: 7256
Location: Paris, France
Maybe you should consider commercial support.

_________________
Emmanuel


Top
 Profile  
 
 Post subject: Hibernate on multi-databases
PostPosted: Mon Jan 24, 2005 5:45 pm 
Newbie

Joined: Sun Jan 09, 2005 2:30 am
Posts: 7
I'll consider commercial support if the hibernate team tells me there's feasible solution to this. Then I'm willing to pay to achieve that solution.

-Todd


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jan 24, 2005 6:32 pm 
Hibernate Team
Hibernate Team

Joined: Mon Aug 25, 2003 9:11 pm
Posts: 4592
Location: Switzerland
Yes, I think there should be a solution for this.


Top
 Profile  
 
 Post subject: Hibernate mapping one class to multiple databases
PostPosted: Fri Feb 04, 2005 9:20 pm 
Newbie

Joined: Sun Jan 09, 2005 2:30 am
Posts: 7
I've solved this problem for my purposes to enable mapping 1 class to multiple database by enhancing Spring's Hibernate DAO support. I've published my solution and source code here:

http://jroller.com/page/thuss/Blog/hibe ... e_class_to

Hope this helps anyone else trying to solve this problem.

-Todd


Top
 Profile  
 
 Post subject:
PostPosted: Fri Feb 04, 2005 9:44 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 7:19 pm
Posts: 2364
Location: Brisbane, Australia
Thanks - its also a good idea to add the details to the wiki.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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.