Maneira mais simples, legível e eficiente de executar SQL nativo com Spring Boot
Não podemos negar que a especificação JPA e todos os vendors facilitam em muito o nosso trabalho, mas em muitos casos temos que executar consultas nativas no banco de dados, e quando isso acontece acabamos tendo que concatenar strings, adicionando ou não uma nova condição na cláusula “where” conforme parâmetros informados pelo usuário, até mesmo em queries com centenas de linhas, todo esse código acaba poluindo nossas classes, dificultando a sua manutenção.
Pensando em como melhorar a manutenção dessas consultas é que surgiu a biblioteca Spring Native Query, que tem como objetivo principal a separação de responsabilidades, criando um arquivo SQL para cada consulta, eliminando assim as consultas que ficariam dentro das classes Java, adicionando também o JTwig que é um framework de processamento para que possamos alterar o arquivo SQL em tempo de execução, para resolver a questão dos parâmetros dinâmicos nas consultas. Utilizando também os objetos Pageable e Sort do Spring Data para paginação e ordenação dos registros.
O Spring Native Query depende do Spring Boot e Spring Data, pois ele é auto configuravel, faz uso de injeção de dependências, além de utilizar o EntityManager e NamedJdbcTemplate fornecidos pelo Spring Data. A ideia da biblioteca é parecida com a do Spring Data. Ao criar uma interface que herde de SpringNativeQuery ao subir a aplicação em tempo de execução é criado uma implementação para ela, onde por meio de proxy sabemos qual método da interface é executado, e a partir disso que toda magica acontece. Cada método da interface obrigatoriamente precisa ter um arquivo contendo o SQL e o nome do arquivo deve ser o mesmo do nome do método, os arquivos SQL devem estar dentro de uma pasta chamada nativeQuery, que deve ficar dentro da raiz do diretório resource. Os parâmetros do método e seus tipos são enviados para JTwig assim que o método está sendo executado, para que possamos modificar o SQL em tempo de execução. Para converter o resultado da consulta utilizamos o ResultTransform do Hibernate ou o BeanPropertyRowMapper do NamedJdbcTemplate. Portanto conseguimos converter as consultas para POJOS.
Para um melhor entendimento vamos criar uma simples aplicação contendo varias consultas nativas, expondo o resultado das consultas através de uma API Rest.
Inicialmente vamos criar um projeto Spring Boot contendo as seguintes dependências.
<!--Dependência do framework responsável por executar as consultas nativas-->
<dependency>
<groupId>io.github.gasparbarancelli</groupId>
<artifactId>spring-native-query</artifactId>
<version>1.0.28</version>
</dependency>
<!--demais dependências do Spring e do banco de dados-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>O próximo passo é criamos um arquivo chamado data.sql
CREATE TABLE USER (
cod INT NOT NULL,
full_name VARCHAR(45) NULL,
active INT NULL,
PRIMARY KEY (cod)
);
INSERT INTO USER (cod, full_name, active)
VALUES (1, "Gaspar", 1),
(2, "Elton", 1),
(3, "Lucini", 1),
(4, "Diogo", 1),
(5, "Daniel", 1),
(6, "Marcos", 1),
(7, "Fernanda", 1),
(8, "Maicon", 1),
(9, "Rafael", 0);Ao subir nossa aplicação o Spring automaticamente executara esse script, criando toda estrutura do banco de dados necessária para continuarmos o nosso exemplo.
Agora vamos adicionar as seguintes configurações no arquivo application.properties
# Pacote padrão do seu projeto
native-query.package-scan=io.github.gasparbarancelli.demospringnativequery
# Sufixo utilizado nos arquivos SQL
native-query.file.sufix=sqlCrie o seguinte POJO (Plain Old Java Objects) que será utilizado para retornas os dados das nossas consultas.
import java.io.Serializable;
public class UserTO implements Serializable {
private Integer id;
private BigDecimal height;
private String name;
// get e set das propriedades
}Também crie um DTO (Data Transfer Object) que será utilizado como filtro de algumas consultas.
import io.github.gasparbarancelli.NativeQueryOperator;
import io.github.gasparbarancelli.NativeQueryParam;
public class UserFilter {
private Number id;
@NativeQueryParam(value = "name", operator = NativeQueryOperator.CONTAINING)
private String name;
// get e set das propriedades
}Segue a interface que contem as chamadas para as consultas nativas.
import io.github.gasparbarancelli.NativeQuery;
import io.github.gasparbarancelli.NativeQueryParam;
import io.github.gasparbarancelli.NativeQuerySql;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import java.util.List;
import java.util.Map;
import java.util.Optional;
public interface UserNativeQuery extends NativeQuery {
// Retorna uma lista de todos os usuários
List<UserTO> findUsers();
@NativeQuerySql("SELECT cod as \"id\", full_name as \"name\" FROM USER")
List<UserTO> findBySqlInline();
/*
Passa para o template um map contendo o nome das propriedades e valores como filtro
*/
List<UserTO> findWithMap(Map<String, Object> params);
/*
Adiciona como parâmetro os fields da classe UserFilter
*/
List<UserTO> findUsersByFilter(@NativeQueryParam(value = "filter", addChildren = true) UserFilter filter);
/*
Exemplo de paginação filtrando apenas os usuários ativos
*/
List<UserTO> findActiveUsers(Pageable pageable);
/*
Exemplo de ordenação filtrando apenas os usuários ativos
*/
List<UserTO> findActiveUsersWithSort(Sort sort);
/*
Retorna um objeto Page que contem a quantidade de registros do banco de dados, utilizado para paginação
*/
Page<UserTO> findActiveUsersWithPage(Pageable pageable);
/*
Adicionando parâmetro com o nome de código na consulta
*/
UserTO findUserById(@NativeQueryParam(value = "codigo") Number id);
// Executa consulta que retorna apenas os ids dos usuários
List<Number> getUsersId();
// Obtém o nome do usuário filtrando pelo id
String getUserName(Number id);
// Filtra o usuário por ID e retorna um optional
Optional<String> getOptionalUserName(Number id);
// Filtra novamente o usuário por ID mas agora preenchendo o objeto UserTO
Optional<UserTO> findOptionalUserById(@NativeQueryParam(value = "codigo") Number id);
}Crie uma pasta chamada nativeQuery
O próximo passo é criar os arquivos contendo as consultas nativas, lembrando que devemos ter um arquivo para cada método existente na interface acima, e que cada arquivo precisa ter o mesmo nome do método.
# findUsers.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER
# findWithMap.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER
WHERE 1=1
/* for item in params */
AND {{item}} = :{{item}}
/* endfor */
# findUsersByFilter.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER
WHERE 1=1
/* if (filterId != null) */
AND cod = :filterId
/* endif */
/* if (filterName != null) */
AND full_name like :filterName
/* endif */
# findActiveUsers.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findActiveUsersWithPage.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findActiveUsersWithSort.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findUserById.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE cod = :codigo
# getUsersId.sql
SELECT cod as "id" FROM USER
# getUserName.sql
SELECT full_name as "name" FROM USER WHERE cod = :id
# findUsersByFilter.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER
WHERE 1=1
/* if (filterId != null) */
AND cod = :filterId
/* endif */
/* if (filterName != null) */
AND full_name like :filterName
/* endif */
# findActiveUsers.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findActiveUsersWithPage.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findActiveUsersWithSort.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE ACTIVE = true
# findUserById.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE cod = :codigo
#getUsersId.sql
SELECT cod as "id" FROM USER
# getUserName.sql
SELECT full_name as "name" FROM USER WHERE cod = :id
# getOptionalUserName.sql
SELECT full_name as "name" FROM USER WHERE cod = :id
# findOptionalUserById.sql
SELECT cod as "id", height as "height", full_name as "name" FROM USER WHERE cod = :codigoVamos criar o seguinte RestController, onde vamos injetar a nossa interface e criar endpoints para acessarmos cada método da interface.
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
@RestController
@RequestMapping("user")
public class UserController {
@Autowired private UserNativeQuery userNativeQuery;
@GetMapping()
public List<UserTO> findUsers() {
return userNativeQuery.findUsers();
}
@GetMapping("inline")
public List<UserTO> findBySqlInline() {
return userNativeQuery.findBySqlInline();
}
@GetMapping("map")
public List<UserTO> findWithMap() {
Map<String, Object> map = new HashMap<>();
map.put("cod", 1);
map.put("full_name", "Gaspar");
return userNativeQuery.findWithMap(map);
}
@PostMapping("filter")
public List<UserTO> findUsers(@RequestBody UserFilter filter) {
return userNativeQuery.findUsersByFilter(filter);
}
@GetMapping("active")
public List<UserTO> findUsers(
@RequestParam(value = "page", defaultValue = "0", required = false) int page,
@RequestParam(value = "size", defaultValue = "10", required = false) int size) {
return userNativeQuery.findActiveUsers(PageRequest.of(page, size));
}
@GetMapping("activeWithPage")
public Page<UserTO> findActiveUsersWithPage(
@RequestParam(value = "page", defaultValue = "0", required = false) int page,
@RequestParam(value = "size", defaultValue = "5", required = false) int size) {
return userNativeQuery.findActiveUsersWithPage(PageRequest.of(page, size));
}
@GetMapping("activeWithSort")
public List<UserTO> findActiveUsersWithSort(
@RequestParam(value = "columnName") String columnName) {
return userNativeQuery.findActiveUsersWithSort(Sort.by(columnName));
}
@GetMapping("{id}")
public UserTO findUser(@PathVariable("id") Number id) {
return userNativeQuery.findUserById(id);
}
@GetMapping("ids")
public List<Number> getIds() {
return userNativeQuery.getUsersId();
}
@GetMapping("{id}/name")
public String getUserName(@PathVariable("id") Number id) {
return userNativeQuery.getUserName(id);
}
@GetMapping("{id}/optional/name")
public Optional<String> getOptionalUserName(@PathVariable("id") Number id) {
return userNativeQuery.getOptionalUserName(id);
}
@GetMapping("{id}/optional")
public Optional<UserTO> findOptionalUser(@PathVariable("id") Number id) {
return userNativeQuery.findOptionalUserById(id);
}
}Por fim, podemos subir nossa aplicação e fazer requisições HTTP para os endpoints acima e verificarmos o retorno das consultas.
O código fonte dessa aplicação esta no repositório hospedado no GitHub.
