[FIXED] Custom Result handling by calling store procedure in Spring Data JPA

Issue

I have requirement to call store procedures which takes input parameters. This store procedure returns custom result set, that result set i need to read and process further before return to UI. How we can achieve this?

EG:

@Query("CALL SP_EMPLOYEE_REPORT(:year)",nativeQuery = true)
List<EmpolypeeCustomReportBean> getEmployeeReport(@param("year") Integer year);

Solution

Given the following stored procedure.

CREATE PROCEDURE NAME_OF_THE_PROCEDURE(IN param VARCHAR(255), OUT retval INT)

You can call it from interface query:

@Procedure(value = "NAME_OF_THE_PROCEDURE")
int getFromStoredProcedure(String param);

Also by @Query annotation:

@Query(value = "CALL NAME_OF_THE_PROCEDURE(:input_value);", nativeQuery = true)
Integer findSomeThing(@Param("input_value") Integer name);

Or you can use named stored procedure query too.

@Entity
@NamedStoredProcedureQuery(name = "MyObj.getSomethingFromProc", 
  procedureName = "NAME_OF_THE_PROCEDURE", parameters = {
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "param", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.OUT, name = "retval", type = Integer.class)})
public class MyObj{
    // class definition
}

Then call it.

@Procedure(name = "MyObj.getSomethingFromProc")
Integer getSomethingFromStoredProc(@Param("param") String model);

Also you can use resultClasses and resultSetMapping properties in @NamedStoredProcedureQuery for complex return types.

Complex example provided by Eclipselink:

   @NamedStoredProcedureQuery(
       name="ReadUsingMultipleResultSetMappings",
       procedureName="Read_Multiple_Result_Sets",
       resultSetMappings={"EmployeeResultSetMapping", "AddressResultSetMapping", "ProjectResultSetMapping", "EmployeeConstructorResultSetMapping"}
   )

   @SqlResultSetMappings({
       @SqlResultSetMapping(
           name = "EmployeeResultSetMapping",
           entities = {
               @EntityResult(entityClass = Employee.class)
           }
       ),
       @SqlResultSetMapping(
           name = "EmployeeConstructorResultSetMapping",
           classes = { 
               @ConstructorResult(
                   targetClass = EmployeeDetails.class,
                   columns = {
                       @ColumnResult(name="EMP_ID", type=Integer.class),
                       @ColumnResult(name="F_NAME", type=String.class),
                       @ColumnResult(name="L_NAME", type=String.class),
                       @ColumnResult(name="R_COUNT", type=Integer.class)
                   }
               )
           }
       )
   })
   public Employee(){
       ....
   }

Answered By - zlaval