Criteria Search - JOIN - Query Creation

ms0516 · **Joined:** Tue Apr 22, 2008 8:29 am **Posts:** 3

Not sure where to begin for creating a Criteria query for this situation. I consulted the documentation but still haven't found a solution.

This is the table structure.

Group has a non-primary foreign key relations to both Vector and Name.

Code:

+---------+
|Vector
|---------
|v_id*
|v_ds
|...
+---------+
   |
   |
   |
   ^
+---------+
|Group
|+---------+
|g_id*
|v_id (fk)
|n_id (fk)
|...
+---------+
   V
   |
   |
   |
+---------+
Name
+---------+
|n_id*
|n_ds
|...
+---------+

A native SQL query already exists for an older application that returns
the desired result:

Code:

select v.v_id
from vector v, group g, name n 
where g.n_id = n.n_id
and a.v_id=v.v_id;

My question is how to execute this as a Hibernate Criteria query to return the same result as the Native SQL query?

Useful Information:

Using Hibernate 3.0 w/o annotations and J2SE 1.4

Database: Oracle 10g

Vector.hbm.xml and Name.hbm.xml do not have any mappings to Group. But Group has mappings to both Vector and Name.

Here is a snippet from Group.hbm.xml wrt Name and Vector mappings

Code:

<many-to-one
  name="name_ref"
  column="n_id"
  class="Name"
  not-null="true">
</many-to-one>

<many-to-one
  name="vector_ref"
  column="v_id"
  class="Vector"
  not-null="false">
</many-to-one>   


class Group {
  private Integer id;
  private Vector vector_ref;
  private Name name_ref;

  // with appropriate getters/setters
}

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

ms0516,

I think your problem is not how to write the Criteria Query, which is relatively simple in this case, but your mappings and object design being wrong.

I would do it this way.

- The Vector has a set of Groups, and in the mapping files that is defined as a one-to-many relationship, from Vector to Groups, bidirectional so that you can call Group.getVector().

- The Name class has also set of Groups. There has to be a bidirectional many-to-one relationship between Groups and name, so that you can call both Name.getGroups() and group.getName().

The tricky conceptual part of your problem, very common, is to see "Groups" as a table by its own right, rather than a mere connection table.

thinkpad · **Joined:** Fri Apr 11, 2008 1:48 am **Posts:** 36

You are searching for a possibility to create many-to-many relations (theta join) with an Criteria object. I have got the same problem :-(

I did read in the book: "Hibernate in Action" (2004: Bauer, King) that such theta joins are not possible. I hope that we got a solution today?!

My Problem:

Entity T1 with a Relation to T1_T2 (only IDs) Objects
Entity T2 with a Relation to T1_T2 (only IDs) Objects

How can I create a Critera which has restrictions on T1 and restricitions to T2. T2 should be joined first to T1 with the relation in T1_T2.