Curso de Grails & Groovy

Groovy - Consultando banco de dados com createCriteria, HQL e Dynamic Finding

Se preferir, assista à vídeo-aula deste conteúdo disponível em
https://www.youtube.com/watch?v=8vPDSMROq0s

No framework Grails, existem três formas principais de consulta ao banco de dados: dynamic finding, através de métodos criados implicitamente pela linguagem Groovy baseados nos atributos das classes de domínio; createCriteria, que é uma implementação mais simples do criteria já existente no framework Hibernate; e HQL, que é a linguagem padrão do framework Hibernate.

No final da página você encontra exemplos práticos básicos e avançados de consulta a banco de dados com dynamic finding, createCriteria e HQL

Dynamic Finding

A partir dos atributos das classes de domínio, o Grails cria automaticamente os métodos no formato findAllBy e findBy. Por exemplo, considere a seguinte classe de domínio:

class Cliente{
	String nome
    	Date dataNascimento
    	Integer idade
}

Neste caso, o Grails criará, por exemplo, os seguintes métodos: findByNome(), findAllByNome(), findByDataNascimento(), findAllByDataNascimento(), findByIdade(), findAllByIdade().
Além disso, são criados outros métodos com operadores como Not Equal, Like, Ilike, Greater Than, Less Than, Is Null, Is Not Null, In List, Between, tais como:
Cliente.findAllByNomeLike("%João%")
Cliente.findAllByNomeIlike("%João%")
Cliente.findAllByDataNascimentoBetween(ontem, new Date())
Cliente.findAllByIdadeGreaterThanEquals(30)
Cliente.findAllByIdadeNotEqual(20)
Cliente.findAllByIdadeLessThan(50)
Cliente.findAllByNomeInList(["João","José","Juca"])
Cliente.findAllByDataNascimentoIsNull()
Cliente.findAllByDataNascimentoIsNotNull()

O Grails também cria métodos combinando os operadores de comparação com operadores lógico (OR e AND). Exemplos:
Cliente.findAllByNomeLikeAndIdadeGreaterThan("%João%",30)
Cliente.findAllByIdadeLessThanOrDataNascimentoIsNull(30)
Cliente.findAllByNomeIlikeOrDataNascimentoLessThan("%Zé%",new Date())

Saiba mais na documentação oficial do Grails

createCriteria()

É uma implementação simplificada do Criteria do framework Hibernate. É uma ótima solução para consultar dados do banco de dados sem a necessidade de escrever comandos HQL, e permite aninhar critérios de seleção com facilidade.

A estrutura básica do createCriteria() é:

def lista = Cliente.createCriteria().list{	
    ilike("nome","%João")    
    order("idade")
}
Os operadores mais comuns do createCriteria() são:

Operador Exemplo
between between("balance", 500, 1000)
eq (equal - igual) eq("nome", "Leandro Guarino")
eq (ignore-case - ignorando maiúsculas e minúsculas) eq("nome", "Leandro Guarino", [ignoreCase: true])
gt (greater than - maior que) gt("idade", 30)
ge (greater than or equal - maior ou igual) ge("idade", 30)
like (like - 'como') like("nome", "%José%")
ilike (ignore-case like - 'like' ignorando maiúsculas e minúsculas) ilike("nome", "%José%")
in (in a list - em uma lista) 'in'("nome", ["João","José","Juca"])
isNull isNull("dataNascimento")
isNotNull isNotNull("dataNascimento")
lt (less than - menor que) lt("idade",50)
le (less than or equal - menor ou igual) le("idade",50)
order (para ordenar a lista) order("nome") / order("nome","desc")
sqlRestriction sqlRestriction "date_part('year',data_nascimento)=2015"

Saiba mais na documentação oficial do Grails

HQL - Hibernate Query Language

É a linguagem nativa do framework Hibernate muito semelhante à linguagem SQL. No entanto, com a linguagem HQL, as consultas são criadas considerando as classes de domínio e não as tabelas do banco de dados.

A estrutura básica para executar uma consulta HQL no Grails é

def lista = Cliente.executeQuery("select cli from Cliente cli 
where cli.nome like :parametroNome",
["parametroNome": "%João%"])

Saiba mais na documentação oficial do Hibernate

Exemplos práticos - do básico ao avançado

Logo abaixo, são apresentados exemplos práticos básicos e avançados de consulta a banco de dados.
Os exemplos são baseados no projeto RestauranteToComFome do curso de Grails (http://bit.ly/1R63pG6), cujo código-fonte está disponível em https://github.com/leandroguarino/RestauranteToComFome.

1) Listar todos os clientes

Dynamic Finding
def lista = Cliente.findAll()
createCriteria()
def lista = Cliente.createCriteria().list{
}
HQL
def lista = Cliente.executeQuery("select cli from Cliente cli")

2) Listar todos os clientes ordenando pelo nome crescentemente

Dynamic Finding
def lista = Cliente.findAll().sort{it.nome}
createCriteria()
def lista = Cliente.createCriteria().list{
	order("nome")
}
HQL
def lista = Cliente.executeQuery("select cli from Cliente cli order by cli.nome")

3) Listar todos os clientes ordenando pelo nome decrescentemente

Dynamic Finding
def lista = Cliente.findAll().sort{it.nome}.reverse()
createCriteria()
def lista = Cliente.createCriteria().list{
	order("nome","desc")
}
HQL
def lista = Cliente.executeQuery("select cli from Cliente cli order by cli.nome desc")

4) Listar todos os clientes cujo nome contém a string "Rober" (ignore-case)

Dynamic Finding
def lista = Cliente.findAllByNomeIlike("%Rober%")
createCriteria()
def lista = Cliente.createCriteria().list{
	ilike("nome","%Rober%")
}
HQL
String hql = "select cli from Cliente cli "+
" where lower(cli.nome) like lower('%Rober%')"
def lista = Cliente.executeQuery(hql)

5) Listar as bebidas com mais de 500 ml

Dynamic Finding
def lista = Bebida.findAllByUnidadeAndLiquidoGreaterThan("ml",500)
createCriteria()
def lista = Bebida.createCriteria().list{
	eq("unidade","ml")
    	gt("liquido",500d) //500d porque o atributo "liquido" 
    					é Double na classe
}
HQL
String hql = "select beb from Bebida beb "+
" where unidade=:parametroUnidade and liquido > :parametroLiquido"
def lista = Bebida.executeQuery(hql, [parametroUnidade: "ml", parametroLiquido: 500d])

6) Listar os pratos que são para 2 ou mais pessoas

Dynamic Finding
def lista = Prato.findAllByQuantidadePessoasGreaterThanEquals(2)
createCriteria()
def lista = Prato.createCriteria().list{
	ge("quantidadePessoas",2)
}
HQL
String hql = "select p from Prato p "+
" where quantidadePessoas >= :parametroQtde"
def lista = Prato.executeQuery(hql, [parametroQtde: 2])

7) Listar os pratos cujo nome começa com a letra A ou a letra B

Dynamic Finding
def lista = Prato.findAllByNomeIlikeOrNomeIlike("A%","B%")
createCriteria()
def lista = Prato.createCriteria().list{
	or{
        ilike("nome","A%")
        ilike("nome","B%")
    }
}
HQL
String hql = "select p from Prato p "+
" where lower(nome) like lower('A%') or lower(nome) like lower('B%')"
def lista = Prato.executeQuery(hql)

8) Listar as sobremesas que possuem mais de 200 calorias e possuem a palavra 'bolo' no nome, ou custam menos de R$ 5

Dynamic Finding
//Seria isto, mas o dynamic finding permite apenas um operador lógico AND / OR
//Ou é tudo AND, ou é tudo OR
ERRO: 
def lista = Sobremesa.findAllByCaloriasGreaterThanAndNomeIlikeOrPrecoLessThan(200,"%bolo%",5.00)
createCriteria()
	def lista = Sobremesa.createCriteria().list{
		or{
            and{
                ilike("nome","%bolo%")
                gt("calorias",200)
            }
            lt("preco",5d)
    	}
	}
HQL
String hql = "select s from Sobremesa s "+
" where (lower(nome) like lower('%bolo%') and calorias > 200) or preco < 5"
def lista = Sobremesa.executeQuery(hql)
[... A partir daqui vamos abandonar o Dynamic Finding devido às suas limitações ...]

9) Listar os produtos que possuem mais de 5 unidades disponíveis no estoque

createCriteria()
def lista = Produto.createCriteria().list{
	estoque{
    		gt("quantidade",5)
    	}
}
HQL
String hql = "select p from Produto p "+
" where p.estoque.quantidade > 5 "
def lista = Produto.executeQuery(hql)

10) Listar os pedidos que possuem mais de 1 item

createCriteria()
def lista = Pedido.createCriteria().list{
	sizeGt("itens",1)
}
HQL
String hql = "select p from Pedido p "+
" where p.itens.size > 1 "
def lista = Pedido.executeQuery(hql)

11) Listar os pedidos em que algum item pedido possua observação

createCriteria()
def lista = Pedido.createCriteria().list{
	createAlias("itens","item")
        isNotNull("item.observacao")
}
HQL
String hql = "select p from Pedido p "+
" join p.itens item "+
" where item.observacao is not null "
def lista = Pedido.executeQuery(hql)

12) Listar os pedidos dos produtos que possuem menos de 3 unidades no estoque

createCriteria()
def lista = Pedido.createCriteria().list{
	createAlias("itens","item")
    	createAlias("item.produto","prod")
    	createAlias("prod.estoque","estoc")
        lt("estoc.quantidade",3)
}
HQL
String hql = "select p from Pedido p "+
" join p.itens item "+
" join item.produto prod "+
" join prod.estoque estoc "+
" where estoc.quantidade < 3 "
def lista = Pedido.executeQuery(hql)

ou 

String hql = "select p from Pedido p "+
" join p.itens item "+
" where item.produto.estoque.quantidade < 3 "
def lista = Pedido.executeQuery(hql)
[... Para funções de agregação (COUNT, SUM, etc.) use HQL, é mais fácil ...]

13) Contar quantos clientes estão cadastrados

HQL
String hql = "select count(*) from Cliente c"
def resultado = Cliente.executeQuery(hql)

O resultado dessa consulta será uma lista com um único valor, por exemplo: [14]
Para acessar a quantidade de clientes cadastrados seria necessário escrever o seguinte código em Groovy:

Integer qtde = resultado.first()
		ou 
Integer qtde = resultado[0]

Uma solução mais adequada é transformar o resultado da consulta HQL em um mapa, usando o apelido (alias) para os campos na consulta.

String hql = "select new map(count(*) as QtdeClientes) from Cliente c"
def resultado = Cliente.executeQuery(hql)

O resultado dessa consulta será uma lista em que cada item é um mapa com os apelidos definidos na consulta, por exemplo: [{QtdeClientes:14}]
Para acessar a quantidade de clientes cadastrados seria necessário este código em Groovy:

Integer qtde = resultado.first().QtdeClientes

Nos próximos exemplos veremos que essa solução torna mais legível o código que manipulará o resultado da consulta.

14) Contar a quantidade de pedidos feitos em 2015

HQL
String hql = "select new map(count(*) as QtdePedidos) from Pedido p "+
" where year(p.dataHora) = 2015 "
def resultado = Pedido.executeQuery(hql)

15) Contar quantas vezes cada produto foi pedido

HQL
String hql = "select "+
" new map(count(*) as Qtde, pro.nome as NomeProduto, pro.id as IdProduto) "+
" from Produto pro "+
" join pro.itens "+
" group by pro.nome, pro.id "
def resultado = Produto.executeQuery(hql)

Agora, temos mais de um campo no resultado da consulta e também temos mais de uma linha.
Podemos manipular o resultado da consulta assim:

resultado.each{ linha ->
	println linha.Qtde
	println linha.NomeProduto
	println linha.IdProduto
}

Observe que, devido ao uso de mapa no retorno da consulta, podemos acessar cada campo pelo apelido definido na consulta, e isso torna o código mais legível.

16) Somar os itens pedidos para gerar o valor total de cada pedido

HQL
String hql = "select "+
" new map(sum(item.quantidade*item.valorVenda) as ValorTotal, p.id as IdPedido) "+
" from ItemPedido item "+
" join item.pedido p "+
" group by p.id "
def resultado = ItemPedido.executeQuery(hql)

17) Listar os 5 produtos mais pedidos

HQL
String hql = "select "+
" new map(count(*) as Qtde, pro.id as IdProduto, pro.nome as NomeProduto) "+
" from Produto pro "+
" join pro.itens item "+
" group by pro.id, pro.nome "+
" order by Qtde desc "
def resultado = Produto.executeQuery(hql,[max: 5, offset: 0])

18) Listar os produtos que foram pedidos mais de 2 vezes

HQL
String hql = "select "+
" new map(count(*) as Qtde, pro.id as IdProduto, pro.nome as NomeProduto) "+
" from Produto pro "+
" join pro.itens item "+
" group by pro.id, pro.nome "+
" having( count(*) > 2 ) "
def resultado = Produto.executeQuery(hql)

19) Listar as datas em que houve pedidos, eliminando duplicidade

HQL
String hql = "select "+
" distinct new map(p.dataHora as DataHora) "+
" from Pedido p "
def resultado = Pedido.executeQuery(hql)

20) Listar a média dos preços atuais dos produtos de cada pedido.

É possível fazer com JOIN, mas vamos usar SUBSELECT para exemplificar.

HQL
String hql = "select "+
" new map( p.id as IdPedido, p.dataHora as DataHora, "+
" (select avg(pro.preco) from ItemPedido item "+
" join item.produto pro where item.pedido.id = p.id) as Media)"+
" from Pedido p "
def resultado = Pedido.executeQuery(hql)
Voltar ao ndice