Preface

Apache Commons dbutils version 1.7 shows the calls that support stored procedures. Please pay attention to the version issue when using this code.

1.7 version update method

int execute(String sql, Object… params) executes SQL statements, including stored procedure calls, which do not return any result sets.
List execute(String sql, ResultSetHandler rsh, Object… params) executes SQL statements, including stored procedure calls, that return one or more result sets.
Note: for oracle, if a stored procedure returns a result set, it will usually be specified in the out parameter through a cursor. dbutils currently does not provide method processing.

Demo1, calling parameterless stored procedure

The simple storage procedure without parameters is as follows:

 create or replace procedure pro1 
 is
 begin
      update emp set sal=sal+100;
 end pro1;

Using dbutils call:

//Retrieve current result as update count, return - 1 if result is ResultSet result set object or no result
int count = queryRunner.execute("{call pro1()}");

Demo2, calling the parameterized stored procedure

There are simple stored procedures for reference (in reference: employee number, out reference: employee name, salary, employment date) as follows:

create or replace procedure pro2(eno in number,ename out varchar2, sal out number,hiredate out date) 
is
begin
    select e.ename,e.sal,e.hiredate into ename,sal,hiredate from emp e where e.empno=eno;
end pro2;

Using the dbutils call, encapsulate the return value of any type as an OutParameter. The first parameter of the constructor is the jdbc data type, and the second parameter is the receive data type

OutParameter<String> out1=new OutParameter<>(Types.VARCHAR, String.class);
OutParameter<BigDecimal> out2=new OutParameter<>(Types.NUMERIC, BigDecimal.class);
OutParameter<java.util.Date> out3=new OutParameter<>(Types.TIMESTAMP, java.util.Date.class);
int count = queryRunner.execute("{call pro2(?,?,?,?)}",1111,out1,out2,out3);
System.out.println(count +","+out1.getValue()+","+out2.getValue()+","+out3.getValue());

Demo3, calling stored procedure with result set

The stored procedure to return the cursor is as follows: (sys_cursor is the built-in weak cursor type of oracle)

create or replace procedure pro3(datas out sys_refcursor) 
is 
begin
    open datas for select * from emp;
end pro3; 

dbutils cannot be called if you know how to call the message below.

(1) A wrong attempt to use dbutils

OutParameter<ResultSet> out = new OutParameter<>(OracleTypes.CURSOR, ResultSet.class);
queryRunner.execute(connection,"{call pro3(?)}", out);//out can actually receive the OracleJDBCRowSet object
System.out.println(connection.isClosed());//false
System.out.println(out.getValue().isClosed());//true, result set is closed

(2) Native jdbc call

CallableStatement call = connection.prepareCall({call pro3(?)});
call.registerOutParameter(1, OracleTypes.CURSOR); //For out parameters, declare the cursor (Collection) type
call.execute();
ResultSet rs = ((OracleCallableStatement)call).getCursor(1); 
while(rs.next()){
		String name = rs.getString("ename");		
		double sal = rs.getDouble("sal");
}

@author chenyuchao