Hibernate – Projections com API Criteria
A várias postagens atrás, mais especificamente no ano de 2012, foram postados dois tutorias específicos sobre consultas com a API Criteria do Hibernate Framework. Os tutorias Consultas com Hibernate e a API Criteria, partes [1] e [2] demonstraram exemplos de consultas de vários tipos e níveis usando a API Criteria. Dentre estas consultas algumas abordaram exemplos simples sobre o uso de Projeções (Projections).
Neste tutorial irei postar alguns métodos demonstrando um pouco mais sobre o uso de projeções. Consultas que na época não foram abordadas. Para quem não sabe qual a função das projeções na API Criteria é possível fazer uma relação com as funções do SQL do tipo MAX, MIN, COUNT, DISTINCT, GROUP BY, SUN e AVG.
O tutorial foi escrito utilizando o banco de dados MySQL, o Hibernate 4.3.4.Final e o Maven como gerenciador das dependências. O projeto estará disponível para download no final do tutorial através do GitHub.
1. Banco de dados
A base de dados abaixo será usada como exemplo para a execução das consultas. Um script com os dados a serem inseridos na tabela estão disponíveis junto ao projeto no Github.
+----+----------------+---------+---------------+--------+-------+-----------------+ | ID | CITY | COUNTRY | FIRST_NAME | SALARY | STATE | SURNAME | +----+----------------+---------+---------------+--------+-------+-----------------+ | 1 | Porto Alegre | Brasil | Ana Maria | 1500 | RS | de Souza | | 2 | Florianopolis | Brasil | Marta | 1250 | SC | Soares | | 3 | Campinas | Brasil | Julio Cesar | 3100 | SP | da Silva | | 4 | Rio de Janeiro | Brasil | Pedro | 3500 | RJ | Fagundes | | 5 | Belo Horizonte | Brasil | Maria | 2750 | MG | de Souza Castro | | 6 | Santa Maria | Brasil | Ana Lucia | 3500 | RS | Pereira | | 7 | Porto Alegre | Brasil | Cristiano | 1990 | RS | Martins | | 8 | Rio de Janeiro | Brasil | Carlos Andre | 2600 | RJ | da Silva | | 9 | Sao Paulo | Brasil | Mirian | 2420 | SP | Gouveia | | 10 | Porto Alegre | Brasil | Marco Antonio | 1500 | RS | de Souza | | 11 | Santos | Brasil | Janaina | 1250 | SP | Soares | | 12 | Campinas | Brasil | Maria Rita | 3550 | SP | da Silva | | 13 | Rio de Janeiro | Brasil | Guilherme | 3500 | RJ | Santos Fagundes | | 14 | Ipatinga | Brasil | Mirdes | 2500 | MG | Castro Pereira | | 15 | Porto Alegre | Brasil | Victor | 3500 | RS | Ramos Neves | | 16 | Porto Alegre | Brasil | Cristian | 1990 | RJ | Vidal Silva | | 17 | Rio de Janeiro | Brasil | Andre | 2600 | RJ | Matos Mello | | 18 | Sao Paulo | Brasil | Matilda | 1990 | SP | Gouveia Campos | +----+----------------+---------+---------------+--------+-------+-----------------+ 18 rows in set (0.00 sec)
2. Classe de entidade Employee
Na Listagem 1 veremos a classe de entidade mapeada por meio de anotações. Os atributos desta classe representam as colunas da tabela Employees no banco de dados. Quando usamos a API Criteria para realizar as consultas devemos informar o nome do atributo como parâmetros e não o nome das colunas do banco de dados.
package com.mballem.tutorial.entity;
import javax.persistence.*;
import java.io.Serializable;
/**
* User: Marcio Ballem
* Date: 10/03/14
* Time: 18:47
* http://www.mballem.com
*/
@Entity
@Table(name = "EMPLOYEES")
public class Employee implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private Long id;
@Column(name = "FIRST_NAME")
private String firstName;
@Column(name = "SURNAME")
private String surname;
@Column(name = "SALARY")
private Integer salary;
@Column(name = "COUNTRY")
private String country;
@Column(name = "STATE")
private String state;
@Column(name = "CITY")
private String city;
public Employee() {
super();
}
// gere os métodos get/set, equals/hashCode e toString.
}
A partir deste ponto irei demonstrar algumas consultas possíveis de se executar usando projeções. Para cada consulta será postado o código Java, o resultado da consulta sobre a tabela Employees e também como essa consulta seria em SQL. Vamos começar com as consultas mais básicas em projeções. Nestas consultas veremos como utilizar funções do tipo SUM, AVG, COUNT, etc.
Na Listagem 2 temos um exemplo de consulta em SQL que retorna o maior valor de salário encontrado na tabela Employees. Veja agora na Listagem 3 a mesma consulta porém desta fez em Java utilizando Criteria com Projections. Na consulta em Java devemos ter um retorno, que neste caso será um objeto do tipo org.hibernate.Criteria. Esta classe fornece acesso ao método setProjection() o qual deverá receber como parâmetro a projeção que será utilizada, neste caso, será do tipo MAX.
select max(SALARY) from EMPLOYEES
Os métodos de projeção são disponibilizados pela classe org.hibernate.criterion.Projections e são do tipo estáticos, não precisando de uma instancia para invoca-los, como demonstrado na Listagem 3. No método max() devemos passar como parâmetro a String que representa o nome do atributo da classe Employee que mapeou a coluna SALARY da tabela Employees.
Criteria max = getSession().createCriteria(Employee.class)
.setProjection(Projections.max("salary"));
System.out.println("The highest salary : " + max.uniqueResult());
O resultado desta consulta pode ser observado a seguir:
The highest salary : 3550
Outros métodos como: min(), para encontrar o menor salário; sum() para somar todos os salários; avg() para retornar a média entre todos os salários e rowCount() para retornar o número de linhas da tabela estão descritos na Listagem 4.
Criteria min = getSession().createCriteria(Employee.class)
.setProjection(Projections.min("salary"));
System.out.println("The lowest salary: " + min.uniqueResult());
Criteria sum = getSession().createCriteria(Employee.class)
.setProjection(Projections.sum("salary"));
System.out.println("Sum of salaries: " + sum.uniqueResult());
Criteria avg = getSession().createCriteria(Employee.class)
.setProjection(Projections.avg("salary"));
System.out.println("Average of salaries: " + avg.uniqueResult());
Criteria rows = getSession().createCriteria(Employee.class)
.setProjection(Projections.rowCount());
System.out.println("Total of rows: " + rows.uniqueResult());
Os resultados destas consultas podem ser observados a seguir, juntamente com os SQL’s referentes a cada consulta:
select
min(SALARY)
from
EMPLOYEES
--The lowest salary: 1250
select
sum(SALARY)
from
EMPLOYEES
--Sum of salaries: 44990
select
avg(SALARY)
from
EMPLOYEES
--Average of salaries: 2499.4444
select
count(*)
from
EMPLOYEES
--Total of rows: 18
Nos exemplos anteriores, cada consulta em Java utilizou apenas uma única projeção. É possível adicionar, entretanto, uma lista de projeções dentro da mesma consulta. Um projeção que será bastante útil será o método groupProperty(), com função idêntica a função GroupBy do SQL.
select
count(*),
max(SALARY),
min(SALARY),
sum(SALARY),
avg(SALARY),
STATE
from
EMPLOYEES
group by
STATE
No SQL apresentado na Listagem 5 temos uma consulta que agrupa valores pela coluna STATE. Como retorno será exibido o maior, o menor, a soma, a média e a quantidade de linhas que contém um valor de salário para cada estado:
+----------+-------------+-------------+-------------+-------------+-------+ | count(*) | max(SALARY) | min(SALARY) | sum(SALARY) | avg(SALARY) | STATE | +----------+-------------+-------------+-------------+-------------+-------+ | 2 | 2750 | 2500 | 5250 | 2625.0000 | MG | | 5 | 3500 | 1990 | 14190 | 2838.0000 | RJ | | 5 | 3500 | 1500 | 11990 | 2398.0000 | RS | | 1 | 1250 | 1250 | 1250 | 1250.0000 | SC | | 5 | 3550 | 1250 | 12310 | 2462.0000 | SP | +----------+-------------+-------------+-------------+-------------+-------+ 5 rows in set (0.00 sec)
Veja no exemplo da Listagem 6, como transcrever a consulta SQL da Listagem 5 em uma consulta usando os métodos rowCount(), max(), min(), sum(), avg() e groupProperty(). Em relação as consultas anteriores, o objeto de retorno não será um objeto org.hibernate.Criteria, mas sim um objeto do tipo java.util.List. Isto porque agora o retorno poderá conter vários resultados e não apenas um único valor.
Para ter acesso ao retorno, será necessário realizar um for() no objeto que recebe o retorno da consulta (List results). Cada linha do objeto List contém um objeto array, referente a cada agrupamento, neste caso, a cada Estado. Sendo assim, para obter os resultados precisamos transformar cada linha da lista em um objeto do tipo array e depois acessar cada posição deste array. Cada posição do array equivale a um dos métodos usados na projeção, a não ser groupProperty().
List results =
getSession().createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.rowCount())
.add(Projections.max("salary"))
.add(Projections.min("salary"))
.add(Projections.sum("salary"))
.add(Projections.avg("salary"))
.add(Projections.groupProperty("state"))
).list();
for (Object objects : results) {
Object[] o = (Object[]) objects;
System.out.println("------------ "+ o[5] +" -------------");
System.out.println("Number of Employees: " + o[0]);
System.out.println("The highest salary: " + o[1]);
System.out.println("The lowest salary: " + o[2]);
System.out.println("Sum of salaries: " + o[3]);
System.out.println("Average of salaries: " + o[4]);
}
Vamos usar a mesma consulta das Listagens 5 e 6, porém desta vez iremos adicionar uma ordenação decrescente pela coluna STATE. Observe primeiramente a consulta em SQL – Listagem 7 – e em seguida o resultado apresentado.
select
count(*),
max(SALARY),
min(SALARY),
sum(SALARY),
avg(SALARY),
STATE
from
EMPLOYEES
group by
STATE
order by
STATE desc
+----------+-------------+-------------+-------------+-------------+-------+ | count(*) | max(SALARY) | min(SALARY) | sum(SALARY) | avg(SALARY) | STATE | +----------+-------------+-------------+-------------+-------------+-------+ | 5 | 3550 | 1250 | 12310 | 2462.0000 | SP | | 1 | 1250 | 1250 | 1250 | 1250.0000 | SC | | 5 | 3500 | 1500 | 11990 | 2398.0000 | RS | | 5 | 3500 | 1990 | 14190 | 2838.0000 | RJ | | 2 | 2750 | 2500 | 5250 | 2625.0000 | MG | +----------+-------------+-------------+-------------+-------------+-------+ 5 rows in set (0.00 sec)
Utilizando a ordenação mudamos a ordem de apresentação dos resultados. Para adicionar a ordenação na consulta Java com projeções, vamos informar através do método as() um alias para o atributo STATE. Este alias pode ser qualquer nome, neste caso, foi usado orderBy. Em seguida, fora do método setProjection() adicionamos no método addOrder() a ordenação DESC ou ASC e o alias referente ao atributo que vamos usar para ordenar os resultados. Confira a consulta na Listagem 8.
List results =
getSession().createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.rowCount())
.add(Projections.max("salary"))
.add(Projections.min("salary"))
.add(Projections.sum("salary"))
.add(Projections.avg("salary"))
.add(Projections.groupProperty("state").as("byOrder"))
).addOrder(Order.desc("byOrder")).list();
for (Object objects : results) {
Object[] o = (Object[]) objects;
System.out.println("------------ "+ o[5] +" -------------");
System.out.println("Number of Employees: " + o[0]);
System.out.println("The highest salary: " + o[1]);
System.out.println("The lowest salary: " + o[2]);
System.out.println("Sum of salaries: " + o[3]);
System.out.println("Average of salaries: " + o[4]);
}
Vamos agora executar uma consulta que retorne a linha do empregado que possui o maior salário em cada Estado. Para isso, usamos a função MAX(SALARY) e agrupamos o resultado pela coluna STATE, conforme Listagem 9.
select
FIRST_NAME,
SURNAME,
CITY,
STATE,
max(SALARY),
STATE
from
EMPLOYEES
group by
STATE
O resultado da operação do SQL da Listagem 10 será este:
+-------------+-----------------+----------------+-------+-------------+-------+ | FIRST_NAME | SURNAME | CITY | STATE | max(SALARY) | STATE | +-------------+-----------------+----------------+-------+-------------+-------+ | Maria | de Souza Castro | Belo Horizonte | MG | 2750 | MG | | Pedro | Fagundes | Rio de Janeiro | RJ | 3500 | RJ | | Ana Maria | de Souza | Porto Alegre | RS | 3500 | RS | | Marta | Soares | Florianopolis | SC | 1250 | SC | | Julio Cesar | da Silva | Campinas | SP | 3550 | SP | +-------------+-----------------+----------------+-------+-------------+-------+ 5 rows in set (0.00 sec)
Para transcrever o SQL da Listagem 9 em Criteria, usando Projections, implementamos o código conforme a Listagem 10. Neste caso, temos o método property() para que seja retornado o valor do atributo, correspondente a coluna na tabela Employees. O método max() seleciona o maior salário entre o grupo de cada Estado. Desta forma, o retorno será o mesmo obtido através da consulta SQL.
List results =
getSession().createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.property("firstName"))
.add(Projections.property("surname"))
.add(Projections.property("city"))
.add(Projections.property("state"))
.add(Projections.max("salary"))
.add(Projections.groupProperty("state"))
).list();
for (Object objects : results) {
Object[] o = (Object[]) objects;
System.out.println("-------------------------------------");
System.out.println("Name: " + o[0] + " " + o[1]);
System.out.println("City: " + o[2] + ", " + o[3]);
System.out.println("The highest salary: " + o[4]);
}
A consulta SQL apresentada na Listagem 11 irá ter como retorno cada valor de salário e a quantidade de linhas que possuem esse valor.
select
SALARY,
count(SALARY)
from
EMPLOYEES
group by
SALARY
Veja a seguir o resultado:
+--------+---------------+ | SALARY | count(SALARY) | +--------+---------------+ | 1250 | 2 | | 1500 | 2 | | 1990 | 3 | | 2420 | 1 | | 2500 | 1 | | 2600 | 2 | | 2750 | 1 | | 3100 | 1 | | 3500 | 4 | | 3550 | 1 | +--------+---------------+ 10 rows in set (0.00 sec)
List results =
getSession().createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.count("salary"))
.add(Projections.groupProperty("salary"))
).list();
for (Object objects : results) {
Object[] o = (Object[]) objects;
System.out.println("-------------------------------------");
System.out.println("Salary equal to " + o[1] + ": " + o[0]);
}
O último exemplo a ser demonstrado neste tutorial será com a função DISTINCT. Essa função tem o objetivo de não repetir, no retorno de uma consulta, linhas que possuem colunas com os mesmos valores. No exemplo do SQL da Listagem 13, vamos usar o DISTINCT na coluna SALARY. Assim, mesmo que na tabela existam linhas com o mesmo valor de salário, apenas uma linha será retornada com tal valor.
select
distinct SALARY,
STATE
from
EMPLOYEES
order by
STATE asc
A seguir temos o resultada da consulta da Listagem 13. Observe que apenas 14 linhas foram retornadas, enquanto a tabela Employees possui 18 linhas. Isso porque, 4 destes valores são valores repetidos na coluna SALARY.
+--------+-------+ | SALARY | STATE | +--------+-------+ | 2500 | MG | | 2750 | MG | | 1990 | RJ | | 2600 | RJ | | 3500 | RJ | | 1500 | RS | | 1990 | RS | | 3500 | RS | | 1250 | SC | | 1250 | SP | | 1990 | SP | | 2420 | SP | | 3100 | SP | | 3550 | SP | +--------+-------+ 14 rows in set (0.00 sec)
Na Listagem 14 temos a consulta anterior escrita com Criteria e Projections. No método distinct(), passamos como parâmetro a propriedade salary. Em seguida informamos que queremos o retorno dos estados, através da propriedade state. E por fim ordenamos o resultado por Estado.
List results =
getSession().createCriteria(Employee.class)
.setProjection(
Projections.projectionList()
.add(Projections.distinct(Projections.property("salary")))
.add(Projections.property("state"))
).addOrder(Order.asc("state")).list();
for (Object objects : results) {
Object[] o = (Object[]) objects;
System.out.println("-------------------------------------");
System.out.println("Salaries in " + o[1] + " state: " + o[0]);
}
Saiba mais
- Criteria com Hibernate
- Projections, aggregation and grouping
- Consultas com Hibernate e a API Criteria | Parte I
-
Consultas com Hibernate e a API Criteria | Parte II
