La mayoría de aplicaciones trabajan con datos alojados en bbdd (aunque no tienen porqué, podríamos consumir un API REST, por ejemplo, para tratar los datos).

Todos los lenguajes de programación tienen librerías para poder conectar con las diferentes bbdd. Ésto puede suponer un problema, ya que, si cambiamos de SGBD (Oracle a MySQL, por ejemplo) nos obligaría probablemente a modificar nuestras clases.

Por suerte, la mayoría de lenguajes de programación también tienen librerías capaces de conectarse a diferentes bbdd sin necesidad de modificar los métodos. En realidad, son un conjunto de interfaces y clases abstractas, con lo que si cualquier fabricante quiere que su driver sea compatible con esas librerías tiene que seguir sus especificaciones.

En Java la más utilizada es JDBC.

JDBC (Java Data Base Connection) es un API incorporado a java que nos permite la conexión y manipulación de diferentes SGBD. Existen drivers de la mayoría de SGBD (Oracle, MySQL, MariaDB, SQLServer…) que implementan dicha API (recordemos que es un conjunto de interfaces y clases abstractas), con lo que podemos utilizar los mismos métodos para diferentes SGBD.

JDBC nos ayuda a realizar en nuestras aplicaciones 3 tareas básicas con SGBD:

• Conectar con una bbdd

• Ejecutar queries en la bbdd

• Tratar los resultados de las queries anteriores

En JDBC normalmente usamos dos clases que nos permiten conectar con una fuente de datos:

• DriverManager

• DataSource

La diferencia fundamental es la gestión que hace de las diferentes conexiones abiertas. Con la primera (DriverManager), cada vez que queramos ejecutar una consulta abrimos una conexión, realizamos la consulta y cerramos la conexión. Con la segunda clase (DataSource) tenemos un pool de conexiones abiertas y cada vez que queramos ejecutar alguna consulta nos asigna una que esté libre.

En nuestro caso, vamos a utilizar DriverManager, ya que vamos a hacer aplicaciones sencillas y no necesitamos controlar diferentes conexiones ni concurrencia de hilos.

Para conectar a nuestra bbdd tenemos que ejecutar el método getConnection de la clase DriverManager. A este método le tenemos que pasar un String con la URL de conexión a nuestra bbdd. Esta URL está compuesta por el driver que vamos a usar (MySQL, Oracle, SQLServer…), el host, puerto y nombre de la bbdd y otras opciones, como el usuario y la contraseña de nuestra bbdd.

jdbc:mysql://localhost:3306/nombre_bbdd?user=usuario_bbdd&password=password_bbdd

Vamos a crear un proyecto donde conectaremos con una bbdd MariaDB de imdb.zip. Lo primero que haremos será crear el proyecto Maven. En nuestro caso, el SGBD es MariaDB, con lo que tendremos que bajarnos la librería JDBC correspondiente (MariaDB Connector/J). Asegúrate de instalar la dependencia correcta en Maven:

    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>3.1.2</version>
    </dependency>

Aunque podríamos poner la url de conexión a mano, vamos a utilizar un archivo de propiedades para configurar nuestra conexión. Igual que hace Spring, llamaremos al archivo application.properties y lo ubicaremos en la carpeta resources. Dentro configuraremos nuestra conexión:

#BBDD
daw1bookstore.datasource.url = jdbc:mariadb://localhost:3306/imdb
daw1bookstore.datasource.username = root
daw1bookstore.datasource.password = root

A continuación, crearemos una clase para poder leer ese archivo de conexión:

public class AppPropertiesReader {

    private final Properties properties = new Properties();

    public static AppPropertiesReader instace;

    private AppPropertiesReader() {
        loadAppProperties();
    }

    public static AppPropertiesReader getInstance() {
        if (instace == null) {
            instace = new AppPropertiesReader();
        }
        return instace;
    }

    private void loadAppProperties() {
        String fileName = "application.properties";
        try (InputStream input = Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName)) {
            properties.load(input);
        } catch (IOException e) {
            System.out.println("Error loading properties file: " + fileName);
            throw new RuntimeException("Error loading properties file: " + fileName);
        }
    }

    public String getProperty(String key) {
        return properties.getProperty(key);
    }

}

Hemos hecho la clase Singleton, con lo que sólo podremos acceder al objeto a través de su método getInstance(). Además, usamos un objeto de la clase properties, la cual se utiliza para manejar conjuntos de propiedades del sistema, como configuraciones de aplicaciones, almacenándolas en pares clave-valor y facilitando su lectura y escritura desde y hacia archivos de propiedades.

Para cargar el fichero de propiedades, primero cargamos nuestro recurso (application.properties) mediante Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName).getResourceAsStream(fileName). Thread.currentThread().getContextClassLoader()Thread.currentThread().getContextClassLoader() obtiene una referencia al ClassLoader asociado con el contexto de carga actual, lo que permite cargar recursos como archivos de propiedades desde el classpath de la aplicación. Esta técnica es comúnmente utilizada en aplicaciones Java para cargar recursos de forma dinámica en tiempo de ejecución.

Por último, el método load() de la clase Properties carga las propiedades desde el flujo de entrada (input) proporcionado. En este caso, las propiedades se cargarán desde el archivo application.properties y se almacenarán en el objeto properties de nuestra clase.

Ahora, cada vez que queramos cargar una propiedad de nuestro fichero, será tan sencillo como:

private final AppPropertiesReader appPropertiesReader = AppPropertiesReader.getInstance();
appPropertiesReader.getProperty("daw1bookstore.datasource.url")

Nuestro siguiente paso será crear la clase de conexión con la bbdd:

public class DBConnection {

    private final Connection connection;

    private final AppPropertiesReader appPropertiesReader = AppPropertiesReader.getInstance();

    public DBConnection() {
        System.out.println("Establishing connection with the database...");
        try {
            connection = DriverManager.getConnection(
                    appPropertiesReader.getProperty("daw1bookstore.datasource.url"),
                    appPropertiesReader.getProperty("daw1bookstore.datasource.username"),
                    appPropertiesReader.getProperty("daw1bookstore.datasource.password")
            );
            System.out.println("Connection established with the database");
        } catch (SQLException e) {
            throw new RuntimeException("Error connecting to the database.");
        }
    }

    public Connection getConnection(){
        return connection;
    }
}

Para ejecutar consultas mediante una conexión abierta, primero tenemos que prepararla mediante parametrización.

¿Qué son las consultas parametrizadas? Imaginemos que hacemos una web de una biblioteca donde mostremos un formulario para consultar libros por su título. Supongamos que el título que nos envía el usuario lo almacenamos en la variable title. Nuestro siguiente paso sería ejecutar una consulta SELECT para buscar el título del libro, que sería tan sencilla como:

String title = //aquí recogeríamos el título enviado por el usuario
String sql = "SELECT * FROM libros WHERE titulo = '" + title + "'";

En principio, esto funcionaría de forma correcta (obviamente, podríamos mejorar la consulta para buscar títulos parciales, buscar por autor…). Si el usuario nos envía “El nombre de la rosa”, nuestra consulta se transformaría en:

SELECT * FROM libros WHERE titulo = 'El nombre de la rosa'

¿Qué pasa si el usuario nos envía la siguiente cadena: “';DELETE FROM libros”?

Ahora, nuestra consulta sería:

SELECT * FROM libros WHERE titulo =''; DELETE FROM libros

Como ves, si no tomamos medidas y nos fiamos de los datos que nos envían los usuarios, nuestra bbdd es vulnerable a diversos tipos de ataques. El ataque anterior es uno de los más populares, y se conoce como SQL Injection. Para evitar este tipo de ataques usamos sentencias parametrizadas.

Para poder ejecutar sentencias SQL, primero debemos crear un objeto Statement. Un objeto Statement sirve para poder procesar una sentencia SQL y obtener los resultados. Existen 3 tipos de objetos Statement:

• Statement: Ejecutar sentencias sencillas SQL sin parámetros

• PreparedStatement: Ejecutar sentencias SQL con parámetros

• CallableStatement: Ejecutar procedimientos almacenados con parámetros de entrada y salida

Una vez tenemos creado el objeto Statment, podemos utilizar uno de sus siguientes métodos para ejecutar consultas SQL:

• execute(): Devuelve True si el primer objeto que la query devuelve es un ResultSet (conjunto de registros de una tabla)

• executeQuery(): Devuelve un objeto ResultSet. Usado para ejecutar sentencias SELECT

• executeUpdate(): Devuelve un entero indicando el número de filas afectadas por la sentencia SQL. Se utiliza para ejecutar sentencias INSERT, UPDATE o DELETE

Por ejemplo, si queremos insertar una película:

            String sql = """
                INSERT INTO movies (imdb_id, title, year, image, runtime, description, director_id) 
                VALUES ('tt0071524', 'Segunda plana', 1974, null, 105, null, 'nm0000697')                    
            """;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

Para modificar:

            String sql = "UPDATE movies SET title = 'Primera plana' WHERE title = 'Segunda plana'";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

Por último, para borrar una película:

            String sql = "DELETE FROM movies WHERE title = 'Primera plana'";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();

Antes hemos visto como nunca es buena idea añadir los valores directamente a una consulta. En lugar de eso, debemos utilizar parámetros y después sustituir esos parámetros por los valores reales. Para hacerlo, primero debemos sustituir los valores de nuestras consultas por interrogantes (?):

            String sql = """
                INSERT INTO movies (imdb_id, title, year, image, runtime, description, director_id) 
                VALUES (?, ?, ?, null, ?, null, ?)                    
            """;

Después, debemos preparar la consulta y darle valor a los parámetros con el método correspondiente (setTipoDato), indicando la posición del parámetro que queremos sustituir empezando por 1:

            preparedStatement.setString(1, "tt0071524");
            preparedStatement.setString(2, "Primera plana");
            preparedStatement.setInt(3, 1974);
            preparedStatement.setInt(4, 105);
            preparedStatement.setString(5, "nm0000697");
            preparedStatement.executeUpdate();

De esta forma, el código completo quedaría:

            String sql = """
                INSERT INTO movies (imdb_id, title, year, image, runtime, description, director_id) 
                VALUES (?, ?, ?, null, ?, null, ?)                    
            """;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "tt0071524");
            preparedStatement.setString(2, "Primera plana");
            preparedStatement.setInt(3, 1974);
            preparedStatement.setInt(4, 105);
            preparedStatement.setString(5, "nm0000697");
            preparedStatement.executeUpdate();

Vamos a crear una clase que nos permita ejecutar consultas SQL de manera sencilla en una base de datos relacional. Con esta clase, podremos enviar consultas personalizadas junto con parámetros opcionales y obtener los resultados correspondientes.

public class RawSql {

    private static final DBConnection dbConnection = new DBConnection();

    public static ResultSet select(String sql, List<Object> params) {
        try {
            PreparedStatement preparedStatement = setParameters(sql, params);
            return preparedStatement.executeQuery();
        } catch (Exception e) {
            throw new RuntimeException("Error executing SQL query: " + sql);
        }
    }

    private static PreparedStatement setParameters(String sql, List<Object> values){
        try {
            Connection connection = dbConnection.getConnection();
            PreparedStatement preparedStatement =  connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            if(values != null) {
                for(int i=0;i<values.size();i++) {
                    Object value = values.get(i);
                    preparedStatement.setObject(i+1,value);
                }
            }
            return preparedStatement;
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}

Al llamar al método select, pasamos la consulta SQL y una lista opcional de parámetros. La clase se encarga de configurar los parámetros en la consulta preparada y ejecutarla, devolviendo el resultado en un ResultSet.

Por último, el método setParameter() se encarga de configurar los parámetros en una consulta preparada.

Al llamar al método connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS), preparamos la consulta SQL para ser ejecutada. El parámetro Statement.RETURN_GENERATED_KEYS indica que queremos obtener las claves generadas automáticamente después de ejecutar la consulta. Esto es útil cuando insertamos datos en una tabla con una columna autoincremental, por ejemplo, y necesitamos recuperar el valor de la clave generada.

En el bucle for, recorremos la lista de valores pasados como parámetro y los establecemos en la consulta preparada utilizando el método preparedStatement.setObject(i+1, value). Es importante destacar que utilizamos i+1 como primer parámetro del método setObject porque los índices de los parámetros en una consulta preparada comienzan en 1, no en 0.

Finalmente, devolvemos la consulta preparada lista para ser ejecutada.

Por último, creamos los diferentes métodos para insertar, actualizar o borrar registros de nuesta bbdd:

    public static Object insert(String sql, List<Object> params) {
        try {
            PreparedStatement preparedStatement = setParameters(sql, params);
            preparedStatement.executeUpdate();
            ResultSet resultSet = preparedStatement.getGeneratedKeys();
            if(resultSet.next()){
                return resultSet.getObject(1);
            } else {
                throw new RuntimeException("Unable to retrieve the last generated ID");
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error executing SQL query:" + sql);
        }
    }

    public static int update(String sql, List<Object> params) {
        return RawSql.statement(sql, params);
    }

    public static int delete(String sql, List<Object> params) {
        return RawSql.statement(sql, params);
    }


    private static int statement(String sql, List<Object> params) {
        try {
            PreparedStatement preparedStatement = setParameters(sql, params);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException("Error executing SQL query: " + sql);
        }
    }

En este caso, creamos el método statement() que utilizaremos para ejecutar consultas UPDATE o DELETE. Estos dos métodos devolverán el número de filas afectadas (actualizadas o borradas). El método insert() lo definimos aparte, lo que nos permite devolver el último id generado (por si lo necesitamos).

Ahora ya podemos utilizar esos métodos en nuestra clase principal:

        String sql = """
                INSERT INTO movies (imdb_id, title, year, image, runtime, description, director_id) 
                VALUES (?, ?, ?, null, ?, null, ?)                    
            """;
        List<Object> params = List.of(
                "tt0071524", "Primera plana", 1974, 105, "nm0000697"
        );
        System.out.println("Película insertada con id " + RawSql.insert(sql, params));

Nuestro método RawSql.select() ejecuta la consulta SQL y devuelve un conjunto de resultados de la clase ResultSet. Esta clase tiene un conjunto de métodos para recuperar los datos en función del tipo de dato de la columna (getTipoDato).

Para acceder a los datos de nuestro objeto ResultSet, lo hacemos a través de un cursor. Este cursor es un puntero que apunta a una fila de datos en el objeto ResultSet. Inicialmente, el cursor está posicionado antes de la primera línea. Podemos mover el cursor utilizando varios métodos definidos en el objeto ResultSet.

Por ejemplo, podemos utilizar el método ResultSet.next() para mover el cursor una fila hacia delante:

            String sql = "SELECT * FROM movies WHERE title = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "Seven");
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            System.out.println(
                "Título: " + resultSet.getString("title") + 
                "\nAño: " + resultSet.getInt("year") + 
                "\nDuración: " + resultSet.getInt("runtime") + " min\n"
            );

Título: Seven
Año: 1995
Duración: 127 min

El método ResultSet.next() devolverá false si ya no quedan filas por recorrer. Por lo tanto, si queremos recorrer un conjunto de datos, podemos usar un bucle while:

            String sql = "SELECT * FROM movies WHERE year = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1994);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(
                    "Título: " + resultSet.getString("title") + 
                    "\nAño: " + resultSet.getInt("year") + 
                    "\nDuración: " + resultSet.getInt("runtime") + " min\n"
                    );
            }

Título: Cadena perpetua
Año: 1994
Duración: 142 min      

Título: Pulp Fiction
Año: 1994
Duración: 154 min   

Título: Forrest Gump
Año: 1994
Duración: 142 min   

Título: El profesional (Léon)
Año: 1994
Duración: 110 min

Con todo, para mostrar el listado de películas utilizando nuestra clase RawSql:

        try {
            ResultSet resultSet = RawSql.select("SELECT * FROM movies", null);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("title"));
            }
        } catch (SQLException e) {
            System.out.println("Error al recuperar las películas");
        }

Ejercicio 1

Crea las clases DBConnection y RawSql tal y como se explica en este tema. En tu clase principal, úsala para conectar y desconectar con la bbdd imdb.zip.

Ejercicio 2

Realiza las siguientes consultas en tu clase principal usando RawSql:

• Listado de todas las películas

• Buscar una película por id

• Insertar una película

• Actualizar una película

• Borrar una película

• Listado de películas ordenadas por año de forma descendente

• Buscar una película por id con el nombre de su director en su información (ten en cuenta que la clave ajena es imdb_id del director)

• Listado de películas por imdb_id del director

• Actores de una película por id

Ejercicio 3

Crea una web sencilla donde se muestre la imagen del listado de películas en la página principal. Cuando se pinche en cada una de las imágenes se mostrará los detalles de la película, incluyendo el nombre del director y los actores.

Ejercicio 4

Haz que se puedan insertar películas en la web con actores y directores que ya existan en la bbdd.