aspose file tools*
The moose likes Object Relational Mapping and the fly likes How to map one class to multiple similar tables with Hibernate? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "How to map one class to multiple similar tables with Hibernate?" Watch "How to map one class to multiple similar tables with Hibernate?" New topic
Author

How to map one class to multiple similar tables with Hibernate?

Ximing Yu
Greenhorn

Joined: Apr 18, 2010
Posts: 3
I'm working with a legacy database and have about 30 tables with same columns. There will be 13 million rows in total in the 30 tables. Is it possible to map a class to all 30 tables and have an attribute in class that map to different table name?

Using SQL to do query in any one table is already quite slow, so I doubt combining all 30 tables to one big table will result in unbearable low efficiency. Anyone have any idea about solving this kind of problem? Thanks.
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 539
    
    7
Hi Ximing,

Welcome to the Ranch!

Just a few suggestions....

Does the legacy database support database views, so you can construct one virtual table and then optimize performance through indexes or other database mechanisms?

Other than that, perhaps you can have a look at the Hibernate Shards framework - your tables are like shards, except that the term "shards" usually implies partitioning across databases. I'm not sure if Hibernate Shards supports your use case - you may have to experiment.

I'll be very grateful to you if you can please let me know your findings when you've eventually settled on an approach, since I find the problem interesting.
Ximing Yu
Greenhorn

Joined: Apr 18, 2010
Posts: 3
Thanks Karthik. I'll take look at Hibernate Shards.

Karthik Shiraly wrote:Hi Ximing,

Welcome to the Ranch!

Just a few suggestions....

Does the legacy database support database views, so you can construct one virtual table and then optimize performance through indexes or other database mechanisms?

Other than that, perhaps you can have a look at the Hibernate Shards framework - your tables are like shards, except that the term "shards" usually implies partitioning across databases. I'm not sure if Hibernate Shards supports your use case - you may have to experiment.

I'll be very grateful to you if you can please let me know your findings when you've eventually settled on an approach, since I find the problem interesting.
Ximing Yu
Greenhorn

Joined: Apr 18, 2010
Posts: 3
Karthik, I read the documentation of Hibernate Shard and found it to be a viable solution to my problem. However, I also noticed that it does not support HQL very well and I may need a lot sorting and other aggregation in my query. So I guess it might lead to some trouble.
It's also a pity that Hibernate Shard does not support JPA...
Ran Pleasant
Ranch Hand

Joined: Jan 16, 2003
Posts: 75
Ximing Yu wrote:I'm working with a legacy database and have about 30 tables with same columns. There will be 13 million rows in total in the 30 tables. Is it possible to map a class to all 30 tables and have an attribute in class that map to different table name?

Using SQL to do query in any one table is already quite slow, so I doubt combining all 30 tables to one big table will result in unbearable low efficiency. Anyone have any idea about solving this kind of problem? Thanks.


Hibernate can map subclasses to one table or to their own table. I have not done this myself (I always used one table for all subclasses) but I guessing that you can create a parent class and then create a subclass for each of the tables. This would allow you to work with given objects/tables as needed and allow you to work with the parent table so as to query within all of the tables.


Ran
Karthik Shiraly
Ranch Hand

Joined: Apr 04, 2009
Posts: 539
    
    7
Ximing Yu wrote:Karthik, I read the documentation of Hibernate Shard and found it to be a viable solution to my problem. However, I also noticed that it does not support HQL very well and I may need a lot sorting and other aggregation in my query. So I guess it might lead to some trouble.
It's also a pity that Hibernate Shard does not support JPA...


Hi Ximing,

Thank you very much for that information!

Karthik
Jennifer Debroone
Greenhorn

Joined: Apr 28, 2010
Posts: 5
Ximing Yu wrote:I'm working with a legacy database and have about 30 tables with same columns. There will be 13 million rows in total in the 30 tables. Is it possible to map a class to all 30 tables and have an attribute in class that map to different table name?

Using SQL to do query in any one table is already quite slow, so I doubt combining all 30 tables to one big table will result in unbearable low efficiency. Anyone have any idea about solving this kind of problem? Thanks.


Using a native SQL query shouldnt be any slower if you use UNION ALL
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to map one class to multiple similar tables with Hibernate?