Usando CallableStatements para ejecutar procedimientos almacenados
0. Índice de contenidos.
- 1. Introducción.
- 2. Entorno
- 3. Interfaz CallableStatements
- 4. Ejemplo
- 5. Referencias
- 6. Conclusiones
1. Introducción
JDBC (Java Database Connectivity) es un API que nos proporciona una serie de interfaces para poder acceder a cualquier base de datos que la implemente. El API
nos abstrae de los detalles específicos de cada base de datos permitiendonos conectar practicamente de igual manera independientemente de la bases de datos.
De entre todas las interfaces que nos proporciona el API nos centraremos en la interfaz CallableStatements que nos permitirá ejecutar procedimientos almacenados.
Esto puede venir bien sobre todo cuando estamos trabajando en proyectos en los que por distintos motivos gran parte del modelo de negocio se encuentra en base de
datos y se quiere por ejemplo explotar ese negocio desde una nueva aplicación desarrollada en otra tecnología, Java concretamente.
Para el tutorial de hoy se ha utilizado MySQL con lo que necesitaremos el driver correspondiente Conector/J
que actualmente esta en la versión 5.1 . Normalmente es cada base de datos la que pone a nuestra disposición el driver para JDBC que permitirá relizar la conexión de manera sencilla.
2. Entorno
El tutorial está escrito usando el siguiente entorno:
- Hardware : Portátil Mac Book Pro 15″ (2,6 Ghz Intel Core i7, 4 GB DDR3).
- Sistema Operativo:Mac OS X Snow Leopard 10.6.7
- Eclipse Helios
- MySQL 5.1.35
- Java 1.6
3. Interfaz CallableStatements
Lo primero de todo cometaros que podemos obtener más información acerca de la síntaxis de los procedimientos almacenados de MySQL desde
el siquiente enlace Procedimientos almacenados y funciones.
A partr de MySQL 5.0 cuando se usa con Conector/J 3.1.1 o posterior la interfaz CallableStatements esta completamente implementada
excepto el metodo getParameterMetaData().
Partimos de la situación inicial de un procedimiento almacenado que retorna el valor de un inOutParam incrementado en 1 y la cadena de caracteres pasada via
inputParam como Resultset:
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT) BEGIN DECLARE z INT; SET z = inOutParam + 1; SET inOutParam = z; SELECT inputParam; |
Para usar el procedimiento almacenado demoSP seguimos los siguientes pasos:
- 1. Preparamos el callable statement usando Connection.prepareCall().
Tened siempre en cuenta que hemos de usar la sintaxis de escape de JDBC siendo obligatorios los paréntesis que rodean los parámetros.
123456789101112131415import java.sql.CallableStatement;...//// Prepare a call to the stored procedure 'demoSp'// with two parameters//// Notice the use of JDBC-escape syntax ({call ...})//CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");cStmt.setString(1, "abcdefg");...Nota:
Connection.prepareCall() es un método costoso, debido a la petición de metadatos que hace el driver para suportar los parámetros de salida.Por razones de rendimiento, intentad minimizar llamadas innecesarias a Connection.prepareCall() reusando instancias de CallableStatement.
- 2. Registramos los parámetros de salida (si existen)
Para recibir los valores de los parámetros de salida (parámetros especificados como OUT o INOUT cuando se crea el procedimiento), JDBC requiere que
esten especificados antes de la ejecución del statement usando los distinitos modos de
registrar un parámetro con el método registerOutputParameter():1234567891011121314151617...// Registers the second parameter as output, and// uses the type 'INTEGER' for values returned from// getObject()//cStmt.registerOutParameter(2, Types.INTEGER);//// Registers the named parameter 'inOutParam', and// uses the type 'INTEGER' for values returned from// getObject()//cStmt.registerOutParameter("inOutParam", Types.INTEGER);... - 3. Especificamos los parámetros de entrada (si existen)
Los parámtros de entrada y de entrada/salida se especifican como en los objetos PreparedStatement .
Sin embargo, CallableStatement también soporta especificar los parámetros por nombre:1234567891011121314151617181920212223242526272829...//// Set a parameter by index//cStmt.setString(1, "abcdefg");//// Alternatively, set a parameter using// the parameter name//cStmt.setString("inputParameter", "abcdefg");//// Set the 'in/out' parameter using an index//cStmt.setInt(2, 1);//// Alternatively, set the 'in/out' parameter// by name//cStmt.setInt("inOutParam", 1);. - 4. Ejecutamos CallableStatement , y recibimos cualquier conjunto de resultados o parámetros de salida.
Mientras CallableStatement soporta llamar a cualquiera de los métodos de ejecución de Statement ( executeUpdate(), executeQuery() o execute() ),
el método más flexible es execute(), ya que no necesita saber de antemano si el procedimiento almacenado retorna un conjunto de resultados:1234567891011121314151617181920212223...// Process all returned result setscStmt.execute();final ResultSet rs = cStmt.getResultSet();while (rs.next()) {// process result set...}//// Retrieve output parameters//// Connector/J supports both index-based and// name-based retrieval//int outputValue = cStmt.getInt(1); // index-basedoutputValue = cStmt.getInt("inOutParam"); // name-based...
4. Ejemplo.
A continuación os muestro un simple ejemplo completo para ver un poquito lo que hemos explicado anteriormente. Será necesario añadir al buildPath el
jar correspondiente del driver de MySQL.
Por un lado tenemos una clase de apoyo que nos ayuda a establecer la conexión con la base de datos:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
package com.autentia.tutos; import java.sql.Connection; import java.sql.DriverManager; public class ConnectionMySQL{ public static Connection con_mysql; public static Connection conectar(String host, String user, String password, String db) throws Exception { String databaseURL = "jdbc:mysql://" + host + "/" + db; Class.forName("com.mysql.jdbc.Driver"); con_mysql = DriverManager.getConnection(databaseURL, user, password); return con_mysql; } } |
y por otro lado la clase que realiza la llamada al procedimiento almacenado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
package com.autentia.tutos; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; public class SampleStoredProcedures { public static void main(String[] args) throws SQLException{ Connection conn = null; try { // Creamos la conexion conn=ConnectionMySQL.conectar("localhost", "user", "password", "****"); conn.setAutoCommit(false); CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}"); cStmt.setString(1, "abcdefg"); cStmt.setInt(2, 0); cStmt.registerOutParameter("inOutParam", Types.INTEGER); cStmt.execute(); final ResultSet rs = cStmt.getResultSet(); while (rs.next()) { System.out.println("Cadena de caracteres pasada como parametro de entrada="+rs.getString("inputParam")); } int outputValue = cStmt.getInt("inOutParam"); System.out.println("Parametro de salida incrementado="+outputValue); }catch (Exception e) { conn.rollback(); e.printStackTrace(); }finally{ conn.close(); } } } |
Obteniendo como resultado:
Cadena de caractéres pasada como parametro de entrada=abcdefg
Parametro de salida incrementado =1
5. Referencias.
6. Conclusiones.
Como hemos visto la interfaz CallableStatements nos permite ejecutar procedimientos almacenados e interactuar con el resultado de una manera rápida y sencilla
posicionandose como una buena opción a la hora de trabajar con procedimientos almacenados.
Espero que les sirva de utilidad, hasta próximos tutoriales.
Un saludo.
Saúl
Gracias por el aporte, pero quisiera ver un ejemplo donde pueda registrar los datos mediante formulario.
Hola que pasa si a la conexión no se le hace un rollback en el Exception y en el finally un close? que riesgos hay?. Gracias!
muchas gracias, me sirvió mucho
Buenas,
¿sabéis si hay opción de pasar el nombre del procedimiento al que se llama parametrizado? es decir algo así:
String nom_procedimiento = ‘demoSp’;
CallableStatement cStmt = conn.prepareCall(«{call ?(?, ?)}»);
No valdría lo siguiente por que se activa una vulnerabilidad de seguridad (SQL_INJECTION)
CallableStatement cStmt = conn.prepareCall(«{call «+nom_procedimiento+»(?, ?)}»);
Gracias,
Saludos.