Curso Spring Data JPA. 13: SQL

Después de doce capítulos, hemos conseguido mucho sin escribir ni una sola sentencia SQL. Tal es el poder de Spring Data JPA. Entonces, ¿por qué querríamos escribir SQL? Y llegado el caso, ¿cómo lo hacemos? Este capítulo responde ambas preguntas.

<12 QueryDSL

ÍNDICE

Rutinas SQL 14>

¿Qué nos aporta SQL?

Las consultas derivadas resuelven con facilidad búsquedas muy simples. Cuando necesitamos más, contamos con JPQL. Este lenguaje incorpora las capacidades básicas de SQL e incluso permite invocar funciones propias de la base de datos subyacente. Asimismo, la implementación de JPQL de Hibernate (HQL) ofrece funcionalidades adicionales. Por ejemplo, desde Hibernate 6 cuenta con las funciones de ventana (window functions) de SQL.

JPQL presenta dos grandes beneficios:

Es independiente de la base de datos. Esto significa que una sentencia JPQL obtiene los mismos resultados en MySQL, PostgreSQL, Oracle, etcétera, sin requerir su adaptación a cada base de datos. Dado que éstas no «hablan» JPQL, los proveedores de JPA se encargan de traducir las consultas JPQL al SQL de cada base de datos.
Escribes las consultas para el modelo de clases de entidad. Así, desaparece la dualidad modelo de clases \ modelo de tablas.

Todo lo anterior ya ha sido tratado en el curso, en especial en el capítulo 6. Además, ofrezco una introducción a JPQL \ HQL en estos capítulos de mi curso Jakarta EE:

JPA con Hibernate (15): trabajando con JPQL \ HQL (1)

JPA con Hibernate (17): el lenguaje JPQL\HQL (2). Reuniones

JPA con Hibernate (18): el lenguaje JPQL\HQL (3). Funciones

Ahora bien, JPQL tiene una limitación, que se intuye con la expresión capacidades básicas que dije antes. De vez en cuando nos encontraremos con alguna consulta de cierta complejidad que se nos resiste, pero que podemos o sabemos escribir con SQL gracias a funcionalidades inexistentes en JPQL. O quizás sí seamos capaces expresar la búsqueda que queremos con JPQL, pero resulte más compleja o ineficiente que una equivalente en SQL.

Cuando una consulta SQL sea la solución idónea, apuesta por ella. Saca partido a las posibilidades de tu base de datos, están ahí para ayudarte.

En contra de esta decisión podría argumentarse la pérdida de la portabilidad que JPQL garantiza entre bases de datos. Tengamos presente que el lenguaje SQL presenta diferencias notables entre proveedores. Sin embargo, la experiencia me dice que cambiar de base de datos o ser compatible a la vez con varias es un requerimiento que aparece en pocos proyectos. No renuncies a SQL por un problema que no tienes y que probablemente nunca tendrás.

El empleo de SQL alcanza su máxima expresión con la creación de procedimientos y funciones dentro de la base de datos. Lo veremos en la próxima entrega del curso.

JPA ofrece un soporte de primera para ejecutar SQL, como demuestra este artículo. Y Spring Data JPA añade su mágico polvo de hadas. ¿El resultado? Disfrutarás de lo mejor de dos mundos: la simplicidad de los repositorios y la potencia del SQL de tu base de datos.

Nota 1. Todas las consultas SQL que veremos son triviales de escribir con JPQL o incluso de manera derivada, y así te recomiendo que lo hagas. Se trata de ejemplos sencillos con los que demostraré el uso de SQL con Spring Data JPA.

Nota 2. Puedes beneficiarte de características de SQL inexistentes en JPQL y Criteria API, y sin tener que recurrir a escribir sentencias SQL en cadenas, con las librerías QueryDSL, Blaze Persistence y jOOQ. Hablé de ellas en el capítulo precedente.

Consultas nativas con @Query

En JPA el término consultas nativas se refiere al lenguaje de consultas propio de la base de datos, esto es, SQL.

En Spring Data JPA declaramos una consulta nativa igual que una JPQL: escribiéndola en la anotación @Query, la cual marca el método de un repositorio que la ejecuta. Pero se requiere de una configuración adicional. Veámoslo con el siguiente ejemplo:

@Transactional(readOnly = true)
public interface CountrySqlQueryRepository extends Repository<Country, Long> {

    @Query(value = """
            SELECT c.* FROM countries c
            WHERE UPPER(c.name) LIKE UPPER(:text) OR UPPER(c.capital) LIKE UPPER(:text)
            ORDER BY c.name ASC""",
            nativeQuery = true)
    List<Country> findByNameOrCapital(String text);

}

Este método encuentra los países cuyo nombre o capital contienen una cadena dada, ignorándose la capitalización. Por ser nativa, debemos activar el flag nativeQuery de @Query, ya que su valor predeterminado es false, lo que indica que la consulta está escrita con JPQL.

El resto del capítulo revisa cuáles de las funcionalidades ya vistas en el curso para JPQL son compatibles con SQL y cuáles no. Por ello, seré breve y me centraré en darte ejemplos descriptivos.

Variables de la consulta

Como muestra el ejemplo anterior, las variables de la consulta se vinculan con los parámetros del método mediante su nombre. También puedes relacionar variables y parámetros según la posición de estos últimos en la signatura del método:

    @Query(value = """
            SELECT c.* FROM countries c
            WHERE UPPER(c.name) LIKE UPPER(?1) OR UPPER(c.capital) LIKE UPPER(?1)
            ORDER BY c.name ASC""",
            nativeQuery = true)
    List<Country> findByNameOrCapital(String text);

Como novedad frente a JPQL, Spring Data JPA admite el formato de JDBC consistente en marcar cada variable con una interrogación. La secuencia de variables debe coincidir, en orden y número, con la secuencia de parámetros. Por tanto, si la consulta reutiliza variables, deberán repetirse los parámetros, circunstancia que también se da en las consultas derivadas.

    @Query(value = """
            SELECT c.* FROM countries c
            WHERE UPPER(c.name) LIKE UPPER(?) OR UPPER(c.capital) LIKE UPPER(?)
            ORDER BY c.name ASC""",
            nativeQuery = true)
    List<Country> findByNameOrCapital(String textName, String textCapital);

De todas formas, mejor quédate con las dos sintaxis anteriores; el código resulta más comprensible.

Consultas nativas nombradas

La alternativa a la declaración de consultas SQL con @Query es el sistema de consultas nombradas de JPA. También disponibles para JPQL (véase el capítulo 6), estas consultas se identifican por un nombre único. En el caso de SQL, declaras una consulta nombrada en una clase de tipo entidad así:

@NamedNativeQuery(name = "Country.findByMinPopulation",
        resultClass = Country.class,
        query =  """
            SELECT * FROM countries
            WHERE population >:minPopulation ORDER BY population""")
@Entity
public class Country {

@NamedNativeQuery contiene la consulta SQL, su nombre y el tipo de resultado que puede ser una clase de entidad o un escalar. Este es el método que ejecuta la consulta:

public interface CountrySqlQueryRepository extends Repository<Country, Long> {

  @Query(nativeQuery = true)
  List<Country> findByMinPopulation(int minPopulation);

Método y consulta se vinculan si el nombre de la segunda se compone del nombre de la entidad que gestiona el repositorio, un punto y el nombre del método. Así pues, para la consulta Country.findByMinPopulation el código anterior es válido. Con todo, te liberas de esta convención si pones el nombre de la consulta en la propiedad name de @Query.

Al igual que las consultas JPQL nombradas, puedes declarar las nativas nombradas en el fichero /META-INF/orm.xml estándar de JPA:

<named-native-query  name="Country.findByMinPopulation"
            result-class="com.danielme.springdatajpa.model.entity.Country">
    <query>SELECT * FROM countries WHERE population >:minPopulation ORDER BY population</query>
</named-native-query>

Retorno de datos

Tal y como demuestran los ejemplos previos, el retorno de entidades es directo. La SELECT debe proyectar exactamente todas las columnas de la entidad, ni más ni menos. Aquí tienes un ejemplo con Optional:

@Query(value = "SELECT * FROM countries WHERE UPPER(name) = UPPER(:name)",
            nativeQuery = true)
Optional<Country> findByName(String name);

También es directa la devolución de un resultado asimilable a un tipo primitivo de Java:

@Query(value = "SELECT COUNT(*) FROM countries WHERE population>:minPopulation",
            nativeQuery = true)
int countByMinPopulation(int minPopulation);

En definitiva, lo expuesto al final del capítulo 3.

Pero no es suficiente. En muchas consultas tendrás que devolver objetos que no se correspondan con una entidad o un tipo primitivo. ¿Cómo conseguirlo? Te lo expliqué en el capítulo 7. A continuación repaso las opciones que vimos y las acompaño de ejemplos basados en SQL. Te adelanto que solo hay una diferencia con respecto a JPQL.

Resultados en crudo (RAW)

Lo más directo es recoger cada resultado en un array de Object cuyas posiciones respetan el orden de cada elemento declarado en la SELECT:

@Query(value = """
        SELECT id, name FROM countries
        WHERE population >:minPopulation ORDER BY population""",
        nativeQuery = true)
List<Object[]> findByMinPopulationAsRaw(int minPopulation);

Simple, pero nada práctico. Tendrás que convertir el array en objetos más cómodos:

@Test
void testFindByMinPopulationAsRaw() {
    List<Object[]> over100millionRaw = countryRepository.findByMinPopulationAsRaw(100_000_000);
    List<IdNameDTO> over100millionDTO = over100millionRaw
                .stream()
                .map(c -> new IdNameDTO((Long) c[0], (String) c[1]))
                .toList();

     assertThat(over100millionDTO)
                .extracting(IdNameDTO::getId)
                .containsExactly(MEXICO_ID, USA_ID);
 }

Proyección en objetos `Tuple`

Cambiemos el array de Object por objetos de la interfaz Tuple de JPA:

    @Query(value = """
            SELECT id, name FROM countries
            WHERE population >:minPopulation ORDER BY population""",
            nativeQuery = true)
    List<Tuple> findByMinPopulationAsTuple(int minPopulation);

Lo cierto es que no supone una gran mejora:

 List<Tuple> over100millionTuple = countryRepository.findByMinPopulationAsTuple(100_000_000);
        List<IdNameDTO> over100millionDTO = over100millionTuple
                .stream()
                .map(c -> new IdNameDTO((Long) c.get("id"), (String) c.get("name")))
                .toList();

Proyección en interfaz

Este mecanismo, exclusivo de Spring Data, recoge en objetos de una interfaz los resultados de una consulta. El requisito es que todos los elementos de la SELECT tengan nombres o alias coincidentes con nombres de los atributos que representan los getters de la interfaz. Además, los tipos de datos de esos elementos deben ser compatibles con las clases que devuelven los getter.

Tomemos como ejemplo la siguiente interfaz:

public interface IdName {

    Long getId();

    String getName();

}

IdName te sirve para cualquier consulta que proyecte solo un campo id, un campo name, o ambos:

@Query(value = """
        SELECT id, name FROM countries
        WHERE population >:minPopulation ORDER BY population""",
        nativeQuery = true)
List<IdName> findByMinPopulationAsInterface(int minPopulation);

Muy cómodo, ¿verdad?

Proyección en constructor

Esta es la característica de JPQL que más me gusta. Tomemos como ejemplo el record empleado ya tantas veces a lo largo del curso:

public record IdNameRecord(Long id, String name) {
}

Invocas al constructor de IdNameRecord en la misma consulta JPQL:

@Query("""
       SELECT new com.danielme.springdatajpa.model.dto.IdNameRecord(c.id, c.name)
       FROM Country c WHERE c.confederation.id=:id""")
List<IdNameRecord> findIdNameAsRecordByConfederationId(Long id);

Lo anterior es imposible con SQL; la base de datos desconoce qué diantres es eso de new.

La buena noticia es que JPA ofrece un mecanismo que permite la proyección en constructor con SQL. Tienes que establecer la relación entre los elementos de la proyección y los parámetros de un constructor. Una forma de hacerlo es con las anotaciones @SqlResultSetMapping y @ConstructorResult en una clase de tipo entidad. He aquí un ejemplo esclarecedor:

@SqlResultSetMapping(
        name = "IdNameRecordMapping",
        classes = @ConstructorResult(
                targetClass = IdNameRecord.class,
                columns = {
                        @ColumnResult(name = "id"),
                        @ColumnResult(name = "name")
                }))
@Entity
public class Country {

Con @SqlResultSetMapping defines una forma de convertir la proyección de una SELECT en objetos. Una de esas formas posibles es usar un constructor, configurado con @ConstructorResult. Debes proporcionarle la clase y la lista de parámetros del constructor.

Configuras cada parámetro con @ColumnResult, indicando el nombre del componente de la SELECT que será su argumento. Con caracter opcional, puedes especificar el tipo del parámetro en la propiedad (en el ejemplo es innecesario):

@ColumnResult(name = "id", type = Long.class)

La otra forma de asociar proyección y constructor es hacerlo en el fichero orm.xml.

<sql-result-set-mapping name="IdNameRecordMapping">
    <constructor-result target-class="com.danielme.springdatajpa.model.dto.IdNameRecord">
        <column name="id"/>
        <column name="name"/>
    </constructor-result>
</sql-result-set-mapping>

Y ahora, ¿cómo ordenas a Spring Data JPA que aplique la conversión llamada Country.IdNameRecordMapping? No busques un atributo en @Query o una anotación específica para estos menesteres; sigue estos pasos:

Crea una consulta nombrada nativa.
Relaciónala con el conversor.
Declara un método que la ejecute.

Los pasos uno y tres ya sabes cómo darlos, y el segundo resulta trivial:

@SqlResultSetMapping(
        name = "IdNameRecordMapping",
        classes = @ConstructorResult(
                targetClass = IdNameRecord.class,
                columns = {
                        @ColumnResult(name = "id"),
                        @ColumnResult(name = "name")
                }))
@NamedNativeQuery(name = "Country.findByMinPopulationAsRecord",
        query =  """
            SELECT id, name FROM countries
            WHERE population >:minPopulation ORDER BY population""",
        resultSetMapping = "IdNameRecordMapping")
@Entity
public class Country {

Como ves, la propiedad resultSetMapping de @NamedNativeQuery contiene el nombre del transformador del resultado. Su equivalente en el fichero orm.xml es result-set-mapping:

<named-native-query  name="Country.findByMinPopulationAsRecord"
                     result-set-mapping="IdNameRecordMapping">
    <query>SELECT name, id FROM countries WHERE population >:minPopulation ORDER BY population</query>
</named-native-query>

Elijas anotaciones o XML, este método ejecuta la consulta:

@Query(nativeQuery = true)
List<IdNameRecord> findByMinPopulationAsRecord(int minPopulation);

Ordenación

Las consultas nativas y la ordenación dinámica de Sort son incompatibles. Ni lo intentes:

Caused by: org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting in method

Esta petición, rechazada hace años, explica el motivo. Ahora bien, en la próxima sección veremos un caso en el que sí funciona.

Como idea, si necesitas esta funcionalidad, quizás seas capaz de implementarla a la medida de ciertas consultas con QueryRewriter.

Paginación

En SQL es válido el contenido del capítulo 9. Contamos con la paginación configurable con Pageable:

@Query(value = """
          SELECT * FROM countries
          WHERE united_nations_admission > :minDateAdmission""",
          nativeQuery = true)
List<Country> findByMinAdmissionDate(LocalDate minDateAdmission, Pageable page);

Y aquí sí funciona la ordenación dinámica:

@Test
void testFindByMinAdmissionDate() {
    Sort sort = Sort.by(Country_.NAME);
    LocalDate minDate = LocalDate.of(1955,1,1);
    Pageable page1 = PageRequest.of(0, 2, sort);

    List<Country> countriesAfterDate = countryRepository.findByMinAdmissionDate(minDate, page1);

    assertThat(countriesAfterDate)
            .extracting(Country::getId)
            .containsExactly(KOREA_ID, SPAIN_ID);
}

Es un buen momento para subrayar que la paginación debe acompañarse de ordenación. Así, garantizas la coherencia del reparto de los resultados entre las páginas.

Puedes devolver Page y Slice. Recuerda que cuando retornas Page, Spring Data JPA crea y ejecuta una consulta de tipo SELECT COUNT a fin de conocer el número total de resultados. Si esta consulta te causara problemas (no es la situación del ejemplo), declara una consulta a medida con la propiedad countQuery:

@Query(value = """
        SELECT * FROM countries
        WHERE united_nations_admission > :minDateAdmission""",
        countQuery = """
                SELECT COUNT(id) FROM countries
                WHERE united_nations_admission > :minDateAdmission""",
        nativeQuery = true)
Page<Country> findByMinAdmissionDate(LocalDate minDateAdmission, Pageable page);

Si tu consulta fuera una nativa nombrada, ten en cuenta que la ordenación definida en Pageable no funciona, al menos con las versiones del proyecto del curso (Spring Boot 3.1 e Hibernate 6.2.5 ). Y para colmo de males, cuando devuelvas Page tendrás que escribir la consulta de conteo.

Para esto último, crea esa consulta con el mismo nombre que la consulta principal más el sufijo «.count»:

@NamedNativeQuery(name = "Country.findByMinAdmissionDate",
        resultClass = Country.class,
        query =  """
            SELECT * FROM countries
            WHERE united_nations_admission > :minDateAdmission""")
@NamedNativeQuery(name = "Country.findByMinAdmissionDate.count",
        resultClass = Long.class,
        query =  """
            SELECT COUNT(*) FROM countries
            WHERE united_nations_admission > :minDateAdmission""")

@Entity
public class Country {

Si no te gusta esta convención, establece el nombre de la consulta de conteo en la propiedad countName de @Query:

@Query(nativeQuery = true,
      countName="nombre_de_consulta_nombrada_de_conteo")
Page<Country> findByMinAdmissionDate(LocalDate minDateAdmission, Pageable page);

Operaciones de escritura

Las operaciones de escritura ya las revisamos al final del capítulo 6 para el lenguaje JPQL. En resumen, declaras la sentencia con @Query en un método marcado con @Modifying y que deberás ejecutar en una transacción con permiso de escritura.

Con SQL nada cambia, salvo la imprescindible activación de la opción nativeQuery. Aquí tienes uno de los ejemplos que vimos para JPQL reescrito con SQL:

@Transactional
@Modifying
@Query(value ="""
        UPDATE countries SET name = CONCAT(c.name, ' (', :confederationName, ')')
        WHERE c.confederation_id IN
            (SELECT id FROM  confederations WHERE name LIKE :confederationName)""",
        nativeQuery = true)
int appendConfederationToName(String confederationName);

Recuerda el inconveniente de los cambios efectuados por las sentencias UPDATE y DELETE: no afectan a las entidades de los contextos de persistencia activos. Vimos que este potencial problema se mitigaba con las propiedades clearAutomatically y flushAutomatically de @Modifying.

Además de UPDATE y DELETE, puedes ejecutar operaciones de tipo DDL; es decir, aquellas que alteran la estructura de la base de datos. No obstante, será raro que necesites hacer algo así.

 @Transactional
 @Modifying
 @Query(value = "ALTER TABLE countries ADD area INTEGER",
            nativeQuery = true)
 void addColumn();

Resumen

Las claves del capítulo:

Las consultas SQL se declaran en métodos de repositorios con la anotación @Query. Debes activar la opción nativeQuery para informar que se trata de una consulta nativa (SQL); de lo contrario, Spring asume que está escrita con JPQL.
También cuentas con consultas nombradas.
Disfrutas de todas las funcionalidades disponibles para las consultas JPQL, salvo la ordenación dinámica directa con Sort. La ordenación definida en la configuración de paginación (Pageable) sí funciona, excepto con consultas nombradas.
La proyección en constructor te exige declarar la consulta como una consulta nativa nombrada. Esto te permite relacionar la SELECT con los parámetros de un constructor. Todo esto puedes hacerlo con anotaciones o en el fichero orm.xml.
Las operaciones de escritura (UPDATE, DELETE, DDL) se configuran con la anotación @Modifying. Ejecútalas siempre dentro de una transacción con la capacidad de escritura.

Código de ejemplo

El proyecto, explicado en el capítulo dos, se encuentra en GitHub. Si necesitas ayuda para obtener y configurar proyectos alojados en GitHub, consulta este tutorial.

<12 QueryDSL

ÍNDICE

Rutinas SQL 14>

Otros tutoriales relacionados con Spring

Introducción a Spring Boot: Aplicación Web con servicios REST y Spring Data JPA

Spring Boot: Gestión de errores en aplicaciones web y REST

Testing en Spring Boot con JUnit 45. Mockito, MockMvc, REST Assured, bases de datos embebidas

Spring JDBC Template: simplificando el uso de SQL