QueryException: in expected in a SIMPLE query

pkrouse · **Posted:** Wed Nov 24, 2004 2:43 pm

Hibernate version:
2.1.3
Mapping documents:
2 of them, patient and rx. Here is patient

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 package="com.txdx.pojo">
<class name="Patient" table="patients">
    <id name="id" column="patient_id" type="long" unsaved-value="null">
      <generator class="hilo"/>
    </id>
    <property name="salutation"  type="string" length="4"  not-null="true" />
    <property name="firstName"   type="string" length="25" not-null="true" />
    <property name="initial"     type="string" length="1"  not-null="false"/>
    <property name="lastName"    type="string" length="25" not-null="true" />
    <property name="suffix"      type="string" length="3"  not-null="false"/>
    <property name="haddr1"      type="string" length="35" not-null="true" />
    <property name="haddr2"      type="string" length="35" not-null="false"/>
    <property name="hcity"       type="string" length="15" not-null="true" />
    <property name="hstate"      type="string" length="2"  not-null="true" />
    <property name="hzip"        type="string" length="10" not-null="true" />
    <property name="hphone"      type="string" length="13" not-null="false"/>
    <property name="hcell"       type="string" length="13" not-null="false"/>
    <property name="hphoneother" type="string" length="13" not-null="false"/>
    <property name="hwebsite"    type="string" length="50" not-null="false"/>
    <property name="DOB"         type="date"/>
    <property name="SSN"         type="string" length="11" not-null="false"/>
    <property name="workplace"   type="string" length="35" not-null="false"/>
    <property name="waddr1"      type="string" length="35" not-null="false" />
    <property name="waddr2"      type="string" length="35" not-null="false"/>
    <property name="wcity"       type="string" length="15" not-null="false" />
    <property name="wstate"      type="string" length="2"  not-null="false" />
    <property name="wzip"        type="string" length="10" not-null="false" />
    <property name="wphone"      type="string" length="13" not-null="false"/>
    <property name="wphone2"     type="string" length="13" not-null="false"/>
    <property name="wphoneother" type="string" length="13" not-null="false"/>
    <property name="wwebsite"    type="string" length="50" not-null="false"/>
    <!-- TODO: link to authorized contact here, 1-to-1 contact null OK -->
    <bag name="Rxs" cascade="all" inverse="true" lazy="false" order-by="rxDate">
      <key column="patient_id"/>
      <one-to-many class="com.txdx.pojo.Rx"/>
    </bag>
    <!--
    <set name="Rxs" cascade="all" inverse="true" lazy="false">
      <key column="patient_id"/>
      <one-to-many class="com.txdx.pojo.Rx"/> 
    </set>
    -->
  </class>
</hibernate-mapping>

here is rx

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 package="com.txdx.pojo">
<class name="Rx" table="rxs"> 
    <!-- we will try this without an ID for now... -->
 
    <id name="id" column="rx_id"
    type="long" unsaved-value="null">   
      <generator class="hilo"/>
    </id> 
    
    <property name="brandName"    type="string" length="35" not-null="true"/>
    <property name="genericName"  type="string" length="35"/> 
    <property name="rxDate"       type="date"/>
    <property name="dosage"       type="float"/> 
    <property name="instructions"  type="string"/>
    <many-to-one name="patient" class="com.txdx.pojo.Patient" column="patient_id" not-null="true"/>
  </class>
</hibernate-mapping>

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

Code:

Configuration cfg = new Configuration().addClass(Patient.class)
               .addClass(Rx.class);
         HibernateExample ex = new HibernateExample();
         //generate our session and open it
         SessionFactory factory = cfg.buildSessionFactory();
         // cfg no longer used once session factory is created
         // this session is opened on a JDBC connection handled by
         // hibernate
         Session session = factory.openSession();

...

Code:

System.out.println("*-*-*-*-*-*-*-*");
             System.out.println("java.class.path="+System.getProperties().getProperty("java.class.path"));
             System.out.println("*-*-*-*-*-*-*-*");
            for (int i = 0; i < firstNames.length; i++) {
                System.out.println("searching for "+firstNames[i]+" "+lastNames[i]);
               List patients = session
                     .find(
                           "from medical patients as p where p.firstName=? and p.lastName=?",
                           new Object[] { firstNames[i], lastNames[i] },
                           new Type[] { Hibernate.STRING,
                                 Hibernate.STRING });
               System.out.println("found " + patients.size()
                     + "matching records");
               for (Iterator it = patients.iterator(); it.hasNext();) {
                  Patient p = (Patient) it.next();
                  System.out.println("===================");
                  System.out.println("name: " + p.getFirstName() + ", "
                        + p.getLastName());
                  System.out.println("DOB: " + p.getDOB().toString());
                  System.out.println("------- RX info: -------");
                  List rxs = p.getRxs();
                  for (Iterator rxList = rxs.iterator(); rxList.hasNext();) {
                     Rx rx = (Rx) rxList.next();
                     System.out.println("   drug:" + rx.getBrandName()
                           + "[" + rx.getGenericName() + "] at "
                           + rx.getDosage());
                     System.out.println("   instructions:\n   "
                           + rx.getInstructions());
                     System.out.println("   ----------");
                  }
               }
            }

Full stack trace of any exception that occurs:
[java] (cfg.Environment 462 ) Hibernate 2.1.3
[java] (cfg.Environment 496 ) loaded properties from resource hibernate.properties: {hibernate.c3p0.timeout=1800, hibernate.connection.driver_class=com.mysql.jdbc.Driver, hibernate.cglib.use_reflection_optimizer=true, hibernate.c3p0.max_statements=50, hibernate.c3p0.max_size=20, hibernate.cache.use_query_cache=true, hibernate.max_fetch_depth=1, hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect, hibernate.jdbc.use_streams_for_binary=true, hibernate.c3p0.min_size=5, hibernate.jdbc.batch_size=0, hibernate.connection.username=root, hibernate.connection.url=jdbc:mysql://localhost:3306/medical, hibernate.show_sql=true, hibernate.connection.password=password, hibernate.connection.pool_size=3}
[java] (cfg.Environment 518 ) using java.io streams to persist binary types
[java] (cfg.Environment 519 ) using CGLIB reflection optimizer
[java] (cfg.Configuration 347 ) Mapping resource: com/txdx/pojo/Patient.hbm.xml
[java] (cfg.Binder 229 ) Mapping class: com.txdx.pojo.Patient -> patients
[java] (cfg.Configuration 347 ) Mapping resource: com/txdx/pojo/Rx.hbm.xml
[java] (cfg.Binder 229 ) Mapping class: com.txdx.pojo.Rx -> rxs
[java] (cfg.Configuration 613 ) processing one-to-many association mappings
[java] (cfg.Binder 1168) Mapping collection: com.txdx.pojo.Patient.Rxs -> rxs
[java] (cfg.Configuration 622 ) processing one-to-one association property references
[java] (cfg.Configuration 647 ) processing foreign key constraints
[java] (dialect.Dialect 82 ) Using dialect: net.sf.hibernate.dialect.MySQLDialect
[java] (cfg.SettingsFactory 58 ) Maximim outer join fetch depth: 1
[java] (cfg.SettingsFactory 62 ) Use outer join fetching: false
[java] (connection.C3P0ConnectionProvider 48 ) C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/medical
[java] (connection.C3P0ConnectionProvider 49 ) Connection properties: {user=root, password=password}
[java] (transaction.TransactionManagerLookupFactory 33 ) No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
[java] Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@c4fe76 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@dc6a77 [ acquireIncrement -> 1, autoCommitOnClose -> false, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, idleConnectionTestPeriod -> 0, initialPoolSize -> 5, maxIdleTime -> 1800, maxPoolSize -> 20, maxStatements -> 50, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@11e1e67 [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:mysql://localhost:3306/medical, properties -> {user=root, password=password} ] , propertyCycle -> 300, testConnectionOnCheckout -> false ] , factoryClassLocation -> null, numHelperThreads -> 3 ]
[java] (cfg.SettingsFactory 102 ) Use scrollable result sets: true
[java] (cfg.SettingsFactory 105 ) Use JDBC3 getGeneratedKeys(): true
[java] (cfg.SettingsFactory 108 ) Optimize cache for minimal puts: false
[java] (cfg.SettingsFactory 114 ) echoing all SQL to stdout
[java] (cfg.SettingsFactory 117 ) Query language substitutions: {}
[java] (cfg.SettingsFactory 128 ) cache provider: net.sf.ehcache.hibernate.Provider
[java] (cfg.Configuration 1093) instantiating and configuring caches
[java] (impl.SessionFactoryImpl 119 ) building session factory
[java] (impl.SessionFactoryObjectFactory 82 ) no JNDI name configured
[java] (cache.UpdateTimestampsCache 35 ) starting update timestamps cache at region: net.sf.hibernate.cache.UpdateTimestampsCache
[java] (config.Configurator 123 ) No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/D:/Eclipse30/workspace/MedicalRCP/lib/hibernate/ehcache-0.7.jar!/ehcache-failsafe.xml
[java] (hibernate.Plugin 95 ) Could not find configuration for net.sf.hibernate.cache.UpdateTimestampsCache. Configuring using the defaultCache settings.
[java] (cache.QueryCache 39 ) starting query cache at region: net.sf.hibernate.cache.QueryCache
[java] (hibernate.Plugin 95 ) Could not find configuration for net.sf.hibernate.cache.QueryCache. Configuring using the defaultCache settings.
[java] *-*-*-*-*-*-*-*
[java] java.class.path=D:\Eclipse30\workspace\MedicalRCP;D:\Eclipse30\plugins\org.eclipse.core.runtime_3.0.0\runtime.jar;D:\Eclipse30\plugins\org.eclipse.jface_3.0.0\jface.jar;D:\Eclipse30\plugins\org.eclipse.swt.win32_3.0.0\ws\win32\swt.jar;D:\Eclipse30\plugins\org.eclipse.ui.workbench_3.0.0\workbench.jar;D:\Eclipse30\workspace\MedicalRCP\src\properties;D:\Eclipse30\workspace\MedicalRCP\src;D:\Eclipse30\workspace\MedicalRCP\bin;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate-ext\jdom.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate-ext\velocity-1.3.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\ant-1.5.3.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\ant-optional-1.5.3.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\c3p0-0.8.4.5.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\cglib-full-2.0.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\commons-collections-2.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\commons-dbcp-1.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\commons-lang-1.0.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\commons-logging-1.0.3.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\commons-pool-1.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\concurrent-1.3.2.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\connector.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\dom4j-1.4.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\ehcache-0.7.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\hibernate-tools.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\hibernate2.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jaas.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jboss-cache.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jboss-common.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jboss-jmx.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jboss-system.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jcs-1.0-dev.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jdbc2_0-stdext.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jgroups-2.2.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\jta.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\junit-3.8.1.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\log4j-1.2.8.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\odmg-3.0.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\oscache-2.0.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\proxool-0.8.3.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\swarmcache-1.0rc2.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\xalan-2.4.0.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\xerces-2.4.0.jar;D:\Eclipse30\workspace\MedicalRCP\lib\hibernate\xml-apis.jar;D:\Eclipse30\workspace\MedicalRCP\lib\mysql\mysql-connector-java-3.0.15-ga-bin.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\aopalliance.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-aop.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-context.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-core.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-dao.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-orm.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-web.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring-webmvc.jar;D:\Eclipse30\workspace\MedicalRCP\lib\spring\spring.jar
[java] *-*-*-*-*-*-*-*
[java] searching for Bob Newhart
[java] net.sf.hibernate.QueryException: in expected: patients [from medical patients as p where p.firstName=? and p.lastName=?]
[java] at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
[java] at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
[java] at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
[java] at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
[java] at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)

[java] at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
[java] at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
[java] at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1554)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1525)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
[java] at com.txdx.examples.HibernateExample.main(Unknown Source)
Name and version of the database you are using:
MySQL 4.0
The generated SQL (show_sql=true):
Don't know where it ends up.
Debug level Hibernate log excerpt:

I looked through the archives, and there are a few errors similar to this one being discussed, and I have loaded the patient and rx classes as discussed elsewhere. I am using a find command instead of a query. Maybe that is my mistake?

As an aside, this code used to work at one time. I had added some spring commands, trying to create DAOs with it, and backed off until I could get this hibernate code to work in my eclipse RCP project. I backed out all the spring jar files in the classpath, but to no avail. The MySQL database is up, the database and tables are there, and I verified the data is in the tables from a sql command prompt.

Any ideas?

--PK

Issahar · **Posted:** Wed Nov 24, 2004 6:06 pm

pkrouse wrote:

... .find("from medical patients as p where p.firstName=? and p.lastName=?"

What's medical doing here? shouldn't it be like:

Code:

"from Patient as p ....."

?

seems like hibernate doesn't know how to translate your query...

maybe before the change, the name of the tables were different, and you didn't change the hql?

Jus.

Issahar · **Posted:** Wed Nov 24, 2004 6:12 pm

I just remembered:
you can download the Hibern8IDE from www.hibernate.org (Hibernate Extentions), put the jars in your project, and run the main class.
you have there an open HQL parser, and it will use your DB (coz it is inside your project).

You could test your HQL there.

Jus.

pkrouse · **Posted:** Wed Nov 24, 2004 9:14 pm

Thanks Issahar.

This has been an exhausting experience. The hibernate documentation and examples/articles I have found on the internet have gotten me this far, but the chapter on HQL has thrown me completely. Does anyone know where I can get an example-based introduction to HQL? I'm at a standstill right now.

arikkfir · **Joined:** Thu Dec 02, 2004 3:44 pm **Posts:** 2

+1

I also felt like my whole HQL experience has been a "trial-by-error" one. The docs are not bad, for a start. But whenever you need to start "getting dirty" and move to more advanced uses, they prove to be a little inefficient. I'd love to help out and enhance them, alas, I'm not experienced enough with them...

Does anyone know of any complementary documentation about HQL?

cheers ;-)

Issahar · **Posted:** Sun Dec 05, 2004 3:06 am

My man Arik Kfir is in the house!!!

at last!!!

was waiting for you...

as I said, the hibern8IDE is the best way to trial-and-error, as i still do in SQL, btw.

You'll love Query-by-Example, btw, it is exactly what we had hand-written in the BL.

Jus.

arikkfir · **Joined:** Thu Dec 02, 2004 3:44 pm **Posts:** 2

I didn't even notice it was you, Jus!! I guess I should start looking
at the signatures more often (heck, if it ain't Java, it's probably
chinese, no?)

10x for tip, I'll try and give it a go, although I'm more of an IntelliJ
man rather than Eclipse.

Btw Jus - are you working with Hibernate in your company? I was
wondering: since Hibernate returns proxies as the objects from a
query (HQL or not) - how do you disconnect them and ship/serialize
them to the client tier?

I know the proxies have a reference to the Session object, so does
that mean I need the Hibernate Jar in my fat-client side?

Actually even in a web tier this is not desirable since the object in the
web tier should never try to reconnect a session.

I've strolled around in the forums trying to find an answer to that,
but only came to the conclusion that there is no answer. Some do
deep-copy and create a "fresh" copy DTOs, others simply don't care
and put the hibernate in the client, but make sure no association is
lazy, and thus prevent the proxies from going back to the db. But I
haven't saw an elegant solution...yet....Cordless Mouse to anyone with
a bullet-proof concept! ;-)

cheers everyone ;-)

Issahar · **Posted:** Tue Dec 07, 2004 5:40 pm

Heyya...

first thing's first:

Hibern8IDE is just java code, so i put the jars inside my IntelliJ project, and run the class. YOU KNOW I WON'T TOUCH ECLIPSE!!!.

If you don't have lazy initialization, i think it is safe to disconnect them and use them as the ValueObject (or our Info). If not, it is very tidious: you need Info, ActionForm and Hb POJO - three classes that does exactly the same. I have a project at our company that works with Hb on a web project. I ll check exactly what they did and post back to you.

in the meantime, cheers to you too...

Jus.