Query entity from db including collections

Shadow82 · **Joined:** Fri Mar 16, 2007 5:26 am **Posts:** 4

Hello,

i've got one problem. Maybe there is a simple way to manage it...

I've got an entity CustomerConfiguration which includes a Set of Area-entities. Every Area-entity includes a Set of SRAgreement-entities.

CustomerConfiguration -> Area-Set with Area-Objects -> SRAgreement-Set with SRAgreement-Objects

Now i want to load the complete CustomerConfiguration-Object from DB, including all initialized Sets.

I've tried with the following query:
"from CustomerConfiguration as c inner join Area as a inner join SRAgreement as s where c.custid = a.custid and a.srid = s.srid"

This query provides many CustomerConfiguration-Objects dependent of the size of Sets, but i need the complete object as a single result.

Ok, as far as clear...

But how is it possible to load the complete initialized object?
I thought about session.load(...), but the id is not known in my application so i have to find another way.

Following the mapping file of this constellation:

Code:

<?xml version="1.0" ?>
 <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
 <hibernate-mapping>
 <class name="com.plecto.bimarp.ext.configuration.entity.CustomerConfiguration" table="CUST_CONFIG">
 <id name="id" type="long" column="CUSTID">
  <generator class="com.plecto.bimarp.ext.dba.impl.IdGenerator">
     <param name="startValue">1</param>
     <param name="column">id</param>
     <param name="table">CustomerConfiguration</param>
  </generator>
 </id>
 
 <property name="customerName" type="string" column="CUSTOMERNAME"/>
 
 <set name="areas" inverse="true" cascade="all" fetch="join" lazy="false">
   <key>
         <column name="custid"/>
   </key>
   <one-to-many class="com.plecto.bimarp.ext.configuration.entity.Area"/>
   
 </set>
 </class>
 
 <class name="com.plecto.bimarp.ext.configuration.entity.Area" table="CUST_CONFIG_AREAS">
   <id name="id" type="long" column="AREAID">
     <generator class="com.plecto.bimarp.ext.dba.impl.IdGenerator">
           <param name="startValue">1</param>
           <param name="column">id</param>
           <param name="table">Area</param>
      </generator>
   </id>
   
   <property name="custId" type="long" column="CUSTID"/>
   <property name="areaName" type="string" column="AREANAME"/>
   
   <set name="sra" inverse="true" cascade="all" fetch="join" lazy="false">
     <key>
       <column name="areaid"/>
     </key>
     <one-to-many class="com.plecto.bimarp.ext.configuration.entity.SRAgreement"/>
   </set>
 </class>
 
 <class name="com.plecto.bimarp.ext.configuration.entity.SRAgreement" table="CUST_CONFIG_SR">
   <id name="id" type="long" column="SRID">
     <generator class="com.plecto.bimarp.ext.dba.impl.IdGenerator">
           <param name="startValue">1</param>
           <param name="column">id</param>
           <param name="table">SRAgreement</param>
      </generator>
   </id>
   
   <property name="areaId" type="long" column="AREAID"/>
   <property name="sender" type="string" column="SENDER"/>
   <property name="senderInterface" type="string" column="SENDERIF"/>
   <property name="senderInterfaceNamespace" type="string" column="SENDERIFNS"/>
   <property name="receiver" type="string" column="RECEIVER"/>
   <property name="receiverInterface" type="string" column="RECEIVERIF"/>
   <property name="receiverInterfaceNamespace" type="string" column="RECEIVERIFNS"/>
 
 </class>
 
</hibernate-mapping> 

I appreciate any helpful solution :)

evdelst · **Joined:** Wed Aug 24, 2005 11:49 am **Posts:** 63

Watch out with the fetch='join' one too many sets. You will get cartesian products in the sql resultset, resulting in a huge dataset that has to be processed. Better use fetch='select' on the 'sra' set. (or all sets and override in the query when needed)

Hql could be:
from CustomerConfiguration as c
left join fetch c.areas

You will get back a list of CC objects with duplicaties, put them in a Set to make them unique.

Shadow82 · **Joined:** Fri Mar 16, 2007 5:26 am **Posts:** 4

Well, i've tried this and i'm getting the following exception. I don't know why this exception is thrown because there is no "(" inside the query....

Code:

[2007-03-18 23:28:08,828 DEBUG] JDBCExceptionReporter     (SAPEngine_Application_Thread[impl:3]_0) > could not execute query [select customerco0_.CUSTID as CUSTID26_0_, areas1_.AREAID as AREAID27_1_, customerco0_.CUSTOMERNAME as CUSTOMER2_26_0_, areas1_.CUSTID as CUSTID27_1_, areas1_.AREANAME as AREANAME27_1_, areas1_.custid as custid0__, areas1_.AREAID as AREAID0__ from CUST_CONFIG customerco0_, CUST_CONFIG_AREAS areas1_ where customerco0_.CUSTID=areas1_.custid(+) and customerName=? and customerco0_.CUSTID=areas1_.CUSTID]
com.sap.sql.log.OpenSQLException: The SQL statement "select customerco0_.CUSTID as CUSTID26_0_, areas1_.AREAID as AREAID27_1_, customerco0_.CUSTOMERNAME as CUSTOMER2_26_0_, areas1_.CUSTID as CUSTID27_1_, areas1_.AREANAME as AREANAME27_1_, areas1_.custid as custid0__, areas1_.AREAID as AREAID0__ from CUST_CONFIG customerco0_, CUST_CONFIG_AREAS areas1_ where customerco0_.CUSTID=areas1_.custid(+) and customerName=? and customerco0_.CUSTID=areas1_.CUSTID" contains the syntax error[s]: SQL syntax error: the token "(" was not expected here

   at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:106)
   at com.sap.sql.log.Syslog.createAndLogOpenSQLException(Syslog.java:145)
   at com.sap.sql.jdbc.common.StatementAnalyzerImpl.parse(StatementAnalyzerImpl.java:77)
   at com.sap.sql.jdbc.common.StatementAnalyzerImpl.preprepareStatement(StatementAnalyzerImpl.java:125)
   at com.sap.sql.jdbc.common.StatementAnalyzerImpl.preprepareStatement(StatementAnalyzerImpl.java:109)
   at com.sap.sql.jdbc.common.AbstractCommonStatement.parseStatement(AbstractCommonStatement.java:360)
   at com.sap.sql.jdbc.common.CommonConnectionImpl.prepareStatement(CommonConnectionImpl.java:350)
   at com.sap.engine.services.dbpool.cci.ConnectionHandle.prepareStatement(ConnectionHandle.java:81)
   at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
   at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
   at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560)
   at org.hibernate.loader.Loader.doQuery(Loader.java:661)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2144)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2028)
   at org.hibernate.loader.Loader.list(Loader.java:2023)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:393)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
   at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
   at com.plecto.bimarp.ext.dba.impl.HibernatePersistenceManager.executeQuery(HibernatePersistenceManager.java:182)
   at com.plecto.bimarp.ejb.dba.DatabaseAccessBean.executeQuery(DatabaseAccessBean.java:84)
   at com.plecto.bimarp.ejb.dba.DatabaseAccessLocalLocalObjectImpl0_0.executeQuery(DatabaseAccessLocalLocalObjectImpl0_0.java:319)
   at com.plecto.bimarp.ejb.configuration.ConfigurationHandlerBean.getConfig(ConfigurationHandlerBean.java:92)
   at com.plecto.bimarp.ejb.configuration.ConfigurationHandlerLocalLocalObjectImpl0_0.getConfig(ConfigurationHandlerLocalLocalObjectImpl0_0.java:103)
   at com.plecto.bimarp.ejb.test.TestEJBBean.testConfigurationModule(TestEJBBean.java:115)
   at com.plecto.bimarp.ejb.test.TestEJBObjectImpl0_0.testConfigurationModule(TestEJBObjectImpl0_0.java:119)
   at com.plecto.bimarp.ejb.test.TestEJB_Stub.testConfigurationModule(TestEJB_Stub.java:56)
   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.sap.engine.services.ejb.session.stateless_sp5.ObjectStubProxyImpl.invoke(ObjectStubProxyImpl.java:187)
   at $Proxy122.testConfigurationModule(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 com.sap.engine.services.webservices.runtime.EJBImplementationContainer.invokeMethod(EJBImplementationContainer.java:126)
   at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:157)
   at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:79)
   at com.sap.engine.services.webservices.runtime.servlet.ServletDispatcherImpl.doPost(ServletDispatcherImpl.java:92)
   at SoapServlet.doPost(SoapServlet.java:51)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
   at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:401)
   at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:266)
   at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:387)
   at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:365)
   at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:944)
   at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:266)
   at com.sap.engine.services.httpserver.server.Client.handle(Client.java:95)
   at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:160)
   at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)
   at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)
   at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)
   at java.security.AccessController.doPrivileged(Native Method)
   at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)
   at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)
Caused by: com.sap.sql.sqlparser.CommonSQLParserException: SQL syntax error: the token "(" was not expected here

I wonder, why hibernate includes this (+) after ids, never saw it anywhere. I think maybe that's the problem, but didn't find any solution.

I've tried different types of this query:

"FROM CustomerConfiguration as c left join fetch c.areas"
"FROM CustomerConfiguration as c left join fetch c.areas as a left join fetch a.sra as s"

All queries throw the same exception...

btw: In the mapping file i changed the fetch attribute to "select".

christian · **Posted:** Sun Mar 18, 2007 7:05 pm

You have configured an Oracle dialect in Hibernate, that is a proprietary Oracle join syntax. Doesn't look like your Adabas/SAP SB understands that.

Shadow82 · **Joined:** Fri Mar 16, 2007 5:26 am **Posts:** 4

The hibernate dialect is configured correctly.

<property name="dialect">org.hibernate.dialect.SAPDBDialect</property>

But now i solved my problem.
I've only executed "FROM CustomerConfiguration" and all Sets are initialized.

Thanks anyway for your help :)

I've got only one more problem to update an object, i queried before...
But first i'll try to solve it myself. If i'll be so confused i will come back ;)