Are left outer joins supported where no association exists?

tima · **Joined:** Wed Oct 04, 2006 2:47 am **Posts:** 12

I'd like to replicate the following SQL in HQL:

Code:

select patient.name, customer.name
from test_acts act
left outer join test_participations ppatient on act.act_id=ppatient.act_id and ppatient.type='patient'
left outer join test_participations pcustomer on act.act_id=pcustomer.act_id and pcustomer.type='customer'
left outer join test_entities patient on patient.entity_id = ppatient.entity_id
left outer join test_entities customer on customer.entity_id = pcustomer.entity_id

However the following HQL produces a NullPointerException:

Code:

select patient.name, customer.name 
from test.Act as act 
left outer join act.participations ppatient with ppatient.type='patient' 
left outer join act.participations pcustomer with pcustomer.type='customer' 
left outer join test.Entity patient with patient.id=ppatient.entityId 
left outer join test.Entity customer with customer.id=pcustomer.entityId 

The act.participations is a set of test.Participation instances. The test.Entity class is referred to in the left outer joins as there is no explicit association between Participation and Entity.

I can't find any reference that suggests this is supported syntax -
is there any way to replicate the above SQL without resorting to nested selects?

Thanks,

Tim

Corresponding classes

Code:

package test;
...
public class IMObject {
    public void setId(long id);
    public long getId();
}

public class Entity extends IMObject {
    public void setName(String name);
    public String getName();
}

public class Act extends IMObject {
    public void setParticipations(Set<Participation> participations);
    public Set<Participation> getParticipations();
}

public class Participation extends IMObject {
    public void setActId(long actId);
    public long getActId();
    public void setEntityId(long entityId);
    public long getEntityId();
    public void setType(String type);
    public String getType();
}

Hibernate version:

3.2.1 GA

Mapping documents:

Code:

   <hibernate-mapping>

        <class name="test.Entity" table="test_entities">
            <id name="id" type="long" unsaved-value="-1">
                <column name="entity_id" index="entity_id_idx"/>
                <generator class="native"/>
            </id>
            <property name="name" access="property" column="name" type="string"
                      length="100" index="entity_name_idx"/>
        </class>

        <class name="test.Act" table="test_acts">
            <id name="id" type="long" unsaved-value="-1">
                <column name="act_id" index="act_id_idx"/>
                <generator class="native"/>
            </id>
            <set name="participations" access="property" inverse="true"
                 cascade="all,delete-orphan" batch-size="50">
                <key column="act_id" not-null="true"/>
                <one-to-many class="test.Participation"/>
            </set>
        </class>

        <class name="test.Participation" table="test_participations">
            <id name="id" type="long" unsaved-value="-1">
                <column name="participation_id" index="participation_id_idx"/>
                <generator class="native"/>
            </id>
            <property name="entityId" access="property" type="long"
                      column="entity_id" index="participation_entity_id_idx"/>
            <property name="actId" access="property" type="long"
                      column="act_id" index="participation_act_id_idx"/>
            <property name="type" access="property" column="type" type="string"
                      length="100"/>
        </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

        
String hql = "select act "
    + "from test.Act as act "
    + "left outer join act.participations ppatient with ppatient.type='patient' "
    + "left outer join act.participations pcustomer with pcustomer.type='customer' "
    + "left outer join test.Entity patient";
    
Query query = session.createQuery(hql);
query.list();

Full stack trace of any exception that occurs:

Code:

java.lang.NullPointerException
   at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:312)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3275)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3067)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2945)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:688)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:544)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
   at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
   at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
   at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
   at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
   at test.Main.main(Main.java:49)

Name and version of the database you are using:

MySQL 4.1

The generated SQL (show_sql=true):

none

Debug level Hibernate log excerpt:

Code:

10:50:52,468 DEBUG QueryPlanCache,main:70 - unable to locate HQL query plan in cache; generating (select patient.name, customer.name from test.Act as act left outer join act.participations ppatient with ppatient.type='patient' left outer join act.participations pcustomer with pcustomer.type='customer' left outer join test.Entity patient with patient.id=ppatient.entityId left outer join test.Entity customer with customer.id=pcustomer.entityId)
10:50:52,593 DEBUG QueryTranslatorImpl,main:246 - parse() - HQL: select patient.name, customer.name from test.Act as act left outer join act.participations ppatient with ppatient.type='patient' left outer join act.participations pcustomer with pcustomer.type='customer' left outer join test.Entity patient with patient.id=ppatient.entityId left outer join test.Entity customer with customer.id=pcustomer.entityId
10:50:52,656 DEBUG AST,main:266 - --- HQL AST ---
 \-[QUERY] 'query'
    \-[SELECT_FROM] 'SELECT_FROM'
       +-[FROM] 'from'
       |  +-[RANGE] 'RANGE'
       |  |  +-[DOT] '.'
       |  |  |  +-[IDENT] 'test'
       |  |  |  \-[IDENT] 'Act'
       |  |  \-[ALIAS] 'act'
       |  +-[JOIN] 'join'
       |  |  +-[LEFT] 'left'
       |  |  +-[OUTER] 'outer'
       |  |  +-[DOT] '.'
       |  |  |  +-[IDENT] 'act'
       |  |  |  \-[IDENT] 'participations'
       |  |  +-[ALIAS] 'ppatient'
       |  |  \-[WITH] 'with'
       |  |     \-[EQ] '='
       |  |        +-[DOT] '.'
       |  |        |  +-[IDENT] 'ppatient'
       |  |        |  \-[IDENT] 'type'
       |  |        \-[QUOTED_STRING] ''patient''
       |  +-[JOIN] 'join'
       |  |  +-[LEFT] 'left'
       |  |  +-[OUTER] 'outer'
       |  |  +-[DOT] '.'
       |  |  |  +-[IDENT] 'act'
       |  |  |  \-[IDENT] 'participations'
       |  |  +-[ALIAS] 'pcustomer'
       |  |  \-[WITH] 'with'
       |  |     \-[EQ] '='
       |  |        +-[DOT] '.'
       |  |        |  +-[IDENT] 'pcustomer'
       |  |        |  \-[IDENT] 'type'
       |  |        \-[QUOTED_STRING] ''customer''
       |  +-[JOIN] 'join'
       |  |  +-[LEFT] 'left'
       |  |  +-[OUTER] 'outer'
       |  |  +-[DOT] '.'
       |  |  |  +-[IDENT] 'test'
       |  |  |  \-[IDENT] 'Entity'
       |  |  +-[ALIAS] 'patient'
       |  |  \-[WITH] 'with'
       |  |     \-[EQ] '='
       |  |        +-[DOT] '.'
       |  |        |  +-[IDENT] 'patient'
       |  |        |  \-[IDENT] 'id'
       |  |        \-[DOT] '.'
       |  |           +-[IDENT] 'ppatient'
       |  |           \-[IDENT] 'entityId'
       |  \-[JOIN] 'join'
       |     +-[LEFT] 'left'
       |     +-[OUTER] 'outer'
       |     +-[DOT] '.'
       |     |  +-[IDENT] 'test'
       |     |  \-[IDENT] 'Entity'
       |     +-[ALIAS] 'customer'
       |     \-[WITH] 'with'
       |        \-[EQ] '='
       |           +-[DOT] '.'
       |           |  +-[IDENT] 'customer'
       |           |  \-[IDENT] 'id'
       |           \-[DOT] '.'
       |              +-[IDENT] 'pcustomer'
       |              \-[IDENT] 'entityId'
       \-[SELECT] 'select'
          +-[DOT] '.'
          |  +-[IDENT] 'patient'
          |  \-[IDENT] 'name'
          \-[DOT] '.'
             +-[IDENT] 'customer'
             \-[IDENT] 'name'

10:50:52,656 DEBUG ErrorCounter,main:68 - throwQueryException() : no errors
10:50:52,718 DEBUG HqlSqlBaseWalker,main:111 - select << begin [level=1, statement=select]
10:50:52,765 DEBUG FromElement,main:109 - FromClause{level=1} :  test.Act (act) -> act0_
10:50:52,765 DEBUG FromReferenceNode,main:51 - Resolved :  act -> act0_.act_id
10:50:52,781 DEBUG FromElement,main:532 - handling property dereference [test.Act (act) -> participations (class)]
10:50:52,781 DEBUG DotNode,main:507 - getDataType() : participations -> org.hibernate.type.SetType(test.Act.participations)
10:50:52,781 DEBUG FromElementFactory,main:360 - createEntityAssociation() : One to many - path = act.participations role = test.Act.participations associatedEntityName = test.Participation
10:50:52,781 DEBUG FromElement,main:109 - FromClause{level=1} :  test.Participation (ppatient) -> participat1_
10:50:52,781 DEBUG FromClause,main:233 - addJoinByPathMap() : act.participations -> test_participations participat1_
10:50:52,796 DEBUG DotNode,main:267 - dereferenceCollection() : Created new FROM element for act.participations : test_participations participat1_
10:50:52,796 DEBUG FromReferenceNode,main:51 - Resolved :  act.participations -> .
10:50:52,812 DEBUG FromReferenceNode,main:51 - Resolved :  ppatient -> participat1_.participation_id
10:50:52,812 DEBUG FromElement,main:532 - handling property dereference [test.Participation (ppatient) -> type (class)]
10:50:52,812 DEBUG DotNode,main:507 - getDataType() : type -> org.hibernate.type.StringType@21b42f
10:50:52,812 DEBUG FromReferenceNode,main:51 - Resolved :  ppatient.type -> participat1_.type
10:50:52,828 DEBUG HqlSqlWalker,main:332 - handleWithFragment() : -- with clause --
 \-[WITH] SqlNode: 'with'
    \-[EQ] BinaryLogicOperatorNode: '='
       +-[DOT] DotNode: 'participat1_.type' {propertyName=type,dereferenceType=4,propertyPath=type,path=ppatient.type,tableAlias=participat1_,className=test.Participation,classAlias=ppatient}
       |  +-[ALIAS_REF] IdentNode: 'participat1_.participation_id' {alias=ppatient, className=test.Participation, tableAlias=participat1_}
       |  \-[IDENT] IdentNode: 'type' {originalText=type}
       \-[QUOTED_STRING] LiteralNode: ''patient''

10:50:52,859 DEBUG HqlSqlWalker,main:322 - createFromJoinElement() : -- join tree --
 \-[JOIN_FRAGMENT] FromElement: 'test_participations participat1_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=ppatient,role=test.Act.participations,tableName=test_participations,tableAlias=participat1_,origin=test_acts act0_,colums={act0_.act_id ,className=test.Participation}}

10:50:52,859 DEBUG FromReferenceNode,main:51 - Resolved :  act -> act0_.act_id
10:50:52,859 DEBUG FromElement,main:532 - handling property dereference [test.Act (act) -> participations (class)]
10:50:52,859 DEBUG DotNode,main:507 - getDataType() : participations -> org.hibernate.type.SetType(test.Act.participations)
10:50:52,859 DEBUG FromElementFactory,main:360 - createEntityAssociation() : One to many - path = act.participations role = test.Act.participations associatedEntityName = test.Participation
10:50:52,859 DEBUG FromElement,main:109 - FromClause{level=1} :  test.Participation (pcustomer) -> participat2_
10:50:52,859 DEBUG FromClause,main:233 - addJoinByPathMap() : act.participations -> test_participations participat2_
10:50:52,859 DEBUG DotNode,main:267 - dereferenceCollection() : Created new FROM element for act.participations : test_participations participat2_
10:50:52,859 DEBUG FromReferenceNode,main:51 - Resolved :  act.participations -> .
10:50:52,859 DEBUG FromReferenceNode,main:51 - Resolved :  pcustomer -> participat2_.participation_id
10:50:52,859 DEBUG FromElement,main:532 - handling property dereference [test.Participation (pcustomer) -> type (class)]
10:50:52,859 DEBUG DotNode,main:507 - getDataType() : type -> org.hibernate.type.StringType@21b42f
10:50:52,859 DEBUG FromReferenceNode,main:51 - Resolved :  pcustomer.type -> participat2_.type
10:50:52,859 DEBUG HqlSqlWalker,main:332 - handleWithFragment() : -- with clause --
 \-[WITH] SqlNode: 'with'
    \-[EQ] BinaryLogicOperatorNode: '='
       +-[DOT] DotNode: 'participat2_.type' {propertyName=type,dereferenceType=4,propertyPath=type,path=pcustomer.type,tableAlias=participat2_,className=test.Participation,classAlias=pcustomer}
       |  +-[ALIAS_REF] IdentNode: 'participat2_.participation_id' {alias=pcustomer, className=test.Participation, tableAlias=participat2_}
       |  \-[IDENT] IdentNode: 'type' {originalText=type}
       \-[QUOTED_STRING] LiteralNode: ''customer''

10:50:52,859 DEBUG HqlSqlWalker,main:322 - createFromJoinElement() : -- join tree --
 \-[JOIN_FRAGMENT] FromElement: 'test_participations participat2_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=pcustomer,role=test.Act.participations,tableName=test_participations,tableAlias=participat2_,origin=test_acts act0_,colums={act0_.act_id ,className=test.Participation}}

10:50:52,875 DEBUG LiteralProcessor,main:121 - setSQLValue() test.Entity -> null
10:50:52,875 DEBUG FromReferenceNode,main:51 - Resolved :  test.Entity -> null

Siegbert · **Joined:** Mon Jan 05, 2004 11:04 am **Posts:** 16

Hi,

why your POJOs doesnt have
attributes defined (only for id and name)?

You have only defined the getters and setters.

Please put the attributes to your POJOs and initialize
the set with new HashSet().

tima · **Joined:** Wed Oct 04, 2006 2:47 am **Posts:** 12

Siegbert wrote:

Hi,

why your POJOs doesnt have
attributes defined (only for id and name)?

You have only defined the getters and setters.

Please put the attributes to your POJOs and initialize
the set with new HashSet().

Just did it that way to cut down the length of the post. Here they are in full:

Code:

package test;

public class IMObject {

    private long id;

    public void setId(long id) {
        this.id = id;
    }

    public long getId() {
        return id;
    }
}

public class Entity extends IMObject {

    private String name;

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }
}

public class Act extends IMObject {

    private Set<Participation> participations = new HashSet<Participation>();

    public void setParticipations(Set<Participation> participations) {
        this.participations = participations;
    }

    public Set<Participation> getParticipations() {
        return participations;
    }
}


public class Participation extends IMObject {

    private long actId;

    private long entityId;

    private String type;

    public void setActId(long actId) {
        this.actId = actId;
    }

    public long getActId() {
        return actId;
    }

    public void setEntityId(long entityId) {
        this.entityId = entityId;
    }

    public long getEntityId() {
        return entityId;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getType() {
        return type;
    }
}