Por padrão, o Cronapp utiliza o JPQL para realizar consultas ao banco de dados. Porém, agora também é possível utilizar consultas SQL nativas, possibilitando o uso de diversos recursos não suportados pelo JPQL. O SQL Nativo está disponível apenas em Fontes de dados e, após configurada, poderá ser utiliza utilizada como uma fonte de dados comum: gerando CRUD, serviço REST, paginação, ordenação, filtro de consultas e todos os demais recursos da Fonte de dados.

Limitações

Como se trata de um novo recurso novo dentro da plataforma, existem algumas limitações que já estão mapeadas em nosso roadmap e serão tratadas em um futuro próximo.

• O recurso está disponível apenas para as Fontes de dados.
• Irá funcionar com praticamente todos os recursos da Fonte de dados, com exceção da aba "Tratamento de Erro". 
• É suportado para a maioria dos bancos de dados aceitos na plataforma Cronapp: PostgreSQL, MySQL, H2, SQL Server, Firebird e Oracle.
• Ainda será possível utilizar o Assistente de consulta visual (gerando SQL nativo), porém consultas mais complexas será necessário conhecimento de SQL e do banco de dados utilizado.
• Alguns bancos de dados suportam o uso de caracteres especiais em nomes de tabelas ou campos, por exemplo, porém nem todos os caracteres especiais são suportados pela funcionalidade SQL Nativo do Cronapp.
• A Fonte de dados do tipo SQL Nativo necessita estar vinculada a um namespace, e por isso, será necessário criar um diagrama de dados com a conexão de banco desejada e gerar a camada de persistência. Não é necessário que o diagrama ou namespace possuam classes.
• Observações sobre instruções SQL:O "*" (asterisco) não deve ser utilizado para representar todos os campos (exemplo:  "select * from <tabela>"), sendo necessário informar todos os campos da consulta. O Assistente de consulta visual possui recursos para listar os campos de forma automática, facilitando consultas em tabelas com muitos campos.
  
  • A instrução "LIMIT" não é suportada.
  • Expressões nos campos, como subqueries ou "CASE WHEN", necessitam sempre de um alias.
  • É necessário que a Fonte de dados possua ao menos um campo de chave primária (PK). Caso a consulta não retorne campos com chaves primárias, acesse a aba Campos da Fonte de dados e defina um campo como chave primária ou adicione um.

Pré-requisito

Antes de começar a seguir os passos do tutorial é preciso ter certeza de que se tem um ambiente minimamente preparado para reproduzir o exemplo. Abaixo estão os requisitos principais.

Requisitos:

• Projeto criado. Caso haja dúvidas de como criar, acesse o link criar projeto;

Passos

Passos apresentados nesse tutorial:

• Passo 1: Criar uma conexão de banco de dados.
• Passo 2: Criar um diagrama, vincular com a conexão de banco de dados e gerar a camada de persistência.
• Passo 3: Criar uma Fonte de dados do tipo SQL Nativo.

Conexão com o banco de dados

Iremos criar uma conexão com o banco de dados, caso já possua isso configurado em seu projeto, avance para o próximo tópico.

Acesse a funcionalidade conexão com o Banco de dados do Cronapp (destaque 1 da figura 1) e selecione o botão Novo para abrir a janela de configuração (destaque 2).

Figura 1 - Criando uma conexão com o banco de dados

Se o seu banco está em nosso Cloud Services, selecione-o no campo Banco de dados na Nuvem (destaque 1 da figura 1.1) para que todos os campos sejam automaticamente preenchidos, caso contrário, informe manualmente cada campo e clique em OK (2).

Figura 1.1 - Criando uma conexão com o banco de dados

Salve a aba de Banco de dados ao final.

Diagrama de dados

Nesse momento é necessário vincular a conexão do Banco de dados com um Diagrama de dados e gerar a camada de persistência. Esse procedimento é necessário, pois, ao gerar a camada de persistência o Cronapp também gera outras configurações necessárias para que o sistema consiga se comunicar com o banco de dados.

Após configurar o diagrama será possível:criado, o Diagrama pode ser utilizado de 3 formas diferentes:

• Manter Não relacionar classes ao Diagrama de dados, deixar o diagrama vazio (sem classes) e realizar apenas consultas SQL nativa a partir da Fonte de dadosutilizá-lo apenas para conseguir executar as consultas SQL nativas, como veremos nesse tutorial.
• Realizar a engenharia reversa do banco de dados para criar a camada de persistências com as classes obtidas, podendo utilizar consulta padrão (JPQL) ou SQL Nativa.
• Criar novas classes para gerar novas entidades e tabelas no banco de dados, além das existentes, podendo utilizara consulta padrão (JPQL) nas classes criadas e SQL Nativo nas demais tabelas do banco.

Acesse o menu de contexto do diretório Diagramas de Dados (Localização: Diagramas de Dados/  

) e selecione Novo > Diagrama de Dados, informe um nome para o novo diagrama e clique em OK (Figura 2) para abrir a ferramenta.

Figura 2 - Criando um Diagrama de dados

No campo do Banco da Dados (destaque 1 da figura 2.1), selecione o banco configurado no passo anterior. Em seguida, clique no botão Gerar Camada de Persistência (destaque 2) para abrir a janela de configuração.

Por fim, clique em Gerar para criar a camada de persistência (Figura 2.1).

Figura 2.1 - Vinculando um banco e gerando a camada de persistência

Fonte de dados com SQL Nativo

As Fontes Fonte de dados do tipo SQL Nativo possuem um ícone diferente para se diferenciar dos demais (destaques 1 da figura 3), destacando dos demais tipos. Para criarmos a . Esse ícone só será exibido após criarmos uma Fonte de dados desse tipo, assim, clique no ícone Fonte de dados (destaque 2) e na janela com a lista das Fontes de dados, clique em Nova fonte de dados (3).

Figura 3 - Criando uma Fonte de dados

Seleção do namespace

Preencha os os campos Identificador e Nome da consulta com base na função desejada para a Fonte de dados. Em seguida, selecione a opção SQL Nativo no campo que define o tipo da Fonte de dados (destaque 1 da figura 3.1), clique no botão "..." (2) para abrir a janela de seleção de namespaces e selecione a opção que geramos no Diagrama de dados (3), clique em OK ao final. 

Por fim, na aba Filtro da Fonte de dados, clique no botão para abrir o Assistente de consulta visual (destaque 4 da figura 3.1).

Figura 3.1 - Configurando a Fonte de dados para permitir consultas em SQL Nativo

Assistente de Consulta

O Assistente de consultas do Cronapp pode ser utilizado para criar consultas simples de forma visual ou consultas complexas em SQL Nativo. Também é possível iniciar a criação de uma consulta de forma visual e depois alterar para textual, porém o processo inverso não é permitido.

Algumas observações ao utilizar o Assistente de consultas com SQL Nativo:

• O campo Entidades (destaque 1 da figura 3.2) não estará relacionado com as classes Java, mas com as tabelas contidas no Schema selecionado no Banco de dados.
• Por motivo de simplificação, se o alias possuir o mesmo nome da tabela, ele será ignorado na consulta SQL.
• Não será possível selecionar um campo que retorne todos os atributos da entidade, como ocorre na consulta JPQL ou o "*" (asterisco) em consultas nativas (exemplo: "select * from <tabela>"). Dessa forma, é obrigatório informar todos os campos da consulta.
  • Utilize o botão "+ Adicionar Todos" (destaque 3 da figura 3.2) para exibir todos os campos das Tabelas selecionadas e depois exclua apenas os campos desnecessários.

Figura 3.2 - Criando uma consulta simples com o Assistente de consultas

Com o Assistente aberto, siga os passos abaixo.

1. Na área das Entidades (destaque 1 da figura 3.2), clique em "..." e selecione uma tabela e , depois clique em "+ Adicionar", sendo possível vincular várias tabelas à consulta.
2. Na área dos campos (destaque 2), utilize o botão "+ Adicionar Todos" (destaque 3) para facilitar a listagem dos campos.
3. Clique em Editar como Texto para alterar do modo visual para o editor de comandos SQL.

Na figura 3.3, vemos as instruções SQL obtidas do assistente visual e, logo abaixo, acrescentamos mais algumas instruções manualmente, incluindo subquery, filtro e ordenação.

Figura 3.3 - Incluindo uma instrução SQL

Clique em OK para finalizar o Assistente de consulta e retornar as configurações da Fonte de dados.

Aba Filtro da Fonte de dados

A aba Filtro irá exibir a consulta obtida do assistente (destaque 1 da figura 3.4), porém esse campo não é editável, caso queira modificar a consulta, acesse novamente o assistente. Logo abaixo, no destaque 2, é possível visualizar e configurar todos os filtros dinâmicos passados na consulta.

Figura 3.4 - Aba Filtro da Fonte de dados exibindo a consulta e seus parâmetros

Aba Campos da Fonte de dados

Ao acessar a aba Campos, podemos ver que o Cronapp obteve todos os campos utilizados na consulta e os configurou automaticamente na Fonte de dados, definido seus tipos, se permitem nulo e chave primária (Figura 3.5).

Os nomes dos campos da Fonte de dados serão exibidos no formato "<nome_campo>" para as colunas selecionada da tabela principal ou "<tabela ou alias>_<nome_campo>" para os campos das demais tabelas e subqueries.

O botão com o ícone de engrenagem (destaque 1) permite acessar as expressões usadas nos campos, como subqueries ou "CASE WHEN", caso o campo não possua expressão, exibirá apenas o nome do campo no formato: <tabela><tabela ou alias>.<nome_campo>.

Figura 3.5 - Aba Campos da Fonte de dados

Por fim, salve a Fonte de dados. Ela será exibida na lista das Fontes de dados (destaques 1 da Figura 3).

Exemplo de uso

No exemplo abaixo, após selecionar um dos sobrenome contido na base de dados, a Fonte de dados, que alimenta a Grade, filtra todos os atores com o mesmo sobrenome e informar o número total de filmes feitos por que cada um atuou.

Figura 3.6 - Grade sendo alimentada pela Fonte de dados desenvolvida nesse tutorial