Para criar as consultas ao banco de dados, o Cronapp utiliza um assistente de consulta visual, que facilita a criação de consultas ao banco de dados.

Visão Geral 

Âncora
Habilitar Paginação Habilitar Paginação

As consultas são realizadas em JPQL (Java Persistence Query Language), que é uma linguagem de consulta ORM que atua sobre classes e objetos, diferentemente do SQL que realiza as consultas baseadas em tabelas. Apesar de a diferença estrutural, a linguagem JPQL é bem semelhante ao SQL, porém a primeira possui a vantagem de que a mesma consulta pode ser realizada em todos os bancos de dados.

O Cronapp possui um assistente de consulta visual para facilitar a criação de consultas JPQL. O assistente de consulta é utilizado tanto em blocos de programação quanto em fontes de dados, mas existem pequenas diferenças entre o assistente de consulta do bloco e da fonte de dados. O assistente permite criar consultas de forma visual (low-code) ou manual (high-code).

Figura 1.1 - Assistente de consulta visual (Bloco de programação)

1. Estrutura de Consulta: Possui as opções para a criação da consulta, como selecionar as entidades, campos, criar regras e outros.
2. Modo de Consulta: Altera a exibição dos dados em modo de teste da consulta, que pode ser por tabelas ou objetos (Figuras 1.2 e 1.3). 
3. Aplicar dados da requisição: Aplica as requisições (paginação, ordenação e outros) diretamente na consulta. Disponível apenas em consultas via bloco de programação.
4. Habilitar Paginação: Realiza a paginação dos dados em uma página de forma automática (Disponível apenas em consultas via bloco de programação). 
5. Testar Consulta: Exibe o resultado da consulta criada em uma nova janela, altere o campo Modo de Consulta para modificar a forma de exibição (Figuras 1.1 e 1.2).
6. Editar como Texto: Muda a janela para editar a consulta manualmente (high-code). Após alterar uma consulta manualmente, não será possível editar novamente em modo visual.
7. Visualizar como Texto: Exibe a consulta feita no formato visual em forma de texto.
8. OK e Cancelar: Salva ou cancela a consulta.

   Figura 1.2 - Exibição de consulta em modo objetos

Figura 1.3 - Exibição de consulta em modo tabela

Estrutura da consulta

Agora vamos entender melhor as opções disponíveis na estrutura da consulta. 

Figura 2.1 - Estrutura visual da consulta

1. Entidade: Adiciona as entidades que farão parte da consulta.
2. Campos: Define quais campos serão buscados na consulta.
3. Regras: Regra que será considerada ao buscar os dados no banco.
4. Agrupar: Agrupa linhas que têm os mesmos valores.
5. Having: Cria regras para serem usadas juntos com os campos agrupados.
6. Ordenar: Define a ordem em que os dados serão retornados.

Entidade

Adiciona as entidades que farão parte da consulta. Equivale a seleção de tabelas após sentença "FROM" em uma consulta SQL.

Em consultas JPQL não é necessário usar o "JOIN", pois na área de seleção dos campos já mapeia todos os campos das entidades relacionadas. Exemplo: Entidade A tem relação com B, a entidade B tem relação com C e C possui um campo chamado "nome", dessa forma, basta selecionar a entidade A e na área Campos (abaixo) selecionar a opção "A.B.C.nome"

Figura 2.2 - Área de seleção das entidades

• Entidade: Exibe a entidade recém selecionada.
• " ... " : Abre a janela para seleção da entidade.
• Alias: Permite adicionar um apelido.
• + Adicionar: Adiciona a entidade selecionada a consulta.
• Excluir: Exclui entidade adicionada.

1. Exibe a entidade selecionada.
2. Exibe o alias da entidade selecionada,

Para adicionar as entidades que farão parte da consulta, clique no botão "...", selecione a classe desejada e finalize clicando em "+ Adicionar".

Campos

Define quais campos serão buscados na consulta. Equivale a seleção dos campos das tabelas após sentença "SELECT" em uma consulta SQL.

Figura 2.3 - Área de seleção dos campos

É possível adicionar os campos desejados através do botão "Novo Campo"ou adicionar todos os campos da entidade de uma só vez clicando em "Adicionar Todos". 

• + Novo Campo: Adiciona um novo campo na consulta.
• + Adicionar Todos: Insere todos os campos das entidades selecionadas.
• Excluir: Exclui o campo.

1. Tipo de retorno: Define como o valor do campo será retornado. Os tipos são:
   • Obter:  Retorna o valor do campo como está no banco;  
   • MIN: Retorna o menor valor dos campos;
   • MAX: Retorna o maior valor dos campos;
   • AVG: Retorna a média aritmética dos valores dos campos registrados no banco;
   • SUM: Retorna a soma dos valores do campo;
   • COUNT: Retorna a quantidade de registros do campo.
2. Campo selecionado: Seleciona qual o campo será consultado. 

Ao selecionar somente o alias (exemplo do campo "lg" na imagem 2.3) a consulta retorna todos os campos, nesse caso, não seria necessário selecionar o segundo campo "lg.providerDisplayName".

Não é necessário fazer JOIN em consultas JPQL, o exemplo do campo "lg.user.name" retornará o nome do usuário que faz relação com o login selecionado.

Regras

Nessa propriedade é definida a regra que será considerada ao buscar os dados no banco. Por meio do botão "Nova Regra" é possível adicionar regras e selecionar um condicional único entre as regras ou adicionar vários grupo de regras e selecionar condicionais para cada grupo individualmente através do botão "Novo Grupo". Equivale ao filtro após sentença "WHERE" em uma consulta SQL.

Figura 2.4 - Área de seleção das regras da consulta

1. Negação e operador lógico: nega a sentença vinculada, adicionando um "not" antes, e define se o operador lógico entre as sentenças vinculadas será "e" ou "ou".
2. Inverter: alterna o operador lógico e os campos condicionais das sentenças vinculadas, por exemplo: se estiver selecionado "ou", será substituído por "e", isso também ocorrerá no campo condicional (campo 4 da figura 2.4), que alternará de "igual" para "diferente".
3. Campo da entidade: campo da entidade que será utilizado para a criação da regra.
4. Condição: define a condição que deve existir entre o campo e o parâmetro, são elas
   • Igual;
   • diferente;
   • contido;
   • não contido;
   • iniciando com;
   • não iniciando com;
   • contém;
   • não contém;
   • terminando com;
   • terminado sem;
   • é vazio;
   • não é vazio;
   • é nulo e 
   • não é nulo.
5. Parâmetro: Define-se o nome do parâmetro que será comparado com o campo da entidade. É possível inserir o conteúdo literal, ou definir uma variável que será passada por parâmetro, adicionando ":" (dois pontos) na frente da variável.

6. ↑ ↓ : Permite arrastar uma regra ou grupo para dentro de um grupo ou ordená-los,

A regra (where) definido na figura 2.4 ficará da seguinte forma:

WHERE
usr.name = :name AND (NOT (lg.user.phoneNumber LIKE (CONCAT('%', '(71)', '%')) ))

Agrupar

Possui a função de agrupar linhas que têm os mesmos valores em um ou mais campos específicos. Equivale a sentença "GROUP BY" em uma consulta SQL.

Figura 2.5 - Área de seleção do Agrupar

1. Campo a ser agrupado: Seleciona qual o campo será agrupado.

• + Novo Campo: Adiciona outro campo para agrupar.
• Excluir: exclui o campo selecionado.

Having

A propriedade having funciona de forma semelhante a propriedade Regra, entretanto ela cria uma regra para os grupos criados com a propriedade agrupar. Por isso, o having sempre deve ser usado junto com o agrupar.

Figura 2.6 - Área de seleção das regras do Having

1. Tipo de dados da condição: É definido qual o tipo será retornado do banco para ser comparado na regra criada pelo having. Os tipos são:
   
   • MIN: Retorna o menor valor dos campo;
   • MAX: Retorna o maior valor dos campo;
   • AVG: Retorna a média aritmética dos valores do campo registrados no banco;
   • SUM: Retorna a soma dos valores dos campos e
   • COUNT: Retorna a quantidade de registros do campo.
2. Campo: Campo usado para a comparação com o parâmetro.
3. Condição: Condição que será utilizado para comparar o valor obtido no campo e o parâmetro. São eles:
   • Igual;
   • Diferente;
   • Menor;
   • Menor ou igual;
   • Maior e
   • Maior ou igual.
4. Parâmetro: Valor que será comparado com o tipo de dado retornado.

Ordenar

Nessa opção é definida a ordem de retorno dos itens. Por exemplo, é possível trazer os dados organizados do menor id para o maior ou o contrário.

Figura 2.7 - Área de ordenação da consulta

1. Tipo de dado: Define qual o tipo de dado que será utilizado para ordenar os dados.
   • Obter:  Retorna o valor do campo como está no banco;  
   • MIN: Retorna o menor valor dos campos;
   • MAX: Retorna o maior valor dos campos;
   • AVG: Retorna a média aritmética dos valores dos campos registrados no banco;
   • SUM: Retorna a soma dos valores do campo;
   • COUNT: Retorna a quantidade de registros do campo.
2. Campo: Campo que será utilizado para a ordenação.
3. Tipo de ordenação: Define o tipo de ordenação que será considerada na propriedade.
   • Asc: Ascendente e
   • Desc: Descendente.

Diferença

entre consultas: Visual, JPQL e SQL

Como informado anteriormente, o Assistente de consulta visual gera uma consulta em JPQL e esta se parece muito com o SQL. Veja abaixo uma imagem representando uma consulta usando o Assistente Visual (Figura 3.1), a consulta JPQL gerada pela consulta visual e uma consulta equivalente em SQL.

Image Added

Figura 3.1 - Exemplo de consulta visual 

SELECT		c.estado FROM Cidade c
WHERE		c.quantidadeHabitantes <> :minimo
GROUP BY	c.estado
HAVING 		SUM(c.quantidadeHabitantes) > :quantidadeHabitantes
ORDER BY 	c.estado ASC

Exemplo da mesma consulta feita em SQL:

SELECT 		estado FROM cidade 
WHERE  		quantidadehabitantes > minimo 
GROUP  BY 	estado 
HAVING Sum		SUM(quantidadehabitantes) > quantidadehabitantes 
ORDER  BY 	estado ASC 

Realizando consultas entre duas tabelas

É possível também realizar consultas entre duas tabelas. No exemplo abaixo, iremos retornar o nome dos países em que as cidades possuem mais de 50 mil habitantes e a soma dos habitantes de todas as cidades do país devem ser iguais ou superiores a 1 milhão.

Figura 4 - Consulta visual JPQL (Bloco de programação)

Siga os passos abaixo para obter a consulta da figura 4:

1. Entidade:  Na propriedade entidade, utilizamos a entidade Cidade que é representada como 'c'.
2. Campos: Na propriedade campos, selecionamos a opção 'c.pais.nome' para podermos retornar o nome do país em que a cidade está relacionada.
3. Regra: Nessa propriedade a regra definida foi que a quantidade de habitantes das cidades retornadas deve ser maior que 0.
4. Agrupar: Os itens foram agrupados por país, por meio do campo 'c.pais.nome'.
5. Having: Na propriedade having, a regra definida foi que o número de habitantes dos países(O atributo utilizado no exemplo foi o 'c.quantidadeHabitantes', mas como as cidades estão agrupadas por país considera-se que essa soma dos habitantes das cidades representa o total de habitantes do país) deve ser maior ou igual a 1 milhão.

Exemplo da mesma consulta feita em SQL:

SELECT c.fk_pais, p.nome 
FROM Cidade as c 
INNER JOIN Pais as p 
ON p.id = c.fk_pais 
WHERE c.quantidadeHabitantes > quantidade 
GROUP BY p.nome 
HAVING SUM(c.quantidadeHabitantes) > quantidadeHabitantes

Âncora
Diferença Diferença

Fonte de dados VS Bloco de programação 

Existem algumas diferenças entre o assistente de consulta JPQL da fonte de dados e o do bloco de programação. 

Figura 5 - Consulta visual JPQL (Bloco de programação)

Figura 5.1 - Consulta visual JPQL (Fonte de dados) 

O assistente visual do bloco de programação possui duas funcionalidades a mais que o assistente visual da fonte de dados. São elas, Aplicar Dados da Requisição e Habilitar Paginação.

É possível aplicar todos os parâmetros de uma requisição a uma fonte de dados em uma consulta usada com a função Abrir Consulta.

O Cronapp permite uso de um bloco como fonte de dados. Quando usamos isso, na maioria das vezes, o intuito é usar uma consulta com a função Abrir Consulta. Nas versões anteriores existia uma opção chamada Paginação Automática. Essa opção aplicava os dados de paginação da requisição da fonte de dados à consulta. O Cronapp evoluiu essa opção que passou a se chamar Aplicar Dados da Requisição. Essa opção agora aplica paginação, ordenação, filtros e qualquer outro dado da requisição ODATA à consulta. Com isso, todas as operações de interface serão enviadas à consulta e buscas, ordenações etc passam a funcionar com Fonte de Dados com Bloco de Programação.

Figura 5.2 - Opção de habilitar paginação 

Blocos que utilizam o assistente visual JPQL

1. Bloco Inserir:

Bloco do tipo servidor que adiciona uma nova entidade ao banco de dados por meio do assistente visual do JPQL ( consulte mais detalhes aqui).

2. Bloco Abrir consulta:

Bloco do tipo servidor que realiza uma consulta no banco de dados por meio do assistente visual do JPQL (consulte mais detalhes aqui).

3. Bloco Abrir consulta:

Bloco do tipo servidor que atualiza uma entidade no banco de dados por meio do assistente visual JPQL (consulte mais detalhes aqui).

4. Bloco Abrir consulta:

Bloco do tipo servidor que remove uma entidade do banco de dados por meio do assistente visual JPQL (consulte mais detalhes aqui).