How to return columns from multiple joined tables to class

jbchurn · **Posted:** Wed Apr 12, 2006 1:01 am

Dear Sir,

I'm new to Hibernate and have not really into development using it.

But I have read the tutorial and some pages from the documentation.

I have a simple question which hope experience hibernate user can give some advise.

Most of the time, I need the query to return columns from multiple tables.

Sometime 2 tables, sometimes the same 2 tables join with another table, so 3 tables.

From the documentation, I saw mostly the query will return to a class that map to a table. So, as per my question above, how to make the query return columns from different table to different classes in the project?

Please advise.
Thank you.

edward.yakop · **Posted:** Wed Apr 12, 2006 2:10 am

There're multiple ways to do this:

Code:

SELECT A.ColumnA1, A.ColumnA2, B.ColumnB1, B.ColumnB3, C.ColumnC1
FROM ClassNameA AS A
LEFT JOIN A.ColumnPropertyToB AS B
LEFT JOIN B.ColumnPropertyToC AS C

This is documented as scalar queries in the hibernate reference.

Or,

Code:

SELECT A, B, C
FROM ClassNameA AS A
LEFT JOIN A.ColumnPropertyToB AS B
LEFT JOIN B.ColumnPropertyToC AS C

If you prefer to get the entity object instead.
Note: A - B and B - C must not have one-to-many or many-to-many relationship.

Regards,
Edward

jbchurn · **Posted:** Wed Apr 12, 2006 2:29 am

Hi,

I think the sql part is quite ok with me, but what I want to know is whether I need to create a separate class that holds the combination fields for the 2 or 3 tables that I join in my query.

Please advise.
Thank you.[/quote]

edward.yakop · **Posted:** Wed Apr 12, 2006 2:36 am

This post is removed as it is misleading.[/quote]

jbchurn · **Posted:** Wed Apr 12, 2006 2:45 am

Hi,

I see, if i have 5 tables, where sometimes I join 2, sometimes 3 and sometimes maybe 5 tables.. so that means I need :

. one class for tableAnB
. one class for tableAnBnC
. one class for tableAnBnCnDnE

Is that how they usually design they class?

Btw, how do people usually manage these classes? I mean if there's a change in the table, do we go to each class to change manually? Anyway we can update it faster?

Please advise.
Thank you.

edward.yakop · **Posted:** Wed Apr 12, 2006 2:54 am

Opps.. I didn't read it properly.. no no. u do not need to create each class to hold the combinations between two tables.

When u do scalar queries it will return the individual field. Otherwise, all u have to do is just navigate the object graph from the root entity. i.e. A

If performance is a concern what u can do is do Left JOIN FETCH. This will trigger hibernate to prefetch the other join. This trick can cause a performance problem by itself if hibernate has to fetch many rows. Use with caution.

Regards,
Edward

jbchurn · **Posted:** Wed Apr 12, 2006 3:01 am

Sorry, I think I'm still new to this, but I think this part is very important to me cos we need to join multiple tables and get the result quite often instead of execute separate query for each table.

Do you have an example that can illustrate to me?

Or maybe you can highlight which section of scalar queries that you refer in documentation? cos I search scalar queries and didn't find any from the documentation.

Thank you.

edward.yakop · **Posted:** Wed Apr 12, 2006 3:14 am

Hibernate example for parent/child relationship:
http://www.hibernate.org/hib_docs/v3/re ... ppings-emp
Take particular attentions between Employer and employee relationship.

To get All unique employer (current and ex) of a particular employee

Code:

SELECT DISTINCT employer
FROM Employement AS employment
INNER JOIN employment.employee AS employee
INNER JOIN employment.employer AS employer
WHERE
employee.id = 1;

Note: we have to query from employment instead of employee, because the relationship declaration between employee -> employement is missing.

Hibernate documentation for scalar queries:
http://www.hibernate.org/hib_docs/v3/re ... ing-scalar

jbchurn · **Posted:** Wed Apr 12, 2006 4:03 am

Hi,

Thanks for the resource.

HOwever, hope you can advise the following scenario.

Assuming i have department and staff table, 2 tables. A department has many staff, but staff can only in one department. One to many relationship.

I want my query to return me staff name and department name, so I need to join these 2 tables.

so normally, the sql will be

SELECT staff_name, dept_name
FROM staff, department
WHERE staff.dept_id = department.dept_id;

Below i just roughly illustrate cos I don't know how to write Hql yet.

Staff aStaff = {this will be the above query that return staff_name anddept_name}

*assuming aStaff has same fields as Staff table, which is without department name.

So, when the query execute, how would the "aStaff" hold the dept_name? My concern is there may be more tables join, where I need selected fields from each of the table. How would hibernate user normally do it?

Hope you get my question.

Please advise.
Thank you.

edward.yakop · **Posted:** Wed Apr 12, 2006 4:51 am

Ok, normally the hbm.xml of Department and Staff in this case would be

Code:

<class name="Department" table="Department">
  <id name="id">
    <generator class="native"/>
  </id>
  <property name="name"/>
  <set name="staffs">
    <key column="department_id"/>
    <one-to-many class="Staff"/>
  </set>
</class>

<class name="Staff" table="Staff">
  <id name="id">
    <generator class="native"/>
  </id>
  <property name="name"/>
  <many-to-one name="department" column="department_id" not-null="true"/>
</class>

You would be able to query by doing the following:

Code:

Query query = session.createQuery( "FROM Department" );
Iterator it = query.iterate();
while( it.hasNext() )
{
     Department dep = (Department) it.next();
     Set staffs = dep.getStaffs();
     Iterator itStaffs = staffs.iterator();
     while( itStaffs.hasNext() )
     {
             .....
     }
}

You should get the point :)

Otherwise, you can do scalar queries.

Code:

Query query = session.createQuery( "SELECT d.name, s.name FROM Department d INNER JOIN d.staffs AS s ORDER BY d.name, s.name" );
Iterator it = query.iterate();

while( it.hasNext() )
{
   Object[] results = (Object[]) it.next();
   String depName = (String) results[0];
   String staffName = (String) results[1];

   ....
}

jbchurn · **Posted:** Wed Apr 12, 2006 5:17 am

Hi,

You example was very helpful.

I think I have started to get the picture already. Basically you trying to show me after defining the relationship, inside Java coding, we'll have outer loop for the one relation and inner loop for the many relation, correct? Can you show me the case for one-to-one relationship on how to query it? Assuming 1 department has maximum 1 staff only. :P

1 more question.
Do I still need to create mapping file XXX.hbm.xml if I use scalar queries? Beause it looks like the scalar queries return to Object[] and not to specific class like department or staff.

Please advise.
Thank you.

edward.yakop · **Posted:** Wed Apr 12, 2006 5:55 am

For one to one:
- How to define the relationship in hbm.xml
I guessed u can read the reference doc yourself :P for this one. There are reasons why the reference doc's author spent a lot of time writing it.
http://www.hibernate.org/hib_docs/v3/re ... n-onetoone

- How to query it?
It will be the same to many-to-one case (i.e. Employment -> Employee or Employment -> Employer)

Scalar queries:
- Yes, you still need to define the hbm.xml and the .java files for both Department and Staff. Hibernate needs these metadata information to perform its task.

jbchurn · **Posted:** Wed Apr 12, 2006 6:05 am

Thanks Edward.

Hibernate gives me the feeling of needs to do quite a bit of mapping work coding.

edward.yakop · **Posted:** Wed Apr 12, 2006 6:09 am

It is possible to minimise the work
- Use Hibernate annotation; OR
- Construct hbm.xml by hand and use the hibernate tools to generate the java files; Or
- Use xdoclet.

jbchurn · **Posted:** Wed Apr 12, 2006 6:13 am

Thanks for the info, will look into that.

Actually I'm also looking at iBatis besides Hibernate. You know iBatis? If yes, any comment why you use Hibernate and not iBatis?

Cheers.