-->
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
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
mcaughey
 Post subject: 5 Tables 3 table join incorrect HQL
PostPosted: Thu Sep 11, 2008 12:27 pm 
Newbie

Joined: Thu Oct 26, 2006 11:50 am
Posts: 17
Location: Chesterfield, VA
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

Mapping documents:

########## Student
Code:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.caughey.tkd.data.hibernate.Student" table="Student" >
        <id name="studentId" type="java.lang.Integer">
            <column name="Student_Id" />
            <generator class="native" />
        </id>
        <many-to-one name="user" class="com.caughey.tkd.data.hibernate.User" fetch="select">
            <column name="User_Id" />
        </many-to-one>
        <property name="firstName" type="java.lang.String">
            <column name="First_Name" length="40" not-null="true" />
        </property>
        <property name="lastName" type="java.lang.String">
            <column name="Last_Name" length="40" not-null="true" />
        </property>
        <property name="emailAddress" type="java.lang.String">
            <column name="Email_Address" />
        </property>
        <property name="emailVerifyCode" type="java.lang.String">
            <column name="Email_Verify_Code" length="12" />
        </property>
        <property name="emailVerified" type="java.lang.Boolean">
            <column name="Email_Verified" />
        </property>
        <property name="active" type="java.lang.Boolean">
            <column name="Active" />
        </property>
        <property name="tiger" type="java.lang.Boolean">
            <column name="Tiger" />
        </property>
        <set name="users" inverse="true">
            <key>
                <column name="Student_Id" />
            </key>
            <one-to-many class="com.caughey.tkd.data.hibernate.User" />
        </set>
        <set name="studentDates" inverse="true" fetch="join" lazy="false">
            <key>
                <column name="Student_Id" not-null="true" />
            </key>
            <one-to-many class="com.caughey.tkd.data.hibernate.StudentDate" />
        </set>
        <set name="studentContacts" inverse="true" fetch="join" lazy="false">
            <key>
                <column name="Student_Id" not-null="true" />
            </key>
            <one-to-many class="com.caughey.tkd.data.hibernate.StudentContact" />
        </set>
        <set name="studentLetters" inverse="true">
            <key>
                <column name="Student_Id" not-null="true" />
            </key>
            <one-to-many class="com.caughey.tkd.data.hibernate.StudentLetter" />
        </set>
        <set name="studentAddresses" inverse="true" fetch="join" lazy="false">
            <key>
                <column name="Student_Id" not-null="true" />
            </key>
            <one-to-many class="com.caughey.tkd.data.hibernate.StudentAddress" />
        </set>
    </class>
    <query name="com.caughey.tkd.data.student.studentSearch">
       <![CDATA[
         from Student as student
         where student.firstName like :firstName
          and  student.lastName like :lastName
          order by lastName, firstName 
      ]]>
   </query>
    <query name="com.caughey.tkd.data.student.studentSearchCount">
       <![CDATA[
          select new map(count(*) as count)
         from Student as student
         where student.firstName like :firstName
          and  student.lastName like :lastName
      ]]>
   </query>
   <!--  This one I don't think will work -->
    <query name="com.caughey.tkd.data.student.studentByDateLetterQuery">
       <![CDATA[
         SELECT distinct student
         FROM Student student
               left join student.studentDates
                left join student.studentLetters
         where student.studentDates.dateType.dateTypeId = :dateTypeId
          and  student.studentDates.dateValue <= :dateValue
          and  student.studentLetters.letterType.letterTypeId != :letterTypeId      
      ]]>
   </query>

   
   
   </hibernate-mapping>


############### StudentDate
Code:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.caughey.tkd.data.hibernate.StudentDate" table="Student_Date" >
        <id name="studentDateId" type="java.lang.Integer">
            <column name="Student_Date_Id" />
            <generator class="native" />
        </id>
        <many-to-one name="student" class="com.caughey.tkd.data.hibernate.Student" fetch="select" >
            <column name="Student_Id" not-null="true" />
        </many-to-one>
        <many-to-one name="dateType" class="com.caughey.tkd.data.hibernate.DateType" fetch="join" lazy="false">
            <column name="Date_Type_Id" not-null="true" />
        </many-to-one>
        <property name="dateValue" type="java.util.Date">
            <column name="Date_Value" length="10" not-null="true" />
        </property>
    </class>
    <query name="com.caughey.tkd.data.studentDate.studentDates">
       <![CDATA[
         from StudentDate as dates
         where dates.student.studentId = :studentId
      ]]>
   </query>
</hibernate-mapping>

################# StudentLetter
Code:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="com.caughey.tkd.data.hibernate.StudentLetter" table="Student_Letter" catalog="mcaughey_tkddev">
        <id name="studentLetterId" type="java.lang.Integer">
            <column name="Student_Letter_Id" />
            <generator class="assigned" />
        </id>
        <many-to-one name="letterType" class="com.caughey.tkd.data.hibernate.LetterType" fetch="select">
            <column name="Letter_Type_Id" not-null="true" />
        </many-to-one>
        <many-to-one name="student" class="com.caughey.tkd.data.hibernate.Student" fetch="select">
            <column name="Student_Id" not-null="true" />
        </many-to-one>
        <property name="dateCreated" type="java.util.Date">
            <column name="Date_Created" length="10" />
        </property>
        <property name="datePrint" type="java.util.Date">
            <column name="Date_Print" length="10" />
        </property>
    </class>
</hibernate-mapping>




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

The problem is with the Query in Student.hbm.xml

com.caughey.tkd.data.student.studentByDateLetterQuery


Full stack trace of any exception that occurs:

Code:
ERROR -  illegal syntax near collection: dateType
ERROR - Error in named query: com.caughey.tkd.data.student.studentByDateLetterQuery
org.hibernate.QueryException: could not resolve property: dateTypeId of: com.caughey.tkd.data.hibernate.StudentDate [
      
         SELECT distinct student
         FROM com.caughey.tkd.data.hibernate.Student student
               left join student.studentDates
                left join student.studentLetters
         where student.studentDates.dateType.dateTypeId = :dateTypeId
          and  student.studentDates.dateValue <= :dateValue
          and  student.studentLetters.letterType.letterTypeId != :letterTypeId      
      
   ]
   at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
   at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
   at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1265)
   at org.hibernate.persister.collection.AbstractCollectionPersister.toType(AbstractCollectionPersister.java:1255)
   at org.hibernate.hql.ast.tree.FromElementType.getPropertyType(FromElementType.java:279)
   at org.hibernate.hql.ast.tree.FromElement.getPropertyType(FromElement.java:372)
   at org.hibernate.hql.ast.tree.DotNode.getDataType(DotNode.java:539)
   at org.hibernate.hql.ast.tree.DotNode.prepareLhs(DotNode.java:221)
   at org.hibernate.hql.ast.tree.DotNode.resolve(DotNode.java:172)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:94)
   at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:90)
   at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:725)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1215)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4032)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3518)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1758)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1683)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1683)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:776)
   at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:577)
   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:218)
   at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
   at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
   at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
   at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
   at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:364)
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:328)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:805)
   at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:745)
   at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:134)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1198)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1167)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:427)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:267)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:110)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1095)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:857)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:423)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:291)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:122)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:66)
   at com.caughey.tkd.prez.util.BusinessTierUtil.setInstance(BusinessTierUtil.java:27)
   at com.caughey.tkd.prez.util.BusinessTierUtil.getInstance(BusinessTierUtil.java:34)
   at com.caughey.tkd.struts.action.base.SpringAction.execute(SpringAction.java:27)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
   at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
   at java.lang.Thread.run(Thread.java:595)
WARN - Unhandled Exception thrown: class org.springframework.beans.factory.BeanCreationException
Sep 11, 2008 12:08:38 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet action threw exception
org.hibernate.HibernateException: Errors in named queries: com.caughey.tkd.data.student.studentByDateLetterQuery
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:339)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:805)
   at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:745)
   at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:134)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1198)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1167)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:427)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:267)
   at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveValueIfNecessary(BeanDefinitionValueResolver.java:110)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyPropertyValues(AbstractAutowireCapableBeanFactory.java:1095)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:857)
   at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:423)
   at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)
   at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)
   at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)
   at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:291)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:122)
   at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:66)
   at com.caughey.tkd.prez.util.BusinessTierUtil.setInstance(BusinessTierUtil.java:27)
   at com.caughey.tkd.prez.util.BusinessTierUtil.getInstance(BusinessTierUtil.java:34)
   at com.caughey.tkd.struts.action.base.SpringAction.execute(SpringAction.java:27)
   at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
   at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
   at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
   at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:261)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:581)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
   at java.lang.Thread.run(Thread.java:595)



Name and version of the database you are using: MySQL 4 something

The generated SQL (show_sql=true): The rpoblem is in reading the HQL.


Debug level Hibernate log excerpt:
The rpoblem is in reading the HQL.


Problems with Session and transaction handling?
The rpoblem is in reading the HQL.


Read this: http://hibernate.org/42.html

Here is the DDL for the tables:

Code:
DROP TABLE IF EXISTS Date_Type;
CREATE TABLE Date_Type (
  Date_Type_Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  PRIMARY KEY(Date_Type_Id)
)
ENGINE=InnoDB;

DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
  Student_Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  First_Name VARCHAR(40) NOT NULL,
  Last_Name VARCHAR(40) NOT NULL,
  Email_Address VARCHAR(255) NULL,
  Email_Verify_Code VARCHAR(12) BINARY NULL,
  Email_Verified BOOL NULL,
  Tiger BOOL NULL,
  Active BOOL NULL,
  User_Id INT(11) UNSIGNED,
  PRIMARY KEY(Student_Id)
)
ENGINE=InnoDB;


DROP TABLE IF EXISTS Student_Date;
CREATE TABLE Student_Date (
  Student_Date_Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  Date_Type_Id INT(11) UNSIGNED NOT NULL,
  Student_Id INT(11) UNSIGNED NOT NULL,
  Date_Value DATE NOT NULL,
  PRIMARY KEY(Student_Date_Id)
)
ENGINE=InnoDB;


DROP TABLE IF EXISTS Letter_Type;
CREATE TABLE Letter_Type (
  Letter_Type_Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  location VARCHAR(255) NULL,
  class_id VARCHAR(80) not NULL,
  PRIMARY KEY(Letter_Type_Id)
)
TYPE=InnoDB;

DROP TABLE IF EXISTS Student_Letter;
CREATE TABLE Student_Letter (
  Student_Letter_Id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Letter_Type_Id INTEGER UNSIGNED NOT NULL,
  Student_Id INTEGER UNSIGNED NOT NULL,
  Date_Created DATE NULL,
  Date_Print  DATE NULL,
  PRIMARY KEY(Student_Letter_Id)
)
TYPE=InnoDB;


Alter table Student_Letter
   Add Foreign key fk_Student_Letter_Letter_Type_Id(Letter_Type_Id)
   references Letter_Type(Letter_Type_Id);

Alter table Student_Letter
   Add Foreign key fk_Student_Letter_Student_Id(Student_Id)
   references Student(Student_Id);

Alter table Student_Date
   Add Foreign key fk_Student_Date_date_type (Date_Type_Id)
   references Date_Type(Date_Type_Id);

Alter table Student_Date
   Add Foreign key fk_Student_Date_Student_Id(Student_Id)
   references Student(Student_Id);





So I have 5 tables here. Student has a relationship with both StudentDates and StudentLetters. StudentDates has a relationship with StudentDateTypes. A similar relationship exists between StudentLetter and LetterType.

So I have a date type of start date and a letter type of new student. I'm trying to create a query which will give me all the students that have a start date (I'll pass the date type in as an Integer) before the dateValue that I pass in and that don;t already have the LetterType ( New Student)

I have tried many different Queries, I have looked through books for how to do this, I've searched the Internet. I cannot find examples like this where there multiple joins and criteria in more than on sub table.

Help...
Top
 Profile  
 
nordborg
 Post subject:
PostPosted: Thu Sep 11, 2008 3:01 pm 
Expert
Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden
Have you tried assigning aliases to the joined tables?

Code:
SELECT distinct student
         FROM com.caughey.tkd.data.hibernate.Student student
               left join student.studentDates dates
                left join student.studentLetters letters
         where dates.dateType.dateTypeId = :dateTypeId
          and  dates.dateValue <= :dateValue
          and  letters.letterType.letterTypeId != :letterTypeId


I have found that Hibernate generates rather strange SQL if you do both explicit and implicit joins on the same association.
Top
 Profile  
 
mcaughey
 Post subject:
PostPosted: Thu Sep 11, 2008 3:27 pm 
Newbie

Joined: Thu Oct 26, 2006 11:50 am
Posts: 17
Location: Chesterfield, VA
Actually I just found a resouce which suggested the following:
Code:
         SELECT distinct student
         FROM Student student
               left join student.studentDates as dates
                  with dates.dateType.dateTypeId = :dateTypeId
                   and dates.dateValue <= :dateValue
               left join student.studentLetters as letters
                  with letters.letterType.letterTypeId != :letterTypeId


I have gotten it to work. It returns 19 records. I need to do some further tests to determine if it does exactly what I need.
Top
 Profile  
 
mcaughey
 Post subject:
PostPosted: Thu Sep 11, 2008 3:28 pm 
Newbie

Joined: Thu Oct 26, 2006 11:50 am
Posts: 17
Location: Chesterfield, VA
To anwser the post have I tried alias's, yes i did. That didn;t work either.
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
  [ 4 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.