1:M lost getting lists

[graham king]

I've just started using Hibernate and am having problems getting the many side of a relationship. I promise I'll buy a book soon, but, in the meantime I've been using the web as my main resource...

Here's the problem:
I used an ant script to reverse eng the db which is in use. Chapter 4. Ant Tools. Great script. I hate writing mapping files. This also helped: hbm2java

This gave me a slew of mapping files, but, now I'm not able to get the many side of the relationship using HQL?

Here's part of each mapping file:

    
<hibernate-mapping>
    <class name="com.model.hbm.Exam" table="exam" schema="dbo" catalog="Sites">
        <id name="examId" type="int">
            <column name="exam_id" />
            <generator class="assigned" />
        </id>        
        <set name="examQuestions" inverse="true">
            <key>
                <column name="exam_id" not-null="true" />
            </key>
            <one-to-many class="com.model.hbm.ExamQuestion" />
        </set>
    </class>
</hibernate-mapping>
 
<!-- The corresponding mapping file  -->
<hibernate-mapping>
    <class name="com.model.hbm.ExamQuestion" table="exam_question" schema="dbo" catalog="Sites">
        <id name="examQuestionId" type="int">
            <column name="exam_question_id" />
            <generator class="assigned" />
        </id>
        <many-to-one name="Exam" class="com.model.hbm.Exam" fetch="select">
            <column name="exam_id" not-null="true" />
        </many-to-one>
        <property name="questionText" type="clob">
            <column name="question_text" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

I was able to figure out how to get the 1 side but not the many. Tried based on: Hibernate Querying 101 and HibernateUtil Here's how I'm accessing the tables:

package com.delegate;
 
import com.util.HibernateUtil;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
public class HibExamDel {
    private static transient Logger logger = Logger.getLogger(HibExamDel.class.getClass());    
    public static com.model.hbm.Exam getExam(int examId){
        Session session = HibernateUtil.getSessionFactory().getCurrentSession(); 
        session.beginTransaction();        
        
        // this also works
        //Exam ae = (Exam)session.load(Exam.class, new Integer(examId));
        com.model.hbm.Exam ae = (com.model.hbm.Exam) session                
                .createQuery("from Exam a where a.examId = :examId " + // dnw
                "left join fetch a.examQuestions")                
                .setParameter("examId", new Integer(examId))
                .uniqueResult();                         
        /*
        Query q = session.createQuery(
           );
        List questions = q.setParameter("examId", new Integer(examId)).list();        
        logger.info("size-"+questions.size());
        //ae.setExamQuestions(Collections.synchronizedSet(new HashSet(questions)));
        
         * WORKS BUT DON'T KNOW HOW TO GET THE LISTS
        com.model.hbm.Exam exam = (com.model.hbm.Exam) session
                .createQuery("select a from Exam a where a.id = :aid")
                .setParameter("aid", new Integer(examId)).uniqueResult();    
        
        // find the questions -- DOESN'T WORK
        Query q = session.createQuery(
           "from ExamQuestion aeq where aeq.exam.examId = :examId");
        List questions = q.setParameter("examId", new Integer(examId)).list();        
        exam.setExamQuestions(Collections.synchronizedSet(new HashSet(questions)));
         */
        
        session.getTransaction().commit();
        session.close();
        return ae;
    }
}

The above gives me the following:

javax.servlet.ServletException: unexpected token: left near line 1, column 80 [from com.model.hbm.Exam a where a.examId = :examId left join fetch a.examQuestions]
org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:545)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:486)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:274)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:320)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
 
root cause
 
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: left near line 1, column 80 [from com.model.hbm.Exam a where a.examId = :examId left join fetch a.examQuestions]
org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:244)
org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:155)
org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:109)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:75)
org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:54)
org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:134)
org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:113)
org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1602)
sun.reflect.GeneratedMethodAccessor911.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
$Proxy238.createQuery(Unknown Source)
com.delegate.HibAnnualExamDel.getExam(Unknown Source)
com.action.AdminExamAction.showExamDetail(Unknown Source)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:324)
org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.tiles.TilesRequestProcessor.doForward(TilesRequestProcessor.java:274)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.tiles.TilesRequestProcessor.processForwardConfig(TilesRequestProcessor.java:320)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)

Here's the class files:

package com.model.hbm;
// Generated Feb 17, 2006 3:02:23 PM by Hibernate Tools 3.1.0.beta4
import java.util.HashSet;
import java.util.Set;
 
/**
 * Exam generated by hbm2java
 */
public class Exam  implements java.io.Serializable {
    // Fields    
     private int examId;
     private Set annualExamQuestions = new HashSet(0);
 
    // Constructors
    /** default constructor */
    public Exam() {
    }
/** minimal constructor */
    public Exam(int examId) {
        this.examId = examId;
    }    
    /** full constructor */
    public Exam(int examId, Set annualExamQuestions) {
        this.examId = examId;
        this.examQuestions = examQuestions;
    }    
   
    // Property accessors
    // gets and sets
    public int getExamId(){...}
    public void setExamId(int examId) {...}
    public Set getExamQuestions() {...}    
    public void setExamQuestions(Set examQuestions) {...}
}
/* And the other */
package com.model.hbm;
// Generated Feb 17, 2006 3:02:23 PM by Hibernate Tools 3.1.0.beta4
import java.sql.Clob;
import java.util.HashSet;
import java.util.Set;
 
/**
 * ExamQuestion generated by hbm2java
 */
public class ExamQuestion  implements java.io.Serializable {
 
    // Fields    
     private int examQuestionId;
     private Exam exam;
     private Clob questionText;
 
    // Constructors
    /** default constructor */
    public ExamQuestion() {
    }
/** minimal constructor */
    public ExamQuestion(int examQuestionId, Exam exam, Clob questionText) {
        this.examQuestionId = examQuestionId;
        this.exam = exam;
        this.questionText = questionText;
    }
    
    // Property accessors
    // gets and sets
    public int getExamQuestionId(){...}
    public void setExamQuestionId(int examQuestionId){...}
    public Exam getExam(){...}
    public void setExam(Exam exam){...}
    public Clob getQuestionText(){...}
    public void setQuestionText(Clob questionText){...}
}

Please help! I'm burnt.

Graham

[graham king]

The following HQL query ended up working:

com.model.hbm.Exam ae = (com.model.hbm.Exam) session                
                .createQuery(
                "from Exam a, ExamQuestion aeq where a.examId = :examId AND aeq.exam = a")                
                .setParameter("examId", new Integer(examId))
                .uniqueResult();   

There is a db.table.column named reference which needed to be enclosed in [reference]...

[graham king]

I spoke too soon. There's another field:

        <property name="questionText" type="clob">
            <column name="question_text" not-null="true"/>
        </property>

Results in:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unsupported data conversion.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BaseData.unsupportedConversion(Unknown Source)
at com.microsoft.jdbc.base.BaseData.getClob(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.getClob(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.getClob(Unknown Source)
at org.hibernate.type.ClobType.get(ClobType.java:58)
at org.hibernate.type.ClobType.nullSafeGet(ClobType.java:112)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1899)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
at org.hibernate.loader.Loader.getRow(Loader.java:1197)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
at org.hibernate.loader.Loader.doQuery(Loader.java:689)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749)

Which I guess is unsupported with MS SqlServer. So I changed it to:

        <property name="questionText" type="string">
            <column name="question_text" not-null="true" length="256"/>
        </property>

-- but i didn't chane the VO to match... no wonder it's called ranting like an incoherent fool! :roll:

And now I get the following:

org.hibernate.PropertyAccessException: exception setting property value with CGLIB (set hibernate.cglib.use_reflection_optimizer=false for more info) setter of com.model.hbm.ExamQuestion.setQuestionText
org.hibernate.tuple.PojoEntityTuplizer.setPropertyValuesWithOptimizer(PojoEntityTuplizer.java:215)
org.hibernate.tuple.PojoEntityTuplizer.setPropertyValues(PojoEntityTuplizer.java:185)
org.hibernate.persister.entity.AbstractEntityPersister.setPropertyValues(AbstractEntityPersister.java:3232)
org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:126)
org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:842)
org.hibernate.loader.Loader.doQuery(Loader.java:717)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
org.hibernate.loader.Loader.doList(Loader.java:2145)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749)
...
Caused by: net.sf.cglib.beans.BulkBeanException
at com.model.hbm.ExamQuestion$$BulkBeanByCGLIB$$b8f62d53.setPropertyValues(<generated>  :wink:  
at org.hibernate.tuple.PojoEntityTuplizer.setPropertyValuesWithOptimizer(PojoEntityTuplizer.java:212)
... 66 more
Caused by: java.lang.ClassCastException
... 68 more

Is there a way to solve this?

TIA,
Graham
[ February 21, 2006: Message edited by: graham king ]

[graham king]

ok, just let a ranting fool, rant!

i upgraded the driver to ms sql server 2005.

re-changed all string(clobs) back to clobs.

told the query to list() instead of uniqueResult().

and removed the session.close();

Alls Good!

  Session session = HibernateUtil.getSessionFactory().getCurrentSession(); 
  session.beginTransaction();        
        
  Object o = session                
                .createQuery(
                "from AnnualExam a, AnnualExamQuestion aeq where a.annualExamId = :examId AND aeq.annualExam = a"                                               
                ).setParameter("examId", new Integer(annualExamId)).list();                         
        logger.debug("createQuery returned "+o.getClass().getName());
        
        session.getTransaction().commit();
        //session.close();

[graham king]

Makes better sense now. What a difference a day makes. Here's another version of the query:

Session session = HibernateUtil.getSessionFactory().getCurrentSession(); 
        session.beginTransaction();        
        
 
        com.model.hbm.AnnualExam annualExam = (com.model.hbm.AnnualExam) session
                .createQuery("select a from AnnualExam a where a.id = :aid")
                .setParameter("aid", new Integer(annualExamId)).uniqueResult();    
        
        // find the questions 
        Query q = session.createQuery(
           "from AnnualExamQuestion aeq where aeq.annualExam = :ae");
        List questions = q.setParameter("ae", annualExam).list();        
        annualExam.setAnnualExamQuestions(Collections.synchronizedSet(new HashSet(questions)));         
        
        session.getTransaction().commit();
        //session.close();