While somewhat overlooked, stored procedures/functions are important database objects. Normally, external programs (such as Java via JDBC ) can only directly interface with procedures or functions that retun or pass SQL types (database specific types, e.g. Pl/SQL or Pg/Plsql types are supported only within database boundaries). That said, most O/R mapping libraries have at least some support for stored procedures. Knowing how EasyORM handles stored procedures should be sufficient to create stored procedures or functions that are supported by EasyORM. If you have some existing stored procedures (e.g. legacy code) that are not supported, as a workaround, you may have to write a new stored procedure that wraps your old procedure (an example is provided under the Oracle tab).

Several code examples of calling stored procedures are given below.

Retrieving a scalar value by using getScalarValueForStoredProcedure

ConnectionPool connPool = ConnectionPool.getInstance(jdbcDriver, jdbcURL, user, password);
//stored procedures in PostgreSQL can only be used in a transaction context //PostgreSQL functions, unlike in Oracle, must be executed within a transaction context
DBTransaction dbTrx = new DBTransaction(connPool); //creating a transaction is not required in Oracle and most other DB systems to call a stored proc
DBSelect dbSelect = new DBSelect(dbTrx);
//calling the procedure
Integer largestDeptCount = (Integer) dbSelect.getScalarValueForStoredProcedure("get_largest_dept_count", null, java.sql.Types.INTEGER);

The actual PostgreSQL function (get_largest_dept_count) is defined as

//this Postgre function returns the number of employees in the largest department
CREATE OR REPLACE FUNCTION get_largest_dept_count()
RETURNS integer AS
declare
bus_dept integer default 0;
hum_dept integer default 0;
fin_dept integer default 0;
begin
select count(*) into bus_dept from employee where department = 'Business analysis';
select count(*) into hum_dept from employee where department = 'Human Resources';
select count(*) into fin_dept from employee where department = 'Finances';
if (bus_dept >= hum_dept and bus_dept >= fin_dept) then
return bus_dept;
elsif (hum_dept >= fin_dept) then
return hum_dept;
else
return fin_dept;
end if;
end;

Retrieving a cursor by using getRecordsForStoredProcedure

//first create a list for input arguments
List<Object> params = new ArrayList<>(); params.add("Human Resources"); //Human Resources is the department for which employees should be retrieved List<EmployeeDB> lEmps = dbSelect.getRecordsForStoredProcedure("get_emp_by_dept", EmployeeDB.class, params, java.sql.Types.OTHER);

The definition of the get_emp_by_dept PostgreSQL function is as the following

//this Postgre function returns a cursor that holds employee information for a particular department.
CREATE OR REPLACE FUNCTION get_emp_by_dept("deptName" character varying)
RETURNS refcursor AS
declare empCur refcursor;
begin
open empCur for select * from employee where department = "deptName";
return empCur;
end;

Retrieving a result from a not supported function

An unsupported Pl/Sql function could be defined as

CREATE OR REPLACE FUNCTION getEmployeeRecord(id IN integer )
RETURN employee%rowtype IS
emp employee%rowtype;
BEGIN
select * into
emp from employees where employee_id = id;
RETURN emp;
END;

While we can't call getEmployeeRecord directly from Java (because of the unsupported return type), we can write a new stored procedure (or function) that will invoke the unsupported function and map the result to supported types (in this case we're using OUT parameters of varchar2 and number types respectively)

CREATE OR REPLACE PROCEDURE getEmployeeRecordWrapper(empId IN integer, fName OUT varchar2, lName OUT varchar2, department OUT varchar2) AS
emp employee%rowtype;
BEGIN
emp := getEmployeeRecord(empId); //call getEmployeeRecord
IF emp is not null THEN
//map the results to the OUT variables
fName := emp.first_name;
lName := emp.last_name;
department = emp.department;
END IF;
END;

Java code calling getEmployeeRecordWrapper might look like

//set the parameters
List<Integer> inParamPositions = new ArrayList<>();
inParamPositions.add(1);
List<Object> inParamValues = new ArrayList<>();
inParamValues.add(10); //empId = 10
List<Integer> outParamPositions =new ArrayList<>();
outParamPositions.add(2);
outParamPositions.add(3);
outParamPositions.add(4);
List<Integer> outParamTypes = new ArrayList<>();
outParamTypes.add(java.sql.Types.VARCHAR);
outParamTypes.add(java.sql.Types.VARCHAR);
outParamTypes.add(java.sql.Types.VARCHAR);
//call the stored procedure
List<Object> listEmp = dbSelect.getScalarValuesForStoredProcedure("getEmployeeRecordWrapper", inParamPositions, inParamValues, outParamPositions, outParamTypes);

Retrieving a result set rather than a cursor

//We call the procedure exactly as we would if it returned a cursor but in SQL Server it doesn't have to be within a transaction. Besides, while an Sql server function/procedure can return a cursor, it normally cannot be called from an API such as JDBC, OLE DB etc.
List<EmployeeDB> lEmps = dbSelect.getRecordsForStoredProcedure("getEmployeeCertificates", EmployeeDB.class, null, 0);

The SQL server function (getEmployeeCertificates) is defined as

//Note that the procedure returns, in addition to employee information, certificate names from the certificate table so our POJO class (EmployeeDB) should have one additional getter/setter method (getCertificateName/setCertificateName) to store/retrieve that information
CREATE PROCEDURE getEmployeeCertificates AS
select a.first_name, a.last_name, b.cert_name from dbo.employee a, dbo.certificate b,
dbo.employee_certificate c where a.id = c.emp_id and b.id = c.cert_id;