artigo SQL Magazine 04 - JAVA STORED PROCEDURES

Não se pode negar a influência que o Java tem exercido em todo o processo de desenvolvimento de software, ditando normas e padrões que estão sendo adotados por um número cada vez maior de empresas. O constante crescimento e evolução da linguagem, somado à sua característica multiplataforma e estrutura 100% orientada a objetos, tem feito muitos desenvolvedores mudarem seus paradigmas.

A Oracle evoluiu com essa tendência. A cada versão de seus produtos, a utilização do Java se torna mais significativa. Neste artigo veremos o uso da linguagem na implementação de stored procedures e functions, recurso disponível a partir da versão 8i.

A execução de código Java no Oracle é garantida pela Virtual Machine instalada junto com o banco de dados, denominada Aurora JVM. Ela possui customizações que tiram proveito dos recursos do servidor e otimizam os processos Java, sejam eles internos, incluídos em ferramentas Oracle ou implementados pelo desenvolvedor sob a forma de Java Stored Procedures.

Por definição, Java Stored Procedures são objetos do banco de dados que mapeiam métodos Java, publicando-os em um formato compatível com o SQL. Uma vez chamada pela aplicação, a stored procedure recebe os parâmetros e repassa para o método Java associado. Como a Aurora JVM é compatível com a Virtual Machine padrão, as classes podem ser escritas em qualquer IDE Java.

A Aurora JVM e seus componentes

A Java Virtual Machine do Oracle é um ambiente Java completo. Sua execução é feita no mesmo processo do Kernel do servidor, compartilhando a memória de processamento do banco e acessando diretamente os dados nele armazenados. A Aurora JVM disponibiliza um run-time environment completo para objetos Java, provendo suporte total a estruturas de dados, publicação de métodos, tratamento de erros, threads, garbage collector, além de reconhecer todas as bibliotecas de classes do core Java, como java.lang, java.io, java.net, java.math e java.util. Abaixo são destacados alguns componentes da Aurora JVM

• Library Manager - Responsável por armazenar e carregar o código Java para o banco de dados. Os objetos importados poderão ser manipulados somente pela Aurora JVM.
• Memory Manager - Responsável por gerenciar e otimizar o processo de garbage collection para objetos instanciados.
• Compiler - Compila automaticamente o arquivo de código fonte importado.
• Interpreter - Interpretador Java que executa as classes armazenadas no banco de dados.
• Class Loader - Localiza, carrega e inicializa as classes Java armazenadas no servidor, provendo a estrutura necessária para sua execução.
• Server-Side JDBC Driver - Driver JDBC desenvolvido e customizado para rodar dentro do servidor. É utilizado em conexões feitas a partir de Java Stored Procedures.

Carregando Métodos Java

Disponibilizar arquivos Java (.java, .class ou .jar) para serem utilizados pelo banco de dados consiste em importá-los para o Oracle, tornando-os disponíveis para execução. Esse procedimento pode ser efetuado com o utilitário de linha de comando loadjava. Após carregada, a classe fica disponível com o mesmo nome de origem. A sintaxe para execução do aplicativo é a seguinte:

loadjava {-user | -u} username/password[@database]  [-option_name -option_name ...] filename filename ...

Onde:

• -user username/password – informações de login com o banco, para a classe que será carregada.
• @database - alias de conexão com o Oracle, configurado no arquivo tnsnames.ora.

As principais opções da lista [-option_name -option_name ...] estão disponíveis na tabela 1.

Tabela 1 – Opções da linha de comando do loadjava

Como exemplo vamos importar o arquivo Formatacao.Java, mostrado na listagem 1. Essa classe possui um método para formatar um número de acordo com a quantidade de casas decimais indicadas. A linha de comando para carga do arquivo é a seguinte:

loadjava -user sqlmagazine/sqlm@sqlm -resolve -verbose formatacao.java

 01 import java.text.*;
 02 public class Formatacao
 03 { public static String formatar (double numero, int iNunCasasDecimais)
 04 { 
 05   NumberFormat nf = NumberFormat.getNumberInstance();
 06   nf.setMaximumFractionDigits (iNunCasasDecimais);
 07   nf.setMinimumFractionDigits (iNunCasasDecimais);
 08   nf.setMinimumIntegerDigits (1);
 09   String sFormatado = nf.format (numero);
 10   return sFormatado;
 11 }
 12 }

listagem 1 – arquivo Formatacao.Java

O comando mostrado na listagem 2 confirma se a classe foi importada para o banco de dados com sucesso. A tabela 2 exibe o resultado; repare que a coluna OBJECT_TYPE exibe os tipos de objeto criados - Java Source e Java Class. Este último foi automaticamente gerado pelo Oracle, no momento da importação do código fonte.

SELECT 
   OBJECT_TYPE, STATUS, OBJECT_NAME 
 FROM 
   USER_OBJECTS
 WHERE 
   OBJECT_TYPE LIKE 'JAVA%' AND UPPER(OBJECT_NAME) = ‘FORMATACAO’

Listagem 2 – Consultando os objetos java importados

OBJECT_TYPE STATUS OBJECT_NAME

------------------ ------- ------------

JAVA CLASS VALID Formatacao

JAVA SOURCE VALID Formatacao

Tabela 2 – Resultado da execução do comando da listagem 2

A exclusão dos objetos é efetuada pelo utilitário dropjava, através da sintaxe:

dropjava {-user | -u} username/password[@database]  [-option_name -option_name ...] filename filename ...

Os parâmetros de conexão são os mesmos de loadjava e a lista –option name inclui as opções oci8, schema, thin e verbose (tabela 1).

Caso sejam efetuadas alterações no código fonte já importado, é necessário carregar a classe novamente com o utilitário loadjava.

Criando Java Stored Procedures

Loadjava carrega as classes mas não publica seus métodos de forma automática. Aqui entra o papel das Java Stored Procedures: mapear um método de classe para um formato compatível com o SQL.

Este procedimento é efetuado pelos comandos CREATE FUNCTION ou CREATE PROCEDURE. A sintaxe geral para criação de Java Stored Procedures é a seguinte:

CREATE [OR REPLACE]
{  PROCEDURE procedure_name [(param[, param]...)]
 | FUNCTION function_name [(param[, param]...)] RETURN sql_type}
{IS | AS} LANGUAGE JAVA
NAME 'method_fullname (java_type_fullname[, java_type_fullname]...)
  [return java_type_fullname]';

Onde:

• procedure_name|function_name - Nome do objeto a ser criado;
• [(param[param]...)] - Lista de parâmetros (opcional);
• sql_type - Valor de retorno do objeto, no caso de uma Function;
• method_fullname - Nome completo do método Java a ser executado, incluindo o nome da classe e do package, caso exista;
• java_type_fullname - Tipo de dado Java que será mapeado para SQL.

A tabela 3 contém uma listagem simplificada de equivalência entre tipos SQL e Java.

Tabela 3 – Equivalência entre tipos de dados SQL e Java

O comando a seguir cria uma function utilizando a classe Formatacao importada no tópico anterior:

CREATE OR REPLACE
  FUNCTION Formatacao (Numero NUMBER, NumCasasDecimais Number) RETURN VarChar
AS LANGUAGE JAVA
NAME 'Formatacao.formatar (double, int) return java.lang.String';

Não esqueça que o Java faz distinção entre letras maiúsculas e minúsculas. Após a criação da function, o método estará disponível para acesso via qualquer frase SQL ou bloco PL/SQL. Por exemplo, a execução do comando:

‘SELECT FORMATACAO(1000.5454767, 3) FROM DUAL’ 

Retorna o valor ‘1000,545’, processado pelo método formatar, da classe Formatacao.

Conectando com Banco de Dados

Em uma classe Java, a conexão com um servidor de banco de dados pode ser efetuada através da API JDBC. O driver JDBC utilizado por uma Java Stored Procedure é do tipo Server-Side, ou seja, sua execução é interna ao servidor. O objeto de conexão é instanciado pelo comando abaixo:

Connection conn = new OracleDriver().defaultConnection();

NOTA: Para maiores informações sobre o driver Server-Side, leia a matéria “Conexão com Front-Ends Parte II”, publicada na SQL Magazine 2.

Não é necessário indicar a string de conexão com o banco, uma vez que esta é estabelecida de forma automática. Semelhante ao PL/SQL, a execução de métodos commit ou rollback não pode ser efetuada dentro da rotina, pois seu contexto transacional é o mesmo da seção na qual foi disparada.

Para exemplificar, criaremos uma tabela Cidades, contendo os campos código e nome. Uma classe com um método para inserir registros nessa tabela e uma Java Stored Procedure também serão construídas. Veja os comandos abaixo:

CREATE SEQUENCE SEQCIDADES 
    NOCACHE START WITH 1;
 
CREATE TABLE CIDADES 
(IDCIDADES NUMBER PRIMARY KEY NOT NULL, 
NOME VARCHAR2(60));

A listagem 4 mostra o código fonte da classe Java. Vejamos algumas considerações:

• A linha 7 armazena em uma String o comando SQL. O parâmetro representado pelo caracter “?”é preenchido na linha 11;
• A linha 9 estabelece a conexão com o servidor, através do driver JDBC Server-Side;
• A linha 10 compila o comando SQL no servidor, deixando-o em estado de espera. Essa técnica otimiza o processamento da instrução. O caracter “?” no comando SQL representa um parâmetro; o servidor ficará aguardando a passagem deste valor para iniciar a execução;
• A linha 11 atribui um valor para o parâmetro;
• A linha 12 efetiva a execução do comando SQL.

01 import java.sql.*;
b2 import java.io.*;
03 import oracle.jdbc.driver.*;
04 
05 public class Cidades {
06   public static void inserir (String nomeCidade) throws SQLException {
07      String sql = "INSERT INTO CIDADES (IDCIDADES, NOME) VALUES (SEQCIDADES.NEXTVAL,?)";
08      try {
09        Connection conn = new OracleDriver().defaultConnection();
10        PreparedStatement pstmt = conn.prepareStatement(sql);
11        pstmt.setString(1, nomeCidade);
12        pstmt.executeUpdate(); 
13        pstmt.close();
14      } catch (SQLException e) {System.err.println(e.getMessage());}
15    }   
16  }

listagem 4 – código fonte Java da Classe cidades

Faça a importação da classe Java com o comando abaixo:

-user sqlmagazine/sqlm@sqlm -resolve –verbose Cidades.Java. 

A stored procedure pode ser criada com o seguinte comando:

CREATE OR REPLACE
  Procedure InsereCidades (Nome Varchar)
AS LANGUAGE JAVA
NAME 'Cidades.inserir(java.lang.String)';

A procedure será executada através do comando execute InsereCidades('Rio de Janeiro’). Para verificar se o cadastro ocorreu com sucesso, utilize o comando SELECT * FROM CIDADES.

Conclusão

Vimos uma abordagem prática envolvendo Java Stored Procedures. Acompanhe as próximas edições da SQL Magazine para conferir novos artigos sobre o assunto.