Tutoriales

Usando CallableStatements para ejecutar procedimientos almacenados

Por

Saúl García Díaz

4 agosto, 2011

94373

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:

CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUT inOutParam INT)
BEGIN
    DECLARE z INT;
    SET z = inOutParam + 1;
    SET inOutParam = z;

    SELECT inputParam;

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.

import 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");  

   ...

import 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():

...  

// 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);  
...

...

// 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:

...  

  //  
  // 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);  

.

...

// 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:

   ...  

   // Process all returned result sets  
      cStmt.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-based  

    outputValue = cStmt.getInt("inOutParam"); // name-based  

...

...

// Process all returned result sets

cStmt.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-based

outputValue = 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:

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;  
    }  

}

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:

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();  
        }  
    }  
}

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.

http://dev.mysql.com/

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

sgdiaz@autentia.com

4 Comentarios

Renzo Garcia 30 junio, 2015 at 5:17 am

Gracias por el aporte, pero quisiera ver un ejemplo donde pueda registrar los datos mediante formulario.

Responder
Jonathan 26 enero, 2016 at 12:09 am

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!

Responder
bk 6 mayo, 2016 at 7:57 pm

muchas gracias, me sirvió mucho

Responder
SPM 1 junio, 2017 at 10:28 am

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.

Responder

Usando CallableStatements para ejecutar procedimientos almacenados

Usando CallableStatements para ejecutar procedimientos almacenados

0. Índice de contenidos.

1. Introducción

2. Entorno

3. Interfaz CallableStatements

4. Ejemplo.

5. Referencias.

6. Conclusiones.

4 Comentarios

Dejar respuesta Cancelar respuesta

Envíanos tu tutorial

Usando CallableStatements para ejecutar procedimientos almacenados

0. Índice de contenidos.

1. Introducción

2. Entorno

3. Interfaz CallableStatements

4. Ejemplo.

5. Referencias.

6. Conclusiones.

Artículo relacionadosMás del autor

SQL: función de ventana LAG y LEAD

Sector STEM y talento femenino: Una relación en construcción

Microfrontends con Module Federation

Cómo hacer un buen CV

¿DevOps? Eso es cosa del pasado, conoce MLOps

4 Comentarios

Dejar respuesta Cancelar respuesta

Envíanos tu tutorial

Artículo relacionados Más del autor