Spring JDBC Template: simplificando el uso de SQL

logo spring

Generalmente el uso y explotación de bases de datos SQL en Java lo realizamos a través de herramientas de mapeo ORM tales como Hibernate o Apache OpenJPA que permiten desarrollar la capa de persistencia de nuestras aplicaciones de forma muy rápida con una alta abstracción sobre el modelo relacional. Spring no sólo es muy fácil de integrar con estos productos tal y como vimos en el tutorial “Persistencia en BD con Spring: Integrando JPA, c3p0, Hibernate y EHCache”, sino que además proporciona con Spring Data JPA su propia capa de abstracción que nos facilita y simplifica aún más el trabajo tal y como vimos en la correspondiente serie de tutoriales al respecto.

Sin embargo, en muchos escenarios no es una buena opción o incluso es inviable el uso de Hibernate y similares, al menos como única solución:

  • Bases de datos no normalizadas ya existentes, con tablas que incluso carecen de claves primarias, y cuya estructura no podemos modificar y que por tanto resultan muy complejas de modelar con entidades.
  • Tenemos que utilizar directamente SQL para aprovechar las funcionalidades proporcionades por la propia de base datos y/o optimizar al máximo el rendimiento.
  • Capas de persistencia que se basan en el uso de procedimientos almacenados, práctica muy habitual en empresas que apuestan por Oracle.

Aunque siempre podemos utilizar Hibernate porque proporciona soporte para la ejecución de SQL nativo, en tales escenarios suele preferirse trabajar directamente con JDBC. Spring nos ayuda en esta tarea simplificando y estandarizando el uso de JDBC gracias a las clases que conforman Spring JDBC template.

Master Pyhton, Java, Scala or Ruby

Uso estándar de JDBC

Antes de pasar a utilizar Spring JDBC template, echemos un vistazo rápido a cómo se suele trabajar con JDBC con el siguiente método que simplemente obtiene un listado de objetos a partir de una tabla.

	public List<Country> findAllPureJdbc() {
		List<Country> results = new LinkedList<>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;		
		try {
			connection = jdbcTemplate.getDataSource().getConnection();
			preparedStatement = connection.prepareStatement("SELECT * FROM country");
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Country country = new Country(resultSet.getLong("id"), 
						resultSet.getString("name"),
						resultSet.getInt("population"));
				
				results.add(country);
			}
		} catch (SQLException ex) {
			logger.error(ex);
			throw new RuntimeException(ex);
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException ex) {
					logger.warn(ex);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException ex) {
					logger.warn(ex);
				}
			}
		}

		return results;
	}

Las acciones que se realizan son las siguientes:

  • Apertura de una conexión a la base de datos. En este ejemplo se realiza de forma un tanto particular ya que el código proviene de la aplicación de ejemplo que veremos más adelante para aprender a utilizar 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 datos devueltos por la consulta.
  • Iterar sobre el ResultSet e ir obteniendo los datos de cada fila.
  • Asegurar el cierre de los recursos.
  • Gestionar las excepciones de tipo SQLException ya que siempre debe ser capturadas (o lanzadas de forma explícita). En el ejemplo, he considerado que los errores producidos en el cierre son warnings. Nota: el código se puede simplificar ligeramente utilizando try-with-resources si trabajamos con Java 7.

Demasiado código para hacer una operación tan rutinaria, y ni siquiera hemos tenido que gestionar transacciones. En la práctica el único código que nos interesa escribir es la consulta y la obtención de los datos del ResulSet, el resto es código “boiler-plate” que se repite una y otra vez de forma idéntica en nuestras llamadas a la base de datos. Spring JDBC template nos permitirá precisamente centrarnos en el código que importa, encargándose de lo demás por nosotros con la tranquilidad de saber que esas acciones funcionan correctamente (podemos equivocarnos si las escribimos nosotros mismos).

Configurando Spring JDBC template

Las clases que necesitamos se encuentran en el módulo spring-jdbc. Este módulo es dependencia de spring-orm, por lo que si ya lo estamos utilizando en nuestra aplicación (por ejemplo tenemos Hibernate, Spring Data JPA, etc) no tenemos que hacer nada.

En el proyecto de ejemplo de este tutorial accederemos a una base de datos MySQL utilizando el pool de conexiones c3p0. Adicionalmente, utilizaremos log4j2 y el código lo probaremos mediante tests. Se utilizará Spring 4 aunque el código es perfectamente compatible con Spring 3.

El pom.xml es el siguiente:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0    	http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.danielme.spring</groupId>
	<artifactId>jdbc-template</artifactId>
	<version>1.0</version>
	<name>jdbc-template</name>
	<packaging>jar</packaging>
	<url>https://danielme.com/2017/09/02/spring-jdbc-template-simplificando-el-uso-de-sql</url>
	<inceptionYear>2017</inceptionYear>

	<description>Spring jdbctemplate demo</description>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
		<maven.version>3.0</maven.version>
		
		<spring.version>4.3.11.RELEASE</spring.version>		
		<c3p0.version>0.9.1.2</c3p0.version>
		<log4jframework.version>2.5</log4jframework.version>
		<mysql.driver.version>5.1.43</mysql.driver.version>
		<junit.version>4.12</junit.version>		

		<enforcer.plugin>1.4.1</enforcer.plugin>
		<maven.compiler.plugin.version>3.6.1</maven.compiler.plugin.version>
		<maven.jar.plugin.version>3.0.2</maven.jar.plugin.version>
	</properties>


	<developers>
		<developer>
			<id>dmedina</id>
			<name>Daniel Medina</name>
			<email>danielme_com@yahoo.com</email>
			<url>http://danielme.com</url>
			<roles>
				<role>developer</role>
			</roles>
		</developer>
	</developers>

	<licenses>
		<license>
			<name>GPL 3</name>
			<url>http://www.gnu.org/licenses/gpl-3.0.html</url>
		</license>
	</licenses>


	<build>
		<pluginManagement>
			<plugins>
				<plugin>
					<groupId>org.apache.maven.plugins</groupId>
					<artifactId>maven-jar-plugin</artifactId>
					<version>${maven.jar.plugin.version}</version>
					<configuration>
						<archive>
							<manifest>
								<addClasspath>true</addClasspath>
								<mainClass>${project.build.mainClass}</mainClass>
							</manifest>
						</archive>
					</configuration>
				</plugin>
				<plugin>
					<groupId>org.apache.maven.plugins</groupId>
					<artifactId>maven-compiler-plugin</artifactId>
					<version>${maven.compiler.plugin.version}</version>
					<configuration>
						<source>${java.version}</source>
						<target>${java.version}</target>
						<encoding>${project.build.sourceEncoding}</encoding>
					</configuration>
				</plugin>

				<plugin>
					<groupId>org.apache.maven.plugins</groupId>
					<artifactId>maven-enforcer-plugin</artifactId>
					<version>${enforcer.plugin}</version>
					<executions>
						<execution>
							<id>enforce-java</id>
							<goals>
								<goal>enforce</goal>
							</goals>
							<configuration>
								<rules>
									<requireJavaVersion>
										<version>${java.version}</version>
									</requireJavaVersion>
									<requireMavenVersion>
										<version>${maven.version}</version>
									</requireMavenVersion>
								</rules>
							</configuration>
						</execution>
					</executions>
				</plugin>

			</plugins>

		</pluginManagement>
	</build>

	<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>

		<!-- connection pooling with c3p0 -->
		<dependency>
			<groupId>c3p0</groupId>
			<artifactId>c3p0</artifactId>
			<version>${c3p0.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>

		<!-- testing -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</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>

</project>

En Spring sólo necesitamos configurar el datasource que utilizará Spring JDBC template. Vamos a realizar esta configuración con JavaConfig del mismo modo que en el tutorial “Persistencia en BD con Spring: Integrando JPA, c3p0, Hibernate y EHCache” por lo que no me entetendré en su descripción. Y aunque no es imprescindible, generalmente también vamos a necesitar un gestor de transacciones (TransactionManager).

Todas las funcionalidades de Spring JDBC template son proporcionadas por la clase JdbcTemplate la cual instanciamos con el DataSource a utilizar. Existen otras clases construídas sobre esta como por ejemplo NamedParameterJdbcTemplate que proporcionan ciertas abstracciones específicas, en este tutorial sólo veremos el uso general con JdbcTemplate.

package com.danielme.spring.jdbctemplate;

import java.beans.PropertyVetoException;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import com.mchange.v2.c3p0.ComboPooledDataSource;

@Configuration
@PropertySource("classpath:db.properties")
@ComponentScan("com.danielme.spring")
public class ApplicationContext {
    
    @Bean(destroyMethod = "close")
    DataSource dataSource(Environment env) {
        ComboPooledDataSource ds = new ComboPooledDataSource();
        try {
            ds.setDriverClass(env.getRequiredProperty("jdbc.driverClassName"));
        } catch (IllegalStateException | PropertyVetoException ex) {
            throw new RuntimeException(
                    "error while setting the driver class name in the datasource", ex);
        }
        ds.setJdbcUrl(env.getRequiredProperty("jdbc.url"));
        ds.setUser(env.getRequiredProperty("jdbc.username"));
        ds.setPassword(env.getRequiredProperty("jdbc.password"));
        ds.setAcquireIncrement(env.getRequiredProperty("c3p0.acquire_increment", Integer.class));
        ds.setMinPoolSize(env.getRequiredProperty("c3p0.min_size", Integer.class));
        ds.setMaxPoolSize(env.getRequiredProperty("c3p0.max_size", Integer.class));
        ds.setMaxIdleTime(env.getRequiredProperty("c3p0.max_idle_time", Integer.class));
        ds.setUnreturnedConnectionTimeout(env.getRequiredProperty(
                "c3p0.unreturned_connection_timeout", Integer.class));

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

}

JdbcTemplate lo inyectamos como cualquier otro beans donde lo necesitemos, generalmente en una clase de tipo DAO. En el ejemplo se utilizará la siguiente clase.

@Repository
public class CountryDao {

	private final JdbcTemplate jdbcTemplate;

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

La clase Country para la que se define este DAO modela la siguiente tabla en MySQL la cual necesitaremos para poder ejecutar el proyecto de ejemplo. Los datos de la conexión se establecen en el fichero /src/main/resources/db.properties.

CREATE TABLE IF NOT EXISTS `country` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `population` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Operaciones de consulta

La forma más general de realizar una consulta con Spring JDBC template que devuelva una lista de objetos es pasar a un método query la consulta SQL a ejecutar y una implementación de RowMapper que realice el mapeo de los datos del ResultSet devuelto por la consulta. En el proyecto de ejemplo esta implementación se ha definido como un clase privada del DAO, si fuera de un sólo uso se podría simplemente crear una clase anónima.

public List<Country> findAll() {
		return jdbcTemplate.query("SELECT * FROM country", 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"));
		}
	}

Si fuera necesario, también podemos proporcionar un array con los parámetros a reemplazar en la consulta, por ejemplo:

public List<Country> findByName(String name) {
		return jdbcTemplate.query("SELECT * FROM country WHERE name LIKE ?", new Object[] { name }, new CountryRowMapper());
	}

Para las consultas que pueden devolver como máximo un registro también se pueden utilizar los métodos queryForObject

	public Country findById(Long id) {
		return jdbcTemplate.queryForObject("SELECT * FROM country WHERE id = ?", new Object[] { id },
				new CountryRowMapper());
	}
	
	public int count() {
		return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM country", Integer.class);
	}

Operaciones de modificación

Las operaciones de modificación se realizan de forma similar a las consultas pero utilizando los métodos update que devuelven el número de registros afectados por la operación.

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

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

Las operaciones de inserción más sencillas pueden definirse sin necesidad de escribir el INSERT en SQL, creando en su lugar un objeto de la clase SimpleJdbcInsert.

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

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

Procedimientos almacenados

Spring JDBC template también nos ayuda a la hora de ejecutar procedimientos y funciones SQL. Vamos a crear en nuestra base de datos de ejemplo el siguiente procedimiento con un parámetro de entrada y otro de salida.

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

Para ejecutarlo desde Java construimos una instancia de SimpleJdbcCall con un JdbcTemplate indicando el nombre del procedimiento. Esa instancia ejecutará el procedimiento tras la invocación al método execute que recibe los parámetros de entrada, devolviéndose los parámetros de salida en un Map.

	public Integer callProcedure(String name) {
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("search");

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

		Map<String, Object> out = simpleJdbcCall.execute(in);
		return (Integer) out.get("total");
	}

Para que este código funcione, es necesario que el usuario con el que el datasource de Spring realiza la conexión pueda leer los metadatos de los procedimientos almacenados, en caso contrario obtendremos el siguiente error (en 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 con la siguiente sentencia:

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

Si algún parámetro de salida fuera un ResultSet, podemos hacer que Spring lo procese de forma automática mediante un RowMapper adecuado. Por ejemplo, vamos a suponer que el procedimiento anterior tiene un parámetro de salida denominado “countries” que es un ResultSet.

public List<Country> callProcedure(String name) {
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("search")
				.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 (devuelve siempre un único resultado).

DELIMITER //
CREATE FUNCTION `search2`(name VARCHAR(50)) RETURNS INT 
BEGIN DECLARE total INT;
	SELECT COUNT(*) INTO total
	FROM country c
	WHERE c.name LIKE name; 
	
	RETURN total; 
END//
DELIMITER ;

El códido Java es análogo al anterior, pero utilizando los métodos withFunctionName y executeFunction en lugar de withProcedureName y execute.

	public Integer callFunction(String name) {
		SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("search2");

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

		return simpleJdbcCall.executeFunction(Integer.class, in);		
	}

Probando CountryDAO

Para poder ejecutar los métodos de CountryDAO he creado con JUnit tests en la clase CountryDaoTest siguiendo lo expuesto en el tutorial Testing Spring con JUnit 4.

package com.danielme.spring.jdbctemplate;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.danielme.spring.jdbctemplate.ApplicationContext;
import com.danielme.spring.jdbctemplate.CountryDao;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { ApplicationContext.class })
@Sql("reset.sql")
public class CountryDaoTest {

    @Autowired
    private CountryDao countryDao;

    @Test
    public void testAll() {
        assertEquals(3, countryDao.findAll().size());
    }

    @Test
    public void testDelete() {
        assertEquals(3, countryDao.deleteAll());
    }

    @Test
    public void testInsertQuery() {
        countryDao.insertWithQuery("test", 123456);
        assertEquals(4, countryDao.findAll().size());
    }

    @Test
    public void testInsert() {
        assertEquals(countryDao.insert("test", 123456), (long) countryDao.findByName("test").get(0)
                .getId());
    }

    @Test
    public void testFind() {
        List<Country> list = countryDao.findByName("Spain");
        assertEquals(1, list.size());
        assertEquals("Spain", list.get(0).getName());
    }
    
    @Test
    public void testFindById() {
    	List<Country> list = countryDao.findByName("Spain");    	
        assertEquals("Spain", countryDao.findById(list.get(0).getId()).getName());
    }

    @Test
    public void testProcedure() {
        assertEquals(0, (int) countryDao.callProcedure("test"));
    }
    
    @Test
    public void testFunction() {
        assertEquals(0, (int) countryDao.callFunction("test"));
    }

    @Test
    public void count() {
        assertEquals(3, countryDao.count());
    }

}

Antes de la ejecución de cada test, se lanza el script reset.sql que prepara el contenido de la base de datos. Se utiliza la anotación @Sql, disponible sólo a partir de Spring 4.1.

BEGIN;
TRUNCATE country;
INSERT INTO `country` (`name`, `population`) VALUES ('Mexico',  130497248);
INSERT INTO `country` (`name`, `population`) VALUES ('Spain', 49067981);
INSERT INTO `country` (`name`, `population`) VALUES ('Colombia', 46070146);
COMMIT;

Como es habitual, los tests se pueden lanzar con el comando mvn test o bien desde un IDE como Eclipse.

Código de ejemplo

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

Otros tutoriales sobre Spring y SQL

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

Persistencia en BD con Spring Data JPA (I): Primeros pasos

Persistencia en BD con Spring Data JPA (II): Repositorios personalizados

Persistencia en BD con Spring Data JPA (III): Auditoría

Cursos aplicaciones móviles

Responder

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. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: