4.5. Extras

Arrinconamos bajo este epígrafe algunos aspectos adiciones de los conectores:

4.5.1. Metadatos

Por hacer

DatabaseMetaData y ResultSetMetaData.

4.5.2. Pool de conexiones

Ya tratamos cómo crear un pool de conexiones. Si queremos simplificar la creación del pool y abstraernos de qué se usa para crearlo, podemos envolverlo en una clase como esta:

public class ConnectionPool implements AutoCloseable {

    private static Map<Integer, ConnectionPool> instances = new HashMap<>();
    private final HikariDataSource ds;
    public static short maxConnections = 10;
    public static short minConnections = 1;

    private ConnectionPool(String url, String user, String password) {
        HikariConfig hconfig = new HikariConfig();
        hconfig.setJdbcUrl(url);
        hconfig.setUsername(user);
        hconfig.setPassword(password);
        // Mínimo y máximo de conexiones.
        hconfig.setMaximumPoolSize(maxConnections);
        hconfig.setMinimumIdle(minConnections);
        ds = new HikariDataSource(hconfig);
    }

    /**
     * Genera un pool de conexiones o reaprovecha uno ya creado
     * si coinciden los parámetros de creación.
     * @param url URL de la base de datos.
     * @param user Usuario de conexión
     * @param password Contraseña de conexión
     * @return El pool de conexiones
     */
    public static ConnectionPool getInstance(String url, String user, String password) {
        int hashCode = Objects.hash(url, user, password);
        ConnectionPool instance = instances.get(hashCode);
        if(instance == null || instance.getDataSource().isClosed()) {
            instance = new ConnectionPool(url, user, password);
            instances.put(hashCode, instance);
        }
        return instance;
    }

    /**
     * Genera un pool de conexiones o reaprovecha uno ya creado
     * si ya se creo uno con la URL suministrada.
     * @param url La URL de conexión.
     * @return El pool de conexiones.
     */
    public static ConnectionPool getInstance(String url) {
        return ConnectionPool.getInstance(url, null, null);
    }

    /**
     * Devuelve un pool de conexiones cuando sólo hay un candidato posible.
     * Como efecto secundario, elimina los pools cuyo DataSource esté cerrado.
     * @return El pool de conexiones.
     */
    public static ConnectionPool getInstance() {
        ConnectionPool.clear();
        switch(instances.size()) {
            case 1:
                ConnectionPool instance = instances.values().iterator().next();
                if(instance.isActive()) return instance;
                else instances.clear();
            case 0:
                throw new IllegalArgumentException("No hay definido ningún pool activo");
            default:
                throw new IllegalArgumentException("Ambiguo: hay definidos varios pools");
        }
    }

    /**
     * Elimina los pools cuyos DataSource estén cerrados.
     */
    public static void clear() {
        instances = instances.entrySet()
            .stream().filter(e -> e.getValue().isActive())
            .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
    }

    public Connection getConnection() throws SQLException {
         return ds.getConnection();
    }

    public HikariDataSource getDataSource() {
        return ds;
    }

    public boolean isActive() {
        return !ds.isClosed();
    }

    @Override
    public void close() {
        ds.close();
    }
}

De este modo, para crear un pool con HikariCP basta con hacer lo siguiente[1]:

// Pool de conexiones de una base SQLite en memoria.
ConnectionPool pool = PoolConnection.getInstance("jdbc:sqlite:file::memory:?cache=shared");
DataSource ds = pool.getDataSource();
Connection conn = pool.getConnection();  // o ds.getConnection()

Lo interesante de la clase, además de que simplifica la creación del pool, es que usa un patrón Singleton, de manera que si intentamos crear un pool con los mismos parámetros, devolverá el pool ya creado y no otro distinto:

ConnectionPool pool1 = PoolConnection.getInstance("jdbc:sqlite:file::memory:?cache=shared");
ConnectionPool pool2 = PoolConnection.getInstance("jdbc:sqlite:file::memory:?cache=shared");
pool1 == pool2; // true
ConnectionPool pool3 = PoolConnection.getInstance();
pool1 == pool3; // true
ConnectionPool pool4 = PoolConnection.getInstance("jdbc:sqlite:caca.db");
pool1 == pool4; // false
ConnectionPool pool5 = PoolConnection.getInstance();  // IllegalArgumentException (hay dos, ¿cuál?)

4.5.3. 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 codigo fuente):

public class SqlUtils {

    /**
     * Implementa un iterador a partir de un ResultSet.
     */
    private static class ResultSetIterator implements Iterator<ResultSet> {

        private final ResultSet rs;
        private boolean avanzar;
        private boolean hasNextElement;

        public ResultSetIterator(ResultSet rs) {
            this.rs = rs;
            avanzar = true;
        }

        @Override
        public boolean hasNext() {
            if(avanzar) {
                try {
                    if(rs.isClosed()) {
                        throw new DataAccessException("ResultSet is closed!!!");
                    }
                    hasNextElement = rs.next();
                }
                catch(SQLException err) {
                    throw new DataAccessException(err);
                }
                finally {
                    avanzar = false;
                }
            }
            return hasNextElement;
        }


        @Override
        public ResultSet next() {
            avanzar = true;
            return rs;
        }
    }
    
    /**
     * Como Function<T, R> pero permite propagar una SQLException.
     */
    @FunctionalInterface
    public static interface CheckedFunction<T, R> {
        R apply(T t) throws SQLException;
    }

    /**
     * Transforma el SQLException que propaga una CheckedFUnction en un DataAccessException, que es una excepción
     * que no necesita ser declarada.
     * @param <T> El tipo que devuelve CheckedFunction.
     * @param checked Un "función" CheckedFunction.
     * @return Una "función" que ha sustituido SQLException por DataAccessException.
     */
    public static <T> Function<ResultSet, T> checkedToUnchecked(CheckedFunction<ResultSet, T> checked) {
        return t -> {
            try {
                return checked.apply(t);
            }
            catch(SQLException err) {
                throw new DataAccessException(err);
            }
        };
    }

    /**
     * Genera un flujo con las filas generadas en un ResultSet.
     * @param ac  La sentencia que generó rs o la conexión sobre la que se
     * 	          ejecutó la sentencia. Proporciónese una u otra dependiendo de
     * 	          qué es lo que quiere cerrar automáticamente al cerrarse el
     * 	          Stream resultante.
     * @param rs Los resutados de una consulta.
     * @return Un flujo en el que cada elemento es el siguiente estado del ResultSet proporcionado.
     * @throws SQLException Cuando se produce un error al realizar la consulta.
     */
    public static Stream<ResultSet> resultSetToStream(AutoCloseable ac, ResultSet rs) {
        return StreamSupport.stream(Spliterators.spliteratorUnknownSize(new ResultSetIterator(rs), Spliterator.ORDERED), false)
            .onClose(() -> {
                try {
                    rs.close();
                    ac.close();
                }
                catch(Exception err) {
                    throw new DataAccessException(err);
                }
            });
    }

    /**
     * Genera un flujo de objetos derivados del resultado de una consulta.
     * @param <T> La clase del objeto.
     * @param ac  La sentencia que generó rs o la conexión sobre la que se
     * 	          ejecutó la sentencia. Proporciónese una u otra dependiendo de
     * 	          qué es lo que quiere cerrar automáticamente al cerrarse el
     * 	          Stream resultante.
     * @param rs  El objeto que representa los resultado de la consulta.
     * @param mapper La función que permite transformar la fila en un objeto (puede generar un SQLException).
     * @return El flujo de objetos.
     * @throws SQLException Cuando Cuando se produce un error al realizar la consulta.
     */
    public static <T> Stream<T> resultSetToStream(AutoCloseable ac, ResultSet rs, CheckedFunction<ResultSet, T> mapper) {
        return resultSetToStream(ac, rs).map(checkedToUnchecked(mapper));
    }
}

Si incluimos este archivo en nuestro proyecto podremos hacer consultas de este modo:

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

try ( // try-with-resources para asegurarnos de liberar los recursos (stmt y rs)
   Stream<Centro> centros = SqlUtils.resultSetToStream(stmt, rs, fila -> {
      // Esta función puede generar un SQLException
      int id = fila.getInt("id");
      String nombre = fila.getString("nombre");
      // Suponemos que hemos definido mejor Titularidad y hemos creado
      // un método estático que obtiene Titularidad.PUBLICA a partir de "pública", etc.
      Titularidad titularidad = Titularidad.fromNombre(fila.getString("titularidad"));
      return new Centro(id, nombre, titularidad);
   });
) {
   //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(stmt, rs, fila -> {
      int id = fila.getInt("id");
      String nombre = fila.getString("nombre");
      Titularidad titularidad = Titularidad.fromNombre(fila.getString("titularidad"));
      return new Centro(id, nombre, titularidad);
   });
}

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 el primer argumento que se pasa al método es la sentencia o la conexión, dependiendo de lo que más nos interese cerrar al terminar de consumir el flujo. 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)) {
   // Tratamiento del rs (sin usar next, porque ya avanza el solo)
}

4.5.4. Gestor de transacciones

Para facilitar la forma en que se gestionan las transacciones ya estudiadas podemos crear una clase que explote las posibilidades del bloque try-with-resources:

package edu.acceso.ej4_1.backend.sql.jdbcutils;

import java.sql.Connection;
import java.sql.SQLException;

public class TransactionManager implements AutoCloseable {

    private Connection conn;
    private boolean committed;
    private boolean originalAutoCommit;

    public TransactionManager(Connection conn) throws SQLException {
        setConn(conn);
        originalAutoCommit = conn.getAutoCommit();
        conn.setAutoCommit(false);
    }

    public Connection getConn() {
        return conn;
    }

    private void setConn(Connection conn) throws SQLException {
        if(conn == null) throw new IllegalArgumentException("La conexión no puede ser nula");
        if(!conn.isValid(0)) throw new SQLException("La conexión debe ser válida");
        this.conn = conn;
    }

    public void commit() throws SQLException {
        conn.commit();
        committed = true;
    }

    @Override
    public void close() throws SQLException {
        if(!committed) conn.rollback();
        conn.setAutoCommit(originalAutoCommit);
    }
}

La clase podría usarse del siguiente modo:

// Se supone que conn es una conexión ya abierta.
try (TransaccionManager tm = new TransactionManager(conn)) {

   // Ejecutamos todas las sentencias que constituyen esta transacción
   // ...

   tm.commit(); // Confirmamos en la base de datos.
}
catch(SQLException err) {
   // No hay que hacer rollback, ya que se encarga el close().
   err.printStackTrace();
}

4.5.5. Cargar esquema desde archivo

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 instrucciones separadas por comas, no aegura que el SGBD las descomponga y ejecute individualmente[2]. 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.

• Si el guión no es complejo y seguimos algunas premisas:

  1. Los “;” que completan sentencias deben encontrarse a final de línea.

  2. No pueden usarse las palabras begin o end en comentarios, nombres, valores, etc.

  3. No pueden usarse bloques IF en aquellos SGBD que los implementen (pero sí usarse el estándar CASE).

  podemos escribir una solución artesanal, que es la que proponemos.

/**
 * Descompone un guión SQL en las sentencias de que se compone.
 * @param st Entrada de la que se lee el guión
 * @return  Una lista con las sentencias separadadas.
 * @throws IOException
 */
public static List<String> splitSQL(InputStream st) throws IOException {
    Pattern beginPattern = Pattern.compile("\\b(BEGIN|CASE)\\b", Pattern.CASE_INSENSITIVE);
    Pattern endPattern = Pattern.compile("\\bEND\\b", Pattern.CASE_INSENSITIVE);

    try (
        InputStreamReader sr = new InputStreamReader(st, StandardCharsets.UTF_8);
        BufferedReader br = new BufferedReader(sr);
    ) {
        List<String> sentencias = new ArrayList<>();
        String linea;
        String sentencia = "";
        int contador = 0;
        while((linea = br.readLine()) != null) {
            linea = linea.trim();
            if(linea.isEmpty()) continue;

            Matcher beginMatcher = beginPattern.matcher(linea);
            Matcher endMatcher = endPattern.matcher(linea);

            while(beginMatcher.find()) contador++;
            while(endMatcher.find()) contador--;

            sentencia += "\n" + linea;

            if(contador == 0 && linea.endsWith(";")) {
                sentencias.add(sentencia);
                sentencia = "";
            }
        }
        return sentencias;
    }
}

public static void executeSQL(Connection conn, InputStream st) throws SQLException, IOException {
   conn.setAutoCommit(false);

   try (
       Statement stmt = conn.createStatement();
   ) {
       for(String sentencia: splitSQL(st)) {
           stmt.addBatch(sentencia);
       }
       stmt.executeBatch();
       conn.commit();
   }
   catch(SQLException err) {
      conn.rollback();
      throw new SQLException(err);
   }
   finally {
      conn.setAutoCommit(true);
   }
}

Consejo

Podemos añadir ambos métodos a la clase SqlUtils ya propuesta. De hecho, hemos preparado un repositorio de Github listo para su uso:

<!-- En pom.xml -->
<repositories>
     <repository>
         <id>jitpack.io</id>
         <url>https://jitpack.io</url>
     </repository>
 </repositories>

<dependencies>
  <dependency>
      <groupId>com.github.sio2sio2</groupId>
      <artifactId>sqlutils</artifactId>
      <version>1.7.0</version>
  </dependency>
</dependencies>

Notas al pie

[1]

Al método sólo se le proporciona la URL, porque usamos SQLite como SGBD. Si fuera otro como MySQL, tendríamos también que proporcionar un usuario y una contraseña. Por otro lado, en el caso particular de SQLite nos vemos obligados añadir el parámetro cache=shared a la URL, ya que de lo contrario cada conexión que devuelva el pool será una base de datos distinta en memoria. Con una base de datos persistente, no habría sido necesario.

[2]

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.