• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Stored Procedure - Can use unmapped class for result?

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Kimberly Greuling
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Trust God, but always tether your camel... to this tiny ad.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic