Native SQL and addJoin

carm · **Joined:** Mon Mar 06, 2006 5:20 am **Posts:** 7

Hibernate version: 3.0.5
Name and version of the database you are using: oracle 10.2.0.1.0

Hi, I'm having an issue with the addJoin method when using native SQL.
I am not sure I fully understand how it works...

I use the following code (simplified for the sake of clarity) :

Code:

Query query = session.createSQLQuery("select {f.*}, {c.*} from FURNISHER f join CONTACT c on c.idFurnisher = f.idFurnisher and c.name=? where f.name = ?");
query.addEntity("f", foo.bar.Furnisher);
query.addjoin("c", "f.contacts");

The relevant part of the mapping file is the following :

Code:

   <class name="foo.bar.Furnisher"
      table="FURNISHER">
      <id name="idFurnisher" column="IDFURNISHER" type="long">
         <generator class="native">
            <param name="sequence">TESTSEQ</param>
         </generator>
      </id>
      <set name="contacts" table="CONTACT" lazy="false"
         inverse="true">
         <key column="IDFURNISHER"></key>
         <one-to-many class="foo.bar.Contact" />
      </set>
      <property name="idFurnisher" column="IDFURNISHER" type="long" not-null="true" />
      <property name="name" column="NAME" type="string" not-null="true" />
   </class>

   <class name="foo.bar.Contact" table="CONTACT">
      <id name="idContact" column="IDCONTACT" type="long">
         <generator class="native">
            <param name="sequence">TESTSEQ</param>
         </generator>
      </id>
      <property name="idFurnisher" column="IDFURNISHER" type="long" not-null="true" />
      <property name="name" column="NAME" type="string" not-null="true" />
   </class>

The db schema is set accordingly and I already manipulate it without any problem.
Now, I was expecting hibernate to output a single object (of type Furnisher) with a join to several Contact items.
Of course, if I am here, it does not work, it outputs a list of results with as many results as there are valid Furnisher/Contact couples.
For each of these couples, the furnisher does have the correct linked contacts loaded but there still is far too many results (due to the many Contacts linked to the furnisher generating each one line of result as one would expect in standard sql).

So, I tried to remove the "{c.*}" to use the following request : "select {f.*} from FURNISHER f join CONTACT c on c.idFurnisher = f.idFurnisher and c.name=? where f.name = ?"
But the query.list() fails with an exception : unknown alias. It seems it is the "c" in the addjoin that is unknown because it is not in the projections of the request.

I tried to use the same above request without the addJoin. Of course, this time, I get only one object but the "c.name=?" condition is not verified anymore.

Am I missing something ? or is there an other way to load an object with joined items with a condition on the joined items that is not defined in the mapping as a relation between the two objects ?

And if you are wondering why I do not use HQL instead of this .. I tried, but I had an other problem. With a request like the following : "select f from Furnisher join f.contacts c where f.name=? and c.name=?", if there is a matching contact, everything is alright, but if there aren't, hibernate does not seem to return any object. What I would like is to get the furnisher if there is one and load the matching contacts if there are some (I want a furnisher with no contacts if there are none).

Thx in advance and please ask if you need further details on the situation.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

There is no equivalent of setResultTransformer for queries, though you could switch to using critieria to take advantage of that method.

Can you not just ignore the joins and let hibernate create proxies? Sure it'll be a load more selects, but only if the joined objects are referenced.

Finally, you could try HQL along these lines:

Code:

select f
from Furnisher f
left join fetch f.Contacts c
where f.name = :furnisherName
and (size(f.Contacts) = 0 or c.name = :contactName)

That's probably not the right syntax, but you should get the idea: don't compare on f.Contacts if there are none.

carm · **Joined:** Mon Mar 06, 2006 5:20 am **Posts:** 7

First about the HQL you proposed, it does not work. if there are contacts but none matching the criteria : c.name = ?, then it will return nothing.
In fact the (simplified) gnerated sql is the following :

Code:

select ... from FURNISHER f left outer join CONTACT c on f.idFurnisher = c.idFurnisher where .. and ((select count(c2.idFurnisher) from CONTACT c2 where f.idFurnisher=c2.idFurnisher) = 0 or c.name = ?)

Since there is always a contact (in my test case), the count is always <> 0 and is irrelevant. What I would have needed is :

Code:

... and ((select count(c2.idFurnisher) from CONTACT c2 where f.idFurnisher=C2.idFurnisher and c2.name = ?) or c.name =?)

I do not know how it could be possible to translate such a query in HQL.

Second ...

tenwit wrote:

Can you not just ignore the joins and let hibernate create proxies? Sure it'll be a load more selects, but only if the joined objects are referenced.

I did not understand this one .. How will the filter (c.name) been applied ? I would need to set a filter in the mapping or an alternate relationship between the two object with a filter, wouldn't I ?

Third ...
I can not really use criterias as this is meant to be part of a framework that needs to be configured with ORM style requests ... the queries being executed within the framework outisde of app dev's reach ..

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I must be misinterpreting your original HQL. Can you describe the query you want in English? Guessing from the SQL in the last post, you want

Code:

get furnishers that
 1) have at least one contact and
 2) either
    2a) at least one of those contacts is called contactName or
    2b) the furnisher is called contactName

Am I close?

carm · **Joined:** Mon Mar 06, 2006 5:20 am **Posts:** 7

not exactly, but thank you for insisting, here is what I want:
(there should be a post rating for helpers even if the solution is not yet found :))

Code:

 1) The furnisher that matches the where clause concerning the furnisher whatever might be the outcome of the where clause concerning the contacts (name = furnisherName).
 2) The contacts linked to the furnisher that matches the c.name=? if there are some (name = contactName). An empty relation otherwise.

Hope it better explains what I want.

On a side note, I was working on the idea you proposed :

Code:

select f 
from Furnisher f
left join fetch f.Contacts c
where f.name = :furnisherName
and (size(f.Contacts) = 0 or c.name = :contactName)

Even if it does not work, an idea popped from some desperate neurone, size() uses a collection as an argument, AFAIK, the result of a request is a collection, so what about the following :

Code:

select f
from Furnisher f
left join fetch f.Contacts c
where f.name = :furnisherName
and 
 (size(select c1 from Contact as c1 where c1.furnisherId = f.furnisherId and c1.name = ?) = 0 
 or c.name = :contactName)

unfortunatly, it does not seem to work, the line and column point to the "select c1" :

Code:

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: select

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Aha. You want the furnisher no matter what, and the rest of the query is concerned with filtering out some of the furnisher's contacts. I've never used queries like that. To my mind, if a furnisher has a set of contacts, then that's that, you shouldn't filter the in-furnisher set. You should instead write a query that returns a list of contacts, filtered by furnisher and other parameters. But you can do what you're trying to do.

There are two easy options available:

1) Work with Object[2] returns, as you found in your original post.
2) Put a filter on the contacts collection. In the 3.0.5 ref docs, this is covered in section 18.

Even though these will both work for you, I'd still recommend separate queries: one to find the furnisher, and another (taking the furnisher as a parameter) to find the contacts of interest. Or even just filtering the furnisher's contacts in java code. But that's just my preference, I don't like the idea of furnisher.getContacts() returning only a subset of the furnisher's contacts.

carm · **Joined:** Mon Mar 06, 2006 5:20 am **Posts:** 7

K, even though this does not solve the problem, you helped close the issue, thx.