File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Stored Procedure - Can use unmapped class for result? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Stored Procedure - Can use unmapped class for result?" Watch "Stored Procedure - Can use unmapped class for result?" New topic
Author

Stored Procedure - Can use unmapped class for result?

Kimberly Greuling
Greenhorn

Joined: Aug 21, 2006
Posts: 5
I have the following in a mapping file

<hibernate-mapping schema="dbo">


<sql-query name="getManagerSearchResults_SP" callable="true">
<return class="manager.ManagerSearchResultsBean">
<return-property name="compositeName" column="managerName"/>
<return-property name="investDate" column="investDate"/>
<return-property name="fiscalYearEnd" column="fiscalYearEnd"/>
<return-property name="strategyName" column="strategyName"/>
<return-property name="managerID" column="managerID"/>
</return>

{ ? = call getManagerSearchResults(:fundID, :managerName, :legalName, :searchStatus, :startDate, :endDate, :strategyID) }
</sql-query>

</hibernate-mapping>

where manager.ManagerSearchResultsBean is not mapped in a hibernate-mapping. I am getting the following error:

Unknown entity: com.harrisalternatives.struts.research.manager.ManagerSearchResultsBean

This class is not directly related to one table so if it has to be mapped I'm not sure how I would do it. Is it required that the results of a stored procedure call be a class mapped in a hibernate-mapping?
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

In order to do that without mapping the object is to use Scalar results. and map the scalar results to attributes in your object. I think mapping the object might be simpler and easier.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Kimberly Greuling
Greenhorn

Joined: Aug 21, 2006
Posts: 5
I'm not sure how I would map this object. Like I said its not directly related to a table. What this is doing is calling a stored procedure that does a search across multiple tables and returns result rows that span multiple tables.
Harathi Rao
Ranch Hand

Joined: Oct 31, 2004
Posts: 42
I have the same problem. I am forced to map to a dummy table when I am calling stored procedure from hibernate mapping file. Otherwise it throws UnKnown Entity Exception.

Mark says we can overcome using scalar. I would request him to explain with an example.

Thanks
Harathi
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

So using the properties and the columns you have above in your stored procedure call, you create a class mapping that has those properties mapped to those columns, you do not need a table tag for this mapping.

Mark
Kimberly Greuling
Greenhorn

Joined: Aug 21, 2006
Posts: 5
I mapped the class and the stored procedure in the same hibernate mapping as follows

<hibernate-mapping>

<class
name="manager.ManagerSearchResultsBean"
entity-name="managerSearchResultsBean">

<id name="managerID">
<generator class="assigned" />
</id>

<property name="managerName" />
<property name="yearEnd"/>
</class>

<sql-query name="getSearchResults_SP" callable="true">
<return alias="managerSearchResultsBean" entity-name="managerSearchResultsBean"/>

{? = call dbo.getSearchResults (:managerID, :startDate, :endDate)}

</sql-query>
</hibernate-mapping>

This mapping seems to be just fine. When I try to execute the query, I get a SQLException with Invalid Parameter Binding. The stored procedure on my SQLServer DB is just a select across multiple tables

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE dbo.getSearchResults
@pManagerId INT
, @pStartDate smalldatetime
, @pEndDate smalldatetime
AS
BEGIN
SET nocount on
select distinct manager.ManagerID as managerId, manager.ManagerName as managerName, le.FiscalYearEnd as fiscalYearEnd
from Managers manager
join legalEntities le on manager.managerId = le.managerId
join partnershipLegalEntityMap ple on le.LegalEntityId = ple.LegalEntityId
join funds f on ple.fundId = f.fundId
join managerMapping mm on manager.managerId = mm.managerId
join managerComposite mc on mm.CompositeId = mc.CompositeId
JOIN strategyMapping sm ON mc.CompositeID = sm.compositeId
join strategy s on sm.StrategyId = s.strategyId
WHERE manager.ManagerID = isnull(@pManagerId, manager.ManagerID)
SET nocount off
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Besides the SQLException problem, how do I map the id field if its not really an id on a specific table as in this case?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Procedure - Can use unmapped class for result?