Utilizando JPQL para consultas em um banco de dados MySQL

Texto de: Geraldo Daros

Introdução

Consultas aos bancos de dados são feitas diariamente em praticamente todos os softwares existentes, desde o site que você pesquisa por algum livro do seu autor preferido até o streaming de vídeo que você busca por seu gênero de filme favorito. Hoje veremos uma linguagem de consulta muito usada no Java e aprenderemos o necessário para fazermos nossas próprias consultas dinâmicas que atendem as necessidades do usuário.

O que é e para que usamos o JPQL?

Atualmente no ecossistema Java, é muito comum utilizarmos JPQL (Java Persistence Query Language) para fazer consultas. Essa é uma linguagem de consulta orientada a objetos usada para consultar dados em bancos de dados relacionais usando JPA (Java Persistence API). Neste artigo, exploraremos como usar JPQL para realizar consultas em um banco de dados MySQL, com muitos exemplos práticos que você pode usar no seu projeto.

Link para a documentação do JPQL.

Configurando projeto e criando a tabela de produtos

Aqui está o script SQL para criar a tabela de produtos no banco de dados MySQL:

CREATE DATABASE IF NOT EXISTS loja;
USE loja;

CREATE TABLE IF NOT EXISTS produto (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(255) NOT NULL,
    preco DECIMAL(10, 2) NOT NULL,
    quantidade INT NOT NULL
);

Isso criará um banco de dados chamado loja e uma tabela chamada produto com as colunas id, nome, preco e quantidade.

Configurando a entidade JPA

Agora, precisamos configurar uma entidade JPA que representará os dados da tabela de produtos. Aqui está uma classe Java que faz isso:

@Entity
@Table(name = "produtos")
public class Produto {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "nome")
    private String nome;

    @Column(name = "preco")
    private Double preco;

    @Column(name = "quantidade")
    private Integer quantidade;

	// Getters e Setters
}

Certifique-se de que a configuração do seu banco de dados usando o JPA estejam corretas.

Consultando a tabela de produtos com JPQL

Uma vez que temos nossa tabela e entidade configuradas, podemos começar a fazer consultas usando uma consulta JPQL, ou query JPQL, que nada mais é que a tradução de consulta no contexto de solicitar informações do banco de dados. Agora, faremos uma pequena população no banco de dados com esses dados:

INSERT INTO produto (nome, preco, quantidade) VALUES
    ('Camiseta', 29.99, 100),
    ('Calça Jeans', 59.99, 50),
    ('Tênis', 79.99, 30),
    ('Bolsa', 39.99, 20),
    ('Relógio', 99.99, 15),
    ('Shorts', 19.99, 80),
    ('Chapéu', 9.99, 60),
    ('Meias', 5.99, 200),
    ('Sapato', 89.99, 25),
    ('Óculos de Sol', 49.99, 40);

Consultas simples

Primeiramente, criaremos uma consulta JPQL para buscar todos os produtos dessa forma:

SELECT p FROM Produto p

Ou dessa forma sem o alias:

SELECT Produto FROM Produto

No primeiro exemplo usamos um alias p para dar um nome para a nossa variável Produto, isso é comumente usado, mas não é obrigatório. Também usaremos esse alias em outras partes da consulta para referenciar a entidade.

Ambas essas consultas JPQL selecionarão todos os produtos.

Consultas com condições

Abaixo vemos uma consulta com filtro por preços abaixo de 10.

SELECT p FROM Produto p WHERE p.preco < 10

Na próxima consulta utilizamos o filtro para selecionar apenas com produtos que possuem “Cal” no nome, CONCAT(p.nome, '') com o uso do CONCAT estamos forçando que p.nome seja concatenado com uma string vazia, então mesmo que p.nome seja um número, após o CONCAT ele será uma string por ser concatenado com uma string vazia.

SELECT p FROM Produto p WHERE CONCAT(p.nome, '') LIKE '%Cal%’

Também podemos utilizar essa forma sem forçar a conversão para string:

SELECT p FROM Produto p WHERE p.nome LIKE '%Cal%'

No próximo exemplo, filtraremos com base no tamanho do campo nome.

SELECT p FROM Produto p WHERE LENGTH(p.nome) > 10

Consulta com Parâmetros

Até o momento fizemos apenas consultas hard-coded, com os valores inseridos manualmente, mas em projetos reais, quase tudo precisa ser dinâmico. Agora abordaremos o uso de parâmetros nas consultas.

SELECT p FROM Produto p WHERE p.preco < 10

Se transformaria em: 

SELECT p FROM Produto p WHERE p.preco < :nomeDaVariavel

nomeDaVariavel é o nome da variável que você quer usar na expressão. Basicamente quando colocamos dois pontos significa que o que virá a seguir é o nome de uma variável (parâmetro), o nome deverá ser usado para fazer a substituição depois. Segue exemplo usando sem Spring e depois com Spring:

Sem Spring:

String jpql = "SELECT p FROM Produto p WHERE p.preco > :valorLimite";
TypedQuery<Produto> query = entityManager.createQuery(jpql, Produto.class);
query.setParameter("valorLimite", variavelDesejada);

Com Spring:

@Query("SELECT p FROM Produto p WHERE p.preco > :valorLimite")
ArrayList<Produto> obterPorPrecoMenosQue(@Param("valorLimite") Double valorLimite);

Outro tipo de substituição de parâmetros é o uso do ?1, na expressão abaixo ?1, ? é um marcador de posição para um parâmetro de consulta em JPQL enquanto o número que o segue (1, neste caso), indica a ordem em que o parâmetro aparece na consulta. Isso é usado para vincular o parâmetro real à consulta quando você define o valor do parâmetro antes de executar a consulta. Exemplo:

Sem Spring:

String jpql = "SELECT p FROM Produto p WHERE p.preco > ?1";
Query query = entityManager.createQuery(jpql);
query.setParameter(1, variavelDesejada);

Com Spring:

@Query("SELECT p FROM Produto p WHERE p.preco > ?1")
ArrayList<Produto> obterPorPrecoMenosQue(Double valorLimite);

Basicamente a diferença de você usar o :variável para ?1 é que na primeira abordagem você poderá interpolar o nome do parâmetro na consulta e a outra forma será interpolada pela sequência dos parâmetros, podendo ser ?1, ?2, ?3, ?4..., isso depende da forma que você irá receber esses valores.

Consultas úteis

Aqui vai uma lista de algumas consultas úteis no seu dia a dia. Considere os exemplos apenas como base e use a sua entidade, lembrando, estamos usando a entidade Produto configurada anteriormente. O alias que foi inserido foi o p, porque é a inicial de Produto, o que é uma convenção popular. Faremos as consultas com valores hard-coded, mas caso você queira tornar essas consultas dinâmicas, basta adicionar um parâmetro no lugar dos valores comparados como foi mostrado anteriormente.

Ordem alfabética:

SELECT p FROM Produto p ORDER BY p.nome

Maior e menor preço:

SELECT p FROM Produto p WHERE p.preco = (SELECT MAX(p2.preco) FROM Produto p2)
SELECT p FROM Produto p WHERE p.preco = (SELECT MIN(p2.preco) FROM Produto p2)

Soma da quantidade de uma coluna:

SELECT SUM(p.quantidade) FROM Produto p

Preço entre 20.0 e 50.0:

SELECT p FROM Produto p WHERE p.preco BETWEEN 20.0 AND 50.0

Quantidade maior que 10 e ordenada de forma de decrescente, ou seja, na ordem alfabética, porém começando do Z e indo até A.

SELECT p FROM Produto p WHERE p.quantidade > 10 ORDER BY p.preco DESC

Maior valor por unidade:

SELECT p FROM Produto p ORDER BY (p.preco / p.quantidade) DESC

Menor valor total em estoque:

SELECT p FROM Produto p ORDER BY (p.preco * p.quantidade)

Preço maior que a média de preço entre todos os produtos:

SELECT p FROM Produto p WHERE p.preco > (SELECT AVG(p2.preco) FROM Produto p2)

Produtos com nomes que contêm mais de 10 caracteres com uma quantidade superior a 5:

SELECT p FROM Produto p WHERE LENGTH(p.nome) > 10 AND p.quantidade > 5

Produtos cujo nome deve estar incluso no conjunto 'Camiseta', 'Calça Jeans', 'Tênis'.

SELECT p FROM Produto p WHERE p.nome IN ('Camiseta', 'Calça Jeans', 'Tênis')

Acredito que com esses exemplos vocês poderão sair fazendo as próprias consultas de diversas formas diferentes.

Funções úteis do SQL

Aqui deixarei uma lista das funções SQL que temos disponíveis no JPQL com exemplos práticos.

ABS (valor absoluto do preço):

SELECT p FROM Produto p WHERE ABS(p.preco - 50.0) > 10.0

COALESCE (retorna o preço se for menor que 20, o COALESCE verifica uma lista de argumentos separados por vírgula (argumento1, argumento2, argumento3) e irá retornar o primeiro elemento que ele encontrar não nulo, então caso o p.preco for nulo ele retornará 0.0 para fazer a comparação com 20.0):

SELECT p FROM Produto p WHERE COALESCE(p.preco, 0.0) < 20.0

CONCAT (concatena valores):

SELECT CONCAT(p.nome, ' - ', p.quantidade) FROM Produto p

LENGTH (comprimento):

SELECT p FROM Produto p WHERE LENGTH(p.nome) > 10

UPPER (transforma as letras em maiúsculas):

SELECT UPPER(p.nome) FROM Produto p

LOWER (transforma as letras em minúsculas):

SELECT p FROM Produto p WHERE LOWER(p.nome) = 'tenis'

MOD (retorna o resto de uma divisão, como o famoso % que usamos em muitas linguagens de programação):

SELECT p FROM Produto p WHERE MOD(p.quantidade, 5) = 0

NULLIF (função NULLIF aceita dois argumentos e retorna null se os dois argumentos forem iguais; caso contrário, retorna o primeiro argumento. Neste caso, estamos comparando o campo preco de Produto com 0.0. Se o preço for igual a zero ou for nulo, NULLIF retornará null, o que é uma maneira de verificar se essa condição foi atendida ou não, ou seja, buscaremos somente os produtos com o preço null ou 0.0):

SELECT p FROM Produto p WHERE NULLIF(p.preco, 0.0) IS NULL

SQRT (raiz quadrada):

SELECT p FROM Produto p WHERE SQRT(p.preco) > 8.0

SUBSTRING (recebe 3 parâmetros: o primeiro é uma String e os próximos dois parâmetros representam o início e o fim da substring. Isso significa que a expressão abaixo pegará todos p.nome da primeira letra até a terceira letra. OBS: JPQL considera o índice 1 como o nosso índice 0 no Java, então a letra número 1 é realmente a primeira da String.):

SELECT SUBSTRING(p.nome, 1, 3) FROM Produto p

TRIM (remove espaços em branco):

SELECT TRIM(p.nome) FROM Produto p WHERE TRIM(TRAILING FROM p.nome) LIKE '%produto'

CASE (definindo uma expressão CASE, semelhante ao switch case):

SELECT CASE p.nome WHEN 'Camiseta' THEN 'Roupa' WHEN 'Tênis' THEN 'Calçado' ELSE 'Outro' END FROM Produto p

Há mais diversas outras funções e formas de fazer consultas. De forma geral abordamos os elementos que você deve ter domínio para conseguir criar suas próprias consultas.

Conclusão

JPQL é uma ferramenta poderosa para consultar dados em bancos de dados relacionais usando JPA e neste artigo, exploramos como usá-la para realizar consultas em um banco de dados MySQL. Executamos diversos tipos de consultas usando JPQL, incluindo consultas simples, consultas com parâmetros e consultas com ordenação. Espero que este artigo tenha fornecido uma introdução útil ao seu uso.