Desenvolvimento Código Aberto

Stored Procedures são procedimentos de código SQL armazenados no banco de dados, as stored procedures são mais rápidas do que o código tradicional, o que significa que elas estão se tornando cada vez mais populares. Com código da aplicação se mudando dos programas externos para o motor de banco de dados, Os DBAs precisam entender os requisitos de memória relacionados para procedimentos armazenados e saber como gerenciar estes procedimentos para o ideal desempenho do banco de dados. Isto muda de banco para banco, cada um deles possui suas recomendações de melhores praticas para atingir um melhor desempenho.

Neste site existe uma versão deste mesmo programa utilizando código SQL direto dentro da aplicação e podemos comparar a diferença com o código SQL sendo executado dentro do motor do banco de dados e o resultado é visivelmente notado, sem duvida o código executado utilizando procedures é mais rápido.

Ainda existe um outro modo de utilizar o banco de dados que é utilizando um set de dados persistente, onde uma camada  de classes ou componentes geram o código SQL para o banco de dados através de instruções definidas pelas classes, mas este método por possuir uma camada que cria o código para o banco pode se tornar menos atrativo em termos de desempenho, apesar de ser muito mais fácil e rápido para se desenvolver, sendo assim vale avaliar a quantidade de dados que sua aplicação pretende manipular.

Para adquirir o script SQL para criar a tabela deste exemplo ou visualizar o método utilizando SQL direto da aplicação: Clique Aqui.

Vantagens de utilizar Stored Procedures

Esta explicação é dirigida ao banco de dados Oracle, porem também se aplica aos bancos de dados DB2 e MSSQL, levando-se em conta a arquitetura do motor (engine) de cada um dos bancos de dados, para uma definição mais precisa consulte as especificações de cada um dos bancos de dados.

Melhor desempenho

Procedimentos armazenado Oracle carregam uma vez no pool comum e permanecem lá a menos que eles se tornem paginados. Execuções subsequentes do procedimento armazenado Oracle são muito mais rápidos do que as execuções de código externo.

Acoplamento de dados com comportamentos.

Os DBAs podem usar convenções de nomenclatura para tabelas relacionais pares com os comportamentos associados a uma tabela usando os procedimentos armazenados do Oracle como “métodos”.

Isolamento de código.

Uma vez que todos os códigos SQL forem movidos para fora dos programas externos e para dentro dos procedimentos armazenados do Oracle, os programas de aplicação tornam-se nada mais do que chamadas para procedimentos armazenados. Como tal, torna-se muito simples de trocar de um banco de dados para outro.

Exemplo:

Neste exemplo utilizamos Stored Procedures que retornam parâmetros para criar um método dentro do banco de dados ao invés de criar o método dentro da aplicação utilizando a linguagem de programação nativa. Deste modo podemos utilizar o mesmo programa sem alterar uma única linha e utiliza-lo em vários bancos de dados diferentes e ainda obter a melhor performance na execução de instruções SQL.

Obs: Para criar uma Stored Procedure utilizando o IBM Data Studio, você deve utilizar o editor de procedures e não o editor comum de SQL, pois encontrará um erro do caractere ponto e virgula no momento de escrever sua sintaxe.

Oracle

create or replace PROCEDURE buscaFuncionario
- Cria parametros
(pid_funcionario IN FUNCIONARIOS.ID_FUNCIONARIO%TYPE,
 p_nome OUT FUNCIONARIOS.NOME%TYPE,
 p_sobrenome OUT FUNCIONARIOS.SOBRENOME%TYPE,
 p_cargo OUT FUNCIONARIOS.CARGO%TYPE,
 p_salario OUT FUNCIONARIOS.SALARIO%TYPE
 )
AS
BEGIN
-- Insere dados da query dentro dos parametros de saida
  SELECT NOME, SOBRENOME, CARGO, SALARIO
  INTO p_nome, p_sobrenome, p_cargo, p_salario
  FROM FUNCIONARIOS
  WHERE ID_FUNCIONARIO = pid_funcionario;

END;

DB2

-- Cria procedure
CREATE PROCEDURE buscaFuncionario
( IN pid_funcionario INTEGER,
  OUT p_nome VARCHAR(30),
  OUT p_sobrenome VARCHAR(70),
  OUT p_cargo VARCHAR(30),
  OUT p_salario DECIMAL(9,2)
 )

P1: BEGIN

  SELECT NOME, SOBRENOME, CARGO, SALARIO
  INTO p_nome, p_sobrenome, p_cargo, p_salario
  FROM FUNCIONARIOS
  WHERE ID_FUNCIONARIO = pid_funcionario;

END P1

MSSQL

CREATE PROCEDURE buscaFuncionario
   @pfuncionario INT,
   @pnome		 NVarchar(30) OUTPUT,
   @psobrenome	 NVarchar(70) OUTPUT,
   @pcargo	     NVarchar(30) OUTPUT,
   @psalario	 Decimal(9,2) OUTPUT
AS
BEGIN
   SELECT @pnome = Nome,
		  @psobrenome = Sobrenome,
		  @pcargo = Cargo,
		  @psalario = Salario
   FROM Funcionarios
   WHERE ID_Funcionario = @pfuncionario
END

Java

import java.awt.ComponentOrientation;
import java.awt.Container;
import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;
import javax.swing.border.Border;
import javax.swing.border.EtchedBorder;

public class Campos implements ActionListener {
	// Declara componentes
	private JLabel ltitulo;
	private JLabel lid;
	private JLabel lpnome;
	private JLabel lsnome;
	private JLabel lcargo;
	private JLabel lsalario;
	private JLabel lpesquisa;

	private JTextField tid;
	private JTextField tpnome;
	private JTextField tsnome;
	private JTextField tcargo;
	private JTextField tsalario;
	private JTextField tpesquisa;

	private JButton botao;

	private Border borda;

	private JFrame menssagem;

	// Declara objetos de conexão
	private static Connection conn;
	private static Statement query;

	// Declara variaveis
	private static String bconexao;

	// Cria conexão
	public void conectar(String banco) {

		// Verifica strings de conexão

		// ORACLE
		if (banco == "oracle") {
			try {
				// Define Driver de conexão JDBC thin
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(
						"jdbc:oracle:thin:@localhost:1521:xe", "daberto",
						"p@55w0rd");

				// Executa pedido SQL
				query = conn.createStatement();

			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

		// DB2
		if (banco == "db2") {
			try {
				// Define Driver de conexão JDBC
				Class.forName("com.ibm.db2.jcc.DB2Driver");
				conn = DriverManager.getConnection(
						"jdbc:derby:net://localhost:50000/deva", "db2admin",
						"p@55w0rd");

				// Executa pedido SQL
				query = conn.createStatement();
				// JOptionPane.showMessageDialog(menssagem,
				// "Conexão Efetuada com sucesso!");
			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				// JOptionPane.showMessageDialog(menssagem, "Erro na conexão!");
				ex.printStackTrace();
			}

		}

		// MICROSOFT SQL SERVER
		if (banco == "mssql") {
			try {
				// Define Driver de conexão JDBC
				String URL = "jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=devaberto"
						+ ";user=devaberto;password=p@55w0rd";

				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				conn = DriverManager.getConnection(URL);

				// Executa pedido SQL
				query = conn.createStatement();

			}

			catch (ClassNotFoundException ex) {
				ex.printStackTrace();
			}

			catch (SQLException ex) {
				ex.printStackTrace();
			}

		}

	}

	public Container criaPainel() {
		// cria painel
		JPanel painel = new JPanel();
		painel.setLayout(new FlowLayout());
		painel.setBorder(BorderFactory.createEmptyBorder(20, 20, 20, 20));
		painel.setComponentOrientation(ComponentOrientation.RIGHT_TO_LEFT);
		// Cria titulo
		ltitulo = new JLabel("Database - Stored Procedures - Sem conexão");

		painel.add(ltitulo);
		painel.add(Box.createVerticalStrut(60));

		// Cria painel de usuario
		JPanel painelDados = new JPanel(new GridLayout(0, 2, 10, 10));

		// Cria componentes
		lid = new JLabel("Código:");
		lpnome = new JLabel("Primeiro Nome:");
		lsnome = new JLabel("Sobrenome:");
		lcargo = new JLabel("Cargo:");
		lsalario = new JLabel("Salário:");

		tid = new JTextField();
		tpnome = new JTextField();
		tsnome = new JTextField();
		tcargo = new JTextField();
		tsalario = new JTextField();

		tid.setPreferredSize(new Dimension(150, 20));
		// Adiciona componentes no painel
		painelDados.add(Box.createVerticalStrut(10));
		painelDados.add(Box.createVerticalStrut(10));
		painelDados.add(lid);
		painelDados.add(tid);
		painelDados.add(lpnome);
		painelDados.add(tpnome);
		painelDados.add(lsnome);
		painelDados.add(tsnome);
		painelDados.add(lcargo);
		painelDados.add(tcargo);
		painelDados.add(lsalario);
		painelDados.add(tsalario);
		painelDados.add(Box.createVerticalStrut(10));
		painelDados.add(Box.createVerticalStrut(10));

		// Cria painel de pesquisa
		JPanel painelPesquisa = new JPanel(new GridLayout(0, 3, 10, 10));
		borda = BorderFactory.createEtchedBorder(EtchedBorder.LOWERED);
		painelPesquisa.setBorder(borda);

		// Cria pesquisas
		lpesquisa = new JLabel("Pesquisa código:");
		tpesquisa = new JTextField();
		botao = new JButton("Pesquisar");
		botao.addActionListener(this);

		// Define foco do cursor no campo de pesquisa
		javax.swing.SwingUtilities.invokeLater(new Runnable() {
			public void run() {
				tpesquisa.requestFocus();
			}
		});

		// Adiciona compoentes ao painel de pesquisa
		painelPesquisa.add(lpesquisa);
		painelPesquisa.add(tpesquisa);
		painelPesquisa.add(botao);

		// Cria painel de pesquisa

		// Adiciona paineis
		painel.add(painelPesquisa);
		painel.add(painelDados);

		return painel;
	}

	// Clique do botão de pesquisa
	public void actionPerformed(ActionEvent arg0) {

		// Verifica pesquisa
		if (arg0.getSource() == botao) {

			// retorna result de dados
			CallableStatement proc = null;

			try {
				// Exibe a conexão utilizadda
				ltitulo.setText("Database - Stored Procedures - " + bconexao);

				// Prepara stored procedure
				proc = conn.prepareCall("{call buscaFuncionario(?,?,?,?,?)}");

				// Define parametro de entrada
				proc.setInt(1, Integer.parseInt(tpesquisa.getText()));

				// Registra parametros de saida
				proc.registerOutParameter(2, java.sql.Types.VARCHAR);
				proc.registerOutParameter(3, java.sql.Types.VARCHAR);
				proc.registerOutParameter(4, java.sql.Types.VARCHAR);
				proc.registerOutParameter(5, java.sql.Types.DECIMAL);

				// Executa stored procedure
				proc.execute();			

				// Preenche campos da tela

				tid.setText(tpesquisa.getText());
				tpnome.setText(proc.getString(2));
				tsnome.setText(proc.getString(3));
				tcargo.setText(proc.getString(4));
				tsalario.setText(proc.getString(5));				

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void criaGUI() {
		// Cria formulario
		JFrame formulario = new JFrame("Desenvolvimento Aberto");
		formulario.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		// cria painel de conteudo
		Campos acesso = new Campos();
		// conecta ao banco de dados defindo
		// mude a variavel bconexao para escolher o banco de dados
		// importe os drivers para o projeto
		// oracle = ORACLE
		// db2 = IBM DB2
		// mssql = MSSQL Server

		bconexao = "db2";
		acesso.conectar(bconexao);
		formulario.setContentPane(acesso.criaPainel());

		// Exibe o formulario
		formulario.setSize(400, 380);
		formulario.setVisible(true);

	}

	public static void main(String[] args) {

		javax.swing.SwingUtilities.invokeLater(new Runnable() {

			@Override
			public void run() {

				// Mostra GUI
				criaGUI();

			}
		});

	}

}