5 Ejecución de sentencias de descripción de datos¶

Introducción¶

Normalmente cuando desarrollamos una aplicación JDBC conocemos la estructura de las tablas y datos que estamos manejando, es decir, conocemos, las columnas que tienen y cómo están relacionadas entre sí. Es posible que no conozcamos la estructura de las tablas de una base de datos, en este caso la información de la base de datos se puede obtener a través de los meta objetos, que no son más que objetos que proporcionan información sobre las bases de datos.

La interfaz DatabaseMetaData proporciona información sobre la base de datos a través de múltiples métodos de los cuáles es posible obtener gran cantidad de información. Muchos de estos métodos devuelven un ResultSet, algunos son:

El siguiente ejemplo conecta con la base de datos MySQL de nombre empleados y muestra información sobre el producto de base de datos, el driver, la URL para acceder a la base de datos, el nombre de usuario y las tablas y vistas del esquema actual. Un esquema se corresponde generalmente con un usuario de la base de datos. El Método getMetaData() de la interfaz Connection devuelve un objeto DataBaseMetaData que contiene información sobre la base de datos representada por el objeto Connection:

public class MetaData {
    public void show() {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Se establece conexión con la bases de datos
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "acdat", "acdat");
            DatabaseMetaData dbmd = connection.getMetaData();
            ResultSet result = null;

            String name   = dbmd.getDatabaseProductName();
            String driver = dbmd.getDriverName();
            String url    = dbmd.getURL();
            String user   = dbmd.getUserName();

            System.out.println("INFORMACIÓN SOBRE LA BASE DE DATOS");
            System.out.println("==================================");
            System.out.printf("Nombre : %s\n", name);
            System.out.printf("Driver : %s\n", driver);
            System.out.printf("URL    : %s\n", url);
            System.out.printf("User   : %s\n", user);

            // Obtener información de las tablas y vistas que hay
            result = dbmd.getTables(null, "empleados", null,
                    null);

            while(result.next()){
                String catalogue = result.getString(1);
                String schema = result.getString(2);
                String table = result.getString(3);
                String type = result.getString(4);
                System.out.printf("%s - Catalogo: %s, Esquema: %s, Nombre: %s\n",
                        type, catalogue, schema, table);
            }

            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new MetaData().show();
    }
}

Los métodos usados son los siguientes:

Ejecución de sentencias de manipulación de datos¶

En ejemplos anteriores vimos como se podían ejecutar sentencias SQL mediante la interfaz Statement (sentencia), esta proporciona métodos para ejecutar sentencias SQL y obtener los resultados. Como Statement es una interfaz no se pueden crear objetos directamente, en su lugar los objetos se obtienen con una llamada al método createStatement() de un objeto Connection válido: Statement sentencia = connection.createStatement();

Al crearse un objeto Statement se crea un espacio de trabajo para crear consultas SQL, ejecutarlas y para recibir los resultados de las consultas. Una vez creado el objeto se pueden usar los siguientes métodos:

• ResultSet executeQuery(String): se utiliza para sentencias SQL que recuperan datos de un único objeto ResultSet, se utiliza para las sentencias SELECT.
• int executeUpdate(String): se utiliza para sentencias que no devuelven un ResultSet como son las sentencias de manipulación de datos (DML): INSERT, UPDATE y DELETE; y las sentencias de definición de datos (DDL): CREATE, DROP y ALTER. El método devuelve un entero indicando el número de filas que se vieron afectadas y en el caso de las sentencias DDL devuelve el valor 0.
• boolean execute(String): se puede utilizar para ejecutar cualquier sentencia SQL. Tanto para las que devuelven un ResultSet (por ejemplo, SELECT), como para las que devuelven el número de filas afectadas (por ejemplo, INSERT, UPDATE, DELETE) y para las de definición de datos como por ejemplo, CREATE. El método devuelve true si devuelve un ResultSet (para recuperar las filas será necesario llamar al método getResultSet()) y false si se trata de un recuento de actualizaciones o no hay resultados; en este caso se usará el método getUpdateCount() para recuperar el valor devuelto.

En este ejemplo execute() ejecuta una sentencia SELECT, devuelve true; por tanto, es necesario recuperar las filas devueltas usando el método getResultSet()

public class Execute {
    public void show() {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "acdat", "acdat");
            String sql = "SELECT * FROM departamentos";
            Statement sentence = connection.createStatement();
            boolean valor = sentence.execute(sql);

            if(valor){
                ResultSet result = sentence.getResultSet();
                while(result.next()){
                    System.out.printf("%d, %s, %s\n",
                            result.getInt(1),
                            result.getString(2),
                            result.getString(3));
                }
                result.close();
            } else {
                int f = sentence.getUpdateCount();
                System.out.printf("Filas afectadas: %d\n", f);
            }
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Something went wrong");
        }
    }

    public static void main(String[] args) {
        new Execute().show();
    }
}

A través de un objeto ResultSet se puede acceder al valor de cualquier columna de la fila actual por nombre o por posición, también se puede obtener información sobre las columnas como el número de columnas o su tipo. Algunos son:

Ahora, en los siguientes ejemplo, mostraremos una sentencia de inserción, actualización y eliminación.

public class Insert {
    public void show() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "acdat", "acdat");

            int dep = 50;
            String dName = "RRHH";
            String loc = "BILBAO";

            String sql = String.format("INSERT INTO departamentos VALUES (%d, '%s', '%s')",
                    dep, dName, loc);
            System.out.println(sql);

            Statement sentence = connection.createStatement();
            int rows = sentence.executeUpdate(sql);
            System.out.printf("Filas afectadas: %d\n", rows);

            sentence.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Something went wrong");
        }
    }

    public static void main(String[] args) {
        new Insert().show();
    }
}

public class Update {
    public void show() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "acdat", "acdat");

            int dep = 50;
            double update = 10;

            String sql = String.format("UPDATE empleados SET salario = salario + %.0f WHERE dept_no = %d", update, dep);
            System.out.println(sql);

            Statement sentence = connection.createStatement();
            int rows = sentence.executeUpdate(sql);
            System.out.printf("Filas afectadas: %d\n", rows);

            sentence.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Something went wrong");
        }
    }

    public static void main(String[] args) {
        new Update().show();
    }
}

public class Delete {
    public void show() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "acdat", "acdat");

            int dep = 50;

            String sql = String.format("DELETE FROM departamentos WHERE dept_no = %d", dep);
            System.out.println(sql);

            Statement sentence = connection.createStatement();
            int rows = sentence.executeUpdate(sql);
            System.out.printf("Filas afectadas: %d\n", rows);

            sentence.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("Something went wrong");
        }
    }

    public static void main(String[] args) {
        new Delete().show();
    }
}

Ejecución de Scripts¶

Algunas bases de datos admiten la ejecución de varias sentencias DDL y/o DML en una misa cadena. Por ejemplo, múltiples insert. Para ello es necesario indicarlo en la conexión añadiendo la propiedad allowMultiQueries=true, tal que así:

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados?allowMultiQueries=true", "usuario", "usuario");

A continuación, vamos a insertar el siguiente script a la base de datos a través de un programa java:

SET FOREIGN_KEY_CHECKS = 0;
drop table if EXISTS notas;
drop table if EXISTS alumnos;
drop table if EXISTS asignaturas;
CREATE TABLE IF NOT EXISTS ALUMNOS(
DNI VARCHAR(10) NOT NULL primary key,
APENOM VARCHAR(30),
DIREC VARCHAR(15),
POBLA VARCHAR(15),
TELEF VARCHAR(10)
);

CREATE TABLE IF NOT EXISTS ASIGNATURAS(
COD int NOT NULL primary key,
NOMBRE VARCHAR(25)
);

CREATE TABLE IF NOT EXISTS NOTAS(
DNI VARCHAR(10) NOT NULL,
COD int NOT NULL,
NOTA int,
primary key(DNI, COD)
);

/*Create Foreign Keys*/
ALTER TABLE NOTAS
 ADD CONSTRAINT FKNOTASALUM FOREIGN KEY (DNI)
 REFERENCES ALUMNOS (DNI) ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE NOTAS
 ADD CONSTRAINT FKNOTASASIG FOREIGN KEY (COD)
 REFERENCES ASIGNATURAS (COD) ON UPDATE CASCADE ON DELETE RESTRICT;

/*Rellenar Datos*/
INSERT IGNORE INTO ASIGNATURAS VALUES (1, 'Prog. Leng. Estr.');
INSERT IGNORE INTO ASIGNATURAS VALUES (2, 'Sist. Informáticos');
INSERT IGNORE INTO ASIGNATURAS VALUES (3, 'Análisis');
INSERT IGNORE INTO ASIGNATURAS VALUES (4, 'FOL');
INSERT IGNORE INTO ASIGNATURAS VALUES (5, 'RET');

INSERT IGNORE INTO ALUMNOS VALUES('12344345', 'Alcalde García, Elena', 'C/Las Matas, 24', 'Madrid', '917766545');

INSERT IGNORE INTO ALUMNOS VALUES('4448242', 'Cerrato Vela, Luis', 'C/Minas 28 - 3A', 'Madrid', '916566545');

INSERT IGNORE INTO ALUMNOS VALUES('56882942', 'Díaz Fernández, María', 'C/Luis Vives 25', 'Móstoles', '915577545');

INSERT IGNORE INTO NOTAS VALUES('12344345', 1, 6);
INSERT IGNORE INTO NOTAS VALUES('12344345', 2, 5);

INSERT IGNORE INTO NOTAS VALUES('4448242', 4, 6);
INSERT IGNORE INTO NOTAS VALUES('4448242', 5, 8);

INSERT IGNORE INTO NOTAS VALUES('56882945', 1, 8);
INSERT IGNORE INTO NOTAS VALUES('56882942', 3, 7);

COMMIT;

public class ImportScript {
    public void show() {
        File scriptFile = new File("src/main/resources/sql.sql");
        System.out.println("\n\nFichero de consulta: " + scriptFile.getName());
        System.out.println("Convirtiendo el fichero a cadena...");

        try (BufferedReader entrada = new BufferedReader(new FileReader(scriptFile));
             Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados?allowMultiQueries=true",
                     "acdat", "acdat");
             Statement sentence = connection.createStatement()
        ){
            StringBuilder stringBuilder;
            String line;
            stringBuilder = new StringBuilder();
            String jump = System.getProperty("line.separator");

            while ((line = entrada.readLine()) != null) {
                stringBuilder.append(line);
                stringBuilder.append(jump);
            }

            String consulta = stringBuilder.toString();
            System.out.println(consulta);

            int res = sentence.executeUpdate(consulta);
            System.out.println("Script creado con éxito, res = " + res);

        } catch (FileNotFoundException e) {
            System.err.println("ERROR NO HAY FILE: " + e.getMessage());
        } catch (IOException e) {
            System.err.println("I/O Error");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        new ImportScript().show();
    }
}

Sentencias preparadas¶

La interfaz PreparedStatement nos va a permitir construir una cadena de caracteres SQL con placeholder o marcadores de posición, que representarán los datos que serán asignados más tarde, el placeholder se representa mediante el símbolo interrogación (?)

Cada placeholder tiene un índice, el 1 correspondería al primero que se encuentre en la cadena,el 2 al segundo y así sucesivamente. Solo se pueden utilizar para ocupar el sitio de los datos en la cadena SQL, no se pueden usar para representar una columna o un nombre de una tabla, por ejemplo select * from ? sería incorrecto.

Antes de ejecutar un PreparedStatement es necesario asignar los datos para que cuando se ejecute la base de datos asigne variables de unión con estos datos y ejecute la orden SQL. Los objetos PreparedStatement se pueden preparar o pre-compilar una sola vez y ejecutar las veces que queramos asignado diferentes valores a los marcadores de posición, en cambio en los objetos Statement, la sentencia SQL se suministra en el momento de ejecutar.

Los métodos de PreparedStatement tienen los mismos nombres que en Statement: executeQuery(), executeUpdate(), execute() pero no necesita enviar la cadena de caracteres con la orden SQL en la llamada ya que lo hace el método prepareStatement(String).

Para asignar valor a cada uno de los marcadores de posición se utilizan los métodos setXXX(int index, Tipo valor).

Para asignar valores NULL a un parámetro se usa el método setNull() el formato es: void setNull(int index, int tipoSQL), siento tipoSQL una constante definida en la librería java.sql.Types, que son: ARRAY, BIGINT, BIT, BLOB, BOOLEAN, CHAR, CLOB, DATALINK, DATE, DECIMAL, DISTINCT, DOUBLE, FLOAT, INTEGER, JAVA_OBJECT, LONGVARCHAR, LONGVARBINARY, LONGVARCHAR, NCHAR, NCLOB, NULL, NUMERIC, NVARCHAR, OTHER, REAL, REF, REF_CURSOR, ROWID, SMALLINT, SQLXML, STRUCT, TIME, TIME_WITH_TIMEZONE, TIMESTAMP, TIMESTAMP_WITH_TIMEZONE, TINYINT, VARBINARY, VARCHAR

public class PlaceholderQueries {
    public void show() throws ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String sql = "INSERT INTO departamentos VALUES (?,?,?)";
        try (
                Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                        "acdat", "acdat");
                PreparedStatement sentence = connection.prepareStatement(sql);
            ) {

            sentence.setInt(1, 60);
            sentence.setString(2, "DERECHO");
            sentence.setString(3, "C/ Bilbao");

            int rows = sentence.executeUpdate();

            System.out.println("Filas: " + rows);

        } catch (SQLException ex) {
            System.out.println("Something went wrong");
        }
    }

    public static void main(String[] args) throws ClassNotFoundException {
        new PlaceholderQueries().show();
    }
}

Ejecución de Procedimientos¶

Los procedimientos almacenados en la base de datos consisten en un conjunto de sentencias SQL y del lenguaje procedural utilizado por el sistema gestor de base de datos que se pueden llamar por su nombre para llevar a cabo alguna tarea en la base de datos. Pueden definirse con parámetros de entrada (IN), de salida (OUT), de entrada/salida (INOUT) o sin ningún parámetro. También pueden devolver un valor, en este caso se trataría de una función.

delimiter //
CREATE PROCEDURE subida_sal(d INT, subida INT)
BEGIN
 UPDATE empleados SET salario = salario + subida WHERE dept_no = d;
 COMMIT;
END;
//

delimiter //
CREATE FUNCTION nombre_dep(d int) RETURNS VARCHAR(15)
BEGIN
 DECLARE nom VARCHAR(15);
 SET nom = 'INEXISTENTE';
 SELECT dnombre INTO nom FROM departamentos
 WHERE dept_no=d;

 RETURN nom;
END;
//

DELIMITER //
CREATE PROCEDURE datos_dep(d int, OUT nom VARCHAR(15), OUT locali VARCHAR(15))
BEGIN
 SET locali = 'INEXISTENTE';
 SET nom = 'INEXISTENTE';
 SELECT nombre, loc INTO nom, locali FROM departamentos
 WHERE dept_no=d;
END;
//

La interfaz CallableStatement permite que se pueda llamar desde Java a los procedimientos almacenados. Para crear un objeto se llama al método prepareCall(String) del objeto Connection. En el String se declara la llamada al procedimiento o función, tiene dos formatos, uno incluye el parámetro de resultado (usado para las funciones) y el otro no: {? = call <nombre_procedure>[(<arg1\>, <arg2\>, ...)]} o {call <nombre_procedure>[(<arg1\>, <arg2\>, ...)]}

Si los procedimientos y funciones incluyen parámetros de entrada y salida es necesario indicarlos en forma de marcadores de posición. El parámetro de resultado y los parámetros de salida deben ser registrados antes de realizar la llamada.

String sql = "{call subida_sal (?, ?)}";
CallableStatement llamada = connection.prepareCall(sql);

Hay 4 formas de declarar las llamadas a los procedimientos y funciones que dependen del uso u omisión de parámetros, y de la devolución de valores. Son las siguientes:

• {call procedimiento}: para un procedimiento almacenado sin parámetros.
• { ? = call función}: para una función almacenada que devuelve un valor no recibe parámetros, el valor se recibe a la izquierda del igual y es el primer parámetro de resultado.
• { call procedimientos(?, ?,...)}: para un procedimiento almacenado que recibe parámetros
• {? = call función (?,?,...)}: para una función almacenada que devuelve un valor (primer parámetro) y recibe varios parámetros.

public class ProcedureCall {
    public void show() {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/empleados",
                    "root", "");

            int dep = 10;
            float subida = 100;

            //construir orden de llamada
            String sql = "{call subida_sal(?,?)}";

            // Preparar la llamada
            CallableStatement call = connection.prepareCall(sql);

            // Dar valor a los argumentos
            call.setInt(1, dep);
            call.setFloat(2, subida);

            // Ejecutar procedimiento
            call.executeUpdate();
            System.out.println("Subida realizada...");

            call.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException cn){
            System.out.println("Something went wrong");
            cn.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new ProcedureCall().show();
    }
}

En MySQL al ejecutarlo puede que se muestre el siguiente error: java.sql.SQLException: User does not have access to metadata required to determinate stored procedure parameter types..., si el usuario no tiene permisos para ejecutar procedimientos. En este caso debemos darle privilegio SELECT sobre la tabla de sistema mysql.proc que contiene la información sobre todos los procedimientos: GRANT SELECT ON mysql.proc TO 'usuario'@'localhost'

Cuando un procedimiento o función tiene parámetros de salida (OUT) deben ser registrados antes de que la llamada tenga lugar, si no se registra se producirá un error. El método que se utilizará es registerOutParameter(int index, int tipoSQL), siendo el primer parámetro es la posición y el siguiente es una constante definida en la clase java.sql.Types.

Una vez ejecutada la llamada al procedimiento, los valores de los parámetros OUT e INOUT se obtienen con los métodos getXXX() similares a los utilizados para obtener los valores de las columnas en un ResultSet.

try{
    Class.forName(driver);
    Connection connection = DriverManager.getConnection(url);

    int dep = 10;

    String sql = "{? = call nombre_dep (?, ?)}";

    CallableStatement call = connection.prepareCall(sql);

    call.registerOutParameter(1, Types.VARCHAR); // registro de parametro OUT
    call.setInt(2, dep);
    call.registerOutParameter(3, Types.VARCHAR);

    call.executeUpdate();
    System.out.printf("Nombre Dep: %s, Localidad: %s\n", call.getString(1), call.getString(3));

    call.close();
    connection.close();
} catch (ClassNotFoundException cn){
    cn.printStackTrece();
} catch (SQLException e) {
    e.printStackTrace();
}