Two tables that hold the same class.

lachlan · **Joined:** Mon Mar 13, 2006 8:53 pm **Posts:** 4

I have a situation where I have two tables that hold the same class. Let's call the class Person and the tables PERSON and PERSON_ARCHIVE.

Old people get moved to the archive. For most queries, I can use my mapping from Person to PERSON, but for some queries I need to pull people from both tables at once.

My question is, what is the best way to go about this?

I saw this item in the FAQ:
http://www.hibernate.org/117.html#A20

Quote:

Does HQL support UNION (or INTERSECT)?
Not at this time. You may use a native SQL query for this.

Should I abandon HQL for this situation and stick with straight JDBC & SQL?

Christoph · **Joined:** Tue Mar 14, 2006 2:22 am **Posts:** 3

Hi Lachlan,

I have the same problem and I'm looking for a useful solution too. At the
moment I'm using JAVA inheritance and @MappedSuperclass annotation to
build subclasses for which I can set target table.
I don't like this approch because I need to create a lot of subclasses only
because I need to change the target table.

What is your current approch?

Regards,
Christoph

steckemetz · **Posted:** Tue Mar 14, 2006 10:47 am

hi,

you can use entity-names. For example:

Code:

<class name="MyClass"
      entity-name="MyClass1"
      table="mytable"
>

<class name="MyClass"
      entity-name="MyClass2"
      table="mytable"
>

Saving method in Session looks like this:

Code:

MyClass obj;
session.save( "MyClass1", obj );

For HQL Queries you have to specify the EntityName instead
of the classname.

Hope this helps !

lachlan · **Joined:** Mon Mar 13, 2006 8:53 pm **Posts:** 4

steckemetz, I think you misunderstood the question. I have a pattern with two tables & one class, not two classes and one table.

Again, using Person as a simple example:

Code:

TABLE PERSON
ID                    FIRST_NAME                    LAST_NAME
223                   HERBIE                        HANCOCK
2345                  CHICK                         COREA

TABLE PERSON_ARCHIVE
ID                    FIRST_NAME                    LAST_NAME
1234                  MILES                         DAVIS
1155                  JOHN                          COLTRANE
...

and one class:

Code:

public class Person {
    private long id;
    private String firstName;
    private String lastName;
...
}

The goal is to have an elegant solution to select Person from both tables at once.

Christoph, yes, your solution seems to be the only one available in Hibernate right now - use empty sublasses for the two tables
http://www.hibernate.org/hib_docs/v3/reference/en/html/inheritance.html
so Person becomes abstract, and I would have two empty subclasses:
e.g.
PersonCurrent that maps to PERSON
PersonArchive that maps to PERSON_ARCHIVE

and then I can select from Person and get both. Still, as you say, that's a lot of extra classes and mess to solve this problem. Currently, I have no solution, as we are just now introducing the ARCHIVE tables.

lachlan · **Joined:** Mon Mar 13, 2006 8:53 pm **Posts:** 4

Actually, the answer to this question lies here:
http://www.hibernate.org/117.html#A22

Quote:

How do I use multiple databases?
You must configure multiple SessionFactory instances.

You could point to two different tables that contain serializations of the same class by using multiple SessionFactory instances.