07 noviembre, 2006

Invocar Stored Procedures desde Java

Este capítulo se compone de dos partes. Primero analizaremos la forma de invocar procedimientos almacenados en la base de datos utilizando JDBC. Luego, veremos una manera de encapsular toda la complejidad que implica invocarlos, de forma tal que la utilización de stored procedures desde programas Java resulte simple, clara y transparente para el programador.

Analizaremos tres casos:

1 – Como invocar a una función (MK_EMP).
2 – Como invocar a un procedimiento con parámetros de entrada y parámetros de salida (MV_EMPS),
3 – Como invocar a un procedimiento que retorna un CURSOR en uno de sus parámetros de salida (GET_EMPS).


Invocar a una Función

Tenemos la siguiente función:

FUNCTION MK_EMP
(
p_ename IN VARCHAR2
, p_hiredate IN DATE
, p_deptno IN NUMBER
)
RETURN IS r_empno INTEGER

Esta función recibe ename, hiredate y deptno. Inserta una fila con estos datos en la tabla EMP y retorna la PK asignada (empno). La PK la genera automaticamente considerando el siguiente valor de la máxima PK registrada. Es decir: SELECT MAX(empno)+1.



Types.INTEGER es una consante definida en la clase java.sql.Types. Esta clase define constantes que representan a los tipos de dato posibles en SQL.
En el caso de esta función estamos indicando que el valor de retorno (o parámetro de salida) es un valor SQL de tipo INTEGER.


Invocar a un Procedimiento Simple

Consideremos el siguiente stored procedure:

PROCEDURE MV_EMPS
(
old_deptno IN NUMBER
, new_deptno IN NUMBER
, movidos OUT NUMBER
)

Este procedimiento recibe dos números de departamento (old_deptno y new_deptno). Mueve todos los empleados desde el viejo departamento hacia el nuevo y retorna en un parámetro (movidos) de salida la cantidad de empleados movidos.




Invocar un procedimiento que retorna un Cursor

TYPE ROW_EMP is RECORD
(
empno NUMBER(4,0)
, deptno NUMBER(3,0)
, ename VARCHAR2(60)
, hireate DATE
)

TYPE EMP_CURSOR IS REF CURSOR RETURN ROW_EMP

PROCEDURE GET_EMPS
(
empleados OUT EMP_CURSOR
, p_deptno IN NUMBER)
)

Este procedimiento recibe como parámetro un número de departamento (deptno) y retorna un cursor (empleados) con todos los empleados que pertenecen a ese departamento.




Automatizar la invocación de Stored Procedures

Como podemos ver, la forma de invocar procedimientos y funciones es la misma. Pero es suficientemente engorrosa como para desconcentrar al programador y abrirle el camino para cometer errores.

Desarrollaremos una serie de clases con las que podremos representar lo que estudiamos hasta el momento.



Vemos que la clase Stored tiene una colección de Parameter. Las pequeñas diferencias de manejo y de concepto que puedan existir entre un procedimiento y una función las resolveremos en las clases Procedure y Function. Analogamente existen tres tipos diferentes de parámetros: parámetros de salida, de entrada y de entrada/salida. Por esto definimos la clase Parameter como base y sus tres subclases.


La clase Parameter

Simplemente se trata de una estructura con todos los datos que identifican a un parámetro:
  • paramType – puede ser cualquiera de estos: {IN, OUT, INOUT}
  • paramSqlType – Cualquier constante definida en java.sql.Types. Por ejemplo: Types.VARCHAR. También (si fueran tipos más especificos) se puede utilizar tipos propios definidos por el fabricante de la base de datos, por ejemplo: OracleTypes.CURSOR
  • paramName – Nombre del parámetro.
  • paramValue – Solo utilizable para los parámetros de entrada y de entrada/salida.



Ahora analizaremos las clases que representan los tipos de parámetro específicos: INParam, OUTParam, INOUTParam.







Como vemos estas clases simplemente setean en super() los argumentos que pueden harcodearse dado el tipo de parámetro que representan.


Llegó el turno de la clase que maneja toda la lógica: Stored. Veamos el código completo y luego lo analizaremos por partes.










Análisis del código

1 – La clase define dos constantes: PROCEDURE y FUNCTION y dos atributos: procName y type. Justamente type representa el tipo de procedimiento (PROCEDURE o FUNCTION) y procName en nombre del mismo. Ambos valores se setean en el contructor.



2 – Se define un atributo paramIndex (inicializado en 1) y una Hashtable parameters. Esta tabla tendrá instancias de Parameter que se irán agregando con el método addParameter(). Al momento de agregar cada parámetro, en este método se le setea al parámetro la posición (paramIndex) y luego se incrementa paramIndex.
Si bien estamos brindando al usuario la funcionalidad de manejar los parámetros por nombre, internamente los manejamos por posición porque (dependiendo del driver) la funcionalidad de accederlos por nombe no siempre está implementada.
Definimos también un método getParameter() que retorna el valor del parámetro especificado. Esto aplica solo a parámetros de salida los cuales (obviamente) tendrán valor luego de haber ejecutado el procedimiento.



3 – Comienza el método execute(). Este método se ocupa de armar dinamicamente la sentencia SQL en función del tipo de procedimiento (PROCEDURE o FUNCTION) y de los parámetros que se le hayan agregado. Esto se resuelve en el método privado _obtenerSQL() que lo analizaremos mas adelante.



4 - Ya tenemos creada la sentencia SQL y hemos preparado la llamada al procedimiento. Ahora tenemos que indicar que parámetros y de que tipo son y (si corresponde) que valores de entrada tienen.

Con un for recorremos la tabla de parámetros y luego trabajamos en función del tipo de parámetro. Si el parámetro es IN entonces seteamos su valor de entrada. Si el parámetro es OUT lo registramos como parámetro de salida. Si el parámetro es INOUT hacemos las dos cosas.

Luego de setear los parámetros estamos listos para ejecutar el procedimiento.



5 - Por último tenemos que recuperar los resultados de salida. Para esto volvemos a recorrer la tabla de parámetros y por cada parámetro obtenemos su posición relativa (recordemos que la posición se seteó al momento de agregar el parámetro con el método addParameter()).
Solo nos interesan los parámetros de salida y de entrada/salida (OUT e INOUT). Obtenemos el valor del parámetro accediendo al CallableStatement (cs) por posición y lo seteamos en el atributo value de la instancia de Parameter.



6 - Cerramos el método execute()



7 - El método privado _obtenerSQL(int spType) retorna la llamada SQL necesaria en función del tipo de procedimiento (spType) y de los parámetros que se le hayan agregado a través del método addParameter().




Análisis de la clase Procedure



Vemos que es muy simple. Solamente tiene un constructor que recibe el nombre del procedimiento e invoca a super (constructor del padre) pasándole ese nombre y harcodeando el tipo de procedimiento: PROCEDURE.


Analisis de la clase Function

Esta clase es un poco más complicada que la anterior. La diferencia entre una función y un procedimiento es que la función tiene un valor de retorno. Si bien la clase (o interface) CallableStatement maneja este valor como un parámetro de salida más, nosotros vamos a darle al programador la idea de que realmente la función tiene un verdadero valor de retorno.

Para esto definimos como constante un nombre de parámetro especial (RETURN_VALUE) con el que vamos a manejar el valor de retorno de la función.

En el constructor simplemente seteamos el tipo de procedimiento (FUNCTION).

Con el métodos setReturnType registramos como parámetro de salida el valor de retorno de la función.

Por último, para facilitar el trabajo al programador que utilice esta clase definimos varios métodos del tipo getReturnTypeAsXXX donde existe un XXX para cada uno de los tipos de dato de Java. Estos métodos retornan el valor de retorno de la función casteado al tipo de dato que corresponda.




Mapeando nuestros procedimientos y funciones

Teniendo programadas todas las clases base, las utilizaremos para mapear y encapsular nuestros procedimientos y funciones, de forma tal para el programador sean muy simples de usar.



Comencemos recordando la función MK_EMP:

FUNCTION MK_EMP
(
p_ename IN VARCHAR2
, p_hiredate IN DATE
, p_deptno IN NUMBER
)
RETURN IS r_empno INTEGER

La vamos a mapear definiendo una clase MkEmp.java que extienda de Function (como vemos en el diagrama).



Una función main() que utilize lo anterior podría ser:



Recordemos ahora el procedimiento MV_EMPS:

PROCEDURE MV_EMPS
(
old_deptno IN NUMBER
, new_deptno IN NUMBER
, movidos OUT NUMBER
)

La clase que lo mapea es:



y una función main() será:



Por último veremos como invocar un procedimiento que retorna un cursor en un parámetro de salida.

TYPE ROW_EMP is RECORD (
empno NUMBER(4,0)
, deptno NUMBER(3,0)
, ename VARCHAR2(60)
, hireate DATE
)

TYPE EMP_CURSOR IS REF CURSOR RETURN ROW_EMP

PROCEDURE GET_EMPS
(
empleados OUT EMP_CURSOR
, p_deptno IN NUMBER)
)



Y la función main() será: