En esta oportunidad mostraré como manejar, mediante un ejemplo, dicha clase CallableStatement para algo no muy común pero útil que es el llamado a un STORE PROCEDURE que internamente manejará un CURSOR, para la obtención datos de una Tabla en ORACLE y mediante JAVA obtendremos dichos datos en base a una ArrayList de un DTO (Data Transfer Object) , para cualquier manipulación posterior.
Para el ejemplo necesitaremos:
- Eclipse 3.4,
- Motor de datos Oracle 10g
- JDK 1.5 (Superior)
- classes12.jar
- DbVisualizer
I.- Crearemos en un SCHEMA de ORACLE, la tabla respectiva junto con los INSERTs de prueba (Podemos utilizar esta muy buena herramienta DbVisualizer para la creaciòn y manipulaciòn de bases de datos 'Para mi la mas completa'):
CREATE TABLE TB_CLIENTE( CODIGO INT,
TELEFONO VARCHAR2(30),
NOMBRES VARCHAR2(30),
APELLIDOS VARCHAR2(30),
DIRECCION VARCHAR2(50),
ESTADO VARCHAR2(10),
CONDICION VARCHAR2(30)
);
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 1, '5214952 ', 'Ricardo ', 'Guerra ', 'Av.Naranjal 1029 Los Olivos ', 'Activo ', 'Aprobado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 2, '2222222 ', 'Percy ', 'Rojas ', 'Sucasa ', 'Activo ', 'Aprobado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 3, '43434354 ', 'Erick ', 'Arrieta Veliz ', 'Zarate ', 'Activo ', 'Aprobado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 4, '43434555 ', 'Rosa ', 'Zarate Aquino ', 'Los Montes 444 ', 'Inactivo ', 'Eliminado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 5, '33333333 ', 'Ruben Daniel ', 'Diaz Rodriguez ', 'Los Recuerdos 115 int 9 ', 'Inactivo ', 'Eliminado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 6, '3722736 ', 'Jose ', 'Perez Suarez ', 'Las Gardenias ', 'Activo ', 'Aprobado' );
INSERT INTO TB_CLIENTE( CODIGO, TELEFONO, NOMBRES, APELLIDOS, DIRECCION, ESTADO, CONDICION ) VALUES( 7, '65656565 ', 'Harold ', 'Pajuelo Reyes ', 'Los Monos 333 ', 'Inactivo ', 'Eliminado' );
II.- Creamos un ORACLE PACKAGE (CABECERA Y CUERPO) para contener al STORE PROCEDURE que tendrá en su interior al CURSOR, ya que necesariamente un CURSOR esta ligado a un ORACLE PACKAGE.
CREATE OR REPLACE PACKAGE PQ_DEMO AS
-- AUTOR : ADMINISTRADOR RICARDO GUERRA
-- CREACION : 14/08/2009 - 09:25:43 A.M.
-- Declaracion Global
TYPE cursorDinamico IS REF CURSOR;
-- PROCEDURE #1:
PROCEDURE SP_DEMO( msgValidacion OUT VARCHAR2, listaRegistros OUT PQ_DEMO.cursorDinamico );
END PQ_DEMO ;
CREATE OR REPLACE PACKAGE BODY PQ_DEMO IS
-- PROCEDURE #1:
PROCEDURE SP_DEMO( msgValidacion OUT VARCHAR2, listaRegistros OUT PQ_DEMO.cursorDinamico
) AS
--MENSAJE DE EXITO.
MSG_EXITO CONSTANT VARCHAR2(100) := 'Transaccion Exitosa.';
--MENSAJES DE ERROR.
MSG_ERROR1 CONSTANT VARCHAR2(100) := 'Se ejecuto una consulta sin estar conectado a la BD.';
MSG_ERROR2 CONSTANT VARCHAR2(100) := 'No se encontraron registros.';
MSG_ERROR3 CONSTANT VARCHAR2(100) := 'Se efectuo una operacion no valida sobre un cursor.';
MSG_ERROR4 CONSTANT VARCHAR2(100) := 'Se encontro un error la codifcacion PL/SQL.';
MSG_ERROR5 CONSTANT VARCHAR2(100) := 'Error durante la ejecucion del SP.(Other)';
BEGIN
--OBTIENE UNA LISTA DE REGISTROS.
OPEN listaRegistros FOR
SELECT c.CODIGO, c.TELEFONO, c.NOMBRES, c.APELLIDOS, c.DIRECCION, c.ESTADO, c.CONDICION
FROM TB_CLIENTE c;
msgValidacion := MSG_EXITO;
COMMIT;
--VALIDACION DE POSIBLES ERRORES.
EXCEPTION
WHEN NOT_LOGGED_ON THEN
msgValidacion := MSG_ERROR1;
DBMS_OUTPUT.PUT_LINE( MSG_ERROR1 );
ROLLBACK;
WHEN NO_DATA_FOUND THEN
msgValidacion := MSG_ERROR2;
DBMS_OUTPUT.PUT_LINE( MSG_ERROR2 );
ROLLBACK;
WHEN INVALID_CURSOR THEN
msgValidacion := MSG_ERROR3;
DBMS_OUTPUT.PUT_LINE( MSG_ERROR3 );
ROLLBACK;
WHEN PROGRAM_ERROR THEN
msgValidacion := MSG_ERROR4;
DBMS_OUTPUT.PUT_LINE( MSG_ERROR4 );
ROLLBACK;
WHEN OTHERS THEN
msgValidacion := MSG_ERROR5;
DBMS_OUTPUT.PUT_LINE( MSG_ERROR5 );
ROLLBACK;
--CLOSE listaRegistros.
CLOSE listaRegistros;
END SP_DEMO;
END PQ_DEMO ;
III.- En la aplicación demo JAVA preparada manejamos la conexión a ORACLE dinámicamente en base a una clase para multi-conexión de Motores de datos, administrada por un jdbc.properties donde se indica a que motor se desea conectar.
### DATOS CONEXION 'MySql' ###
jdbc_mysql_driverClassName = com.mysql.jdbc.Driver
jdbc_mysql_url = jdbc\:mysql\://localhost\:3306/DB_PLANTILLA
jdbc_mysql_username = root
jdbc_mysql_password = root
### DATOS CONEXION 'Postgres' ###
jdbc_postgres_driverClassName = org.postgresql.Driver
jdbc_postgres_url = jdbc\:postgresql\://localhost\:5432/DB_PLANTILLA
jdbc_postgres_username = postgres
jdbc_postgres_password = postgres
### DATOS CONEXION 'Oracle' ###
jdbc_oracle_driverClassName = oracle.jdbc.OracleDriver
jdbc_oracle_url = jdbc\:oracle\:thin\:@10.226.0.62\:1523\:DB_PLANTILLA
jdbc_oracle_username = oracle
jdbc_oracle_password = oracle
### 'motor_db_usado' => CAMPO EDITABLE PARA LA VALIDACION ###
### DE LA CONEXION A DIFERENTES BASES DE DATOS ###
### 1=MySql, 2=Postgres, 3=Oracle. ###
### IMPORTANTE: Tiene que estar el JAR respectivo dentro de la
aplicacion ###
motor_db_usado = 3
IV.- Creamos nuestro DTO (Data Transfer Object) llamado ClienteDTO para la manipulación de los datos orientada a objetos.
package org.java.bean;
/**
* ClienteResumenDTO objeto DTO para facil manejo de datos respectivos orientados a objetos.
*
* @author Ricardo Guerra
* @version v1.5 - 07-ago-2009
* @since V1.0
*/
public class ClienteDTO{
private int codigo;
private String nombres;
private String apellidos;
private String direccion;
private String telefono;
private String condicion;
private String estado;
// Constructores
public ClienteDTO(){
}
public ClienteDTO( int codigo, String nombres, String apellidos, String direccion, String telefono, String condicion, String estado ){
this.codigo = codigo;
this.nombres = nombres;
this.apellidos = apellidos;
this.direccion = direccion;
this.telefono = telefono;
this.condicion = condicion;
this.estado = estado;
}
public String getNombres(){
return nombres;
}
public void setNombres( String nombres ){
this.nombres = nombres;
}
public String getApellidos(){
return apellidos;
}
…
…
…..
…….
V.- Creamos nuestra clase TestOracle.java que nos permitirá hacer la consulta por medio del STORE PROCEDURE, obtener el mensaje de validación de respuesta y la lista de datos obtenida por medio del CURSOR, hacer el parseo y cargar la lista de Objetos (DTO) .
public class TestOracle{
private ManejoConexiones manejoConexiones = null;
//Construye ...
{
this.manejoConexiones = new ManejoConexiones();
}
/**
* @param argumentos
*/
public static void main( String... argumentos ){
TestOracle test = new TestOracle();
List listaDatos = new ArrayList();
listaDatos.clear();
//Consulta ...
listaDatos = test.buscarCursorOracle();
//Imprime ...
test.imprimeDatos( listaDatos );
}
/**
* buscarCursorOracle
* @return List
*/
private List buscarCursorOracle(){
CallableStatement cstm = null;
Connection conexion = null;
ResultSet rs = null;
ClienteDTO clienteDTO = null;
List listaClienteResumen = new ArrayList();
try{
Connection CONEXION = Conector.getConnection( Conector.getCibertec() );
/** Se ejecuta el StoreProcedure * */
conexion = CONEXION;
cstm = conexion.prepareCall( "begin PQ_DEMO.SP_DEMO( ?, ? ); end;" );
//Registro como parametro OUTPUT #1
cstm.registerOutParameter( 1, OracleTypes.VARCHAR );
//Registra el CURSOR como parametro OUTPUT #2
cstm.registerOutParameter( 2, OracleTypes.CURSOR ); //Obtiene la Lista del CURSOR en base a la posicion del parametro.
cstm.execute();
String parametro1 = (String)cstm.getString( 1 );
System.out.println( "Parametro OUTPUT #1: " + parametro1 );
rs = (ResultSet)cstm.getObject( 2 ); //Forma #1
//rs = ((OracleCallableStatement)cstm).getCursor( 2 ); //Forma #2
System.out.println( "Parametro OUTPUT #2: " + rs );
//Lleno el Objeto ...
while( rs.next( ) ){
clienteDTO = new ClienteDTO();
//Seteos en base a los obtenido en el Store Procedure.
clienteDTO.setCodigo( rs.getInt( "CODIGO" ) );
clienteDTO.setNombres( rs.getString( "NOMBRES" ) );
clienteDTO.setApellidos( rs.getString( "APELLIDOS" ) );
clienteDTO.setDireccion( rs.getString( "DIRECCION" ) );
clienteDTO.setTelefono( rs.getString( "TELEFONO" ) );
clienteDTO.setCondicion( rs.getString( "CONDICION" ) );
clienteDTO.setEstado( rs.getString( "ESTADO" ) );
//Agregando a la lista ...
listaClienteResumen.add( clienteDTO );
}
System.out.println( "TAMAÑO LISTA: " + listaClienteResumen.size() );
}
catch( Exception e ){
e.printStackTrace();
}
finally{
this.manejoConexiones.cerrarConexiones( rs, null, cstm, conexion );
}
return listaClienteResumen;
}
/**
* imprimeDatos
* @param listaClientes
*/
public void imprimeDatos( List listaClientes ){
System.out.println( "" );
System.out.println( "IMPRIMIENDO DATOS DE 'CLIENTES'" );
System.out.println( "-------------------------------" );
for( int i=0; i
ClienteDTO cliente = (ClienteDTO)listaClientes.get( i );
System.out.println( "" );
System.out.println( "CODIGO: " + cliente.getCodigo() );
System.out.println( "NOMBRES: " + cliente.getNombres() );
System.out.println( "APELLIDOS: " + cliente.getApellidos() );
System.out.println( "DIRECCION: " + cliente.getDireccion() );
System.out.println( "TELEFONO: " + cliente.getTelefono() );
System.out.println( "CONDICION: " + cliente.getCondicion() );
System.out.println( "ESTADO: " + cliente.getEstado() );
System.out.println( "" );
}
}
El mensaje de salida obtenido será este:
CONEXION ESTABLECIDA!!! oracle.jdbc.driver.OracleConnection@16930e2
Parametro OUTPUT #1: Transaccion Exitosa.
Parametro OUTPUT #2: oracle.jdbc.driver.OracleResultSetImpl@107077e
TAMAÑO LISTA: 8
IMPRIMIENDO DATOS DE 'CLIENTES'
-----------------------------------------------------
NOMBRES: Ricardo
APELLIDOS: Guerra
DIRECCION: Av.Naranjal 1029 Los Olivos
TELEFONO: 5214952
CONDICION: Aprobado
ESTADO: Activo
NOMBRES: Percy
APELLIDOS: Rojas
DIRECCION: Sucasa
TELEFONO: 2222222
CONDICION: 22222
ESTADO: Activo
NOMBRES: Erick
APELLIDOS: Arrieta Veliz
DIRECCION: Zarate
TELEFONO: 43434354
CONDICION: Aprobado
ESTADO: Activo
…
…
…..
…….
Para descarga del demo completo pulsar AQUÍ.
