Spring JDBC Template: simplificando el uso de SQL

Última actualización: 09/09/2022
logo spring

Generalmente, el uso y explotación de bases de datos relacionales en Java lo 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 alta abstracción sobre el modelo relacional y la (inevitable) API JDBC con la que interactuamos con las bases de datos. Eso sí, es necesario conocer estas herramientas muy bien para evitar graves 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.

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

Introducción

El escenario

Spring no solo es compatible con JPA, sino que su integración y configuración es automática cuando usamos Spring Boot, al menos con Hibernate. Asimismo, cuenta con Spring Data JPA, su propia capa de abstracción que facilita aún más el trabajo, tal y como explico en esta serie de tutoriales.

Sin embargo, en algunos escenarios estas herramientas no son una buena opción o incluso resulta inviable, al menos como única solución.

  • Bases de datos no normalizadas ya existentes, cuya estructura no podemos modificar, y que resultan muy complejas de modelar con entidades de JPA (las clases que representan a las tablas y sus columnas). Incluso pueden tener tablas carentes de clave primaria.
  • 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 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.

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

El problema de JDBC

Echemos un vistazo a la manera en la que se suele programar con JDBC. El siguiente método obtiene un listado de objetos con los registros de un 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 que se realizan son las estas:

  • 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.
  • Creación de la consulta a ejecutar.
  • Proporcionar los parámetros de la consulta (en este ejemplo no hay).
  • Obtener el ResultSet con los resultados e iterar sobre ellos para
  • Asegurar el cierre de los recursos y gestionar las excepciones de tipo SQLException. Siempre deben ser capturadas o lanzadas de forma explícita por no ser de tipo Runtime. Esta tarea la he simplificado con un bloque try-with-resources.

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 operación de lectura. Esto no es consecuencia, ni mucho menos, de un pobre diseño o de la falta de capacidades de JDBC. Simplemente, provee la abstracción más baja para interactuar con la base de datos. Su API relativamente pequeña 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 no es el caso de nadie, 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 «boilerplate», un molesto ruido que se va repitiendo una y otra vez de forma idéntica (o casi) en las llamadas a la base de datos.

En la actualidad, las lambdas y otras capacidades funcionales de Java facilitan sobremanera la reutilización de código. Pero en el mundo Spring no es necesario invertir tiempo en crear y mantener una librería genérica para la ejecución de SQL, pues 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.

Proyecto de ejemplo

Base de datos

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 ya se encarga 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 el contenedor 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 distinto. Si estás en esta situación, usa uno distinto al crear el contenedor.

Si quieres dar un paso más y conseguir que las propias pruebas gestionen el contenedor, echa un vistazo a mi tutorial 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 fue creado originalmente hace varios años y me parece útil conservarlo para ofrecer ejemplos modernos de Spring sin Spring Boot porque son difíciles de encontrar. Además, la configuración del proyecto no es demasiado relevante 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.

Spring Boot

En 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. Además, necesitamos el controlador 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>      
 </dependency>

Configuramos la fuente de datos en el application.properties (o application.yml).

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 precisan este starter. Para más información, consultar este tutorial.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
 </dependency> 
Configuración manual

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.22 (julio de 2022), compatible con Java 8.

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

Lo divertido empieza ahora, porque tenemos que configurar a mano la fuente de datos (interfaz DataSource), entre otros elementos. Vayamos con una configuración básica.

Necesitaremos el controlador JDBC de la base de datos, en nuestro caso MySQL. El DataSource en sí lo construiremos con el pool de conexiones HikariCP, el mismo que configura Spring Boot de manera predeterminada. Adicionalmente, 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 un clase de configuración (@Configuration). Lo necesitaremos para crear en Spring el bean JdbcTemplate, el actor principal de la función, y un gestor de transacciones.

@Configuration
@PropertySource("classpath:db.properties")
@ComponentScan("com.danielme.spring")
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 de ejemplo

Con independencia de cómo se configuró 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 sin necesidad de Spring. Dado que solo hay un constructor, no es necesario anotarlo con @Autowired.

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. Con ella, el contexto de Spring configurado ApplicationContext arrancará y parará cuando sea necesario de forma automática.

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

    @Autowired
    private CountryDao countryDao;

}

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

La anotación @Sql ejecuta el script /src/test/resources/reset.sql antes de cada una de las pruebas contenidas en la clase para establecer en la base de datos 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;

Las pruebas se lanzan con el comando mvn test o bien desde un IDE como Eclipse o IntelliJ.

La imagen tiene un atributo ALT vacío; su nombre de archivo es spring-junit.png

Operaciones de consulta

La forma general de realizar una consulta JDBCTemplate que devuelva una lista de objetos consiste en proporcionar a un método query una cadena con el SQL y una implementación de RowMapper que convierta la información del ResultSet en un listado de objetos. Si no hay resultados, la lista será vacía, nunca nula.

    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"));
        }
    }

Naturalmente, se pueden usar lambdas.

 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"));
 }

Si fuera necesario, proporcionamos como tercer argumento un array (varags) con los parámetros a reemplazar en la consulta respetando el orden en el que aparecen en la cadena con el SQL.

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

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 no tener que 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.

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

Una de las molestias de JDBC es la obligación de tener que capturar o relanzar las excepciones SQLException. Por fortuna, vemos en los ejemplos anteriores que este requerimiento ha desaparecido de la API de JDBC Template. Los errores se recogen en excepciones de tipo DataAccessException que son a su vez de tipo RuntimeException. Asimismo, las trazas de error suelen ser muy 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 Template que recibe esas implementaciones ya se encarga de su adecuada gestión, por lo que tenemos que preocuparnos.

Operaciones de modificación

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, si los hubiera. Todas informan del número de registros afectados.

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

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 anterior 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 insertWithSimpleJdbcInsert construye un objeto SimpleJdbcInsert a partir del nombre de la tabla y de 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 lo más 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 uso 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);

La otra implementación que aparece en el diagrama también resulta muy interesante. Con BeanPropertySqlParameterSource los parámetros 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();
}

Es imprescindible que exista el 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 notablemente más rápida la ejecución de «n» inserciones en una única sentencia INSERT que en «n» sentencias. Recordemos que INSERT admite este formato.

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

Con JdbcTemplate es fácil aprovechar esta característica de SQL gracias 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. Dado 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;

}

Lo que haremos es establecer en el PreparedStatement los parámetros de la sentencia SQL con los valores provenientes de argument, un objeto con los datos a insertar \ actualizar. Se trata, por tanto, de código JDBC puro.

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 y una lambda que se corresponde con un ParametrizedPreparedStatementSetter.

Este test inserta 500 países en lotes de 100.

@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 los datos de la conexión.

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

La salida de la ejecución demuestra 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 del 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);
}

En este ejemplo, el tamaño del lote es el de batchEntries. El «truco» si queremos personalizarlo consiste en llamar en un bucle a batchUpdate con la colección de batchEntries que se quiera insertar en cada lote.

Podemos realizar actualizaciones en lotes en vez de inserciones con los mismos métodos. No en vano se llaman 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 función 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. Una característica que puede que te resulte familiar, pues está disponible en el lenguaje de consultas JPQL de JPA.

Para usar NamedParameterJdbcTemplate, procedemos 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, hay que crear su instancia en Spring del mismo modo que hicimos con JdbcTemplate.

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

Este es un ejemplo sencillo. No parece que NamedParameterJdbcTemplate mejore sustancialmente nuestro código, 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.

    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));
    }

Existen métodos de NamedParameterJdbcTemplate que aceptan los objetos SQLParameterSource (BeanPropertySqlParameterSource y MapSqlParameterSource) que vimos en los ejemplos de inserción. Por ejemplo, ahora podemos usar BeanPropertySqlParameterSource en sentencias SELECT para agrupar los parámetros de la consulta en una clase. Deberá tener un getter para cada parámetro que aparezca en la consulta.

public class CountryQuery {

    private int minPopulation;
    private int 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));
}

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 SqlParameterSourceUtils#createBatch el cual los crea y los devuelve en un array a partir de la lista con los objetos a actualizar.

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

Podemos acceder a la instancia de JdbcTemplate contenida en NamedParameterJdbcTemplate para invocar a sus métodos.

namedParameterJdbcTemplate.getJdbcTemplate();

Procedimientos

Spring JDBC Template también nos asiste a la hora de ejecutar los 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 ;

Para ejecutarlo construimos una instancia de SimpleJdbcCall indicando el nombre del procedimiento. Esa instancia ejecutará el procedimiento cuando invoquemos al 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 ya está preparada para que esta 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. Además, es necesario que el usuario con el que Spring realice la conexión pueda 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'

Si algún parámetro de salida fuera un ResultSet, Spring puede procesarlo con un RowMapper. Supongamos que el procedimiento anterior tiene un parámetro de salida, denominado «countries», que es un ResultSet. Tenemos que registrarlo en SimpleJdbcCall con el método returningResultSet proporcionando su nombre y el RowMapper. 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");
}

Funciones

Ahora, veamos cómo invocar una función. Las funciones devuelven un único valor simple (cadena, número, etcétera). 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 vez 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 SQL que no cuente con métodos específicos como los que hemos visto a lo largo de este tutorial. Es el caso de las sentencias DDL (estructuras de datos) y DCL (control de uso).

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

2 comentarios sobre “Spring JDBC Template: simplificando el uso de SQL

  1. Desde el momento que le metiste C3P0, log4j, Junit y varias cosas mas para hacer el ejemplo. Dejo de ser simple

  2. El insert con SimpleJdbcInsert debería devolverme el id del registro guardado, pero en vez de eso, siempre me da este error:
    PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00928: falta la palabra clave SELECT\n

    Como mas puedo obtenerlo ?

Deja una respuesta

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.