Hibernate version: 3.2.4ga (HSQLDialect)

I'm using native SQL queries and they are not behaving as I expect them to. In particular addJoin() (in Java) or <return-join/> (in XML named query) don't do what I want them to do and in fact don't act any differently than addEntity.

I've been using the Hibernate supplied test case org.hibernate.test.sql.hand.query.NativeSQLQueriesTest.testSQLQueryInterface()
as provided in the release source code.

I can see no difference in the result of:

l = s.createSQLQuery( getOrgEmpPersonSQL() )
.addEntity("org", Organization.class)
.addJoin("emp", "org.employments")
.addJoin("pers", "emp.employee")
.list();

versus this:

l = s.createSQLQuery( getOrgEmpPersonSQL() )
.addEntity("org", Organization.class)
//.addJoin("emp", "org.employments")
//.addJoin("pers", "emp.employee")
.addEntity("emp", Employment.class)
.addEntity("pers", Person.class)
.list();

I expected the former to return a list of Organization objects and the latter to return a list of Object arrays, each containing 3 objects, Organization, Employment, and Person. However, in both cases I get the same result, which is a list of Object arrays as expected in the latter, and in both cases the org.employments and emp.employee have been filled in without further selects.

Is this a bug or is this the expected behavior? If this is not a bug, how can I get the query to return just a list of eagerly initialized Organization objects?

FYI, getOrgEmpPersonSQL() returns


Thanks,
=Jeremy=

I opened a bug in JIRA, ID HHH-2381 about this. Seems that this is how it has always worked. I think it is wrong because it is a "feature" without a difference, but I'm only one voice. If you agree, please vote for it on the JIRA here: http://opensource.atlassian.com/project ... e/HHH-2831

And also leave a comment about what you want and why.

Hi, Jeremy, what about the issue? I'm currently facing a similar problem. Perhaps you - or anybody out there - know a solution for this.

I've got a class with a multi-valued attribute, like this:

public class Person
{
int id;
Set<String> names;
}

In the database you end up with two tables:
- "person" with the field "id"
- "person_names" with the fields "id" and "name"

Now I want to use native SQL to do the following query.

SELECT person.id AS {p.id}, person_names.name AS {p.names}
FROM person LEFT OUTER JOIN person_names USING (id)

The SQL query result would be
1 - christian
1 - paolo
2 - stefano
2 - marco

When I make the following call... createSQLQuery(sql).addEntity("p", Person.class)
... I would expect to retrieve two objects

(1, [christian,paolo])
(2, [stefano,marco])

Actually this does not work - and obviously addJoin will also not help because there's actually no other entity joined... Is there a solution for this available?

Thanks for your help!
Christian

RTFM, Sections 16.1.3 Native SQL: Handling Associations and Collections and 16.1.4 Native SQL: Returning Multiple Entities

http://www.hibernate.org/hib_docs/refer ... l#d0e13095

and also the faq "Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?" at http://www.hibernate.org/117.html#A12 and perhaps back to Section 16.1.1 Native SQL: Scalar Queries

http://www.hibernate.org/hib_docs/refer ... l#d0e13009

Why are you using Native SQL to begin with? You could map the Person class and use HQL and it will work much better. See Section 8.2 Component Mapping: Collections of dependent objects http://www.hibernate.org/hib_docs/refer ... ollections


The short answer is AFAIK no query will return exactly the result format you "expect" above. You could, however, use an HQL query to return fully initialized Person objects; wouldn't that be better?

=Jeremy=