how to map a non-entity result set coming from a NameStoredProcedure in JPA 2.1?

Question

I am calling a postgresql procedure using jpa 2.1 and would like to convert the result set to a non-entity class called StoreAndCategoryID containing two integer fields called: storeid, categoryid. These two fields are the ones returned from the procedure.

@NamedStoredProcedureQuery(
    name = "Category.func_getcategorybytextsearchid",
    procedureName = "func_getcategorybytextsearchid",    
    parameters = {@StoredProcedureParameter(name = "textsearchid", type = Integer.class,
                                            mode = javax.persistence.ParameterMode.IN ),
          @StoredProcedureParameter(name = "mycursor", type = void.class, 
                                    mode = ParameterMode.REF_CURSOR )}
)

the below code is the proc executed on Postgresql

CREATE OR REPLACE FUNCTION func_getcategorybytextsearchid(textsearchid integer )
  RETURNS refcursor  AS
$BODY$
declare mycursor refcursor ;
BEGIN
mycursor   = 'mycursor';

OPEN mycursor FOR (
            select storeid, categoryid 
            from item_full_text_search
            where itemfulltextsearchid = $1;

RETURN mycursor ;
end;

the below java code shows how I am calling the procedure

StoredProcedureQuery q = 
em.createNamedStoredProcedureQuery("Category.func_getcategorybytextsearchid");
q.setParameter("textsearchid", textsearchid);

if (q.execute())
{
   //the result set needs to convert to StoreAndCategoryID class if possible.
   StoreAndCategoryID storeAndCategoryID  =  q.getOutputParameterValue("mycursor");  
}

public class StoreAndCategoryID
{
        int storeid;
        int categoryid;
}

how can I change @NamedStoredProcedureQuery to return/convert non-entity class StoreAndCategoryID?

Thanks,

Answer 1

You can't map stored procedure result set to non entity class using StoredProcedureQuery. But, if you'll use (if you can use JPQL query instead of stored procedure call) TypedQuey you can use JPQL constructor expression or @SqlResultSetMapping in NativeQuery