Download Oracle y Java (2)

Oracle y Java
Continuación *
* Tomado del curso de Francisco Moreno
13/01/08
Seminario de Bases de Datos
1
Consultando un VARRAY de números
desde Java
Previamente se ha creado una tabla cuya estructura es:
CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(4,2);
CREATE TABLE varray_table(
cod VARCHAR2(8),notas num_varray);
INSERT INTO varray_table
VALUES(100, num_varray(3,4));
INSERT INTO varray_table
VALUES(200, num_varray(2,5,3));
13/01/08
Seminario de Bases de Datos
2
import
import
import
import
java.sql.*;
java.math.*;
oracle.sql.*;
oracle.jdbc.driver.*;
//Requiere el driver de Oracle
// Requiere el driver de Oracle
class consulta_varray
{
static public void main(String[] args)
{
Connection conn;
Statement stmt;
ResultSet resultado;
System.out.println( "Intentando conexión a la bd..." );
Continúa
13/01/08
Seminario de Bases de Datos
3
try { // Se carga el driver JDBC-ODBC
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch( Exception e ) {
System.out.println("No hay driver JDBC" );
return;
}
try { // Se establece conexion con la base de datos
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@200.24.8.35:1521:xue", "user", "password");
stmt = conn.createStatement();
}
Continúa
13/01/08
Seminario de Bases de Datos
4
catch( SQLException e ) {
System.out.println( "No se pudo conectar con la base de datos." );
return;
}
try {
System.out.println( "Consultando notas...");
resultado = stmt.executeQuery("SELECT * FROM varray_table");
CHAR ced; //Alternativa a String, pero exclusivo para Oracle
ARRAY mi_array;
BigDecimal out_value;
BigDecimal[] values;
Continúa
13/01/08
Seminario de Bases de Datos
5
while (resultado.next())
{
ced= (CHAR)((OracleResultSet)resultado).getOracleObject(1);
System.out.println("Notas del estudiante con código " + ced);
mi_array = ((OracleResultSet)resultado).getARRAY(2);
values = (BigDecimal[]) mi_array.getArray();
for(int i=0; i<values.length; i++)
{
out_value = (BigDecimal) values[i];
System.out.println("Nota nro " + (i+1) + " " +
out_value.intValue());
}
}
Continúa
13/01/08
Seminario de Bases de Datos
6
conn.close();
}catch( SQLException e ) {
System.out.println("Se produjo el error " + e.getMessage());
return;
}
System.out.println("Consulta finalizada." );
}
}
Fin
13/01/08
Seminario de Bases de Datos
7
13/01/08
Seminario de Bases de Datos
8
Creando y Consultando una tabla
anidada de objetos
Se van a crear desde Java los siguientes objetos:


Tipo dir_type con atributos tel NUMBER(8)
dir VARCHAR2(10)
Tipo de tabla anidada de dir_type contacto_list
Clave Primaria

Tabla estudiante con columnas ced VARCHAR2(8)
contactos contacto_list
Y se insertarán algunas filas…
13/01/08
Seminario de Bases de Datos
9
Estudiante
ced


Las tablas anidadas al igual
que los VARRAYs se mapean al
tipo ARRAY
En este caso para el manejo
de cada uno de los contactos
(dir_type) se necesitará
adicionalmente trasformar el
ARRAY en un tipo Datum
13/01/08
10
20
contactos
101
Cl 4
12
Av 8
201
Cl 9
42
Av 3
Seminario de Bases de Datos
10
import
import
import
import
java.sql.*;
java.math.*;
oracle.sql.*;
oracle.jdbc.driver.*;
class tab_anidada
{
static public void main( String[] args )
{
Connection conn;
Statement sentencia;
ResultSet resultado;
System.out.println("Intentando conexión a la bd..." );
13/01/08
Seminario de Bases de Datos
11
try { // Se carga el driver JDBC-ODBC
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch( Exception e ) {
System.out.println( "No se pudo cargar el driver JDBC" );
return;
}
try { // Se establece la conexion con la base de datos
conn = DriverManager.getConnection
("jdbc:oracle:thin:@200.24.8.35:1521:xue", "user", "password");
sentencia = conn.createStatement();
} catch( SQLException e ) {
System.out.println( "No hay conexion con la base de datos." );
return;
}
13/01/08
Seminario de Bases de Datos
12
System.out.println("Creando objetos...");
try{ sentencia.execute("CREATE TYPE dir_type AS OBJECT(tel NUMBER(8), dir
VARCHAR2(10))"); }
catch( SQLException e ){
System.out.println("Tipo dir_type ya creado" );}
try{ sentencia.execute("CREATE TYPE contacto_list AS TABLE OF dir_type");
}
catch( SQLException e ){
System.out.println("Tipo contacto ya creado" );}
try{ sentencia.execute("CREATE TABLE estudiante (ced VARCHAR2(8) PRIMARY
KEY, contactos contacto_list) NESTED TABLE contactos STORE AS
nestels"); }
catch( SQLException e ){
System.out.println("Tabla estudiante ya creada" );}
13/01/08
Seminario de Bases de Datos
13
try{
sentencia.execute("INSERT INTO estudiante VALUES(10,
contacto_list(dir_type(101, 'cl 4'), dir_type(12, 'av 8')))");
sentencia.execute("INSERT INTO estudiante VALUES(20,
contacto_list(dir_type(201, 'cl 9'), dir_type(42, 'av 3')))");
}catch( SQLException e ){
System.out.println("Tabla estudiante ya creada" );}
try{
System.out.println("Consultando...cedulas y telefonos de cada estudiante"
);
resultado = sentencia.executeQuery("SELECT * FROM estudiante");
ARRAY mi_array;
String cedula;
Datum[] values;
13/01/08
Seminario de Bases de Datos
14
while (resultado.next())
{
cedula = resultado.getString(1);
mi_array = ((OracleResultSet)resultado).getARRAY(2);
System.out.println("Los teléfonos del estudiante con cédula "+
cedula + " son:");
values = (Datum[]) mi_array.getOracleArray();
Object[] attribs;
STRUCT out_value;
for(int i=0;i<values.length;i++){
out_value = (STRUCT) values[i];
attribs = out_value.getAttributes();
System.out.println("Tel " + (i+1) + "= " + attribs[0]);
}
}
13/01/08
Seminario de Bases de Datos
15
} catch( SQLException e ) {
System.out.println("se produjo elerror " +
e.getMessage());
return;
}
System.out.println("Consulta finalizada.");
}
}
13/01/08
Seminario de Bases de Datos
16
13/01/08
Seminario de Bases de Datos
17
Ejecutando una función PL/SQL
desde Java


¿Cómo enviar parámetros y cómo recibir el resultado? (tanto de la
función como de parámetros de retorno: OUT)
Se definirá una función escrita en PL/SQL la cual devuelve una
cadena de caracteres y tiene 2 parámetros:
-Uno de entrada, de tipo VARCHAR
-Uno de salida, de tipo DATE
13/01/08
Seminario de Bases de Datos
18

Se invocará desde Java mediante un bloque anónimo
Begin llamado_función End;



Para ello se usará el objeto: OracleCallableStatement
Luego se recibirá el resultado y se imprimirá desde Java por
pantalla
Inicialmente se crea la función directamente en SQL*Plus, luego se
verá cómo incluso puede ser creada dinámicamente desde Java
13/01/08
Seminario de Bases de Datos
19
CREATE OR REPLACE FUNCTION saludo
(nom IN VARCHAR2, fecha OUT DATE)
RETURN VARCHAR2 IS
cadena VARCHAR2(20);
BEGIN
fecha := SYSDATE;
cadena := 'Hola ' || nom;
RETURN cadena;
END;
/
13/01/08
Seminario de Bases de Datos
20
import
import
import
import
java.sql.*;
java.math.*;
oracle.sql.*;
oracle.jdbc.driver.*;
class llamafunc
{
static public void main( String[] args )
{
Connection conn;
Statement stmt;
ResultSet resultado;
System.out.println("Intentando conexión a la bd..." );
13/01/08
Seminario de Bases de Datos
21
try {// Se carga el driver JDBC-ODBC
Class.forName ("oracle.jdbc.driver.OracleDriver");
} catch( Exception e ) {
System.out.println( "No se pudo cargar el driver JDBC" );
return;
}
try {// Se establece la conexion con la base de datos
conn = DriverManager.getConnection
("jdbc:oracle:thin:@200.24.8.35:1521:xue","user", "password");
stmt = conn.createStatement();
} catch( SQLException e ) {
System.out.println( "No hay conexión con la base de datos." );
return;
}
13/01/08
Seminario de Bases de Datos
22
try {
//Se prepara el llamado y los parámetros
System.out.println( "Invocando una función con parámetros de envio y
retorno..." );
OracleCallableStatement cstmt =
(OracleCallableStatement)conn.prepareCall
("begin ? := saludo(?,?); end;");
cstmt.registerOutParameter(1,Types.CHAR);
cstmt.setString(2,"Jhonny");
cstmt.registerOutParameter(3,Types.DATE);
13/01/08
Seminario de Bases de Datos
23
// Se ejecuta el llamado a la función y se recogen los resultados
cstmt.execute();
CHAR mensaje = (CHAR)((OracleCallableStatement)cstmt).getOracleObject(1);
Timestamp hoy = (Timestamp) ((CallableStatement)cstmt).getObject(3);
System.out.println(mensaje + " la fecha del dia de hoy es: " + hoy);
conn.close();
} catch( SQLException e ) {
System.out.println("Se produjo el siguiente error " +
e.getMessage());
return;
}
System.out.println("Funcion ejecutada" );
}
}
13/01/08
Seminario de Bases de Datos
24
13/01/08
Seminario de Bases de Datos
25
Ahora se creará la función dinámicamente :
Agregar el siguiente código al programa anterior inmediatamente antes de:
//Se prepara el llamado y los parámetros
System.out.println("Se creará la función dinámicamente");
String f;
f = "BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION saludo";
f = f + "(nom IN VARCHAR, fecha OUT DATE)";
f = f + "RETURN VARCHAR2 IS ";
Ojo: Son 2 comillas simples seguidas
f = f + "cadena VARCHAR2(20);";
f = f + "BEGIN";
f = f + " fecha := SYSDATE;";
f = f + " cadena := ''Hola Don '' || nom;";
f = f + " RETURN cadena;";
f = f + "END;'; END;";
//System.out.println( f);
OracleCallableStatement cstmt2 =
(OracleCallableStatement)conn.prepareCall(f);
cstmt2.execute();
13/01/08
Seminario de Bases de Datos
26
13/01/08
Seminario de Bases de Datos
27
También se puede llamar una función de envoltura…por ejemplo:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED
"numero_java" AS import java.math.*;
public class numero_java {
public static BigDecimal factorial(BigDecimal n){
if (n.compareTo(new BigDecimal(0))==0)return new BigDecimal(1);
else return n.multiply(factorial(n.subtract(new BigDecimal(1))));
}
};
/
CREATE OR REPLACE FUNCTION fact(n NUMBER)
RETURN NUMBER AS LANGUAGE JAVA NAME
'numero_java.factorial (java.math.BigDecimal) return java.math.BigDecimal';
/
Ojo: En la función de envoltura debe especificarse la jerarquía completa del tipo (BigDecimal)
13/01/08
Seminario de Bases de Datos
28
import java.sql.*;
import java.math.*;
class llamafact
{
static public void main( String[] args )
{ Connection c; Statement s;ResultSet r;
try{ Class.forName ("oracle.jdbc.driver.OracleDriver");
}catch(Exception e ){ System.out.println("No driver");return;}
try{ c = DriverManager.getConnection
("jdbc:oracle:thin:@200.24.8.35:1521:xue","user", "pass");
s = c.createStatement();
}catch(SQLException e){ System.out.println("Sin conexión");return;}
try{ r = s.executeQuery
("SELECT fact(4) AS mifact FROM dual");
while (r.next()) System.out.println(r.getInt("mifact"));
c.close();
}catch( SQLException e ){ System.out.println(e.getMessage());}
}
}
13/01/08
Seminario de Bases de Datos
29



Lo que significa que al invocar desde Java a esta función se está llamando a un
método de una clase Java construida dentro de Oracle, la cual igualmente
podría ser construida dinámicamente desde el programa Java externo…
Al igual que con Java es posible accesar la base de datos Oracle (y en general
cualquier SGBD) con otros lenguajes, por ejemplo PHP, C++, Visual Basic
etc…y el mecanismo de conectividad y manipulación es similar
El próximo paso es llevar los resultados a la web los cuales serán visualizados
en un navegador (Internet Explorer, Firefox, etc.)
13/01/08
Seminario de Bases de Datos
30