How to map 2 tables without keys

jtmelton · **Joined:** Sat Jan 03, 2009 12:32 pm **Posts:** 3

Hi, I've been trying to find an answer to this in books and online, so I thought I'd try here. My problem is that I'm trying to join 2 tables that don't have a keyed relationship at the DB level, but can be related by a join.

Table 1
-------
abcid
field1
field2

Table 2
-------
abcid
field3
field4

In a normal schema, Table 2 would have a foreign key back to table 1, but in this case, the keys are managed outside of the database by an application. I can't change the schema. So I'd like to still get this to work. I'm hoping to be able to have a Object structure like this:

Code:

public class Object1 {
    private int id;
    private String field1;
    private String field2;
    private Object2 obj2;
}
public class Object2 {
    private int id;
    private String field3;
    private String field4;
}

There is a 1-1 relationship between table 1 and table 2. So far, I've followed this article : http://www.roseindia.net/hibernate/asso ... oins.shtml
and been able to retrieve the data back as an array of objects that I cast out each element of the array, but it's not very clean. Also, I do plan to have 7 or 8 tables joined in this way, and it doesn't appear to work well.

My hql looks like this

Code:

from table1 t1, table2 t2 where t1.abcid = t2.abcid

Can someone help please?

Hibernate version:
3

Name and version of the database you are using:
mysql 5

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

jtmelton,

One of the most attractive features of Hibernate is, precisely, to reduce SQL code clutter by having "implicit" joins already expressed in your mappings.

If the tables you want to join are not related by foreign keys, then your only recourse is what you did: using the " ... where t1.a=t2.a", which is called "theta-join" style.

The Array of Objects that you obtain, is because you are returning something other than a single mapped Class type on your resultset. There is no way around it, because, as your mapped classes are not related, if you were to return only one of the "sides" of the join, you would have no way to access the other.

In your classes, you can put Object2 as a member of Object1 all you want, but Hibernate wont populate it for you. If it is important for you to have it as a variable, you have to populate it manually yourself, as I believe you are already doing.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Thanks for the rating.
Another idea: use native SQL with SQLQuery, and then addEntity / addJoin

Code:

sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, DOG_ID, D_ID, D_NAME FROM CATS c, DOGS d WHERE c.DOG_ID = d.D_ID")
 .addEntity("cat", Cat.class)
 .addJoin("cat.dog");

jtmelton · **Joined:** Sat Jan 03, 2009 12:32 pm **Posts:** 3

Thanks gonzao_diaz,

Your last suggestion is actually what I ended up using. It works fairly well, though I wish I could change the DB to have keyed relationships. Oh well.

Thanks.

petitth · **Joined:** Tue Dec 14, 2010 9:40 am **Posts:** 2

Hello,
I'm trying to do something similar.
I'd like to get a list of beans Order, each one containing a Customer bean.
Used method is an SQL query, with addEntity (and addJoin if needed), without declaring associations into XML files.

Hibernate version: v3.1.3
Name and version of the database you are using: DB2

1) If I try something this way, I get a list of arrays containing 2 objets for each row.

Code:

sess.createSQLQuery("SELECT c.*, m.*  FROM CATS c, CATS m WHERE c.MOTHER_ID = c.ID")
.addEntity("cat", Cat.class)
.addEntity("mother", Cat.class)

2) This way I'm using a bean called OrderDisplay which contains all the datas from Order + an instance of Customer.

Code:

StringBuffer sql = new StringBuffer();
sql.append("SELECT {ORDER.*},{CUSTOMER.*} "); 
sql.append("FROM SCHEMA.ORDER ORDER, ");
sql.append("SCHEMA.CUSTOMER CUSTOMER ");
sql.append("WHERE ORDER.ID_CUSTOMER = CUSTOMER.ID_CUSTOMER ");
SQLQuery query = getSession().createSQLQuery(sql.toString());
query.addEntity("ORDER",OrderDisplay.class);
query.addJoin("CUSTOMER","ORDER.CUSTOMER");
List list = query.list();

my XML file OrderDisplay contains the following line :

Code:

result :

Quote:

could not resolve property: CUSTOMER of: bla.bla.bla.OrderDisplay

Any idea will be welcomed.
Thank you for reading me.

Tom