Hi every body!!! I'm currently using Hibernate 3 support for stored procedures for CUD operations, but I need to use stored procedures for READ operations too, specially for load operations where Hibernate produces the SQL statement and our client constraint us to use stored procedures.

Do i need to implement my custom loader? Or just extend EntityPersister? Does somebody have an example than can guide me?


Thanks a lot


Johannes Correa Lopera

Have you looked at this: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querysql-load? It says it works for stored procedures. I've never done it personally, though.

Thanks a lot for your answer. It has helped us a lot. Have you tried to use loader with stored procedures for associations ? We are trying to do this:

Hibernate Mapping:

<class name="Application" lazy="false" table="APPLICATIONS" schema="SEG" optimistic-lock="none">

<id name="applicationId" type="integer" unsaved-value="-1">
<column name="ID" not-null="true" unique="true" index="APP_PK"/>
<generator class="increment"/>
</id>

<property name="name" type="string">
<column name="NAME" length="16" not-null="true" unique="true" index="SQL051004114018480"/>
</property>

<property name="description" type="string">
<column name="DESC" length="50"/>
</property>

<set name="resources"
lazy="false"
inverse="true"
cascade="all-delete-orphan"
order-by="ALIAS" >
<key foreign-key="RES_FK_APP">
<column name="APPID" not-null="true"/>
</key>
<one-to-many class="Resource"/>
<loader query-ref="loadResxApps"/>
</set>

<bag name="appRoles" lazy="false" inverse="true" cascade="all-delete-orphan">
<key foreign-key="APPROL_FK_APP">
<column name="APPID" not-null="true"/>
</key>
<one-to-many entity-name="co.com.grporg.bancolombia.security.beans.AppRole"/>
</bag>

<loader query-ref="loadApps"/>
<!--
<query-list query-ref="" name=""/>
-->
</class>

<!-- Loading SP -->
<sql-query name="loadApps" callable="true">
<return class="Application" />
{ call SEG.SEL_APPS(?) }
</sql-query>

<!-- Loading SP
Select {RES.*} from SEG.RESOURCES as RES where APPID = :id
{ call SEG.SEL_RESXAPPS(:id) }
-->
<sql-query name="loadResxApps" callable="true">
<load-collection alias="" role="Application.resources"/>
{ call SEG.SEL_RESXAPPS(?) }
</sql-query>

Currently, we can load the object but when we try to load the associated collection 'resources', we get this error:

Exception:
[10/14/05 14:36:31:843 COT] 2c96eb29 SystemOut O Hibernate: { call SEG.SEL_APPS(?) }
... OTHER STUFF...
[10/14/05 14:36:32:062 COT] 2c96eb29 SystemOut O Hibernate: { call SEG.SEL_RESXAPPS(?) }
[10/14/05 14:36:32:062 COT] 2c96eb29 JDBCException W org.hibernate.util.JDBCExceptionReporter SQL Error: -99999, SQLState: null
[10/14/05 14:36:32:077 COT] 2c96eb29 JDBCException E org.hibernate.util.JDBCExceptionReporter Invalid argument: unknown column name ID0_
[10/14/05 14:36:32:077 COT] 2c96eb29 DefaultLoadEv I org.hibernate.event.def.DefaultLoadEventListener Error performing load command
[10/14/05 14:36:32:077 COT] 2c96eb29 DefaultLoadEv I org.hibernate.event.def.DefaultLoadEventListener TRAS0014I: The following exception was logged org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1591)
at org.hibernate.loader.Loader.list(Loader.java:1571)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1410)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initialize(NamedQueryCollectionInitializer.java:49)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:488)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1426)
at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:274)
at org.hibernate.engine.PersistenceContext.initializeNonLazyCollections(PersistenceContext.java:796)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:216)
at org.hibernate.loader.Loader.doList(Loader.java:1588)
at org.hibernate.loader.Loader.list(Loader.java:1571)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1410)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at org.hibernate.persister.entity.NamedQueryLoader.load(NamedQueryLoader.java:55)
at org.hibernate.persister.entity.BasicEntityPersister.load(BasicEntityPersister.java:2471)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:351)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:332)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:113)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:151)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:79)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:603)
at org.hibernate.impl.SessionImpl.load(SessionImpl.java:596)
at co.com.grporg.bancolombia.security.dao.AbstractEntitieDAO.load(AbstractEntitieDAO.java:226)
at test.TestServlet.doOp(TestServlet.java:48)
at test.TestServlet.doGet(TestServlet.java:27)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:672)
Caused by: com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name ID0_


Thanks a lot by any advise.

how to do this? I'm trying to do a similar type of stored procedure call when loading a collection, but I'm not sure how to get mine to work either.

Hello hbmmam.

Inside the class tags you can use this:

<loader query-ref="load_Application"/>

This points to a sql-query that returns objects of type Applications and is a stored procedure.

<sql-query name="load_Application" callable="true">
<return class="Application" />
{ call SEG.SEGSPRAPP2(?) }
</sql-query>

If you want to load a collection of associated objects, as far i know, you can't do it using stored procedures.

I hope it helps you

Are there any plans to allow stored procs the ability to act as custom loaders for collections? This is rather important in our organization to be able to do this. The reasoning behind this is in case we are trying to optimize aspects of our application.

why cant you do that with stored procedures ? if you can with native sql you can with stored procedures too...

Hi.

What are you trying to do? Do you want to have a stored procedure as your default loader for a persistent object and load a collection of those objects? Or, do you want to have a related object and load a collection of them using a stored procedure?

If you have the first scenario, you can use stored procedure. You just have to define your custom loader and use the session.load() method. But, the second is impossible, as far as i know hibernate docs: at section 16.3.2, reference document states that "Notice stored procedures currently only return scalars and entities. <return-join> and <load-collection> are
not supported". Sorry. How we managed this? Loading manually the referenced objects in our dao objects.

Again, I hope it helps you

Can you show an example of how to load a collection of a relationship using a stored procedure? We tried to do that for almost a week and we always had the error i stated in a previos post.

Thanks

read 3.1 docs - it specifies how to put aliases for key and index columns.
For stored procedures these needs to be mapped via <return-property>

Look in the unit tests for ".key"

Could you elaborate this more? (possibley with example).
I searched unit tests files but still couldn't an example simple enough
for me.

From my understading, there is a way to load collectioin for entity
from stored procedure call without using either <load-collection>
or <return-join>.

did you look in the unit tests ?

CustomSQLTest, GeneralTest etc. ?

And no, you have to use load-collection and return-join if you want to return both an entity and a collection from a resultset.

You can also use <loader> for collection if i remember correctly if you want it to be done by the persisters.

Hi Max,

I am new to Hibernate. I am facing problem to call a stored procedure that fetching data from two tables (join using foreign key).

Could you help me to write the proper <sql-query>?

thanks
Tinu


Hibernate version:3.2

Mapping documents:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="patni.sample">
<class name="EmployeeVO" table="EMPLOYEES">
<id column="EMPNO" name="empno" type="string" />
<property generated="never" lazy="false" name="ename">
<column name="ENAME" />
</property>
<many-to-one class="patni.sample.DepartmentVO"
name="department" />
<property generated="never" lazy="false" name="mgr">
<column name="mgr" />
</property>
</class>
<class name="DepartmentVO" table="DEPT" entity-name="dep">
<id name="deptno">
<column name="deptno" />
</id>
<property column="dname" generated="never" lazy="false"
name="dname" />
<set name="set1" sort="unsorted" table="Employees">
<key column="deptno" />
<element type="integer" />
</set>
</class>
<!-- <sql-query callable="true" name="test1">
{call test.getemdata(?)}
<return-scalar column="empno" type="integer"/>
<return-scalar column="ename" type="string"/>
</sql-query>
<sql-query callable="true" name="test2">
{call test.getemployee(?,?,?)}
<return alias="emp" class="EmployeeVO" entity-name="">
<return-property column="empno" name="id" />
<return-property column="ename" name="ename" />
<return-property column="job" name="job" />
</return>
</sql-query> -->


<sql-query callable="true" name="test3">
{call test.getempdept(?)}
<return alias="emp" class="EmployeeVO">
<return-property name="ename" column="ename"></return-property>
<return-property name="mgr" column="mgr"></return-property>
<return-property name="department.dname">
<return-column name="dname" />
</return-property>
</return>

<!-- <return-join alias="dep" property="emp.department">
<return-property name="dname" column="dname"></return-property>
</return-join> -->
<!-- <return-scalar column="department.dname" type="string"/>
<return-scalar column="ename" type="string"/>
<return-scalar column="mgr" type="integer"/> -->
</sql-query>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
Query query = session.getNamedQuery("test3");
query.setResultTransformer(Transformers.aliasToBean(EmployeeVO.class));
List employeeList=query.list();
EmployeeVO employeeVO=(EmployeeVO)employeeList.get(0);
System.out.println("Test 3 result ------ "+employeeVO.getEname());



Full stack trace of any exception that occurs:

7:46:40,997 DEBUG Configuration:1121 - processing collection mappings
17:46:40,997 DEBUG CollectionSecondPass:41 - Second pass for collection: dep.set1
17:46:40,997 DEBUG CollectionSecondPass:57 - Mapped collection key: deptno, element: elt
17:46:40,997 DEBUG Configuration:1132 - processing native query and ResultSetMapping mappings
org.hibernate.MappingException: property not found: depin entity: patni.sample.EmployeeVO
at org.hibernate.mapping.PersistentClass.getRecursiveProperty(PersistentClass.java:337)
at org.hibernate.cfg.ResultSetMappingBinder.bindPropertyResults(ResultSetMappingBinder.java:189)
at org.hibernate.cfg.ResultSetMappingBinder.bindReturn(ResultSetMappingBinder.java:94)
at org.hibernate.cfg.ResultSetMappingBinder.buildResultSetMappingDefinition(ResultSetMappingBinder.java:69)
at org.hibernate.cfg.NamedSQLQuerySecondPass.doSecondPass(NamedSQLQuerySecondPass.java:78)
at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1136)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1283)
at patni.sample.Test.main(Test.java:29)
Caused by: org.hibernate.MappingException: property not found: depin entity: patni.sample.EmployeeVO
at org.hibernate.mapping.PersistentClass.getRecursiveProperty(PersistentClass.java:368)
at org.hibernate.mapping.PersistentClass.getRecursiveProperty(PersistentClass.java:334)
... 7 more
Caused by: org.hibernate.MappingException: property not found: dep on entity patni.sample.EmployeeVO
at org.hibernate.mapping.PersistentClass.getProperty(PersistentClass.java:384)
at org.hibernate.mapping.PersistentClass.getRecursiveProperty(PersistentClass.java:358)
... 8 more
java.lang.NullPointerException
at patni.sample.Test.main(Test.java:43)



Name and version of the database you are using:

Oracle 9.2.0.1.0

the exception refer to "depin" but nothing in your mapping nor queries refer to it.....

Hi Max,

I have gone through the reference document of hibernate 3.2. And I found one statement

"Notice stored procedures currently only return scalars and entities. <return-join> and <load-collection> are
not supported."

I have called a stored procedure fetching data from one table. For that I have used <return-scalar> with "query.setResultTransformer(Transformers.aliasToBean(EmployeeVO.class));" But I am facing problem to create <sql_query> to call a stored procedure that fetching data from two tables

How can we create a <sql_query> that calling a stored procedurre which returning data of two tables (join). Could you provide me sampe code for the same.

thanks,
Tinu