how to map one jpa entity to multiple tables based on a property with Hibernate

Question

I'm going to store messages from external products in a database. All messages are the same and have a CATEGORY property. Since most queries that will be done on the db filter the category my DB Architect suggested to split the one table into one table per category.

Normally I would need one Class per table. Unfortunately There are more than 100 Categories...

Is it possible to implement my scenario with JPA/Hibernate?

JB Nizet · Accepted Answer · 2011-03-07 09:56:47Z

1

I don't think there is an easy way to do that, other than generating the 100 subclasses. If every category of messages has the same properties, the subclasses should be very simple, and could be automatically generated by a small program). This, however, will only work if the set of categories is static, and not dynamic.

However, I would first challenge the design of the architect. By doing this, you will lose the ability to

have foreign keys to messages (since they will be distributed in 100 tables)
have unique constraints across messages (even for their ID)
have sane queries over different message categories

And I don't think you'll gain much, because if the indexes over the table of messages include the category column, the query should be as fast as if the messages of a category were in a dedicated table.

answered Mar 7, 2011 at 9:56

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to map one jpa entity to multiple tables based on a property with Hibernate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related