-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
Venkat.Kalkunda
 Post subject: Newbie - Writing a hql
PostPosted: Thu Mar 01, 2007 4:33 pm 
Newbie

Joined: Thu Mar 01, 2007 4:25 pm
Posts: 2
Writing an hql for the following SQL

Quote:
SELECT
RT.N_RELATIONSHIP_TYPE,
PR.I_PARTY_RELATIONSHIP_KEY,
PR.D_RELATIONSHIP
FROM PICADMNT.PICTBT_PARTY_RELATIONSHIP PR
INNER JOIN PICADMNT.PICTBD_RELATIONSHIP_TYPE RT
ON PR.I_RELATIONSHIP_TYPE_KEY = RT.I_RELATIONSHIP_TYPE_KEY
INNER JOIN PICADMNT.PICTBT_PARTY_ROLE PRL1
ON PR.I_PARTY_ROLE1_KEY = PRL1.I_PARTY_ROLE_KEY
INNER JOIN PICADMNT.PICTBT_PARTY_ROLE PRL2
ON PR.I_PARTY_ROLE2_KEY = PRL2.I_PARTY_ROLE_KEY

Where
PRL1.I_PARTY_ROLE_KEY = 1000
or PRL2.I_PARTY_ROLE_KEY = 1000


This is the query I am trying to emulate using hql. This is what I have now,

Quote:

select new com.fbfs.cp.pic.prdmgmt.dto.RelationshipDto(
RELATIONTYPE.N_RELATIONSHIP_TYPE, RELATIONSHIP.I_PARTY_RELATIONSHIP_KEY,
RELATIONSHIP.D_RELATIONSHIP)

from
PICTBT_PARTY_RELATIONSHIP RELATIONSHIP
join RELATIONSHIP.partyRelationshipType RELATIONTYPE
join RELATIONSHIP.role1 ROLE1 on RELATIONSHIP.I_PARTY_ROLE1_KEY = ROLE1.I_PARTY_ROLE_KEY
join RELATIONSHIP.role2 ROLE2 on RELATIONSHIP.I_PARTY_ROLE2_KEY = ROLE2.I_PARTY_ROLE_KEY
where
ROLE1.I_PARTY_ROLE_KEY = ?
and ROLE1.I_PARTY_ROLE_KEY = ?



But when I run this query I get the following error message.

Quote:

org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:293: unexpected token: on org.springframework.test.AbstractTransactionalSpringContextTests endTransaction
INFO: Rolled back transaction after test execution



Can somebody tell me what is wrong if the syntax and what is the right syntax to emulate the SQL?

Thanks a lot in advance
Venkat
Top
 Profile  
 
chuckc
 Post subject:
PostPosted: Thu Mar 01, 2007 8:19 pm 
Newbie

Joined: Thu Mar 01, 2007 6:58 pm
Posts: 9
We need to see the underlying classes and mapping files. I do notice that your original query uses different tables in the joins, but you are using a single table in your HQL query. Also, you are using column names in the your hql, you should only be using method names and the . notation for embedded properties.

Ex.

Code:

class Foo {
    Long id;
    String foo;
    Bar bar;

... assume getters and setters
}

class Bar {
    Long id;
    String bar;
}

class FooBar {
    String foo;
    String bar;

    FooBar(String foo, String bar)
}

Your hql would be

select new FooBar(f.foo, b.bar) from Foo f join f.bar b;


This is the best I can do without seeing your class structure and mapping files.
Top
 Profile  
 
steve
 Post subject:
PostPosted: Fri Mar 02, 2007 4:21 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
HQL does not support "ad hoc" joins. As such, it does not even recognize ON as special. Instead, the joins should defined in your mappings via associations.
Top
 Profile  
 
Venkat.Kalkunda
 Post subject:
PostPosted: Fri Mar 02, 2007 11:14 am 
Newbie

Joined: Thu Mar 01, 2007 4:25 pm
Posts: 2
I have my mapping set up with all the joins and everything.

Sorry about using column names, I am using java property name.

Here is my mapping file for the PICTBT_PARTY_ROLE
Code:

<hibernate-mapping>
    <class name="com.fbfs.cp.pic.common.model.hibernate.PictbtPartyRole" table="PICTBT_PARTY_ROLE" schema="PICADMNT">
        <id name="partyRoleKey" type="java.lang.Integer">
            <column name="I_PARTY_ROLE_KEY" />
            <generator class="native"></generator>
        </id>
        <many-to-one name="party" class="com.fbfs.cp.pic.common.model.hibernate.PictbtParty" fetch="select">
            <column name="I_PARTY_KEY" not-null="true" />
        </many-to-one>
        <many-to-one name="partyRoleType" class="com.fbfs.cp.pic.common.model.hibernate.PictbdPartyRoleType" fetch="select">
            <column name="I_ROLE_TYPE_KEY" not-null="true" />
        </many-to-one>
        <property name="effectiveDate" type="java.util.Date">
            <column name="D_EFFECTIVE" length="26" not-null="true" />
        </property>
        <property name="expiredDate" type="java.util.Date">
            <column name="D_EXPIRED" length="26" not-null="true" />
        </property>
        <property name="useridEntered" type="java.lang.String">
            <column name="I_USERID_ENTERED" length="10" not-null="true" />
        </property>
        <property name="useridUpdated" type="java.lang.String">
            <column name="I_USERID_UPDATED" length="10" />
        </property>
        <property name="enteredDate" type="java.util.Date">
            <column name="D_ENTERED" length="26" not-null="true" />
        </property>
        <property name="updatedDate" type="java.util.Date">
            <column name="D_UPDATED" length="26" />
        </property>
        <set name="agreementPartyRoles" inverse="true">
            <key>
                <column name="I_PARTY_ROLE_KEY" not-null="true" />
            </key>
            <one-to-many class="com.fbfs.cp.pic.common.model.hibernate.PictbtAgreementPartyRole" />
        </set>
    </class>
</hibernate-mapping>



Here is my mapping code for PICTBT_PARTY_RELATIONSHIP
Code:
<hibernate-mapping>
    <class name="com.fbfs.cp.pic.common.model.hibernate.PictbtPartyRelationship" table="PICTBT_PARTY_RELATIONSHIP" schema="PICADMNT">
        <id name="partyRelationshipKey" type="java.lang.Integer">
            <column name="I_PARTY_RELATIONSHIP_KEY" />
            <generator class="native"></generator>
        </id>
        <many-to-one name="partyRelationshipType" class="com.fbfs.cp.pic.common.model.hibernate.PictbdRelationshipType" fetch="select">
            <column name="I_RELATIONSHIP_TYPE_KEY" not-null="true" />
        </many-to-one>
        <many-to-one name="role1" class="com.fbfs.cp.pic.common.model.hibernate.PictbtPartyRole" fetch="select">
            <column name="I_PARTY_ROLE1_KEY" not-null="true" />
        </many-to-one>
        <many-to-one name="role2" class="com.fbfs.cp.pic.common.model.hibernate.PictbtPartyRole" fetch="select">
            <column name="I_PARTY_ROLE2_KEY" not-null="true" />
        </many-to-one>
        <property name="effectiveDate" type="java.util.Date">
            <column name="D_EFFECTIVE" length="26" not-null="true" />
        </property>
        <property name="expiredDate" type="java.util.Date">
            <column name="D_EXPIRED" length="26" not-null="true" />
        </property>
        <property name="useridEntered" type="java.lang.String">
            <column name="I_USERID_ENTERED" length="10" not-null="true" />
        </property>
        <property name="useridUpdated" type="java.lang.String">
            <column name="I_USERID_UPDATED" length="10" />
        </property>
        <property name="enteredDate" type="java.util.Date">
            <column name="D_ENTERED" length="26" not-null="true" />
        </property>
        <property name="updatedDate" type="java.util.Date">
            <column name="D_UPDATED" length="26" />
        </property>
        <property name="altProducerId" type="java.lang.String">
            <column name="I_ALT_PRODUCER_ID" length="20" />
        </property>
        <property name="yearsOfPriorService" type="java.lang.Short">
            <column name="Q_YEARS_PRIOR_SERVICE"/>
        </property>
        <property name="relationshipDate" type="java.util.Date">
            <column name="D_RELATIONSHIP" length="26" />
        </property>
        <property name="relationshipExpiredDate" type="java.util.Date">
            <column name="D_RELATIONSHIP_EXPIRED" length="26" />
        </property>
    </class>
</hibernate-mapping>


Code:

public class RelationshipDto extends ProducerDto {
   
    private Integer partyRelationshipKey;   
    private Integer relationshipTypeKey;
    private String relationshipTypeName;
    private Integer partyRole1Key;
    private Integer partyRole2Key;
    private String altProducerId;
    private Integer partyKey2;
    private String party2Name;
    private Date effectiveDate;
    private Date expiredDate;
    private Date relationshipDate;
   
    private String effectiveDateString;   
    private String expiredDateString;
    private Short roleTypeKey1;
    private Short roleTypeKey2;

}


Code:
public class PictbtPartyRole  implements java.io.Serializable {


    // Fields   

     private Integer partyRoleKey;
     private PictbtParty party;
     private PictbdPartyRoleType partyRoleType;
     private Date effectiveDate;
     private Date expiredDate;
     private String useridEntered;
     private String useridUpdated;
     private Date enteredDate;
     private Date updatedDate;
}



Code:

public class PictbtPartyRelationship  implements java.io.Serializable {


    // Fields   

     private Integer partyRelationshipKey;
     private PictbdRelationshipType partyRelationshipType;
     private PictbtPartyRole role1;
     private PictbtPartyRole role2;
     private Date effectiveDate;
     private Date expiredDate;
     private String useridEntered;
     private String useridUpdated;
     private Date enteredDate;
     private Date updatedDate;
     private String altProducerId;
     private Short yearsOfPriorService;
     private Date relationshipDate;
     private Date relationshipExpiredDate;
}



Thank you Chuck
Venkat
Top
 Profile  
 
chuckc
 Post subject:
PostPosted: Fri Mar 02, 2007 3:22 pm 
Newbie

Joined: Thu Mar 01, 2007 6:58 pm
Posts: 9
Where is your mapping file and class that represents the PICTBD_RELATIONSHIP_TYPE? Is the class the RelationshipDto?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.