Mapping multiple tables with identical columns to same class

bentandre · **Joined:** Mon Dec 20, 2004 10:21 am **Posts:** 5

Hi Hibernate users,

this is my first post, so I thought I would introduce myself; my name is Bent André Solheim and have been using Hibernate for production development since September.

Currently I am working on a project with multiple tables with identical columns (same kinds of data, different languages). I'm looking for a way to use the same class for each table, and to parameterize the table I want to query or update. I tried to search the archives for some hints on how to do this, and found the following post http://forum.hibernate.org/viewtopic.php?t=929231. In Hibernate 2.1 it's only possible using several SessionFactories, but I hoped to avoid this solution. I'm willing to move to Hibernate 3 as the project is not due for release in several months. Have anybody done similar things using Hibernate 3? Is it possible at all?

Best Regards
Bent André Solheim[/url]

michael · **Posted:** Mon Dec 20, 2004 11:31 am

I think you can do this by using entity names in H3 ...

bentandre · **Joined:** Mon Dec 20, 2004 10:21 am **Posts:** 5

Thank you, Michael, for the tip.

I've been poking around your suggestion for couple of hours, and I'm not sure if I understand entirely how to do this.

One of my requirements is that I have to be able to use the same class for many different tables. Let's call this class A. The number of tables and their names are not known at build-time. They are determined at runtime by user supplied parameters.

I started looking at dynamic-class, and entity-name for a solution, and the way I see things, I have to do the following to make things work;

Before building the Configuration object, I have to determine all possible names of the tables that can be used with class A (this is feasible). I then have to build mappings of dynamic-classes using the table names as entity-names dynamically (this can also be done). When the Configuration has been built, I can use the update/save/create functions with entityName parameters, where the entity names will be the names of my tables. In my DAO's I can copy the properties of objects of class A to Maps before update, and vice versa on load.

Although I can make things work this way, I was hoping for a solution a bit less involved. With my suggested solution, I also have to connect to the database before building the Configuration object - to get the table names - which I'd like to avoid.

Am I way off here? Are there easier solutions to my problem? Any pointers from others in similar situations would be highly appreciated! :)

Best regards,
Bent

max · **Posted:** Wed Dec 22, 2004 4:09 am

first off - you don't need to use dynamic-class with entity-names!

just use entity-name with any other class mapping...then i think it should be ok for you...

like

Code:

 <class name="A" entity-name="A_en">
 </class>

<class name="A" entity-name="A_de">
 </class>

<class name="A" entity-name="A_fr">
 </class>

max · **Posted:** Wed Dec 22, 2004 4:11 am

...and you don't have to build a new mapping on each startup do you ?
Do you add table's dynamically to your app to handle I8N ?

bentandre · **Joined:** Mon Dec 20, 2004 10:21 am **Posts:** 5

Thank you, Max, I'll look into this. It sounds exactly what I'm after! :)

To answer your question regarding i18n tables, actually it would be nice if the application could handle additions of new tables without being reconfigured. Although it does not happen all the time, sometimes we spilt one table into two or more to reduce index size (we have limited hardware resources, and deal with hundres of gigabytes of data). This has proven to be a cheap way to increase performance. That is why I suggested building the mappings dynamically.

So, although it may sound strange at first, the question still remains; are there elegant ways to map a class to a table at runtime when the name of the table is only known by looking it up in the database?

Thank you for the help so far guys! It's been really helpful! :)

Best regards
Bent

max · **Posted:** Wed Dec 22, 2004 5:03 am

well - it's kinda tricky, but you can programmatically alter the configuration via the Configuration API.

But in your case i think i would simply make a template xml
file and do a search-replace for your stuff and add the newly generated xml file (or inmemory string/dom) to the configuration ...

bentandre · **Joined:** Mon Dec 20, 2004 10:21 am **Posts:** 5

hmm... maybe I'll dig into the details of the Configuration API if I feel crazy one evening! ;) But till then, I'm going for your second suggestion - I'm already on it. I discover the tables on application startup (currently a config parameter, not database lookup) and use a template mapping file to generate inmemory mappings that I add to the Configuration object. This is an acceptable solution.

Ideally, in my scenario, Hibernate should have supported wildcard entity-names. For instance;

Code:

<class name="A" entity-name="mytable_*">
[...]
</class>

in java:

Code:

A anObject = ...
Session s = openSession();
s.save("mytable_fr", anObject);

A anOtherObject = ...
s.save("mytable_en", anOtherObject);

would both use the same mapping description, but different tables, because of the wildcard entity-name. This would have been perfect in my situation. I guess this is something a could put in a feature request or something, but it would be nice with a sanity check. How does this sound to you guys?

Thanks again, Max! :)

/Bent

max · **Posted:** Wed Dec 22, 2004 5:43 am

well - your wildcard solution doesn't tell hibernate which table it maps to!

Also remember that hibernate needs to know at "buildSessionFactory()" time the precise names of tables as all the sql (and other things) is pregenerated...

I would instead suggest adding this as a ConfigurationBuilderHelperWhatever class that could validates the stuff for you ....

bentandre · **Joined:** Mon Dec 20, 2004 10:21 am **Posts:** 5

Yeah, well... It was worth a thought! ;)

I was thinking the entityName parameter to the Session functions would be what determined the actual table the objects were mapped to, but as you put it, this will not work since Hibernate pregenerates all queries. (Unless, ofcourse, the table names could also be parameterized in the pregenerated queries).

But I'll stop eating your time, Max, with my endless babble. I have a solution that works, and I'll stick to it. You have been very helpful. Thank you!

/Bent

baliukas · **Posted:** Wed Dec 22, 2004 8:37 am

bentandre wrote:

sometimes we spilt one table into two or more to reduce index size (we have limited hardware resources, and deal with hundres of gigabytes of data). This has proven to be a cheap way to increase performance. That is why I suggested building the mappings dynamically.

Some databases support this kind of split, this feature is known as "partition". It must be cheaper way, if your database supports it.

Misha_Kan · **Joined:** Tue Jan 04, 2005 5:39 am **Posts:** 37

Hi guys,

It's a little bit old discussion, but it's very relevant for me. I also have to implement "partitioning" in application code.

It would be great if Hibernate had this feature! It's really performance improving when dealing with large amount of data.

Instead of paying hundreds of $ for Oracle Enterprise ...

nisroc · **Posted:** Wed Dec 07, 2005 6:06 pm

I'll second that. I stumbled across this topic while looking for ideas on supporting an ad-hoc partitioning scheme done in mysql: in my app, when a particular table grows too big, it is renamed and moved into the reporting DB and a fresh, empty table with the same schema is constructed in the application DB. So I have a similar problem as the original poster: I need to be able to have a single class talk to different tables, in different DBs, that I control programmatically.

I'm off to check out the Configuration API to see how doable this is.

max · **Posted:** Wed Dec 07, 2005 6:11 pm

use entity-name

nisroc · **Posted:** Wed Dec 07, 2005 6:54 pm

Right, I saw that approach above, but that requires a separate <class/> mapping for each table I want to map to. In my case the multiple tables are essentially log rotations: I know that they will be called tablename_1, tablename_2, etc. but I don't know how many of them there will be.

Your suggestion about some sort of Configuration helper object seemed like it would work better for me, which is why I went looking in the configuration API. :)

I'd second Bent's suggestion that a mapping of the form <class name="A" entity-name="mytable_*"> and then a programmatic way to set the value for * when executing a query seems ideal.

Perhaps at config time, hibernate could query the DB, find all tables that match the mytable_* pattern, and automatically generate the set of mapping files.

Hmm, which I guess is what my approach is about to become... I'll have to submit a patch. :)
[/code]