Banco de Dados Relacionais

Banco de Dados Relacionais
Tabelas
•
•
•
•
•
DB Relacionais são baseados em tabelas
As tabelas armazenam registros
Cada registro ocupa uma linha da tabela
Registros são dividos em campos
Cada campo armazena um e somente um
tipo de dado
Tabelas
• DB são baseados em tabelas
campo
Tabela “Cafés”
Tipo de Café
registro
ID_Vendedor
Preço
Vendas
Total
Colombiano
1001
1,35
0
0
Francês
201
1,38
0
0
Expresso
201
1,43
0
0
Brasileiro
1001
1,31
0
0
Descafeinado
201
1,33
0
0
Principais Operações
Realizadas nas Tabelas de
um Banco de Dados
Principais Operações
• Projeção sobre um ou mais campos
• Seleção de um ou mais registros
• Junção (join) de duas ou mais tabelas a
partir dos
valores de dois ou mais de seus campos
Seleção
• Selecione todos os registros cujo preço é maior que 1,34
na tabela Cafés
campo
Tabela “Cafés”
Tipo de Café
registro
ID_Vendedor
Preço
Vendas
Total
Colombiano
1001
1,35
0
0
Francês
201
1,38
0
0
Expresso
201
1,43
0
0
Brasileiro
1001
1,31
0
0
Descafeinado
201
1,33
0
0
Projeção
• Projete o campo Vendas sobre a tabela Cafés
campo
Tabela “Cafés”
Tipo de Café
ID_Vendedor
Preço
Vendas
Total
Colombiano
1001
1,35
0
0
Francês
201
1,38
0
0
Expresso
201
1,43
0
0
Brasileiro
1001
1,31
0
0
Descafeinado
201
1,33
0
0
Uma Linguagem Para
Trabalhar com BD
Relacionais
SQL
• A principal linguagem usada para trabalhar com banco de
dados é o SQL (Structured Query Language). SQL é
dividida em duas partes:
• Linguagem de Definição de Dados (DDL) – usado para
definição da estrutura do banco de dados, suas tabelas e
seus índices.
• Linguagem de Manipulação de Dados (DML) – usado
para operação do banco de dados, consultas, inserções e
remoções nos dados.
DDL
• Definindo a tabela “COFFEES”
CREATE TABLE COFFEES
(
COF_NAME VARCHAR(32),
SUP_ID
INTEGER,
PRICE
FLOAT,
SALES
INTEGER,
TOTAL
INTEGER
)
DML
• Fazendo um seleção e projeção na
tabela “Fornecedores”
– SELECT Fornecedor_ID, Nome
– FROM Fornecedores
– WHERE Cidade LIKE “Salvador”
JDBC
Java Database Connectivity
• JDBC é uma biblioteca Java de acesso universal a
banco de dados.
• Ela usa um protocolo padrão para acessar qualquer
base de dados.
• Os fabricantes de bases de dados são responsáveis
por disponibilizar os drivers de acesso as suas bases
de dados. Estes drivers são classes Java carregadas
em tempo de execução pelos programas que usam
JDBC.
• Exceto pelos drivers, todo o programa segue então
um padrão que pode ser aplicado sobre qualquer BD
que suporta JDBC
Para Usar JDBC
• Precisamos do Java – biblioteca java.sql.*
• Precisamos de um Banco de Dados – neste
curso usaremos o MS Access
• Precisamos de Driver JDBC para este banco de
dados – neste curso usaremos a ponte JDBCODBC que já vem com o Java
Carregando um Driver
O primeiro passo para seu programa usar o JDBC é
carregar um driver para o banco de dados. Isto é feito
com o comando:
Class.forName("jdbc.DriverXYZ");
Esta declaração carrega o driver e o torna disponível para
uso mais tarde quando formos abrir a conexão com o
servidor de banco de dados. Quando é carregado em
memória, o driver registra a se próprio com a classe
java.sql.DriverManager como um dos drivers de banco
de dados disponíveis. No nosso exemplo faremos:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Fazendo Uma Conexão
O próximo passo é efetivamente conectar com o banco
de dados. Isto é feito pelo comando:
Connection con = DriverManager.getConnection(
url, "myLogin", "myPassword");
A única parte difícil nesta declaração é a url . Ela vai
efetivamente definir a localização do banco de dados a
ser acessado usando um dado protocolo. O protocolo é
definido na documentação do fabricante do banco de
dados. No caso das pontes ODBC usa-se:
String url = "jdbc:odbc:MeuBanco";
Connection con = DriverManager.getConnection(
url, “Manoel", “fredflinstone");
O Driver Manager
A classe DriverManager gerencia todos os detalhes do
estabelecimento de uma conexão. A não ser que você va
escrever um driver você mesmo, você não precisa usar
nenhum dos métodos da interface Driver, e o único
método de DriverManager que você usará será
DriverManager.getConnection.
A conexão retornada por este método é um conexão para
o banco de dados pela qual você pode usar comando
JDBC para passar comandos SQL para o gerenciador de
banco de dados (DBMS). No nosso exemplo anterior, con
é esta conexão que foi aberta.
A Conexão
Comandos SQL e respostas
login:
manoel
password: fredflintone
Programa Java
DBMS
(base Aulas)
String url = "jdbc:odbc:MeuBanco";
Connection con = DriverManager.getConnection(
url,“Manoel",“fredflinstone");
Rodando
Comandos SQL com JBDC
Montando um Comando SQL
• JDBC “fala” com o banco de dados usando SQL. Desta
forma qualquer operação JDBC sobre o banco de dados
deve montar um comando SQL em DDL ou DML em uma
String, por exemplo:
• String query = "CREATE TABLE COFFEES
(COF_NAME VARCHAR(32), SUP_ID INTEGER,
PRICE FLOAT, SALES INTEGER, TOTAL
INTEGER)";
• Note que está é exatamente a declaração que nós vimos a
pouco. Ela cria a tabela COFFEES no seu banco de dados.
Executando um Comando SQL
• Após montado, o comando deve ser passado pelo JDBC ao
gerenciador do banco de dados, isto é feito em dois passos.
• Passo 1, obtém-se um objeto “declaração SQL” para a
conexão estabelecida:
– Statement stmt = con.createStatement();
• Passo 2, executa-se o comando SQL através deste objeto:
– stmt.executeUpdate("CREATE TABLE COFFEES " +
"(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE
FLOAT, " + "SALES INTEGER, TOTAL INTEGER)");
– ou
– stmt.executeUpdate(query);
O Método executeUpdate
• Usamos o método executeUpdate na transparência anterior
pois a declaração executada foi um declaração DDL.
Outras declaração nesta linha incluem alterar (alter) e
descartar (drop) tabela.
• O método executeUpdate é também usado para atualizar
(update) uma tabela, um comando DML. Na prática
executeUpdate é muito mais usado para atualizar tabelas
que para cria-las , pois normalmente se cria a tabela uma
vez e se atualiza ela muitas vezes. Isto é, comandos DDL
são muito mais infreqüentes que comandos DML.
Atualizando uma Tabela
• O exemplo abaixo mostra a atualização do
campo Cidade da tabela Fornecedores:
– Statement stmt = con.createStatement();
stmt.executeUpdate("UPDATE
Fornecedores " + "SET Cidade='São
Salvador'" +"WHERE Cidade LIKE
'Salvador'");
Valor retornado por
executeUpdate
• O método executeUpdate retorna um valor inteiro.
Este valor indica quantas linhas da tabela foi
alterada:
– int i = stmt.executeUpdate("UPDATE
Fornecedores " + "SET Cidade='São Salvador'" +
"WHERE Cidade LIKE 'Salvador'");
Inserindo Dados numa Tabela
• O exemplo abaixo mostra a inserção dos dados da tabela
COFFEES:
– Statement stmt = con.createStatement();
– stmt.executeUpdate("INSERT INTO COFFEES " +” VALUES
('Colombian', 101, 7.99, 0, 0)");
– stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES
('French_Roast', 49, 8.99, 0, 0)");
– stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES
('Espresso', 150, 9.99, 0, 0)");
– stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES
('Colombian_Decaf', 101, 8.99, 0, 0)");
– stmt.executeUpdate("INSERT INTO COFFEES " + "VALUES
('French_Roast_Decaf', 49, 9.99, 0, 0)");
Consultas ao
Banco de Dados
Consultas
• Consultas são de longe as operações mais executados em banco de
dados. Em SQL isso é executado através do comando Select. O Select
mais simples tem a seguinte forma: SELECT * FROM COFFEES
• O asterisco (*) indica que todas a colunas devem ser selecionadas. A
ausência do WHERE indica que todas as linhas da tabela devem ser
selecionadas. Desta forma a tabela inteira é retornada como resposta:
–
–
–
–
–
COF_NAME SUP_ID
--------------- -----Colombian 101
French_Roast
Espresso
150
PRICE
----7.99
49
SALES
----0
8.99
9.99
0
TOTAL
----0
0
0
0
Projeção
• O comando select permite a seleção das colunas desejadas
na(s) tabela(s) consultada(s), numa operação chamada de
“projeção”. Por exemplo, o comando SELECT
COF_NAME, PRICE FROM COFFEES retorna:
–
–
–
–
–
–
–
COF_NAME
-------Colombian
French_Roast
Espresso
Colombian_Decaf
French_Roast_Decaf
PRICE
----7.99
8.99
9.99
8.99
9.99
Seleção
• O comando select usa a cláusula
WHERE para selecionar as linhas
desejadas na(s) tabela(s) consultada(s).
Por exemplo, o comando:
– SELECT COF_NAME, PRICE
– FROM COFFEES
– WHERE PRICE < 9.00
Executando Consultas em
JDBC
• Consultas são executadas em JBDC através do
método executeQuery da classe Statement:
• Statement stmt = con.createStatement();
• ResultSet rs = stmt.executeQuery("SELECT
COF_NAME, PRICE FROM COFFEES");
• Note que as consultas retornam valores, muitas
vezes conjunto de dados enormes. Para receber os
valores retornados deve-se usar um objeto da
classe ResultSet.
ResultSet
• Objetos da classe ResultSet recebem os dados da tabela
linha a linha. Ele possui métodos para nominalmente pegar
os campos de dados de uma determinada linha (getXXX).
• O método next move o cursor para a próxima linha e
retorna verdadeiro. Inicialmente o cursor é posicionado
acima da primeira linha. As sucessivas invocações do
método next move o cursor para a linha de baixo até que se
alcance a última linha. Método next retorna falso quando o
cursor chega na ultima linha.
Exemplo de Uso de ResultSet
• Abaixo mostra-se uso dos métodos next() e getXXX() da
classe ResultSet:
– String query = "SELECT COF_NAME, PRICE FROM
COFFEES";
– ResultSet rs = stmt.executeQuery(query);
– while (rs.next()) {
– String s = rs.getString("COF_NAME");
– float n = rs.getFloat("PRICE");
– System.out.println(s + " " + n);
– }
• O resultado retornado é mostrado a seguir:
– Colombian 7.99
– French_Roast 8.99
Formas de Uso do getXXX()
• O JDBC oferece duas formas de identificar a
coluna a qual o método getXXX se refere. Uma
forma é dar o nome da coluna como visto na
transparência passada:
– String s = rs.getString("COF_NAME");
– float n = rs.getFloat("PRICE");
• A outra forma é dar o índice (número) da coluna
na tabela de resultado:
– String s = rs.getString(1);
– float n = rs.getFloat(2);
Declarações Preparadas a
Priori
Declarações Preparadas a
Priori
• Quando o mesmo tipo de declaração tem que ser
executada muitas vezes é mais eficiente usar a
classe PreparedStatement para fazer isso.
• PreparedStatement é uma subclasse de Statement.
• Objetos desta classe permitem a pré-definição de
uma declaração SQL que depois pode ser chamada
várias vezes com diferentes parâmetros (valores).
Criando um
PreparedStatement
• Como nos objetos Statement, você cria objetos
PreparedStatement usando objetos da classe Connection.
• Usando o mesmo objeto “con” dos exemplos
anteriores,nós poderíamos preparar o seguinte
PreparedStatement:
– PreparedStatement updateSales =
con.prepareStatement("UPDATE COFFEES SET SALES =
? WHERE COF_NAME LIKE ?");
• Este objeto deverá receber dois parâmetros de entradana
hora de execução. Este parâmetros são indicados ao
DBMS pêlos sinais de interrogação.
Executando o
PreparedStatement
• Os parâmetros são preparados com métodos setXXX() e
executados com os métodos executeUpdate e/ou
executeQuerry():
– PreparedStatement updateSales =
con.prepareStatement("UPDATE COFFEES SET SALES =
? WHERE COF_NAME LIKE ?");
– updateSales.setInt(1, 75);
– updateSales.setString(2, "Colombian");
– updateSales.executeUpdate();
• Este comando é equivalente a:
– String updateString = "UPDATE COFFEES SET SALES =
75 " +"WHERE COF_NAME LIKE 'Colombian'";
– stmt.executeUpdate(updateString);
Vantagens do
PreparedStatement
• A principal característica de um objeto da classe
PreparedStatement é que, ao contrário de objetos da classes
Statement, ele recebe uma declaração SQL na hora de sua
de criação.
• Isto permite que a declaração seja enviada ao DBMS para
ser compilada na hora de sua declaração. Como resultado,
objetos PreparedStatement contêm declarações précompiladas.
• Quando um PreparedStatement é executado, o DBMS pode
executar seu comando SQL imediatamente sem ter que
compila-lo primeiro.
PreparedStatement em Loops
• PreparedStatement é tipicamente usando em loops, veja o exemplo
abaixo:
– PreparedStatement updateSales;
– String updateString = "update COFFEES " +"set SALES = ? where
COF_NAME like ?";
– // envia comando a DBMS que o pré-compila para execução
– updateSales=con.prepareStatement(updateString);
– // cria lista com dados
– int [] salesForWeek = {175, 150, 60, 155, 90};
– String [] coffees = {"Colombian", "French_Roast", "Espresso",
– "Colombian_Decaf", "French_Roast_Decaf"};
– int len = coffees.length;
– // executa declaração para todos os dados da lista
– for(int i = 0; i < len; i++) {
– updateSales.setInt(1, salesForWeek[i]);
– updateSales.setString(2, coffees[i]);
– updateSales.executeUpdate();
– }