net.sf.hibernate.QueryException: in expected?

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

Hi all,

Here is the code I have:

Code:

        Configuration config = new Configuration(); 

        // Tell it about the classes we want mapped, taking advantage of 
        // the way we've named their mapping documents. 
        config.addClass(gov.nih.nci.caBIO.bean.Gene.class). 
              addClass(gov.nih.nci.caBIO.bean.Pathway.class). 
              addClass(gov.nih.nci.caBIO.bean.ExpressionMeasurement.class). 
              addClass(gov.nih.nci.caBIO.bean.ArrayReporterMap.class). 
              addClass(gov.nih.nci.caBIO.bean.GeneReporter.class). 
              addClass(gov.nih.nci.caBIO.bean.PathwayGene.class); 

        // Get the session factory we can use for persistence 
        SessionFactory sessionFactory = config.buildSessionFactory(); 

        // Ask for a session using the JDBC information we've configured 
        Session session = sessionFactory.openSession(); 


        sqlStr = "select p.* " + 
        "from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, " + 
        "Gene_Reporter gr " + 
        "where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id " + 
        "and gr.gene_id = g.gene_id and gr.reporter_id = r.reporter_id "; 

        net.sf.hibernate.Query query = null; 

        query = session.createQuery(sqlStr); 

        java.util.List c = query.list(); 

When I run it, I get the following exception. Any ideas as to why and what it means? It is weird that the sql statement getting printed out has gov.nih.nci.caBIO.bean.Gene g instead of Gene g!

Code:

gov.nih.nci.common.persistence.PersistenceException: Error getting objects; nest 
ed exception is: 
        net.sf.hibernate.QueryException: in expected: p [select p.* from BIO_Pat 
hways p, gov.nih.nci.caBIO.bean.Gene g, Reporter r, Pathway_Gene_Object pg, Gene 
_Reporter gr where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id and g 
r.gene_id = g.gene_id and gr.reporter_id = r.reporter_id ] 
net.sf.hibernate.QueryException: in expected: p [select p.* from BIO_Pathways p, 
 gov.nih.nci.caBIO.bean.Gene g, Reporter r, Pathway_Gene_Object pg, Gene_Reporte 
r gr where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id and gr.gene_i 
d = g.gene_id and gr.reporter_id = r.reporter_id ] 
        at net.sf.hibernate.hql.FromParser.token(FromParser.java:102) 
        at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87) 
        at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.ja 
va:123) 
        at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29) 
        at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149 
) 
        at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138 
) 
        at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl. 
java:293) 
        at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561) 
        at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1532) 
        at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39) 
        at gov.nih.nci.common.persistence.DefaultPersistenceManager.search(Unkno 
wn Source) 
        at gov.nih.nci.common.manager.DefaultObjectManager.search(Unknown Source 
) 
        at gov.nih.nci.common.remote.rmi.RMIObjectManagerRemote.search(Unknown S 
ource) 
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
        at java.lang.reflect.Method.invoke(Unknown Source) 
        at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source) 
        at sun.rmi.transport.Transport$1.run(Unknown Source) 
        at java.security.AccessController.doPrivileged(Native Method) 
        at sun.rmi.transport.Transport.serviceCall(Unknown Source) 
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source) 
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Sour 
ce) 

Any help would be appreciated,
Payam.

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

Any resolution for this?

anthony · **Posted:** Fri Aug 20, 2004 9:19 am

what happens when you copy/paste generated sql into db client?

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

It works fine when the generated sql is copied and pasted in a sql client.

anthony · **Posted:** Fri Aug 20, 2004 9:30 am

if all the _id are the claases' id

try changing

Code:

        sqlStr = "select p.* " + 
        "from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, " + 
        "Gene_Reporter gr " + 
        "where pg.pathway_id = p.pathway_id and pg.gene_id = g.gene_id " + 
        "and gr.gene_id = g.gene_id and gr.reporter_id = r.reporter_id "; 

to

Code:

        sqlStr = "select p.* " + 
        "from BIO_Pathways p, Gene g, Reporter r, Pathway_Gene_Object pg, " + 
        "Gene_Reporter gr " + 
        "where pg.id = p.id and pg.id = g.id " + 
        "and gr.id = g.id and gr.reporter_id = r.reporter_id "; 

and if these classes are associated... you don't need this query to be so complicated... and show mapping files

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

Anthony.

Thanks for your replies. I tried changing to ids as you suggested but I am still getting the same exception.

Here are my mapping files:

Bio_pathway:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.Pathway" table="BIO_PATHWAYS">
    </meta>

    <id name="id" type="long" column="PATHWAY_ID">
      <generator class="native"/>
    </id>


 <property
    name="name"
    column="PATHWAY_NAME"
    type="string"/>

 <property
    name="displayValue"
    column="PATHWAY_DISPLAY"
    type="string"/>

 <property
    name="diagram"
    column="PATHWAY_DIAGRAM"
    type="string"/>

 <property
    name="taxonId"
    column="TAXON"
    type="string"/>

  </class>
  
</hibernate-mapping>

Gene:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.Gene" table="GENE">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

   <property
      name="locusLinkId"
      column="LOCUS_LINK_ID"
      type="string"/>

   <property
      name="OMIMId"
      column="OMIM_ID"
      type="string"/>

   <property
      name="title"
      column="GENE_TITLE"
      type="string"/>

   <property
      name="name"
      column="GENE_SYMBOL"
      type="string"/>

   <property
      name="locusLinkSummary"
      column="LOCUS_LINK_SUMMARY"
      type="string"/>

   <property
      name="clusterId"
      column="CLUSTER_ID"
      type="string"/>

  </class>
      
</hibernate-mapping>

Reporter:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.GeneReporter" table="GENE_REPORTER">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <generator class="native"/>
    </id>

   <property
      name="reporterId"
      column="REPORTER_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

pathway_gene:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.PathwayGene" table="PATHWAY_GENE_OBJECT">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="PATHWAY_ID">
      <generator class="native"/>
    </id>

   <property
      name="geneId"
      column="GENE_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

Gene_reporter:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.GeneReporter" table="GENE_REPORTER">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <generator class="native"/>
    </id>

   <property
      name="reporterId"
      column="REPORTER_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

Anthony.

Thanks for your replies. I tried changing to ids as you suggested but I am still getting the same exception.

Here are my mapping files:

Bio_pathway:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.Pathway" table="BIO_PATHWAYS">
    </meta>

    <id name="id" type="long" column="PATHWAY_ID">
      <generator class="native"/>
    </id>


 <property
    name="name"
    column="PATHWAY_NAME"
    type="string"/>

 <property
    name="displayValue"
    column="PATHWAY_DISPLAY"
    type="string"/>

 <property
    name="diagram"
    column="PATHWAY_DIAGRAM"
    type="string"/>

 <property
    name="taxonId"
    column="TAXON"
    type="string"/>

  </class>
  
</hibernate-mapping>

Gene:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.Gene" table="GENE">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <meta attribute="scope-set">protected</meta>
      <generator class="native"/>
    </id>

   <property
      name="locusLinkId"
      column="LOCUS_LINK_ID"
      type="string"/>

   <property
      name="OMIMId"
      column="OMIM_ID"
      type="string"/>

   <property
      name="title"
      column="GENE_TITLE"
      type="string"/>

   <property
      name="name"
      column="GENE_SYMBOL"
      type="string"/>

   <property
      name="locusLinkSummary"
      column="LOCUS_LINK_SUMMARY"
      type="string"/>

   <property
      name="clusterId"
      column="CLUSTER_ID"
      type="string"/>

  </class>
      
</hibernate-mapping>

Reporter:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.GeneReporter" table="GENE_REPORTER">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <generator class="native"/>
    </id>

   <property
      name="reporterId"
      column="REPORTER_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

pathway_gene:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.PathwayGene" table="PATHWAY_GENE_OBJECT">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="PATHWAY_ID">
      <generator class="native"/>
    </id>

   <property
      name="geneId"
      column="GENE_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

Gene_reporter:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
          "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

  <class name="gov.nih.nci.caBIO.bean.GeneReporter" table="GENE_REPORTER">
    <meta attribute="class-description">
    </meta>

    <id name="id" type="long" column="GENE_ID">
      <generator class="native"/>
    </id>

   <property
      name="reporterId"
      column="REPORTER_ID"
      type="long"/>
  

  </class>
    
</hibernate-mapping>

anthony · **Posted:** Fri Aug 20, 2004 10:02 am

why aren't you using an oo approach, ORAM is done for that, i don't see any relationship in your pojo, and looking at your query, i'm pretty sure the table are linked

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

I agree the objects are linked. My task is to do a prototype to possibly improve the performance of an existing code written in OJB.

I believe the query I am trying to run can be done using the current mapping. Isn't it true?

Do you see any problems with the way I have mapped the objects and the way I have written my sql qeury?

Payam · **Joined:** Tue Jul 27, 2004 2:50 pm **Posts:** 23

Anthony, at this point in time, I would be willing to change my mapping. Would you please elaborate a little bit more about your OO suggestion? Do you mean using on-to-many/many-to-many relationships in my mapping files?

Teg · **Posted:** Fri Aug 20, 2004 10:28 am

If your query is in SQL you have to use session.createSQLQuery instead of session.createQuery

anthony · **Posted:** Fri Aug 20, 2004 10:38 am

Quote:

I agree the objects are linked. My task is to do a prototype to possibly improve the performance of an existing code written in OJB.

if you want to have a performant app, let's use the power of hibernate. It can manage the relationship for you and optimise query (outer join & lazy).
The way you are working now is simply using the "persistent" aspect of hibernate, not using all its power.

So yes, the solution is to use many-to-one, one-to-many, bidirectional only when you need it