Thursday, November 5, 2009

OBTENCIÓN DE UN ORACLE CURSOR DESDE JAVA

En muchas empresas acostumbran al momento de hacer un desarrollo de aplicaciones manejar la parte de persistencia (El acceso a los datos) , no mediante consultas SQL dentro de la misma aplicación, sino que prefieren manejarlas en base a Procedimientos Almacenados o Funciones que son invocadas desde la misma aplicación. Para este trabajo JAVA proporciona una muy buena clase y similar a PreparedStatement , en lo que respecta a su manipulación de datos, llamada CallableStatement.

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Í.

6 comments:

Alejandro said...

:D jajajajajjaja me mataste entre como disparado

Buenas pagina.

JAVAMAN said...

Que tal Alejandro .. pero no te entendì como que desparado ?

Saludos.

Tango said...

Hola, me gustaria saber mas acerca de esta Conexion, si pudiesemos hablar seria buenisimo.

JAVAMAN said...

Que es lo que deseas saber compañero, cual es tu duda ?, preguntame via este post o por email y te tratare de ayudar en lo que pueda ...

Saludos...

Anonymous said...

Gracias por el artículo, pero tengo una duda respecto al diseño del DTO "ClienteDTO". En caso que la tabla "cliente" tenga una clave foránea, por ejemplo, a una tabla "direccion", como sería el diseño del DTO? contendría una referencia a un nuevo DTO "DireccionDTO" o un atributo tipo Long o String?. La pregunta va en relación en cual sería la mejor manera de exponer al DTO a una capa de presentación o de lógica de negocio.

JAVAMAN said...

Hola compañero, mira el concepto del DTO (Data Transfer Objet) es simplemente el manejo de un Bean Híbrido donde uno pueda poner cualquier campo primitivo o Objeto para poder enviar como parámetro de un lado a otro, serializar o parsear. Eso si los Bean DTO no tienen relación con los objetos de negocio generados (Los que me comentas) ya que estos una vez generados no se deben de tocar. El ejemplo que muestro como no esta orientado a un bean mapeado de una tabla, lo creé con el concepto de un DTO ya que por motivos del ejemplo podría poner en su interior cualquier cosa.

Saludos.