Using javax or eclipse link @NamedStoredProcedureQuery, I would like to call a sql server stored procedure that does not have a cursor or and out parameter and return the result set without having to map the results manually.
When I use springs @Procedure, an OUT parameter is automatically added and I get error back from the database saying I have too many parameters, which makes sense. If I don't, I can only return void but then have no way of getting the results.
Is there any way to avoid all the boiler plate code with mapping the parameters and then manually mapping the result set? I have limited ability to alter the stored procedures since they are shared.
I am looking for some example of how to have results mapped using an entity for a stored procedure that doesn't have an OUT parameter or a Cursor. (I suspect it isn't possible, but I would like to hear that from someone who has been using these for longer than me (2 days))
PS I've seen this done using Oracle, but, they had an out parameter defined.
ALTER PROCEDURE [dbo].[spGetInstitution]
-- Add the parameters for the stored procedure here
@InstitutionID INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
a.InstitutionID,
a.DisplayName
FROM tblInstitution a
WHERE a.InstitutionID = @InstitutionID
END
Have you seen the docs for StoredProcedureQuery? If your procedure returns a result set, getResultList will work if you specify the resultClasses or resultSetMappings when creating the query if these are required.
Something like:
StoredProcedureQuery query = em.createStoredProcedureQuery("spGetInstitution", Institution.class);
query.registerStoredProcedureParameter("InstitutionID", Integer.class, ParameterMode.IN);
query.setParameter("InstitutionID", institutionID);
boolean result = query.execute();
assertTrue("Result did not return true for a result set.", result);
List<Institution> institutionResults = query.getResultList();