SQL nativo con JPA e Hibernate

Última actualización: 01/11/2020

logo hibernate

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 nuestro 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 las principales opciones que tenemos para trabajar directamente con SQL tanto con el estándar JPA como con Hibernate.

Proyecto de ejemplo

Para explorar las posibilidades de ejecución de SQL nativo en JPA vamos a utilizar una aplicación Maven estándar para Java 8 con Hibernate 5 que utiliza la base de datos embebida en memoria HSQLDB. Ejecutaremos el código utilizando tests de JUnit 4 que configuran automáticamente la base de datos.

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, realizadas a través de la implementación de EntityManager de JPA que proporciona Hibernate, se irán codificando en la clase UserDAO.

public class UserDao {

	private final EntityManager em;

	public UserDao(EntityManager em) {
		this.em = em;
	}
...

Usaremos la siguiente clase para hacer pruebas. Al inicio de cada ejecución ejecuta el script /src/test/resources/users.sql para configurar en la BD los datos de prueba. Para más información sobre JUnit, consultar mi tutorial Testing con JUnit 4.

package com.danielme.blog.nativesql.dao;

import com.danielme.blog.nativesql.DbUtils;
import org.junit.BeforeClass;


import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class UserDaoTest {

    private static UserDao userDao;

    @BeforeClass
    public static void setup() throws Exception {
        EntityManagerFactory entityManagerFactory = Persistence
                .createEntityManagerFactory("sqlDemoPersistence");
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        userDao = new UserDao(entityManagerFactory.createEntityManager());
        DbUtils.loadScript(entityManager, "users.sql");
    }

La configuración de Hibernate y la base de datos (HSQLD) se realiza en el fichero /src/main/resources/META-INF/persistence.xml. El modo update de Hibernate creará automáticamente la tabla users si no existe.

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
    <persistence-unit name="sqlDemoPersistence" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <class>com.danielme.blog.nativesql.entities.User</class>

        <properties>
            <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testdb"/>
            <property name="javax.persistence.jdbc.user" value="sa"/>
            <property name="javax.persistence.jdbc.password" value=""/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
        </properties>

    </persistence-unit>
</persistence>

Cursos de programación

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 para definir las consultas en anotaciones o en un fichero XML. 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 encontrado. 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]);
	}	

JPA nos brinda la posibilidad de hacer el código más legible utilizando nombres para identificar los parámetros a reemplazar en la consulta. Esta funcionalidad está disponible tanto para JPQL (el lenguaje de consultas de JPA en el que usamos clases y atributos en lugar de tablas y columnas) como SQL, y es algo que no podemos hacer al trabajar con SQL utilizando directamente JDBC.

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. En este caso la operación debe realizarse dentro de una transacción. Por simplicidad, se ha hecho una gestión “chapucera” de la misma; lo habitual en una aplicación web es usar el sistema de transacciones de Spring o del servidor de aplicaciones (p.e. WildFly) si trabajamos con JEE.

    public void insert(Long id, String name, String email) {
        em.getTransaction().begin();
        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();
        em.getTransaction().commit();
    }

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

Tuplas

El uso de la clase Tuple nos abstrae del array Object[] y nos permite acceder a los resultados a través del alias de la columna en la select.

    public List<User> findAllWithTuples() {
        Query nativeQuery = em.createNativeQuery("SELECT id, name, email FROM user ORDER BY email DESC", Tuple.class);
        List<Tuple> tuples = nativeQuery.getResultList();
        return tuples.stream()
                .map(t -> new User(t.get("id", BigInteger.class).longValue(), t.get("name", String.class), t.get("email", String.class)))
                .collect(Collectors.toList());
    }

SqlResultSetMapping

Se puede definir el mapeo entre el resultado y la clase en la que queremos recogerlo mediante un constructor. 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 la relación entre las columnas obtenidas en el SQL y los parámetros del constructor. De este modo, estamos especificando cómo llamar al constructor con las columnas del resultado.

Este mapeo se puede definir con anotaciones en una clase que sea una entidad JPA.

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

También se puede configurar en el fichero /META-INF/orm.xml.

<entity-mappings
        xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
        version="2.2">

    <sql-result-set-mapping name="DetailMapping">
        <constructor-result target-class="com.danielme.blog.nativesql.UserDetail">
            <column name="id" class="java.lang.Long"/>
            <column name="concat"/>
        </constructor-result>
    </sql-result-set-mapping>

</entity-mappings>

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, aparentemente, 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 as \"id\", CONCAT(name, '-', email) AS \"details\" FROM user");
		nativeQuery.setResultTransformer(new AliasToBeanResultTransformer(UserDetail.class));
		return nativeQuery.list();		
	}

NOTA: los alias de las columnas están con dobles comillas para evitar que HSQLDB los convierta a mayúsculas. Es una particularidad de esta base de datos que no aplica a MySQL o PostgreSQL.

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 pueden definirse 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. Hasta ahora, estos valores los habíamos pasado como parámetros al método createNativeQuery.

@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")
})
@Entity
@Table(name = "user")
public class User {

En el fichero orm.xml se haría del siguiente modo.

    <named-native-query name="selectFindById" result-class="com.danielme.blog.nativesql.entities.User">
        <query>SELECT id, name, email FROM user WHERE id = ?</query>
    </named-native-query>

    <named-native-query name="selectFindAllDetail" result-set-mapping="DetailMapping">
        <query>SELECT id, CONCAT(name, '-', email) AS concat FROM user</query>
    </named-native-query>

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

Una ventaja del uso de named query sobre las llamadas a createNativeQuery es la posibilidad de tipar los resultados con TypedQuery.

    public User findByIdNamedQuery(Long id) {
        TypedQuery<User> nativeQuery = em.createNamedQuery("selectFindById", User.class);
        nativeQuery.setParameter(1, id);
        return nativeQuery.getSingleResult();
    }

    public List<UserDetail> findAllDetailNamedQuery() {
        TypedQuery<UserDetail> nativeQuery = em.createNamedQuery("selectFindAllDetail", UserDetail.class);
        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

Spring JDBC Template: simplificando el uso de SQL

JPA + Hibernate: Claves primarias

JPA e Hibernate: relaciones y atributos Lazy

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 )

Conectando a %s

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