artigo SQL Magazine 04 - JAVA STORED PROCEDURES

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Neste artigo veremos o uso da linguagem na implementação de stored procedures e functions, recurso disponível a partir da versão 8i.

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.

Andresolve

Compila os arquivos e ‘resolve’ todas as suas dependências, como se estivesse sendo carregado

Debug


Gera informações de debug. Esta opção é equivalente a ‘javac –g’.

Force


Força a carga de um arquivo de classe Java (.class) independente se ele foi carregado ou não em uma etapa anterior. Por default, o arquivo de classe carregado anteriormente é rejeitado.

oci8


Direciona a execução do loadjava para conexão com o servidor de banco de dados utilizando o OCI JDBC driver. Esta opção e –thin são mutuamente exclusivas.

resolve


Após todos os arquivos de classe especificados na linha de comando serem carregados e compilados, o ‘resolve’ todas as referências/dependências externas presentes nas classes. Se esta opção não for especificada, os arquivos carregados não serão compilados.

schema


Associa o objeto carregado a outro usuário/owner do banco. É necessário que o usuário logado possua o privilegio CREATE ANY PROCEDURE.

synonym


Cria sinônimos públicos para as classes carregadas, permitindo que elas sejam acessadas por outro esquema de usuário. É necessário que o usuário possua o privilégio CREATE PUBLIC SYNONYM para executar esta opção.

Thin

Direciona a execução do loadjava para conexão com o servidor de banco de dados através do thin JDBC driver. Esta opção e -oci8 (que é a default) são mutuamente exclusivas. Se utilizada, a string representada por @database no parâmetro user deve apontar não mais para o alias TNS, e sim conter a seguinte formatação: @host:port:sid, onde host é o nome do servidor, port é a porta do listner e sid é o identificador da instância para conexão.

verbose


Habilita a exibição das mensagens de progresso do processamento.

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.

SQL

Java

CHAR, NCHAR, LONG, VARCHAR2, NVARCHAR2

oracle.sql.CHAR
java.lang.String
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.lang.Byte
java.lang.Short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double
java.math.BigDecimal
byte, short, int, long, float, double

DATE

oracle.sql.DATE
java.sql.Date
java.sql.Time
java.sql.Timestamp
java.lang.String

NUMBER

oracle.sql.NUMBER
java.lang.Byte
java.lang.Short
java.lang.Integer
java.lang.Long
java.lang.Float
java.lang.Double
java.math.BigDecimal
byte, short, int, long, float, double

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.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?