Spring JDBC Template: simplificando el uso de SQL

En el centro de la mayoría de aplicaciones y sistemas de gestión solemos encontrar una base de datos relacional. Por tanto, como programadores es vital que en nuestro código seamos capaces de usar bases de datos con eficiencia y sencillez. En este tutorial examinaremos con detalle la manera más fácil de ejecutar cualquier tipo de sentencia SQL en Spring Framework sin recurrir a librerías como Hibernate o Spring Data. Y esa «manera» es Spring JDBC Template.

1. Introducción
   1. El escenario
   2. El problema de JDBC
2. Proyecto de ejemplo
   1. Base de datos MySQL
   2. Configuración con Spring Boot
   3. Configuración sin Spring Boot
   4. Las clases
   5. Los tests
3. Operaciones de consulta con SELECT
4. Excepciones
5. Operaciones de modificación (INSERT, UPDATE, DELETE)
6. Modificaciones masivas en lote (batch)
7. Nombrado de parámetros con NamedParameterJdbcTemplate
8. Procedimientos y funciones
9. SQL genérico

Introducción

El escenario

Por lo común, la explotación de bases de datos relacionales en Java la realizamos a través de herramientas ORM tales como Hibernate o Apache OpenJPA, estandarizadas por la especificación JPA. Permiten desarrollar la capa de persistencia de nuestras aplicaciones con rapidez, poco código y una elevada abstracción sobre el modelo relacional y la (inevitable) API JDBC con la que interactuamos con las bases de datos relacionales. Eso sí, es necesario conocer estas potentes herramientas muy bien para evitar problemas de rendimiento.

Para más información, te recomiendo mi curso Jakarta EE en el que dedico más de veinte capítulos al uso de JPA con Hibernate.

Spring Framework no solo es compatible con JPA, sino que su integración y configuración es automática cuando usamos Spring Boot. Asimismo, cuenta con Spring Data JPA, su propia capa de abstracción que facilita aún más el trabajo. Incluso podemos beneficiarnos de Spring Data sin pasar por JPA gracias el módulo Spring Data JDBC.

Sin embargo, a veces estas herramientas no son una buena opción o resultan poco útiles, al menos como única solución. Se me ocurren varios escenarios no excluyentes:

• Tenemos bases de datos no normalizadas ya existentes, cuya estructura no podemos modificar, y que resultan muy complejas de modelar con entidades de JPA (clases que representan a las tablas y sus columnas).
• Capas de persistencia que se basan en el empleo intensivo de SQL y / o de procedimientos almacenados, hasta tal punto que no necesitamos los beneficios de JPA, como la sincronización automática de entidades. Puede ser el caso de una aplicación de informes (reporting) que no escriba en la base de datos y que precise de consultas complejas.
• Se desea tener el control absoluto del SQL empleado y las transformaciones de datos por cuestiones de rendimiento. Los ORM y productos similares generan código SQL y pueden añadir cierta sobrecarga al sistema, como por ejemplo la caché de entidades de JPA.

Aun cuando JPA e Hibernate permiten trabajar con SQL y procedimientos almacenados, tema del tutorial SQL nativo con JPA e Hibernate, en los escenarios anteriores puede ser más práctico decantarnos por usar directamente la API JDBC. Pero esto tiene grandes inconvenientes…

El problema de JDBC

Echemos un vistazo a la forma en la que se programa con la API JDBC. El siguiente método obtiene un listado de objetos con los registros de una tabla:

public List<Country> findAllPureJdbc() throws SQLException {
    try (Connection connection = jdbcTemplate.getDataSource().getConnection();
            PreparedStatement ps = connection.prepareStatement("SELECT * FROM countries")) {
          ResultSet resultSet = ps.executeQuery();
          return mapResults(resultSet);
    } 
}

private List<Country> mapResults(ResultSet resultSet) throws SQLException {
    List<Country> results = new ArrayList<>();
    while (resultSet.next()) {
        Country country = mapResult(resultSet);
        results.add(country);
    }
    return results;
}

private Country mapToCountry(ResultSet resultSet) throws SQLException {
    return new Country(
        resultSet.getLong("id"),
        resultSet.getString("name"),
        resultSet.getInt("population"));
}

Las acciones requeridas para ejecutar una SELECT siempre son las mismas:

1. Apertura de una conexión a la base de datos. En este caso resulta un tanto particular porque el código proviene de la aplicación de ejemplo que cuenta con Spring JDBC Template.
2. Creación de la consulta a ejecutar en un Statement o PreparedStatement
3. Proporcionar los parámetros o variables de la consulta (en este ejemplo no hay).
4. Obtener el ResultSet con los resultados e iterar sobre ellos para convertirlos en objetos de clases de nuestro dominio.
5. Asegurar el cierre de los recursos y gestionar las excepciones de tipo SQLException. Deben ser capturadas o bien lanzadas de forma explícita por no ser de tipo RuntimeException. Esta tarea la he simplificado con un bloque try-with-resources.

¡Artesanía pura! Demasiado código para hacer una operación tan rutinaria, y ni siquiera hemos tenido que lidiar con transacciones de manera explícita por tratarse de una simple lectura. Esto no es consecuencia, ni mucho menos, de un pobre diseño o de la falta de capacidades de JDBC. Provee la abstracción más baja para interactuar con las bases de datos. Su API pequeña y sencilla facilita a los proveedores de bases de datos su implementación.

Si nos pagaran por líneas escritas, nos ha tocado la lotería. Como supongo que ningún programador se encuentra en esta situación, en la práctica el único código que nos interesa escribir es la consulta y la transformación de los datos del ResultSet. El resto de líneas son código repetitivo que los anglosajones denominan boilerplate. Un molesto ruido que se va repitiendo una y otra vez de forma idéntica o con apenas diferencias para consultas distintas.

Así pues, lo razonable es que en algún momento invirtamos tiempo en crear y mantener una librería genérica con el propósito de reutilizar el código requerido para ejecutar SQL. Pero en el mundo Spring ya existe Spring JDBC Template. Esta pequeña joya nos permitirá centrarnos en el código que importa gracias a una API sencilla y práctica. Y, como descubrirás en este tutorial, la curva de aprendizaje es insignificante.

Proyecto de ejemplo

Base de datos MySQL

Exploraremos las capacidades básicas de Spring JDBC Template con un proyecto de ejemplo que accederá a una base de datos MySQL. Contendrá una única tabla, llamada countries:

CREATE TABLE `countries`
(
    `id`         SMALLINT     NOT NULL AUTO_INCREMENT,
    `name`       varchar(255) NOT NULL,
    `population` int(11)      NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UK_1` (`name`)
)

También tendrá un procedimiento almacenado y una función que veremos al final del tutorial.

Para mayor comodidad, he incluido un fichero Dockerfile en la raíz del proyecto con un script SQL (init.sql) que se ocupa de todo. La imagen se puede construir y arrancar como un contenedor de usar y tirar con estos comandos:

docker build -t spring-jdbctemplate-demo-mysql .
docker run --rm -d -p 3306:3306 spring-jdbctemplate-demo-mysql

Docker 1: introducción

Docker 2: imágenes y contenedores

Obsérvese que el contenedor expone el puerto 3306 del MySQL que contiene hacia fuera del mismo a través del 3306. Si tenemos más de un MySQL en ejecución, cada uno deberá usar un puerto diferente. Si es tu caso, usa uno distinto al crear el contenedor.

Si quieres dar un paso más y conseguir que las propias pruebas gestionen el contenedor, revisa mi artículo sobre Testcontainers. Satisfacción garantizada.

Sin duda, Spring Boot es la mejor forma de configurar y gestionar los proyectos Spring. Pero nuestro ejemplo no cuenta con él. Esto se explica por el hecho de que lo creé hace varios años y me parece útil conservarlo para ofrecer ejemplos modernos de Spring sin Spring Boot: cada son más difíciles de encontrar. Además, la configuración del proyecto es irrelevante para los propósitos del tutorial.

Aun así, doy las instrucciones básicas por si estás interesado en configurar un proyecto con Spring Boot y JDBC Template.

Configuración con Spring Boot

Añadimos al pom el starter spring-boot-starter-jdbc, a menos que ya tengamos uno que lo incluya como spring-boot-starter-data-jpa. También necesitamos el controlador o driver JDBC de nuestra base de datos:

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <scope>runtime</scope>
 </dependency>

Establecemos en el application.properties (o application.yml) la configuración que Spring Boot necesita para conectarse a la base de datos:

spring.datasource.url=jdbc:mysql://localhost:3306/country?serverTimezone=UTC
spring.datasource.username=demo
spring.datasource.password=demo

En el caso específico del proyecto de ejemplo, escribiremos pruebas con JUnit 5 que requieren del contexto de Spring. Por ello, se precisa este starter:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
 </dependency> 

Para más información, consulta este tutorial:

Configuración sin Spring Boot

Las clases que necesitamos se encuentran en el módulo spring-jdbc. Es dependencia de spring-orm, por lo que si ya lo tenemos en nuestra aplicación (por ejemplo, estamos usando Hibernate, JPA o Spring Data JPA) no hay que hacer nada. Usaremos la versión 5.3.24 (noviembre de 2022); nada cambió con Spring 6.

<dependency>
    <groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
    <version>${spring.version}</version>
</dependency>

Lo divertido empieza ahora: debemos configurar a mano la fuente de datos (interfaz DataSource), entre otros elementos.

Necesitaremos el controlador JDBC de la base de datos, en nuestro caso MySQL. El DataSource lo construiremos con el pool de conexiones HikariCP, el mismo que configura Spring Boot. Utilizaremos log4j2 y el código lo probaremos con tests basados en JUnit 5. Estas son todas las dependencias:

	<dependencies>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
			<version>${hikari.version}</version>
		</dependency>

		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>${log4jframework.version}</version>
		</dependency>

		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-jcl</artifactId>
			<version>${log4jframework.version}</version>
		</dependency>

		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-slf4j-impl</artifactId>
			<version>${log4jframework.version}</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql.driver.version}</version>
		</dependency>

		<dependency>
			<groupId>org.junit.jupiter</groupId>
			<artifactId>junit-jupiter-engine</artifactId>
			<version>${junit.version}</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${spring.version}</version>
			<scope>test</scope>
		</dependency>

	</dependencies>

Los parámetros de configuración de la base de datos están en el fichero /src/main/resources/db.properties:

jdbc.driverClassName = com.mysql.cj.jdbc.Driver
jdbc.username=demo
jdbc.password=demo
jdbc.url = jdbc:mysql://localhost:3306/country-test?serverTimezone=UTC

Ahora creamos el DataSource en una clase de configuración (@Configuration). Lo necesitaremos para construir el bean JdbcTemplate, el actor principal de la función, y un gestor de transacciones. También activamos el escaneo automático de las clases para Spring (@ComponentScan) e importamos el fichero de configuración (@PropertySource):

@Configuration
@PropertySource("classpath:db.properties")
@ComponentScan
public class AppConfiguration {

    @Bean(destroyMethod = "close")
    DataSource dataSource(Environment env) {
        HikariConfig config = new HikariConfig();
        config.setDriverClassName(env.getRequiredProperty("jdbc.driverClassName"));
        config.setJdbcUrl(env.getRequiredProperty("jdbc.url"));
        config.setUsername(env.getRequiredProperty("jdbc.username"));
        config.setPassword(env.getRequiredProperty("jdbc.password"));
        return new HikariDataSource(config);
    }

    @Bean
    JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

Las clases

Con independencia de cómo se configure el proyecto, JdbcTemplate se inyecta como cualquier bean donde queramos, por lo común en una clase de tipo DAO. En el proyecto tendremos la siguiente:

@Repository
public class CountryDao {

    private final JdbcTemplate jdbcTemplate;

    public CountryDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

}

La inyección se realiza vía constructor, buena práctica que permite la instanciación manual de objetos. Dado que solo hay un constructor, es redundante anotarlo con @Autowired; Spring no tendrá más remedio que usarlo, resolviendo sus argumentos con beans.

La clase Country modela la tabla:

public class Country {

    private Long id;
    private String name;
    private Integer population;

  //constructores, getters y setters

Los tests

Los métodos que iremos creando se probarán con tests escritos en la clase CountryDaoTest. Para integrar Spring con JUnit 5, aplicaremos la extensión SpringExtension. Arranca y detiene el contexto de Spring configurado en ApplicationContext cuando sea necesario, dentro del ciclo de ejecución de las pruebas:

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = {ApplicationContext.class})
@Sql("/dataset.sql")
class CountryDaoTest {

    @Autowired
    private CountryDao countryDao;

}

Si tuviéramos Spring Boot, reemplazaremos las líneas 1 y 2 con la anotación @SpringBootTest.

La anotación @Sql ejecuta el script /src/test/resources/dataset.sql antes de cada una de las pruebas contenidas en la clase para establecer en MySQL el juego de datos esperado:

BEGIN;
TRUNCATE countries;
INSERT INTO `countries` (`id`,`name`, `population`) VALUES (1, 'Mexico',  130497248);
INSERT INTO `countries` (`id`, `name`, `population`) VALUES (2, 'Spain', 49067981);
INSERT INTO `countries` (`id`,`name`, `population`) VALUES (3, 'Colombia', 46070146);
COMMIT;

Ten presente estos tres países cuando veas las clases de prueba.

Las pruebas se lanzan con el comando mvn test o bien desde un entorno de desarrollo como Eclipse o IntelliJ de la forma habitual.

Si no consigues ejecutar las pruebas, lo más probable es que Spring no pueda conectar con MySQL. Revisa que todo esté orden y que los parámetros de conexión sean correctos.

Por último, ten en cuenta que todos los métodos que veamos están respaldados por su correspondiente prueba, pero solo mostraré el código de aquellas que sean más relevantes.

Operaciones de consulta con SELECT

La forma general de realizar una consulta SELECT con JDBCTemplate que devuelva una lista de objetos consiste en proporcionar a uno de sus métodos query una cadena con el SQL y una implementación de la interfaz funcional RowMapper. Su propósito es convertir o mapear cada elemento del ResultSet con los resultados de la consulta en el objeto que queramos. Si la consulta no tiene resultados, JdbcTemplate se responsabiliza de devolver una lista vacía.

@FunctionalInterface
public interface RowMapper<T> {

	@Nullable
	T mapRow(ResultSet rs, int rowNum) throws SQLException;

}

Ejecutemos una consulta que recupere todos los países. Los queremos en una lista de objetos Country:

    public List<Country> findAll() {
        return jdbcTemplate.query("SELECT * FROM countries", 
                                                     new CountryRowMapper());
    }

    private static class CountryRowMapper implements RowMapper<Country> {
        @Override
        public Country mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Country(rs.getLong("id"), rs.getString("name"), rs.getInt("population"));
        }
    }

En RowMapper#mapRow, el ResultSet ya está apuntando al resultado de la consulta a procesar. En concreto al del número indicado por el argumento rowNum. Por consiguiente, jamás debemos llamar a ResultSet#next; la iteración del ResultSet es asunto de JdbcTemplate.

Mejor con una expresión lambda:

 public List<Country> findAll() {
    return jdbcTemplate.query("SELECT * FROM countries", 
                                                (rs, rowNum) -> mapToCountry(rs));
 }

private Country mapToCountry(ResultSet rs) throws SQLException {
        return new Country(
                rs.getLong("id"),
                rs.getString("name"),
                rs.getInt("population"));
 }

Este primer caso de uso de JDBC Template no es casual: es el mismo ejemplo que usé con la API JDBC. Compara el código de ambos y disfruta:

¡Genial! Hemos conseguido lo que planteé en la introducción: escribir solo la consulta y la conversión o mapping del ResultSet. Y es que a Jdbc Template lo bautizaron con sumo acierto: si te fijas en los pasos de ejecución de la SELECT que indiqué en la introducción, se pueden expresar en una plantilla (template) de código en la que rellenar algunas variables. Es lo que hace el código de JdbcTemplate, apoyándose en otras clases e interfaces.

Estas variables son el SQL y el conversor del ResultSet que hemos visto, pero también los parámetros de la consulta. Cuando sean necesarios, los proporcionaremos a query como tercer argumento en un array (formato varargs), respetando el orden en el que aparecen en la cadena con el SQL.

Obtengamos los países seleccionándolos por su nombre:

    public List<Country> findByName(String name) {
        return jdbcTemplate.query("SELECT * FROM countries WHERE name LIKE ?",
                (rs, rowNum) -> mapToCountry(rs), name);
    }

Más adelante veremos alternativas más sofisticadas para el manejo de los parámetros.

Para las consultas que pueden devolver como máximo un registro contamos con las sobrecargas de los métodos queryForObject. Si el registro buscado puede que no exista, recomiendo devolver un Optional para evitar tratar con objetos nulos:

public Optional<Country> findById(Long id) {
    Country country = jdbcTemplate.queryForObject("SELECT * FROM countries WHERE id = ?",
            (rs, rowNum) -> mapToCountry(rs), id);
    return Optional.ofNullable(country);
}

Si el objeto retornado se corresponde con un tipo primitivo, se devuelve tal cual indicando su clase:

public int count() {
    return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM countries", Integer.class);
}

Excepciones

Una de las molestias de JDBC es la obligación de capturar o relanzar las excepciones SQLException. En mi opinión, la existencia de este tipo de excepciones, llamadas checked exceptions, es uno de los grandes fastidios de usar Java. Si no existen en otros lenguajes de programación, por algo será…

Por fortuna, vemos en los ejemplos anteriores que este requerimiento ha desaparecido de la API de JDBC Template. Los errores se recogen internamente en excepciones de tipo DataAccessException. No estamos obligados a capturarlas —¡gracias, gente de Spring!— porque son RuntimeException. Asimismo, las trazas de error que contienen suelen ser bastante descriptivas.

Sí que es posible que tengamos que implementar alguna que otra interfaz, como RowMapper, cuyos métodos declaren SQLException en su signatura. No obstante, el código de JDBC Template que recibe esas implementaciones se encarga de su adecuada gestión, por lo que no tenemos que preocuparnos.

Operaciones de modificación (INSERT, UPDATE, DELETE)

Las operaciones de modificación (INSERT, UPDATE, DELETE) se ejecutan con los métodos update. De entre todas las sobrecargas, la de uso más habitual recibe la cadena con la sentencia SQL seguida de sus parámetros en un varargs, si los hubiera. Todas informan del número de registros afectados.

Aquí tienes unos ejemplos:

public int deleteAll() {
    return jdbcTemplate.update("DELETE from countries");
}

public int updatePopulation(Long id, int newPopulation) {
    return jdbcTemplate.update("UPDATE countries SET population = ? WHERE id = ?", newPopulation, id);
}

public void insertWithQuery(String name, int population) {
    String sql = "INSERT INTO countries (name, population) VALUES(?,?)";
    jdbcTemplate.update(sql, name, population);
}

Las operaciones de inserción tan sencillas como la efectuada por insertWithQuery pueden realizarse sin necesidad de escribir la sentencia INSERT, configurando en su lugar un objeto de la clase SimpleJdbcInsert. Su gran beneficio es que retorna el identificador asignado al nuevo registro:

    public long insertWithSimpleJdbcInsert(String name, int population) {
        SimpleJdbcInsert simpleJdbcInsert =
                new SimpleJdbcInsert(jdbcTemplate.getDataSource())
                        .withTableName("countries")
                        .usingGeneratedKeyColumns("id");

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("name", name);
        parameters.put("population", population);
        return simpleJdbcInsert.executeAndReturnKey(parameters).longValue();
    }

El método anterior construye un objeto SimpleJdbcInsert a partir del nombre de la tabla y de la columna con la clave primaria, un AUTO_INCREMENT generado por la base de datos. La ejecución del INSERT con ese objeto requiere un Map con los valores de todas las columnas que no sean nulas. Su clave es el nombre de la columna.

Esta prueba demuestra que insertWithSimpleJdbcInsert devuelve la clave primaria:

@Test
void testInsert() {
    long idReturned = countryDao.insertWithSimpleJdbcInsert(TEST_NAME, 123456);
    long id = countryDao.findByName(TEST_NAME).get(0).getId();
    assertEquals(idReturned, id);
}

Proporcionar el valor de cada columna en un Map parece natural. No obstante, varios métodos presentan sobrecargas que nos permiten elegir entre un Map o un objeto de la interfaz SqlParameterSource.

La implementación MapSqlParameterSource hace lo que sugiere: utiliza un Map para ir guardando los valores de las columnas.

 SqlParameterSource params = new MapSqlParameterSource()
                .addValue("name", name)
                .addValue("population", population);
return simpleJdbcInsert.executeAndReturnKey(params).longValue();

Aporta dos ventajas frente al empleo directo de Map:

• Los parámetros se pueden añadir cómodamente encadenando llamadas a los métodos addValue.
• Un par de sobrecargas permiten indicar el tipo del parámetro de acuerdo a la clase java.sql.Type. No es necesario en nuestro ejemplo, pero lo haríamos así:

SqlParameterSource params = new MapSqlParameterSource()
                .addValue("name", name, Types.VARCHAR)
                .addValue("population", Types.INTEGER);

Veamos la otra implementación que aparece en el diagrama. Con BeanPropertySqlParameterSource los parámetros para la sentencia INSERT se obtienen invocando a los getters del objeto que recibe su único constructor:

public long insertWithSimpleJdbcInsert(Country country) {
    SimpleJdbcInsert simpleJdbcInsert =
            new SimpleJdbcInsert(jdbcTemplate.getDataSource())
                    .withTableName("countries")
                    .usingGeneratedKeyColumns("id");

    SqlParameterSource params = new BeanPropertySqlParameterSource(country);
    return simpleJdbcInsert.executeAndReturnKey(params).longValue();
}

Esta magia tiene un precio, aunque es bajo: requiere un getter en Country para el nombre de cada columna. De lo contrario, se lanza una excepción que indica con exactitud el problema:

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'name'

Modificaciones masivas en lote (batch)

La agrupación de operaciones de modificación en lotes (batch processing) mejora el rendimiento. Es bastante más rápida la ejecución de «n» inserciones en una única sentencia INSERT que en «n» sentencias distintas. Recordemos que INSERT admite este formato:

INSERT INTO table_name (columns)
VALUES
    (columns1),
    (columns2),
    ...
    (columns_n);

Con JdbcTemplate resulta fácil aprovechar esta característica de SQL debido a las variadas sobrecargas del método batchUpdate. Las que nos otorgan mayor control sobre la asignación de los valores de las columnas de cada registro son aquellas que requieren de una implementación de la interfaz ParameterizedPreparedStatementSetter. Puesto que solo tiene un método, puede ser una lambda. De hecho, está declarada como una interfaz funcional:

@FunctionalInterface
public interface ParameterizedPreparedStatementSetter<T> {

	void setValues(PreparedStatement ps, T argument) throws SQLException;

}

¿Cómo la implementamos? Estableciendo en el PreparedStatement los parámetros de la sentencia SQL con los valores provenientes de argument, objeto con los datos a insertar \ actualizar.

Hagamos una inserción masiva en la tabla countries:

    public void insertBatch(List<Country> countries, int batchSize) {
        String sql = "INSERT INTO countries (name, population) VALUES(?,?)";

        jdbcTemplate.batchUpdate(sql, countries, batchSize,
                (PreparedStatement ps, Country country) -> {
                    ps.setString(1, country.getName());
                    ps.setInt(2, country.getPopulation());
                }
        );
    }

Como puedes ver, el método batchUpdate que he escogido recibe una cadena con el INSERT, la lista de objetos a insertar, el tamaño máximo del lote (batchSize) y una lambda que se corresponde con un ParametrizedPreparedStatementSetter.

Esta prueba inserta 500 países en lotes de 100. Tiene en cuenta que en la tabla ya existen tres países:

@Test
void testBatchInsert() {
    List<Country> countries = IntStream.rangeClosed(1, 500)
            .boxed()
            .map(i -> new Country(String.valueOf(i), i))
            .collect(Collectors.toList());
    long init = System.currentTimeMillis();

    countryDao.insertBatch(countries, 100);

    assertEquals(countries.size() + 3, countryDao.count());
    System.out.println(System.currentTimeMillis() - init + " ms");
}

En el caso concreto de MySQL, es imprescindible activar la opción rewriteBatchedStatements en la url de conexión:

jdbc.url = jdbc:mysql://localhost:3306/demoTemplate?autoReconnect=true&useSSL=false&rewriteBatchedStatements=true

La salida de la ejecución confirma que la inserción de los países en bloques está funcionando:

10-28-2018 06:30:05,313 PM DEBUG JdbcTemplate:1014 - Executing SQL batch update [INSERT INTO country (name, population) VALUES(?,?)] with a batch size of 100
10-28-2018 06:30:05,315 PM DEBUG JdbcTemplate:622 - Executing prepared SQL statement [INSERT INTO country (name, population) VALUES(?,?)]
10-28-2018 06:30:05,315 PM DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
10-28-2018 06:30:05,339 PM DEBUG JdbcUtils:408 - JDBC driver supports batch updates
10-28-2018 06:30:05,342 PM DEBUG JdbcTemplate:1036 - Sending SQL batch update #1 with 100 items
10-28-2018 06:30:05,348 PM DEBUG JdbcTemplate:1036 - Sending SQL batch update #2 with 100 items
10-28-2018 06:30:05,357 PM DEBUG JdbcTemplate:1036 - Sending SQL batch update #3 with 100 items
10-28-2018 06:30:05,365 PM DEBUG JdbcTemplate:1036 - Sending SQL batch update #4 with 100 items
10-28-2018 06:30:05,374 PM DEBUG JdbcTemplate:1036 - Sending SQL batch update #5 with 100 items
10-28-2018 06:30:05,381 PM DEBUG DataSourceUtils:329 - Returning JDBC Connection to DataSource
10-28-2018 06:30:05,383 PM DEBUG JdbcTemplate:453 - Executing SQL query [SELECT COUNT(*) FROM country]
10-28-2018 06:30:05,384 PM DEBUG DataSourceUtils:110 - Fetching JDBC Connection from DataSource
10-28-2018 06:30:05,394 PM DEBUG DataSourceUtils:329 - Returning JDBC Connection to DataSource
152 ms

Con rewriteBatchedStatements activado, la prueba apenas me tarda 200-300ms. Desactivado (inserciones de una en una), nunca baja de 3 segundos.

Como alternativa a ParameterizedPreparedStatementSetter cuando no necesites indicar el tamaño del lote ni el tipo de cada columna, puedes usar una lista de arrays de Object. Dentro del array deben estar los parámetros de cada INSERT en el orden adecuado:

public void insertBatch(List<Country> countries) {
    String sql = "INSERT INTO countries (name, population) VALUES(?,?)";
    List<Object[]> batchEntries = countries.stream()
            .map(c -> new Object[]{c.getName(), c.getPopulation()})
            .collect(Collectors.toList());

    jdbcTemplate.batchUpdate(sql, batchEntries);
}

Podemos realizar actualizaciones en lotes en vez de inserciones con los mismos métodos. No en vano se denominan batchUpdate:

public void updateBatch(List<Country> countries, int batchSize) {
    String sql = "UPDATE countries SET name = ?, population = ? WHERE id = ? ";

    jdbcTemplate.batchUpdate(sql, countries, batchSize,
            (PreparedStatement ps, Country country) -> {
                ps.setString(1, country.getName());
                ps.setInt(2, country.getPopulation());
                ps.setLong(3, country.getId());
           }
     );
}

Nombrado de parámetros con NamedParameterJdbcTemplate

Existe otra forma de utilizar JDBC Template: NamedParameterJdbcTemplate, un wrapper (envoltorio) de JdbcTemplate.

Su cometido es ofrecer versiones de algunos métodos de JdbcTemplate que admitan el nombrado de los parámetros de las sentencias SQL. Esto significa que en lugar de emplear el marcador ‘?’, usaremos nombres únicos para identificar con mayor claridad a cada parámetro. Quizás esta característica te resulte familiar: está disponible en el lenguaje de consultas JPQL de JPA.

Para utilizar NamedParameterJdbcTemplate, procedamos a inyectarla.:

@Repository
public class CountryDao {

    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public CountryDao(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

Si no usas Spring Boot, debes crear su bean del mismo modo que hicimos con JdbcTemplate:

 @Bean
 NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
    return new NamedParameterJdbcTemplate(dataSource);
 }

Este es un ejemplo sencillo:

    public List<Country> findByPopulation(int minPopulation, int maxPopulation) {
        String sql = "SELECT * FROM countries WHERE population " +
                "BETWEEN :minPopulation AND :maxPopulation ORDER BY name";
        Map<String, Integer> params = new HashMap<>();
        params.put("minPopulation", minPopulation);
        params.put("maxPopulation", maxPopulation);
        return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> mapToCountry(rs));
    }

No parece que NamedParameterJdbcTemplate mejore nuestro código. Este método hace lo mismo con JdbcTemplate:

public List<Country> findByPopulation(int minPopulation, int maxPopulation) {
    String sql = "SELECT * FROM countries WHERE population " +
            "BETWEEN ? AND ? ORDER BY name";

     return jdbcTemplate.query(
            sql,
            (rs, rowNum) -> mapToCountry(rs),
            minPopulation, maxPopulation);
}

Pero cuando el SQL tiene muchos parámetros, la consulta es más legible. Además, trabajar con «?» es propenso a errores porque nos obliga a ir contando los parámetros para conocer su índice.

Otra ventaja namedParameterJdbcTemplate es la disponibilidad en NamedParameterJdbcTemplate de métodos que aceptan los objetos SQLParameterSource (BeanPropertySqlParameterSource y MapSqlParameterSource) que vimos en los ejemplos de inserción. Ahora podemos usar BeanPropertySqlParameterSource para agrupar los parámetros de las consultas SELECT en una clase, la cual deberá ofrecer un getter para cada parámetro que aparezca en la consulta.

Reescribamos el ejemplo anterior con BeanPropertySqlParameterSource:

public class CountryQuery {

    private final int minPopulation;
    private final int maxPopulation;

    public CountryQuery(int minPopulation, int maxPopulation) {
        this.minPopulation = minPopulation;
        this.maxPopulation = maxPopulation;
    }

    public int getMinPopulation() {
        return minPopulation;
    }

    public int getMaxPopulation() {
        return maxPopulation;
    }

public List<Country> findByPopulation(CountryQuery countryQuery) {
    String sql = "SELECT * FROM countries WHERE population " +
            "BETWEEN :minPopulation AND :maxPopulation ORDER BY name";
    SqlParameterSource params = new BeanPropertySqlParameterSource(countryQuery);
    return namedParameterJdbcTemplate.query(sql, params, (rs, rowNum) -> mapToCountry(rs));
}

Si usas Java 17, puedes escribir CountryQuery como un record por ser una clase inmutable.

Las actualizaciones en lote también pueden beneficiarse del nombrado de parámetros porque NamedParameterJdbcTemplate provee dos sobrecargas de batchUpdate que dan soporte a esta funcionalidad. En esta ocasión, necesitamos un objeto SqlParameterSource para cada registro a actualizar. Lo más cómodo es usar el método estático SqlParameterSourceUtils#createBatch. Partiendo de un array o una Collection con los objetos a actualizar, retorna un array con los SqlParameterSource correspondientes a esos objetos:

public void updateBatchNamed(List<Country> countries) {
    String sql = "UPDATE countries SET name = :name, population = :population WHERE id = :id";
    SqlParameterSource[] batchParameters = SqlParameterSourceUtils.createBatch(countries);
    namedParameterJdbcTemplate.batchUpdate(sql, batchParameters);
}

Es posible acceder a la instancia de JdbcTemplate contenida en NamedParameterJdbcTemplate para invocar a sus métodos. Así, evitamos inyectar ambas.

namedParameterJdbcTemplate.getJdbcTemplate();

Procedimientos y funciones

Spring JDBC Template nos asiste a la hora de ejecutar procedimientos y funciones SQL definidos en la base de datos. El siguiente procedimiento tiene un parámetro de entrada (name) y otro de salida (total):

DELIMITER //
CREATE
    DEFINER = `root`@`localhost` PROCEDURE `proc_count_countries_by_name`(IN name VARCHAR(50), OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM countries c WHERE c.name LIKE CONCAT('%', @name, '%');
END//
DELIMITER ;

Lo que haremos es construir una instancia de SimpleJdbcCall indicando el nombre del procedimiento. Ejecutará el procedimiento cuando invoquemos su método execute, pasándole los parámetros de entrada en un objeto de tipo SqlParameterSource. Los parámetros de salida se devuelven en un Map:

public Integer callProcedure(String name) {
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("proc_count_countries_by_name");
    SqlParameterSource in = new MapSqlParameterSource().addValue("name", name);
    Map<String, Object> out = simpleJdbcCall.execute(in);
    return (Integer) out.get("total");
}

La imagen de Docker incluida en el proyecto está preparada para que la siguiente prueba sea exitosa:

@Test
void testProcedure() {
   assertEquals(0, (int) countryDao.callProcedure(FUNC_PROC_NAME));
}

Si usas tu propia base de datos, tendrás que crear el procedimiento. Ten en cuenta que el usuario que Spring utilice debe tener permiso para leer los metadatos de los procedimientos almacenados. En caso contrario, verás el siguiente error (hablamos siempre de MySQL):

org.springframework.dao.TransientDataAccessResourceException: CallableStatementCallback; SQL [{call search()}]; User does not have access to metadata required to determine stored procedure parameter types

Podemos asignar los permisos al usuario, user en el ejemplo, con esta sentencia:

GRANT SELECT ON mysql.proc TO 'user'@'localhost'

¿Y si el resultado es un agregado de datos recuperable como un ResultSet? Supongamos que el procedimiento anterior tiene un parámetro de salida denominado countries. Si queremos recibirlo como una lista de países, tenemos que registrarlo en SimpleJdbcCall con el método returningResultSet proporcionando su nombre y el RowMapper idóneo. El resultado lo seguimos recuperando del Map retornado por execute:

public List<Country> callProcedure(String name) {
	SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withProcedureName("proc_count_countries_by_name")
	    	.returningResultSet("countries", new CountryRowMapper());

   SqlParameterSource in = new MapSqlParameterSource().addValue("name", name);

	Map<String, Object> out = simpleJdbcCall.execute(in);
	return (List<Country>) out.get("countries");
}

Ahora, veamos cómo invocar una función. Las funciones devuelven un único valor simple (cadena, número…). Los parámetros, cuando existan, son siempre de entrada (IN):

DELIMITER //
CREATE FUNCTION `func_count_countries_by_name`(name VARCHAR(50)) RETURNS INT
BEGIN
    DECLARE total INT;
    SELECT COUNT(*)
    INTO total
    FROM countries c
    WHERE c.name LIKE CONCAT('%', @name , '%');

    RETURN total;
END//
DELIMITER ;

El código Java es análogo al anterior usado para el procedimiento, pero recurriendo los métodos withFunctionName y executeFunction en lugar de withProcedureName y execute. Como novedad, el resultado se tipa en executeFunction:

public Integer callFunction(String name) {
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
            .withFunctionName("func_count_countries_by_name");
    SqlParameterSource in = new MapSqlParameterSource().addValue("name", name);
    return simpleJdbcCall.executeFunction(Integer.class, in);
}

SQL genérico

Las distintas versiones del método execute permiten ejecutar cualquier tipo de SQL que carezca de métodos específicos como los que hemos visto a lo largo del tutorial. Es el caso de las sentencias DDL (definición estructuras de datos) y DCL (control de uso \ permisos):

jdbcTemplate.execute("ALTER TABLE countries ADD COLUMN code CHAR(2)");

Gracias a estos métodos, no habrá nada que se nos resista con Spring JDBC Template.

Código de ejemplo

El proyecto se encuentra disponible en GitHub. Para más información sobre cómo utilizar GitHub, consultar este artículo.

Otros tutoriales relacionados

SQL nativo con JPA e Hibernate

Persistencia en BD con Spring: Integrando JPA, c3p0, Hibernate y EHCache

Persistencia en BD con Spring Data JPA

Testing Spring con JUnit 4