SQL nativo con JPA e Hibernate

logo hibernate

Tal y como vimos en el tutorial Spring JDBC Template: simplificando el uso de SQL, a pesar de la existencia de herramientas de mapeo ORM tales como Hibernate y estandarizadas con la especificación JPA, existen escenarios en los que necesitamos trabajar directamente con SQL nativo. El caso más habitual suele ser la ejecución de consultas complejas que deben ser optimizadas o bien son difíciles de implementar de forma eficiente con el ORM.

Siempre podemos recurrir al uso directo de JDBC o, en el caso de Spring, apoyarnos en Spring JDBC Template. Sin embargo, si ya estamos utilizando JPA para modelar la capa de acceso a datos podemos utilizar el soporte que JPA proporciona para la ejecución de consultas SQL. En el presente tutorial veremos el uso básico de ese soporte utilizando Hibernate como implementación.

Proyecto de ejemplo

Para explorar las posibilidades de ejecución de SQL nativo en JPA vamos a utilizar una aplicación Maven estándar con Hibernate 5 similar a la utilizada en otros tutoriales del blog. Aunque no es necesario, usaremos Spring 5 para configurar e instanciar la capa de persistencia, esto es, el Datasource y el EntityManager. Ejecutaremos el código utilizando tests de JUnit 4 integrados con Spring. Para cualquier duda consultar los tutoriales:

Se utiliza una base de datos MySQL cuyos parámetros se definen en el fichero /src/main/resources/db.properties. Ahí deberemos establecer la url de conexión completa, incluyendo el nombre de la base de datos de pruebas, el usuario y la contraseña. Esta base de datos, incluyendo datos de pruebas necesarios para que los tests no fallen, se puede crear con el script users.sql incluido en el directorio raíz del proyecto.

La configuración de Spring se realiza de forma programática en la clase ApplicationContext. El datasource utiliza como implementación la proporcionada por el pool de conexiones c3p0.

El proyecto cuenta con una única entidad User:

@Entity
@Table(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;
    
    @Column(nullable = false, unique = true)
    private String email;    

Todas las operaciones de acceso a la base de datos se codifican en la clase UserDAO.

@Repository
public class UserDao {

	@PersistenceContext
	private EntityManager em;
...

Master Pyhton, Java, Scala or Ruby

Ejecución de SQL con JPA

Para poder ejecutar SQL nativo, la especificación de EntityManager define varias sobrecargas del método createNativeQuery, aunque más adelante veremos que también podemos utilizar el método createNamedQuery. Por ejemplo, vamos ejecutar la siguiente consulta:

Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user ORDER BY email DESC");

Esta consulta la podemos lanzar como cualquier consulta definida mediante una instancia de Query, en nuestro caso la ejecución puede devolver una lista. Pero tenemos que tener en cuenta que la ejecución de consultas SQL devuelve un array de Object (Object[]) por cada registro que la consulta tenga que devolver. Cada posición del array se corresponde con una de las columnas retornadas siguiendo el orden definido dentro de la cláusula SELECT. En nuestro ejemplo, el orden de las columnas es id, name y email. Por tanto, si hay resultados iteraremos sobre los mismos para mapear el array con la respuesta a un objeto de tipo User.

	public List<User> findAll() {		
		Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user ORDER BY email DESC");
		
		List<Object[]> results = nativeQuery.getResultList();
		
		return results
				.stream()
				.map(result -> new User(((BigInteger) result[0]).longValue(), (String) result[1], (String) result[2]))
				.collect(Collectors.toList());
	}

Una consulta puede recibir parámetros que reemplazaremos tal y como haríamos con cualquier Query.

public User findById(Long id) {
		Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user WHERE id = ?");
		nativeQuery.setParameter(1, id);		

		Object[] result = (Object[]) nativeQuery.getSingleResult();
		return new User(((BigInteger) result[0]).longValue(), (String) result[1], (String) result[2]);
	}	

Los parámetros también se pueden definir mediante un nombre lo que permite hacer el código más legible.

public User findById(Long id) {
		Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user WHERE id = :id");
		nativeQuery.setParameter("id", id);		

		Object[] result = (Object[]) nativeQuery.getSingleResult();
		return new User(((BigInteger) result[0]).longValue(), (String) result[1], (String) result[2]);
	}	

También podemos realizar operaciones de modificación ejecutando nuestro SQL con el método executeUpdate. Puesto que estamos en Spring y tenemos configurado debidamente el JpaTransactionManager, marcamos el método como transaccional para que se pueda realizar la actualización con el commit correspondiente.

@Transactional
public void insert(Long id, String name, String email) {
		Query query = em.createNativeQuery("INSERT INTO user (id, name, email) VALUES(?,?,?)");
	        query.setParameter(1, id);
	        query.setParameter(2, name);
	        query.setParameter(3, email);
	        query.executeUpdate();
	}

Procesamiento de los resultados

Hemos visto que los registros que obtenemos como respuesta a una consulta SQL están definidos como Object[]. Sin embargo, además de trabajar directamente con esta respuesta tenemos otras opciones para procesar los resultados obtenidos.

El resultado es una entidad

Si el resultado coincide exactamente con una entidad el mapeo puede ser realizado de forma automática. Simplemente tenemos que indicar la entidad cuando construyamos el objeto Query.

public List<User> findAll() {		
		Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user ORDER BY email DESC", User.class);
		
		return nativeQuery.getResultList();		
	}
	
	public User findById(Long id) {
		Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user WHERE id = ?", User.class);
		nativeQuery.setParameter(1, id);
		
		return (User) nativeQuery.getSingleResult();		
	}	
El resultado no es una entidad

En este caso podemos definir el mapeo entre el resultado y la clase en la que queremos recogerlo mediante anotaciones. Por ejemplo, vamos a suponer que deseamos ejecutar la siguiente consulta:

select id, CONCAT(name, '-', email) as concat From user

El resultado queremos recogerlo en la siguiente clase.

package com.danielme.blog.nativesql;


public class UserDetail {

	private Long id;
	private String details;

        public UserDetail() {
		super();
	}

	public UserDetail(Long id, String details) {
		super();
		this.id = id;
		this.details = details;
	}

	public Long getId() {
		return id;
	}

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

	public String getDetails() {
		return details;
	}

	public void setDetails(String details) {
		this.details = details;
	}

}

Podemos hacer que el resultado de la consulta se “mapee” de forma automática en instancias de esta clase UserDetail configurando con anotaciones la relación entre las columnas obtenidas en el SQL y los parámetros del constructor, esto es, cómo llamar al constructor pasándole las columnas del resultado.

Nota: este mapeo hay que definirlo en una clase que sea entidad.

@SqlResultSetMapping(
        name = "DetailMapping",
        classes = @ConstructorResult(
        		targetClass = UserDetail.class,
                columns = {
                	@ColumnResult(name = "id", type=Long.class),
                    @ColumnResult(name = "concat")
                    }))

Ahora tenemos que proporcionar el nombre del SqlResultSetMapping al construir el objeto Query y ya lo tenemos.

	public List<UserDetail> findAllDetail() {		
		Query nativeQuery = em.createNativeQuery("SELECT id, CONCAT(name, '-', email) AS concat FROM user", "DetailMapping");
		
		return nativeQuery.getResultList();		
	}
Procesar el ResultSet (Hibernate)

Hibernate ofrece numerosas funcionalidades no recogidas en el estándar JPA. Una de ellas es el ResultTransformer que permite personalizar el procesamiento de los ResultSet en general y que puede aplicarse a cualquier consulta ya sea JPQL, Criteria o SQL.

Un ResultTransformer debe implementar dos métodos:

  • transformTuple: se invoca por cada registro del ResultSet, y recibimos el Object[] con las columnas del registro así como los nombres de las columnas que se devuelven. Lo usaremos para crear y devolver el objeto con los datos del registro.
  • transformList: se invoca tras el procesamiento completo del ResultSet y, por tanto, después de todas las llamadas a transformTuple. Aquí recibimos la lista con todos los registros que hemos mapeado con el método transformTuple para hacer cualquier tipo de procesamiento final.

Implementemos nuevamente el método findAllDetail utilizando un ResultTransformer. Puesto que estamos trabajando con JPA, tenemos que obtener el objeto Session de Hibernate para poder invocar el método createSQLQuery proporcionando la consulta (este método está marcado como obsoleto en Hibernate 5.2 en favor de createNativeQuery.

El nuevo método queda tal que así

public List<UserDetail> findAllDetailTransformer() {	
		NativeQuery nativeQuery = ((Session) this.em.getDelegate()).createSQLQuery("SELECT id, CONCAT(name, '-', email) AS concat FROM user");
		nativeQuery.setResultTransformer(new DetailTransformer());
		return nativeQuery.list();		
	}
	
	private static class DetailTransformer implements ResultTransformer {

		private static final long serialVersionUID = 1L;

		@Override
		public Object transformTuple(Object[] tuple, String[] aliases) {			
			return new UserDetail(((BigInteger) tuple[0]).longValue(), (String) tuple[1]);
		}

		@Override
		public List transformList(List collection) {
			return collection;
		}
		
	}

Obsérvese que hemos vuelto a realizar el mismo procesamiento de la respuesta que en la versión previa del método y que en la práctica la única ventaja que obtenemos al utilizar el ResultTransformer en nuestro ejemplo es la estandarización del procesamiento del ResultSet. Sin embargo, Hibernate proporciona varias implementaciones de ResultTransformer, y una de ellas puede resultar especialmente útil para nuestro ejemplo ya que realiza de forma automática el mapeo basándose en los setters del bean y en los nombres de las columnas del resultado. Se trata de AliasToBeanResultTransformer y podemos utilizarla de la siguiente forma haciendo que los nombres de los alias del SQL coincidan con los atributos con setters del bean y con sus tipos.

public List<UserDetail> findAllAliasToBean() {	
		NativeQuery nativeQuery = ((Session) this.em.getDelegate()).createSQLQuery("SELECT id, CONCAT(name, '-', email) AS details FROM user");
		nativeQuery.setResultTransformer(new AliasToBeanResultTransformer(UserDetail.class));
		return nativeQuery.list();		
	}

Puesto que para el id Hibernate nos devuelve el valor encapsulado en un objeto BigInteger vamos a cambiar el tipo de este atributo en UserDetail

package com.danielme.blog.nativesql;

import java.math.BigInteger;

public class UserDetail {

	private BigInteger id;
	private String details;

	public UserDetail() {
		super();
	}

	public UserDetail(BigInteger id, String details) {
		super();
		this.id = id;
		this.details = details;
	}

	public BigInteger getId() {
		return id;
	}

	public void setId(BigInteger id) {
		this.id = id;
	}

	public String getDetails() {
		return details;
	}

	public void setDetails(String details) {
		this.details = details;
	}

}

Named Queries

Las consultas SQL nativas puede ser definidas como Named Queries de JPA utilizando tanto anotaciones como XML (orm.xml). Por ejemplo, vamos a hacer nuevas versiones de los métodos findById y findAllDetail.

En primer lugar, definimos las NamedQueries en la entidad User con la anotación @NamedNativeQuery. Además de definir la consulta con el atributo query, opcionalmente podemos indicar la entidad en la que se debe mapear el resultado o el resultSetMapping si lo necesitamos.

@NamedNativeQueries({
    @NamedNativeQuery(name = "selectFindById", query = "SELECT id, name, email FROM user WHERE id = ?", resultClass = User.class),
    @NamedNativeQuery(name = "selectFindAllDetail", query = "SELECT id, CONCAT(name, '-', email) AS concat FROM user", resultSetMapping = "DetailMapping")
})

Ahora ejecutamos estas consultas como cualquier NamedQuery sin ninguna particularidad especial por el hecho de ser consultas en SQL.

public User findByIdNamedQuery(Long id) {
		Query nativeQuery = em.createNamedQuery("selectFindById");
		nativeQuery.setParameter(1, id);
		
		return (User) nativeQuery.getSingleResult();		
	}	
	
	public List<UserDetail> findAllDetailNamedQuery() {		
		Query nativeQuery = em.createNamedQuery("selectFindAllDetail");		
		return nativeQuery.getResultList();		
	}	

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 relacionados con Hibernate y/o Spring

JPA + Hibernate: Claves primarias

Spring JDBC Template: simplificando el uso de SQL

Persistencia en BD con Spring Data JPA

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

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 )

Google+ photo

Estás comentando usando tu cuenta de Google+. 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 )

w

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

A %d blogueros les gusta esto: