14 Stored Procedure

To execute a stored procedure that has been created in database,

Create a StoredProcedureQuery, register IN/INOUT/OUT parameters.
Set IN/INOUT parameter values.
Call execute() method.
Get query results and INOUT/OUT parameter values.

For example,

MySql:

	create procedure getEmployees(IN employerId bigint, INOUT minSalary decimal, 
		OUT totalSalary decimal)
	BEGIN 
		select e.id employeeId, e.name employeeName from Employee e 
			where e.employerId=employerId and e.salary>=minSalary; 
		select min(salary) into minSalary from Employee;
		select sum(salary) into totalSalary from Employee; 
	END

Create and execute stored procedure query:

	StoredProcedureQuery storedProcedureQuery = em.createStoredProcedureQuery("getEmployees");
	storedProcedureQuery.registerStoredProcedureParameter("employerId", Long.class, ParameterMode.IN);
	storedProcedureQuery.registerStoredProcedureParameter("minSalary", BigDecimal.class, ParameterMode.INOUT);
	storedProcedureQuery.registerStoredProcedureParameter("totalSalary", BigDecimal.class, ParameterMode.OUT);
	
	storedProcedureQuery.setParameter("employerId", 1L);
	storedProcedureQuery.setParameter("minSalary", 60000);
			
	storedProcedureQuery.execute();

Fetch results and out parameter values:

	List resultList = storedProcedureQuery.getResultList();
	BigDecimal minSalary = (BigDecimal) storedProcedureQuery.getOutputParameterValue("minSalary");
	BigDecimal totalSalary = (BigDecimal) storedProcedureQuery.getOutputParameterValue("totalSalary");

Oracle stored procedure supports REFCURSOR that returns result set as an output parameter.

	create procedure getEmployees(employerId IN NUMBER, minSalary IN OUT NUMBER, totalSalary OUT NUMBER,
		resultSetCursor OUT SYS_REFCURSOR) AS
	BEGIN
		open resultSetCursor for select e.id employeeId, e.name employeeName from Employee e 
			where e.employerId=employerId and e.salary>=minSalary;
		select min(salary) into minSalary from Employee;
		select sum(salary) into totalSalary from Employee;
	END;

	storedProcedureQuery.registerStoredProcedureParameter("resultSetCursor", 
		ResultSet.class, ParameterMode.REF_CURSOR);

Get result set:

	List resultList = (List) storedProcedureQuery.getOutputParameterValue("resultSetCursor");

Named Stored Procedure

The named stored procedure for MySql example above with result set mapping

	@SqlResultSetMapping(name="employeeResultSetMapping", entities={
			@EntityResult(entityClass = Employee.class, fields={
					@FieldResult(name="id", column="employeeId"),
					@FieldResult(name="name", column="employeeName")
			})}
	)

	@NamedStoredProcedureQuery(name = "getEmployeesSP", procedureName = "getEmployees",
			parameters = {@StoredProcedureParameter(name = "employerId", type = Long.class, mode = ParameterMode.IN),
					@StoredProcedureParameter(name = "minSalary", type = BigDecimal.class, mode= ParameterMode.INOUT),
					@StoredProcedureParameter(name = "totalSalary", type = BigDecimal.class, mode= ParameterMode.OUT)},
			resultSetMappings = {"employeeResultSetMapping"})
	@Entity
	public class Employee {
	
	}

Create and execute stored procedure query:

	StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery("getEmployeesSP");		
	storedProcedureQuery.setParameter("employerId", 1L);
	storedProcedureQuery.setParameter("minSalary", 60000);
		
	storedProcedureQuery.execute();
	List<Employee> employees = storedProcedureQuery.getResultList();

With the result set mapping, the query result list is transformed to a list of Employee entities.

Multiple Result Sets & Update Counts

In addition to OUT/INOUT parameter values, A stored procedure may return multiple result sets and update counts.

Example: a stored Procedure has the following select, update and delete DML statements:

	select statement 1
	select statement 2
	update statement 3
	select statement 4
	delete statement 5

Get result sets and update counts:

	boolean hasMoreResults = storedProcedureQuery.execute(); // true
	List results1 = storedProcedureQuery.getResultList();
	
	hasMoreResults = storedProcedureQuery.hasMoreResults(); // true
	List results2 = storedProcedureQuery.getResultList();
	
	hasMoreResults = storedProcedureQuery.hasMoreResults(); // false
	int updateCount3 = storedProcedureQuery.getUpdateCount();
	
	hasMoreResults = storedProcedureQuery.hasMoreResults(); // true
	List results4 = storedProcedureQuery.getResultList();
	
	hasMoreResults = storedProcedureQuery.hasMoreResults(); // false
	int updateCount5 = storedProcedureQuery.getUpdateCount();
	
	// no more result sets or update counts
	hasMoreResults = storedProcedureQuery.hasMoreResults(); // false
	int updateCount = storedProcedureQuery.getUpdateCount(); // -1

← Native Query→ SubqueryFrames / No Frames