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

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;

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

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

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

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

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

}

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

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