Como Criar Relatórios usando Stored Procedures do Oracle no iReports
O uso de PL/SQL na geração de dados e preenchimento de relatórios é uma prática muito utilizada (dos projetos que participei envolvendo relatórios mais de 90% utilizam procedures). É muito comum ver alguns desenvolvedores desistirem de usar o JasperReports, pois o trabalho de converter as algumas regras de negócio existentes nas procedures para consultas SQL costuma ser dispendioso e alguns casos dependendo da complexidade até impossíveis.
Recentemente, para o alívio dos desenvolvedores de relatório, um subprojeto da comunidade de desenvolvedores do JasperForge foi criado para permitir a execução de Stored Procedures do Oracle no JasperReports. Portanto, agora com algumas modificações na ferramenta iReports é possível a execução de procedures em relatórios do JasperReports.
O principal objetivo deste artigo é explicar como preparar a ferramenta iReports, para que você comece a criar relatórios que usam o poder das PL/SQL do Oracle, e além disto, usar a facilidade que a ferramenta fornece em recuperar campos das consultas de um banco de dados.
Ferramentas Necessárias:
Para começar a trabalhar você deve certificar de ter as seguintes ferramentas e arquivos:
- JDK 5.0.x – http://java.sun.com
- iReport 1.2.6 - http://www.jasperforge.org/
- Driver de conexão JDBC para Oracle para Java 5 o ojdbc14.jar
- Arquivo plsql-ireport1.2.6.zip disponível para download no começo deste artigo. Contém algumas classes alteradas do iReport para executar as PL/SQLs e alguns arquivos do projeto OracleStoredProcedures já compilados.
Se você não possui a J2SE instalada, instale-a (a versão JDK) e configure a variável de ambiente do sistema operacional JAVA_HOME com o diretório de instalação da JDK (ex. C:\Sun\Java\jdk1.5.0_08). Configure também a variável de ambiente PATH para executar os programas dentro de JAVA_HOME/bin.
Instale o iReport 1.2.6 de preferência dentro de uma pasta na raiz do sistema operacional para fácilitar o acesso. Este diretório de instalação chamaremos de IREPORT_HOME. Copie o driver JDBC para dentro de IREPORT_HOME/lib e descompacte o arquivo "plsql-ireport1.2.6.zip" também dentro deste mesmo diretório.
O arquivo "plsql-ireport1.2.6.zip" é composto do arquivo "plsqlexecuter.jar" que contém as classes para executar PL/SQL no JasperReports e uma pasta com nome "it" que é a pasta raiz do pacote de classes do iReport e que será utilizada como um patch para atualizar uma parte do programa iReport permitindo executar procedures dentro dele e recuperar os dados dos cursores das stores procedures.
Para aplicar o patch na ferramenta iReports abra uma tela de comando e dentro do diretório IREPORT_HOME/lib execute o comando abaixo:
jar –uvf ireport.jar it
O comando acima irá atualizar algumas classes do pacote ireport.jar com as classes que fornecidas no arquivo "plsql-ireport1.2.6.zip". Após a execução do comando apague a pasta it dentro do diretório lib.
Criando um relatório com procedure
A seguir temos em exemplo de código de procedure a ser usada em um relatório. Repare que a procedure tem dois parâmetros, um parâmetro do tipo VARCHAR2 e um parâmetro de retorno do tipo SYS_REFCURSOR (Referência para Cursor).
PROCEDURE PROC_USUARIO
( p_nuusuario IN varchar2,
p_cursor OUT sys_refcursor)
IS
BEGIN
OPEN p_cursor FOR
SELECT * FROM USUARIO
WHERE
nuusuario = p_nuusuario;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END; -- Procedure
A Referência de um Cursor nada mais é que uma referência para um conjunto retornado de consultas no banco de dados. Ele é equivalente ao java.sql.ResultSet do java, ou seja, um conjunto de tuplas retornados da consulta do banco através do JDBC.
Vamos criar um relatório que execute esta procedure. Para isto abra a ferramenta iReports e crie um novo relatório clicando no menu “Arquivo » Novo documento...”. Na tela de propriedades do relatório digite as informações do relatório (nome, layout da página) e clique no botão “OK”.
Agora vamos criar os parâmetros a serem usados na consulta do relatório que no caso da procedure do exemplo são dois. Para criar um parâmetro clique com o direito do mouse na pasta Parâmetros da paleta lib e selecione "adicionar » Parameter...".
O primeiro parâmetro por ser do tipo VARCHAR2 criei um parâmetro com nome de nuusuario e tipo java.lang.String. Selecione a caixa “Is for prompting”, isso faz com que quando executar o relatório a ferramenta abra uma janela para colocar o valor desejado para o parâmetro.
O segundo parâmetro é um Cursor e deve ser criado com o tipo java.sql.ResultSet e a com a caixa “Is for prompting” deve ficar necessariamente em branco:
Com os parâmetros criados vamos agora criar uma conexão com o banco para poder executar a Query. Para isto clique no menu “Data » Conexões / Fonte de Dados...”. Na janela que irá abrir clique no botão “New” pra criar uma nova conexão.
Preencha os dados da conexão como na figura a seguir trocando aonde for necessário para refletir as informações de seu banco de dados. Após preencher todos os dados clique no botão “Test” para testar suas configurações. Se você não conseguir conectar com sucesso, confira os dados preenchidos. Se conseguir conectar clique no botão “Save” para salvar sua configuração.
Ao voltar para tela de conexões, irá aparecer a configuração que foi criada. Selecione ela e clique no botão “Set as Default” para usá-la como a conexão padrão na ferramenta iReport. Feche a tela de conexões.
Criando a consulta
A próxima tarefa é criar a query para o relatório. Faça isto clicando no menu “Data » Query do Relatório...”.
Na tela de query do relatório selecione no combo “Query Language” a opção PLSQL, e digite o texto da query no espaço em branco usando a sintaxe abaixo e modificando a mesma de acordo com quantidade de parâmetros da procedure que você estará usando.
{call sua_procedure($P{param1}, ..., $P{paramN}..., $P{cursor})}
Acima o parâmetro $P{cursor} deverá ocorrer apenas uma vez na query. E ele poderá estar antes, depois ou no meio dos paramentos passados. Fique atento para colocar o parâmetro com o cursor (ResultSet) na mesma posição do cursor da procedure do Oracle:
Feito isto clique no botão "Read Fields" para a ferramenta buscar os campos retornados no cursor.
Atenção, a opção "Read Fields" só irá funcionar se seu banco de dados estiver com uma conexão com o Oracle configurada e ativada.
Depois de recuperar os campos da consulta certifique que os campos que você irá usar foram retornados com o tipo de dados adequado.
Por exemplo, campos do tipo oracle.sql.BLOB que contém imagens pode ser retornados usando o tipo java.io.InputStream e depois ser encaixado como uma figura no relatório. E campos numéricos que forem recuperados como java.math.BigDecimal podem em alguns casos ser transformados em tipos numéricos mais simples do Java tais como java.lang.Integer ou java.lang.Double, que no caso dependerá do tamanho e quantidades de casas decimais do NUMBER na tabela consultada no Oracle.
Para alterar o tipo de retorno de um campo clique com o direito em cima do campo localizado na paleta lib e na pasta Campos. Na tela de edição do campo defina o novo retorno em “Field class type”.
Veja abaixo um exemplo de relatório usando os campos da consulta exemplo e logo mais abaixo o relatório impresso.
Para executar o relatório clique em “Criar (build) » Executar Relatório (usar conexão ativa)”
Conclusão
Espero que este artigo possa ajudar muitas pessoas. É possível que muitos desenvolvedores que abandonaram o JasperReports voltarão a pensar nele como alternativa para desenvolver relatórios com procedures. E quando usarem ela, irão descobrir por que é uma das ferramentas para impressão de relatórios em Java mais utilizadas.
