Tuesday, 22 March 2016

How to call Stored Procedure in JPA 2.1

Repository class is as follows

@NamedStoredProcedureQuery(name = "getBalance", procedureName = "getTravelDetails", resultClasses = { TravelDetail.class }, parameters = {
              @StoredProcedureParameter(name = "travelDate", type = Date.class, mode = ParameterMode.IN),
              @StoredProcedureParameter(name = "travelDestination", type = String.class, mode = ParameterMode.IN),

              @StoredProcedureParameter(name = "travelPackage", type = Integer.class, mode = ParameterMode.OUT),
              @StoredProcedureParameter(name = "travelTimings", type = Integer.class, mode = ParameterMode.OUT)})

public class TravelDetail {    
       private Date travelDate;
       private String travelDestination;
       private String travelPackage;
       private Date travelTimings;

  //gettter & setter methods

}


Class that uses the repository

@PersistenceContext
       private EntityManager entityManager;

       @Override
       public void getTravelDetails(Date travelDate,String travelDestination) {
              StoredProcedureQuery storedProcedure = entityManager
                           .createStoredProcedureQuery("getTravelDetails");

              storedProcedure.registerStoredProcedureParameter("travelDate", Date.class,
                           ParameterMode.IN);

              storedProcedure.registerStoredProcedureParameter("travelDestination", String.class,
                           ParameterMode.IN);

              storedProcedure.registerStoredProcedureParameter("travelPackage",String.class,
                           ParameterMode.OUT);
              storedProcedure.registerStoredProcedureParameter("travelTimings", Date.class,
                           ParameterMode.OUT);
           

              storedProcedure.setParameter("travelDate", "20/02/2015");
              storedProcedure.setParameter("travelDestination", "Mount Alps");

              // execute SP

              storedProcedure.execute();

              String travelPackage =  storedProcedure.getOutputParameterValue("travelPackage").toString();        
           

              Date travelTimings = (Date)storedProcedure.getOutputParameterValue("travelTimings");
           
       }

No comments:

Post a Comment