Im having some trouble with hibernate collections in constructing and returning a HQL Query I am attempting to select all transaction objects that are related to a particular market the HQL Query I am using is "from Transaction c where elements(c.purchases).product.productGroup.market.id = 2" [without the surrounding quotes] however when I attempt to run the query it triggers a JDBC error.
Mapping files and outputs are as below any help is much appreciated. IF you need more information please let me know
Hibernate version:hibernate-2.1.4
Mapping documents:
---Transaction Object---
Code:
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name="portal.ecommerce.Transaction" table="tblTransaction" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
<id name="id" column="id" type="int" unsaved-value="-1">
<generator class="native" />
</id>
<list name="purchases" table="purchases_in_transaction" lazy="false" inverse="false" cascade="all" batch-size="1" outer-join="auto">
<key column="fk_transaction" />
<index column="purchase_index" />
<many-to-many class="portal.ecommerce.Purchase" column="fk_purchase" outer-join="auto" />
</list>
<property name="bankAuthenticationCode" type="java.lang.String" update="true" insert="true" access="property" column="bankAuthenticationCode" not-null="false" unique="false" />
<property name="EWayTransactionNumber" type="java.lang.String" update="true" insert="true" access="property" column="EWayTransactionNumber" not-null="false" unique="false" />
<property name="EWayResponseCode" type="java.lang.String" update="true" insert="true" access="property" column="EWayResponseCode" not-null="false" unique="false" />
<property name="EWayResponseText" type="java.lang.String" update="true" insert="true" access="property" column="EWayResponseText" not-null="false" unique="false" />
<property name="cost" type="int" update="true" insert="true" access="property" column="cost" not-null="true" unique="false" />
<property name="status" type="int" update="true" insert="true" access="property" column="status" not-null="true" unique="false" />
<property name="paymentMethod" type="int" update="true" insert="true" access="property" column="paymentMethod" not-null="true" unique="false" />
<many-to-one name="customer" class="portal.user.Customer" cascade="none" outer-join="true" update="true" insert="true" access="property" column="customer" not-null="true" unique="false" />
<many-to-one name="discount" class="portal.ecommerce.Discount" cascade="none" outer-join="auto" update="true" insert="true" access="property" column="discount_id" not-null="false" unique="false" />
<property name="date" type="java.sql.Date" update="true" insert="true" access="property" column="date" not-null="true" unique="false" />
</class>
</hibernate-mapping>
---Purchase Object---
Code:
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name="portal.ecommerce.Purchase" table="tblPurchase" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
<id name="id" column="id" type="int" unsaved-value="-1">
<generator class="native" />
</id>
<many-to-one name="product" class="portal.product.Product" cascade="none" outer-join="true" update="true" insert="true" access="property" column="product_id" not-null="true" unique="false" />
<property name="expiryDate" type="java.util.Date" update="true" insert="true" access="property" column="expiryDate" not-null="false" unique="false" />
<property name="quantity" type="int" update="true" insert="true" access="property" column="quantity" not-null="false" unique="false" />
<many-to-one name="enrollment" class="allocate.clinics.Enrollment" cascade="all" outer-join="auto" update="true" insert="true" access="property" column="enrollment_id" not-null="false" unique="false" />
</class>
</hibernate-mapping>
---Product Object---
Code:
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name="portal.product.Product" table="tblProduct" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
<id name="id" column="id" type="int" unsaved-value="-1">
<generator class="native" />
</id>
<property name="productType" type="int" update="true" insert="true" access="property" column="productType" not-null="true" unique="false" />
<property name="expireDate" type="java.util.Date" update="true" insert="true" access="property" column="expireDate" not-null="false" unique="false" />
<property name="enabled" type="boolean" update="true" insert="true" access="property" column="enabled" not-null="true" unique="false" />
<property name="hidden" type="boolean" update="true" insert="true" access="property" column="hidden" not-null="true" unique="false" />
<property name="name" type="java.lang.String" update="true" insert="true" access="property" column="name" not-null="true" unique="true" />
<many-to-one name="autoResponseEmail" class="portal.email.AutoResponseEmail" cascade="none" outer-join="auto" update="true" insert="true" access="property" column="email_id" not-null="false" unique="false" />
<property name="price" type="int" update="true" insert="true" access="property" column="price" not-null="true" unique="false" />
<property name="viewOrder" type="int" update="true" insert="true" access="property" column="viewOrder" not-null="true" unique="false" />
<many-to-one name="productGroup" class="portal.product.ProductGroup" cascade="none" outer-join="true" update="true" insert="true" access="property" column="productGroup_id" not-null="false" unique="false" />
<property name="longDescription" type="text" update="true" insert="true" access="property" column="longDescription" not-null="true" unique="false" />
<property name="postDescription" type="text" update="true" insert="true" access="property" column="postDescription" not-null="true" unique="false" />
</class>
</hibernate-mapping>
---ProductGroup Object---
Code:
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name="portal.product.ProductGroup" table="tblProductGroup" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
<id name="id" column="id" type="int" unsaved-value="-1">
<generator class="native" />
</id>
<many-to-one name="market" class="portal.market.Market" cascade="none" outer-join="true" update="true" insert="true" access="property" column="market_id" not-null="false" unique="false" />
<property name="viewOrder" type="int" update="true" insert="true" access="property" column="viewOrder" not-null="true" unique="false" />
<property name="hidden" type="boolean" update="true" insert="true" access="property" column="hidden" not-null="true" unique="false" />
<property name="name" type="java.lang.String" update="true" insert="true" access="property" column="name" not-null="true" unique="false" />
</class>
</hibernate-mapping>
---Market Object---
Code:
<hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
<class name="portal.market.Market" table="tblMarket" dynamic-update="false" dynamic-insert="false" mutable="true" polymorphism="implicit" batch-size="1" select-before-update="false" optimistic-lock="version">
<id name="id" column="id" type="int" unsaved-value="-1">
<generator class="native" />
</id>
<property name="url" type="java.lang.String" update="true" insert="true" access="property" column="url" not-null="true" unique="false" />
<property name="name" type="java.lang.String" update="true" insert="true" access="property" column="name" not-null="true" unique="false" />
<property name="description" type="java.lang.String" update="true" insert="true" access="property" column="description" not-null="false" unique="false" />
</class>
</hibernate-mapping>
11:52:44,125 ERROR JDBCExceptionReporter:46 - Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select purchases1_.fk_purchase from purchases_in_transaction pu"
11:52:44,156 ERROR JDBCExceptionReporter:46 - Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select purchases1_.fk_purchase from purchases_in_transaction pu"
11:52:44,156 ERROR JDBCExceptionReporter:38 - Could not execute query
java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select purchases1_.fk_purchase from purchases_in_transaction pu"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1521)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at dev.util.database.HibDatabaseUtils.executeQuery(Unknown Source)
at portal.ecommerce.dao.hibernate.test.TransactionDAOTestCase.testGetInstance(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at com.intellij.rt.execution.junit2.IdeaJUnitAgent.doRun(IdeaJUnitAgent.java:57)
at junit.textui.TestRunner.start(TestRunner.java:172)
at com.intellij.rt.execution.junit.TextTestRunner2.startRunnerWithArgs(TextTestRunner2.java:23)
at com.intellij.rt.execution.junit2.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:97)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
net.sf.hibernate.JDBCException: Could not execute query
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1547)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1521)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)
at dev.util.database.HibDatabaseUtils.executeQuery(Unknown Source)
at portal.ecommerce.dao.hibernate.test.TransactionDAOTestCase.testGetInstance(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at com.intellij.rt.execution.junit2.IdeaJUnitAgent.doRun(IdeaJUnitAgent.java:57)
at junit.textui.TestRunner.start(TestRunner.java:172)
at com.intellij.rt.execution.junit.TextTestRunner2.startRunnerWithArgs(TextTestRunner2.java:23)
at com.intellij.rt.execution.junit2.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:97)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select purchases1_.fk_purchase from purchases_in_transaction pu"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1555)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
... 27 more
java.lang.NullPointerException
at java.util.ArrayList.<init>(ArrayList.java:132)
at portal.ecommerce.dao.hibernate.test.TransactionDAOTestCase.testGetInstance(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.junit2.JUnitStarter.main(JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:78)
Name and version of the database you are using:MYSQL
The generated SQL (show_sql=true):select transactio0_.id as id, transactio0_.bankAuthenticationCode as bankAuth2_, transactio0_.EWayTransactionNumber as EWayTran3_, transactio0_.EWayResponseCode as EWayResp4_, transactio0_.EWayResponseText as EWayResp5_, transactio0_.cost as cost, transactio0_.status as status, transactio0_.paymentMethod as paymentM8_, transactio0_.customer as customer, transactio0_.discount_id as discoun10_, transactio0_.date as date from tblTransaction transactio0_ where ((select purchases1_.fk_purchase from purchases_in_transaction purchases1_ where transactio0_.id=purchases1_.fk_transaction).product.productGroup.market.id=2 )
Debug level Hibernate log excerpt:
Login
FAQ
Search
