Hi
I was checking to see what the syntax would be for doing joins against multiple tables.

Please start reading the documentation. You don't join tables with Hibernate (thats what happens "under the hood"), you join class level associations. We have a whole chapter about HQL (Hibernates extension to SQL) in the reference documentation.

To do a join, I assume you already had your hibernate mapping converted to java classes with getters and setters.

If so, this is a method of joining tables.

Session s;
try
{
s = getTestSession();
Query q = sess.createQuery("select ent, cc from org.hibernate.Datablock as db inner join db.childTables as cc inner join db.entitys ent where db.datablockName = '"+ myDatablockName+"' and cc.type='stuff'");

pes = q.list();
if (pes.size() > 0)
{
iter = pes.listIterator();
while (iter.hasNext())
{
Object[] foo = (Object[])iter.next();
Entity ent = (Entity)foo[0];
ChildTable cc = (ChildTable)foo[1];
if (!ent.getSomeData().equalsIgnoreCase("You bet"))
{
String thisName = cc.getSomeName();
String formula = cc.getFormula();
}
}
}

}

Basically, the table Datablock is a parent to the other two tables. They have a relationship. So the relationship is know internally. You don't have to explicitly do a normal join statement where you would say tableA.id = tableB.id. The java class should just know how to link them, if you did your hibernate mapping correctly.

Once you join them you can access them like I did above. Really simple, very nice.

Hope this helps.

Jeff

Hi,

Can someone confirm me that the use of parantheses is irrelevant
in the "from" clause of the query?

For example, if I use :

from eg.Cat as cat
join cat.mate as mate
left join cat.kittens as kitten

would I get the same result as when using:

from (eg.Cat as cat
join cat.mate as mate)
left join cat.kittens as kitten

or:

from eg.Cat as cat
join (cat.mate as mate
left join cat.kittens as kitten)

Thank you,
Teodor

It's irrevelant since hibernate looks a the collection, not the lecft side of the join.

hi,
I need to join 3 tables. They don't have a foreign-key relationship. How can i do the inner or outer join on the tables whose mapping files or value objects don't have a relationship. Can you please let me know as soon as possible. i am close to missing the deadline. i appreciate if you can tell me what you found out even if you didn't find a solution.

thx in advance

You cannot. Outer joins require the mapping of the relationships.

so.... can you join a table that is represented by a persistent class with a table that isn't represented by a persistent class? What I have is this:
A Roles table (persistent class = Roles), a userId, and a join table (UserRoles; no associated persistent class) that has various roleIds for a userId. So, I'm coming in knowing my userId and I want to retrieve a List of Role objects by joining the Roles table with the UserRoles table where Roles.roleId = UserRoles.roleID and UserRoles.userId = userId.

Scott

I think you'll find your answer in the docos. Read up on many-to-many. (BTW, I've implemented this exact schema many times and it works just fine... you are but a few lines of code away). :)