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

[Ximing Yu]

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]

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]

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]

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]

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.

[Karthik Shiraly]

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]

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