4.1. Operaciones básicas

4.1.1. Conexión

Lo primero que debemos aprender es cómo abrir una conexión a la base de datos:

final String dbProtocol = "jdbc:sqlite:";

// Las bases de datos de SQLite son archivos.
Path dbPath = Path.of(System.getProperty("java.io.tmpdir"), "test.db");
String dbUrl = String.format("%s%s", dbProtocol, dbPath);

// Alternativa particular de SQLite: base de datos en memoria.
//String dbUrl = String.format("%s%s", dbProtocol, ":memory:");

try (
   Connection conn = DriverManager.getConnection(dbUrl, /* usuario */ null, /* contraseña */, null);
) {
   System.out.println("Hemos logrado conectar a la base de datos");
}
catch(SQLException err) {
   System.err.println("Error de conexión. " + err.getMessage());
}

En el código de ejemplo, hay dos claves fundamentales:

• La URL de conexión que se compone, a su vez de:

  • El protocolo que identifica al SGBD

  • La indicación de la base de datos a la que queremos conectar.

  Esta URL depende del SGBD, así que tendremos que tener presente cuál estamos utilizando al construirla:

  SGBD	Librería JDBC	Driver[1]	URL[2]
  SQLite	sqlite-jdbc	org.sqlite.JDBC	jdbc:sqlite:<ruta>
  H2	h2	org.h2.Driver	jdbc:h2:<ruta> jdbc:h2:tcp://<host>:<port>/<ruta>
  MariaDB	mariadb-java-client	org.mariadb.jdbc.Driver	jdbc:mariadb://[<host>[:<port>]]/<base_datos>
  MySQL	mysql-connector-j	com.mysql.cj.jdbc.Driver	jdbc:mysql://[<host>[:<port>]]/[base_datos]
  PostgreSQL	postgresql	org.postgresql.Driver	jdbc:postgresql://[<host>[:<port>]]/<base_datos>
  Oracle	ojdbc11	oracle.jdbc.OracleDriver	jdbc:oracle:thin:@//<host>[:<port>]/<SID/ServerName>
  SQL Server	mssql-jdbc	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://<host>[:<port>];databaseName=<base_datos>

• El objeto de conexión, creado con las estructuras try-with-resources, para que se cierre automáticamente. Al crear el objeto es necesario facilitar la URL, y un usuario y una contraseña con permisos para realizar las operaciones que se deseen llevar a cabo.

  Nota

  Como SQLite no define permisos para usuarios, sólo es necesaria la URL. Se ha dejado explícita esta falta de necesidad, pero podríamos haber definido el método ahorrándonos los dos argumentos:

  Connection conn = DriverManager.getConnection(dbUrl);
  

Truco

Cuando en nuestra aplicación queremos cargar como dependencia la librería que nos proporciona un driver es conveniente hacerlo con el scope runtime. Por ejemplo:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.51.1.0</version>
    <scope>runtime</scope>
</dependency>

Esto asegura que la librería estará disponible durante la ejecución de la aplicación, pero evita que esté disponible en tiempo de compilación, lo que se traduce en que el IDE nunca nos sugerirá clases y métodos incluidos en ella. Esto último es de importancia, ya que nosotros lo que debemos usar exclusivamente son clases proporcionadas por JDBC[3].

Por supuesto, el código es completamente inútil: nos hemos conectado a la base de datos para no hacer absolutamente nada. En los siguientes apartados veremos como leer y escribir datos.

Prudencia

La conexión a una base de datos es una operación costosa, por lo que, si la aplicación abre y cierra constantemente conexiones, el rendimiento se penalizará. Lo habitual en una aplicación que sea algo más que una prueba de concepto, es utilizar un pool de conexiones, el cual es capaz de reaprovechar las conexiones. Lo trataremos más adelante.

4.1.2. Ejecución de sentencias

El acceso mediante conectores se basa en la ejecución de sentencias SQL que pueden ser de dos tipos:

• Sentencias que modifican la base de datos.

• Sentencias que obtienen datos sin llevar a cabo modificación alguna.

A partir de un objeto Statement, para las primeras se usa el método executeUpdate, mientras que para las segundas el método executeQuery.

Nota

Hay otro método, execute, que sirve para ambos casos y que devuelve true, si hay resultados (segundo caso) o false, si no los hay. Como lo normal es que se sepa de antemano si la sentencia devuelve datos o no (p.e. un SELECT devuelve datos; un INSERT, no), podemos prescindir de ella.

4.1.2.1. Simples

Comencemos creando dos tablas:

final String dbProtocol = "jdbc:sqlite:";

String dbUrl = String.format("%s%s", dbProtocol, ":memory:");  // Base de datos en la memoria

try(Connection conn = DriverManager.getConnection(dbUrl)) {

   try(Statement stmt = conn.createStatement()) {
      // Particular de SQLite: respetar la integridad referencial (opcional).
      stmt.execute("PRAGMA foreign_keys = ON");

       stmt.executeUpdate("""
           CREATE TABLE Centro (
               id          INTEGER             PRIMARY KEY,
               nombre      VARCHAR(200)        NOT NULL,
               titularidad CHAR(7)             CHECK (titularidad IN ('pública','privada'))
           );
       """);
       stmt.executeUpdate("""
           CREATE TABLE Estudiante (
               id_estudiante  INTEGER       PRIMARY KEY /* GENERATED BY DEFAULT AS IDENTITY */,
               nombre         VARCHAR(250)  NOT NULL,
               nacimiento     DATE          NOT NULL,
               centro         INTEGER,

               constraint fk_est_cen FOREIGN KEY(centro) REFERENCES Centro(id_centro)
                   ON DELETE SET NULL
                   on UPDATE CASCADE
           );                       
       """);

       // --->AQUÍ PODEMOS AÑADIR INSTRUCCIONES PARA INSERT (ver después)
   }
   catch(SQLException err) {
      err.printStackTrace();
      System.exit(1);
   }
}

El código tiene dos aspectos:

1. Las sentencias SQL, que son sentencias SQL cuya comprensión no forma parte de los objetivos de esta unidad[4], por lo que no entraremos en comentarlas.

   Consejo

   No obstante, sí conviene precisar que procuremos al escribirlas ceñirnos al estándar, a fin de que sean lo más universales posibles y nos sea menos costoso cambiar de SGBD. A este respecto:

   • Dejamos de escribir los nombres de elementos de la base de datos (tablas, columnas) entre comillas dobles, porque el estándar SQL lo permite en caso de que los nombres no contengan caracteres “raros” (espacios, etc.) o no se tenga la intención de distinguir nombres cambiando entre mayúsculas y minúsculas (p.e. que una tabla se llame Persona y otra distinta persona o PERSONA)[5].

   • Escribimos para los tipos los nombres que define el estándar.

   • Las cadenas se encierran entre comillas simples.

2. La ejecución de esas sentencias usando JDBC. Obsérvese que:

• Reaprovechamos el mismo objeto Statement para ejecutarlas todas.

• Las ejecutamos usando el método executeUpdate, puesto que su función es modificar el contenido de la base de datos, no obtener información.

Tampoco las sentencias de inserción devuelven resultados, por lo que supuesto que hayamos definido la clase Centro:

Centro.java

package edu.acceso.test_dao.modelo;

import java.util.Arrays;

/**
 * Modela un centro de enseñanza.
 */
public class Centro implements Entity {

    public static enum Titularidad {
        PUBLICA("público"),
        PRIVADA("privado");

        private String desc;

        Titularidad(String desc) {
            this.desc = desc;
        }

        @Override
        public String toString() {
            return desc;
        }

        /**
         * Obtiene la titularidad a partir de la descripción.
         * @param desc La descripción
         * @return El elemento Titularidad o null, si no hay ninguno con esa descripción.
         */
        public static Titularidad fromString(String desc) {
            return Arrays.stream(Titularidad.values())
                .filter(t -> t.getDescripcion().compareToIgnoreCase(desc) == 0)
                .findFirst()
                .orElse(null);
        }
    }

    /**
     * Código identificativo del centro.
     */
    private Long id;
    /**
     * Nombre del centro.
     */
    private String nombre;
    /**
     * Titularidad: pública o privada.
     */
    private Titularidad titularidad;

    public Centro() {
        super();
    }

    /**
     * Carga todos los datos en el objeto.
     * @param id Código del centro.
     * @param nombre Nombre del centro.
     * @param titularidad Titularidad del centro.
     * @return El propio objeto.
     */
    public Centro cargarDatos(Long id, String nombre, Titularidad titularidad) {
        setId(id);
        setNombre(nombre);
        setTitularidad(titularidad);
        return this;
    }

    /**
     * Constructor que admite todos los datos de definición del centro.
     * @param id Código del centro.
     * @param nombre Nombre del centro.
     * @param titularidad Titularidad del centro (pública o privada)
     */
    public Centro(Long id, String nombre, Titularidad titularidad) {
        cargarDatos(id, nombre, titularidad);
    }

    @Override
    public Long getId() {
        return id;
    }

    @Override
    public void setId(Long id) {
        this.id = id;
    }

    public String getNombre() {
        return nombre;
    }

    public void setNombre(String nombre) {
        this.nombre = nombre;
    }

    public Titularidad getTitularidad() {
        return titularidad;
    }

    public void setTitularidad(Titularidad titularidad) {
        this.titularidad = titularidad;
    }

    @Override
    public String toString() {
        return String.format("%s (%s)", getNombre(), getId());
    }
}

podemos hacer:

   // Estos son los INSERT que quedaron pendientes en el código anterior
   stmt.executeUpdate("INSERT INTO Centro VALUES (11004866, 'IES Castillo de Luna', 'pública')");

   Centro[] centros = new Centro[] {
      new Centro(11007533, "IES Arroyo Hondo", "pública"),
      new Centro(11701164, "IES Astaroth", "pública")
   }

   // ¡ATENCIÓN!: Ni se le ocurre escribir sentencias como la siguiente,
   // esto es una primera aproximación.
   String sqlString = "INSERT INTO Centro VALUES (%d, '%s', '%s');"
   for(Centro centro: centros) {
       stmt.executeUpdate(sqlString.formatted(
         centro.getId(),
         centro.getNombre(),
         centro.getTitularidad()
       ));
   }

Prudencia

Aún estamos empezando y sabemos poco, así que estas sentencias son torpes por dos razones:

• Estamos ejecutando repetidamente (tres veces) la misma sentencia y no es eficiente. Ya aprenderemos cómo hacer operaciones masivas.

• Las sentencias usan valores almacenados en variables y hay que construirlas incluyendo esos valores dentro de las sentencias. Para ello nosotros hemos formateado la cadena indicando dónde obtener los valores. Sin embargo, JDBC ya viene con un mecanismo específico para realizar esta labor: las sentencias parametrizadas.

Por tanto, no tome estos ejemplos muy en serio. Ya aprenderá a escribirlos mejor.

Consultas

Las consultas, en cambio, sí devuelven resultados que deberán procesarse luego:

try(ResultSet rs = stmt.executeQuery("SELECT * FROM Centro")) {
    while(rs.next()) {
        int id = rs.getInt("id");
        String nombre = rs.getString("nombre");
        String titularidad = rs.getString("titularidad");
        System.out.println(String.format("Código: %d -- Nombre: %s (%s)", id, nombre, titularidad));
    }
}

Como se ilustra arriba, el resultado de las consultas se obtiene a través de un objeto ResultSet que se va consumiendo a medida que obtenemos registros de él.

Nota

El objeto ResultSet hay también que cerrarlo, aunque debería cerrarse automáticamente al cerrarse la sentencia que lo originó. En cualquier caso, no está de más asegurarnos de que se cierra en cuanto deja de sernos útil.

En el ejemplo, nos hemos limitado a imprimirlos, pero si nuestro programa pretendiera hacer algo útil, tendríamos que trasladar esta información al modelo de objetos de Java, Por ejemplo, podríamos hacer esto:

Lista<Centro> centros = null;
try(ResultSet rs = stmt.executeQuery("SELECT * FROM Centro")) {
   centros = new ArrayList<>();

   while(rs.next()) {
       int id = rs.getInt("id");
       String nombre = rs.getString("nombre");
       String titularidad = rs.getString("titularidad");
       // Quizás deberíamos comprobar que titularidad es válido.
       centros.add(new Centro(id, nombre, Titularidad.fromString(titularidad)));
   }
}

Importante

Obsérvese que tiene que ser el programador el que traduzca el modelo relacional al modelo de objetos, tal como adelantamos en la introducción.

Prudencia

Los métodos .getInt, .getLong, etc. devuelven tipos primitivos (int, long, etc.) por lo que cuando en la base de datos el valor del campo es NULL, no devuelven null, sino el valor predeterminado del primitivo (p.ej. ejemplo, 0 en caso de int o long). Si necesitamos diferenciar el valor predeterminado del valor nulo, hay dos estrategias. Una consiste en seguir usando estos métodos y preguntar luego si el valor era realmente NULL:

// Suponiendo que se permita que el campo "edad" sea nulo.
Integer edad = rs.getInt("edad");  // si es nulo, edad valdrá 0.
if(rs.wasNull()) edad = null;

La segunda estrategia es usar .getObject especificando la clase en la que se quiere convertir el dato:

Integer edad = rs.getObject("edad", Integer.class);

4.1.2.2. Parametrizadas

Las sentencias, tanto las de manipulación como las de consultas, requieren en muchos casos que se incluyan valores de variables en las propias sentencias (véase, sin ir más lejos, el ejemplo de inserción bajo el epígrafe anterior.). Por este motivo los conectores incluyen un mecanismo para parametrizar sentencias; y, en el caso de JDBC, se hace uso de PreparedStatement:

Centro[] centros = new Centro[] {
   new Centro(11004866, "IES Castillo de Luna", "pública"),
   new Centro(11007533, "IES Arroyo Hondo", "pública"),
   new Centro(11701164, "IES Astaroth", "pública")
}

try(
   PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Centro (id, nombre, titulacion) VALUES (?, ?, ?)")
) {
    for(Centro centro: centros) {
        pstmt.setInt(1, centro.getId());
        pstmt.setString(2, centro.getNombre());
        pstmt.setString(3, centro.getTitularidad());
        pstmt.executeUpdate();
    }
}

Por tanto, cuando se ejecutan sentencias parametrizadas, hay que definir el valor para todos los parámetros (el primero, el segundo, etc.) y cuando se han establecidos todos sus valores, ejecutar la sentencia.

En estos registros, hemos decidido prefijar el valor del identificador. Sin embargo, es común dejar indefinido su valor y dejar que la base de datos escoja uno al agregarlo como registro. En nuestro ejemplo, sería el caso los estudiantes. En esos casos, es probable que al insertarlos queramos conocer qué identificador se definió. Para ello podemos hacer lo siguiente:

DateTimeFormatter df = DateTimeFormatter.ofPattern("dd/MM/yyyy");
// centro es un centro que ya tenemos definido.
Profesor[] profesores = new Profesor[] {
   new Profesor ("Perico de los Palotes", LocalDate.parse("12/06/1995", df), centro),
   new Profesor ("Mariquilla de la O", LocalDate.parse("07/09/1999", df), centro)
};

try(
   PreparedStatement pstmt = conn.prepareStatement(
      "INSERT INTO Estudiante (nombre, nacimiento, centro, id) VALUES (?, ?, ?, ?)",
      Statement.RETURN_GENERATED_KEYS
   )
) {
    for(Profesor profesor: profesores) {
        pstmt.setString(1, profesor.getNombre());
        pstmt.setDate(2, profesor.getNacimiento() == null?null:Date.valueOf(profesor.getNacimiento()));
        pstmt.setObject(3, centro == null?null:centro.getId(), Types.INTEGER);
        pstmt.setNull(4, Types.INTEGER);  // Lo debe generar automáticamente el SGBD
        pstmt.executeUpdate();
        try(ResultSet rs = pstmt.getGeneratedKeys())  {
           if(rs.next()) System.out.printf("'%s' se registró con ID=%d\n", registro[0], rs.getInt(1));
        }
    }
}

Prudencia

Pero no hemos dado aún con la forma eficiente, ya que estamos repitiendo la ejecución de la misma sentencia con distintos valores. Necesitaremos más adelante darle al menos una vuelta más.

Obsérvese que al definir los valores de los parámetros de una consulta, necesitamos cambiar de método según sea el tipo del campo. Sin embargo, JDBC permite usar un método genérico (setObject) que se encargará de inferir el tipo SQL correcto a partir del tipo de Java. La razón por la que en el ejemplo se añade un tercer parámetro se debe a que el valor puede ser nulo y, en ese caso, no podrá inferirse el tipo.

El uso de este último método implica algunas cosas:

1. El driver debe inferir el tipo SQL a partir del tipo de Java: eso provoca una ligera sobrecarga y puede originar que la traducción no sea la deseada. Lo segundo puede solucionarse añadiendo el tercer argumento:

   Integer idCentro = centro == null?null:centro.getId();
   pstmt.setObject(3, centro, Types.INTEGER);
   

2. El segundo argumento se pasa como Object y siempre habrá que transformarlo al tipo requerido: esto implica una nimia sobrecarga también, despreciable en la mayoría de los casos.

3. Si no pasamos literales, sino una variable, la variable puede ser nula y darnos problemas. Eso, de nuevo, se soluciona añadiendo el tercer argumento.

Truco

El código:

pstmt.setObject(3, centro == null?null:centro.getId(), Types.INTEGER);

es más sucinto que este otro equivalente:

if(centro == null) pstmt.setNull(3, Types.INTEGER);
else pstmt.setInt(3, centro.getId());

4.1.3. Carga del esquema inicial

Es muy común que la primera vez que se ejecuta la aplicación, ésta cree la base de datos y defina el esquema y los datos iniciales necesarios. Lo cómodo es que las sentencias necesarias se encuentren en un guión SQL y el programa las lea de él, en vez de encontrarse incrustadas en el código.

Sin embargo, JDBC no tiene definido un método que nos permita ejecutar un guión SQL completo y que pasemos una cadena larga con todas las instrucciones separadas por puntos y coma, no asegura que el SGBD las descomponga y ejecute individualmente[6]. La única forma segura de poder ejecutar sus sentencias es descomponerlas primero. Para ello podemos optar por dos estrategias:

• JSQLParser que es capaz de procesar el código SQL y, por tanto, reconocer los elementos de que se compone.

• Podemos escribir una solución artesanal, si el guión no es complejo y se respeta el estándar SQL. El código de los métodos splitSQL y executeSQL hace esto mismo y se encuentra en la clase SqlUtils de la librería sqlutils.

4.1.4. Tratamiento funcional de las consultas

ResultSet permite ir obteniendo fila a fila los resultados de una consulta. Sin embargo, no proporciona una interfaz funcional que nos permita utilizar las operaciones funcionales habituales. Para paliarlo podemos definir una clase que haga la conversión (véase el código fuente de la clase SqlUtils.

Con estas clases y definiendo un método estático para convertir un registro de la tabla Centro (esto es, un ResultSet) en un objeto Centro:

public static Centro resultSetToCentro(ResultSet rs) throws SQLException {
   int id = rs.getInt("id");
   String nombre = rs.getString("nombre");
   Titularidad titularidad = Titularidad.fromString(rs.getString("titularidad"));
   return new Centro(id, nombre, titularidad);
}

se puede obtener un objeto Stream que permita recorrer los resultados de la consulta:

// No los cerramos, porque se encargara el cierre del flujo de hacerlo.
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Centro");

// try-with-resources para asegurarnos de liberar los recursos (conn, stmt y rs)
try (Stream<Centro> centros = SqlUtils.resultSetToStream(conn, stmt, rs, Main::resultToCentro)) {
   // Tratamos el flujo como estimemos más oportuno.
   for(Centro c: (Iterable<Centro>) centros::iterator) {
      System.out.println(c);
   }
}

Advertencia

El método es de ejecución perezosa, esto es, genera objetos a medida que se consumen. Esto implica que, mientras necesitemos obtener elementos del flujo, tanto el objeto de consulta como el de conexión deben permanecer abiertos, pues de no ser así, se cerrará también automáticamente el objeto ResultSet y dejaremos de obtener filas. Por tanto:

Stream<Centro> centros = null;

try(
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT * FROM Centro");
) {
   centros = SqlUtils.resultSetToStream(conn, stmt, rs, Main::resultToCentro);
}

for(Centro c: centros) {
   // Jamás obtendremos ningún centro
   // porque el objeto "rs" está cerrado.
}

no funciona en absoluto. Es indispensable que no cerremos los objetos Connection, Statement y ResultSet para que el Stream pueda devolver objetos. ¿Cuándo entonces se cierran? De eso se encarga el propio flujo al cerrarse (véase la implementación). Por ese motivo es necesario proporcionar al método los objetos Connection y Statement. Eso sí, debemos asegurarnos de cerrar el flujo al acabar con él, razón por la cual en el ejemplo ilustrativo de su uso hemos usado una sentencia try-with-resources.

Truco

El método resultSetToStream permite no definir la función que transforma la fila (el propio ResultSet) en un objeto. En ese caso, se obtendrá con cada elemento del flujo la propia fila:

try(Stream<ResultSet> result = SqlUtils.resultSetToStream(stmt, rs)) {
   // Tratamos el resultado como un Stream.
}

Truco

Si no nos interesa cerrar la conexión al cerrar el flujo, podemos pasar null en el argummento correspondiente.

Notas al pie

[1]

En las versiones modernas de JDBC no es necesario expresar el nombre del driver, ya que dispone de mecanismos parar inferir cuál es el driver adecuado a partir de la URL de conexión. En cambio, más adelante, al tratar JPA, sí lo necesitaremos.

[2]

Se han supuesto (excepto para SQLite obviamente) conexiones TCP/IP. Sin embargo, en sistemas UNIX el motor podría permitir conexiones a un socket local. Por ejemplo, una URL a socket local para MariaDB podría ser jdbc:mariadb:///mibase?socket=/var/run/mysqld/mysqld.sock o algo parecido.

[3]

Ahora bien, si nuestra intención es usar el pool de conexiones integrado en el driver, sí necesitaremos usar implementación específica para el pool y, en consecuencia, no deberemos incluir <runtime>.

[4]

Aunque haremos una excepción aclarando cómo están definidas las claves primarias en las tablas Claustro, Departamento y Profesor. Nuestra intención es que sus identificadores se generen automáticamente sin necesidad de que especifiquemos un valor. Eso se hace en SQLite con AUTOINCREMENT (aunque es conveniente leer las aclaraciones al respecto), pero en otros motores se expresa de otro modo, ya que no forma parte del estándar. Desde SQL:2003, existe un modo de expresarlo: GENERATED [BY DEFAULT|ALWAYS] AS IDENTITY: ambas fórmulas generan un identificar único automáticamente, pero la primera permite la inserción manual de valores, mientras que la segunda, no. Sin embargo, SQLite no lo soporta y por eso lo hemos dejado comentado. En cambio, tiene un comportamento curioso: cuando se usa INTEGER para definir la clave primaria asume el comportamento de GENERATED BY DEFAULT AS IDENTITY y cuando se usa INT la clave no se genera y hay que especificarla siempre explícitamente (o sea, el comportamiento que esperaríamos al no haber incluido ninguna fórmula adicional como AUTOINCREMENT o GENERATED BY...).

[5]

Según el estándar, un nombre que no se encierra entre comillas dobles, se sobreentiende escrito todo en mayúsculas. En particular, SQLite se salta el estándar en este aspecto y no hace esa distinción, incluso aunque los nombres sí se hayan entrecomillado. Otra razón que avala el que no entrecomillemos es que MariaDB/MySQL exige que cambiemos la configuración predeterminada para soportar el entrecomillado con comillas dobles, ya que este SGBD entrecomilla con el acento grave.

[6]

Con SQLite, por ejemplo, el método .executeUpdate sí será capaz de ejecutar todas las sentencias SQL; execute, en cambio, no lo hará y ejecutará solo la primera. Con otros SGBD el comportamiento puede variar.