Acesso a Dados usando Java JDBC

Sistemas de Bases de Dados,
Dados 7.1
Acesso a Dados usando Java
João Correia Lopes
http://www.fe.up.pt/jlopes/
[email protected]
JDBC
SQLJ
Exemplo
baseado em:
Client/Server Data Access with Java and XML, Dan Chang & Dan Harkey, Wiley, 1998
JDBC
Tipos de drivers
API
Transacções
Excepções
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.2
3
JDBC
? Java API para SQL
– chamadas de baixo nível
– SQL dinâmico
? Implementação pode usar ODBC
– drivers fáceis de obter
? Conforme com SQL-92
? Tipagem forte e estática (se possível)
? API simples
– regra 80%-20%
– 1. comandos SQL s/ parâmetros
– 2. comandos SQL com parâmetros IN, OUT, INOUT
– 3. acesso a meta-informação
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
4
Arquitectura
Java Application/Applet
JDBC API (JDK: java.sql)
JDBC Driver
Manager
JDBC driver API
(JDK: sun.jdbc.odbc)
JDBC-ODBC
Bridge Driver
JDBC
Driver
JDBC-Net
Driver
ODBC Driver
proprietary protocol
JDBC middleware protocol
BD
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.3
5
Tipos de Drivers
? Tipo 1: JDBC-ODBC bridge
– depende do driver ODBC para aceder aos dados
– métodos nativos Java escritos em C
– não portável, não utilizável por applets
? Tipo 2: Native API, partly Java
– acedem directamente à Base de dados
– métodos nativos Java escritos em C
? Tipo 3: Net-protocol, all Java
– usam protocolo de rede independente da base de dados
– métodos em Java puro
? Tipo 4: Native-protocol, all Java
– acedem directamente à base de dados
– métodos em Java puro
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
6
Utilização: cenário 1 (cliente/servidor)
Java Application
JDBC
DriverManager
cliente
JDBC Application
Driver (T1-T4)
BD
servidor
servidor de base de dados
BD
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.4
7
Utilização: cenário 2 (2-tier)
Java Applet
JDBC
DriverManager
cliente
JDBC Applet
Driver (T3, T4)
Intranet/Internet
servidor
servidor de base de dados
servidor Web
BD
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
8
Utilização: cenário 3 (2-tier+)
cliente
servidor
Java Applet
Intranet/Internet
(RMI, Java ORB)
JDBC
DriverManager
JDBC Application
Driver (T1-T4)
servidor de base de dados
servidor Web
BD
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.5
9
Utilização: cenário 4 (3-tier)
cliente
servidor de aplicação
servidor Web
Java Application/Applet
Intranet/Internet
Java Business Objects
JDBC
DriverManager
JDBC Application
Driver (T1-T4)
Intranet
servidor de base de dados
BD
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
10
Tipos
Tipo de dados SQL
Tipo de dados Java
TINYINT
SMALLINT
INTEGER
BIGINT
REAL
DOUBLE
DECIMAL(P,S)
NUMERIC(P,S)
CHAR(N)
VARCHAR(N)
BIT
BINARY(N)
DATE
TIME
TIMESTAMP
byte ou Integer
short ou Integer
int ou Integer
long ou Long
float ou Float
double ou Double
java.math.BigDecimal
java.math.BigDecimal
String
String
boolean ou Boolean
byte[]
java.sql.Date
java.sql.Time
java.sql.Timestamp
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.6
11
API
DriverManager
getConnection(url)
Connection
Connection
createStatement()
Statement
Statement
executeQuery(sql)
ResultSet
getXXX(col)
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
user = readEntry ("user: ");
password = readEntry ("password: ");
db = "jdbc:oracle:thin:@blaster.fe.up.pt:1521:DEV";
Connection conn = DriverManager.getConnection (db, user, password);
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("select ... from ...");
System.out.println(rset.getInt(2));
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
12
Transacções
? Sessão fica por omissão em auto-commit
? Este comportamento pode ser alterado com:
Connection.setAutoCommit(false);
? Nessa altura é necessário:
Connection.commit();
Connection.rollback();
? Pode ser escolhido o nível de concorrência
Connection.setTransactionIsolation(TRANSACTION_NONE);
Connection.setTransactionIsolation(TRANSACTION_READ_UNCOMMITED);
Connection.setTransactionIsolation(TRANSACTION_READ_COMMITED);
Connection.setTransactionIsolation(TRANSACTION_REPEATABLE_READ);
Connection.setTransactionIsolation(TRANSACTION_SERIALIZABLE);
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.7
13
execute()
// may return 1 or more resultSet, 1 or more updateCount, or a combination
stmt.execute(queryString);
while (true) {
int rowCount = stmt.getUpdateCount();
if (rowCount > 0) {
// update
System.out.println("Rows changed = " + rowCount);
stmt.getMoreResults();
continue;
}
if (rowCount = 0) {
// DDL or 0 update
System.out.println("DDL or no rows updated." );
stmt.getMoreResults();
continue;
}
// rowCount = -1; result set or no more results
ResultSet rs = stmt.getResultSet();
if (rs !=null) {
...
while (rs.next) {
// process result
...
}
stmt.getMoreResults();
continue;
}
break;
// no more results
}
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
14
Classe SQLException
? Objectos desta classe contêm:
– uma string descrevendo o erro
– SQLState string para X/Open
– um inteiro que é específico do vendedor
? Estende a interface Exception
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.8
SQLJ
SQL embebido em Java
Resultados e Iteradores
16
SQL Embebido
? A execução directa de interrogações por um interpretador é rara na prática
? Mais frequentemente haverá um programa numa linguagem convencional (host
language) e parte desse programa são instruções SQL
linguagem hospedeira
+
SQL embebido
Pre-processador
linguagem hospedeira
+
chamadas a funções
compilador da
ling. hospedeira
biblioteca SQL
programa na
linguagem hospedeira
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.9
17
Elementos de SQL Embebido
? Comandos SQL executáveis (SQLJ)
– manipular meta-informação: CREATE, ALTER, DROP
– manipular dados: SELECT, INSERT, UPDATE, DELETE; OPEN, FETCH, CLOSE
– manipular transacções: SET TRANSACTION; COMMIT, ROLLBACK
? SQL dinâmico (JDBC)
– preparar e executar comandos SQL em runtime: PREPARE, DESCRIBE, EXECUTE;
DEALLOCATE, GET DESCRIPTOR, SET DESCRIPTOR
? Declarações (SQLJ)
– definir variáveis e cursores: BEGIN DECLARE SECTION, END ..., DECLARE CURSOR
? Comandos de controlo
– manipular sessões SQL: SET AUTHORIZATION, SET SESSION; ligações: CONNECT,
DISCONNECT; excepções: WHENEVER; e diagnósticos: GET DIAGNOSTICS
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
18
Arquitectura
fonte SQLJ
código fonte Java c/ SQL embebido
Tradutor de SQLJ
fonte Java
código fonte Java c/ chamadas a JDBC
Compilador Java
executável java
bytecodes Java c/ chamadas a JDBC
JDBC Driver
base de dados
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.10
19
Características de SQLJ
? Standard para SQL embebido em Java
? Conforme com SQL-92 (SQL-3 no futuro)
? Verificação de tipos (estática)
? Verificação do esquema
? Optimização do SQL
? Consistência com Java
? Componentes de software
? Transparência à localização (em clientes ou servidores)
? Portabilidade
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
20
Variáveis e cláusulas
? Variáveis Java são usadas para passar dados de Java para SQL
SELECT nome, cidade from Edificio WHERE :ano > ano_construcao;
SELECT nome, ano_construcao INTO :n, :a FROM Edificio
WHERE nome="Torre dos Clérigos";
? Cláusulas SQL são iniciadas por #sql
#sql { DELETE FROM Edificio };
? Cláusulas SQLJ executam no contexto de uma ligação
– implicitamente
DefaultContext.setDefaultContext(ctx);
– explicitamente:
#sql (con) { DELETE FROM Edificio };
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.11
21
Resultados e Iteradores
? bind-by-name
// declaracao
#sql public iterator ByName (String name, int year);
// uso numa interrogacao
{
ByName nameIter;
#sql nameIter = { SELECT nome, ano_construcao FROM Edificio};
while(nameIter.next()) {
System.out.println(nameIter.name() + "construída a " + nameIter.year());
}
}
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
22
Resultados e Iteradores
? bind-by-position
// declaração
#sql public iterator ByPosition (String, int);
// uso numa interrogação
{
ByPosition posIter;
String name;
int year;
#sql posIter = { SELECT nome, ano_construcao FROM Edificio};
while(true) {
#sql { FETCH :posIter INTO :name, :year };
if ( posIter.endFetch()) break;
System.out.println(name + "construida a " + year);
}
}
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.12
Exemplo
Arquitectura 3-camadas
HTML
Servlet
24
Arquitectura
cliente Web
servidor Web
página HTML
Intranet/Internet
Java Servlets
JDBC
DriverManager
JDBC Application
Driver (T1-T4)
Intranet
servidor de base de dados
BD
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.13
25
Ficheiro HTML
#
# SelectApplet.html
#
<html>
<form method="post“ action="http://woodview2.stl.ibm.com:8080/servlet/SelectServlet">
ArcWorld building query:<p>
Enter the data source, user id, and password information:<br>
<input size=20 name="DATABASE"> <input size=20 name="LOGIN">
<input size=20 type="PASSWORD" name='PASSWORD'><br>
<br>
Select the city that you would like to query:<br>
<input type="radio" name="CITY" value="*"> All cities <br>
<input type="radio" name="CITY" value="Washington"> Washington<br>
<input type="radio" name="CITY" value="Paris"> Paris <br>
<input type="radio" name="CITY" value="Beijing"> Beijing <p>
<input type="SUBMIT"> <input type="RESET">
</form>
</html>
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
26
Classe SelectServlet
/ * SelectServlet.java
*/
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.io.*;
import java.util.*;
public
class SelectServlet extends HttpServlet {
public void doPost (HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException
{
res.setContentType("text/html");
ServletOutputStream out = res.getOutputStream();
out.println("<html>");
out.println("<head><title>Select Servlet</title></head>");
out.println("<body>")
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01
Sistemas de Bases de Dados,
Dados 7.14
27
Classe SelectServlet...
String database = req.getParameter("DATABASE");
String login = req.getParameter("LOGIN");
String password = req.getParameter("PASSWORD");
String city = req.getParameter("CITY");
String where;
if (city.equals("*")) where = "";
else where = " WHERE city = " + "'" + city + "'";
String sql = "SELECT name, type, city FROM Building" + where;
try {
// Load the JDBC-ODBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:" + database;
Connection con = DriverManager.getConnection(url, login, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
out.println("<table border='border'>");
out.println("<caption> Buildings in the " + "ArcWorld database: </caption>");
Acesso a Dados Relacionais com Java
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
v 1.4, Abril de 2001
28
Classe SelectServlet...
ResultSetMetaData rsmd = rs.getMetaData();
String n1 = rsmd.getColumnName(1);
String n2 = rsmd.getColumnName(2);
String n3 = rsmd.getColumnName(3);
out.println("<tr> <th>" + n1 + " <th>" + n2 + " <th>" + n3);
while(rs.next()) {
String v1 = rs.getString(1); String v2 = rs.getString(2);
String v3 = rs.getString(3);
out.println("<tr> <td>" + v1 +" <td>" + v2 + " <td>" + v3);
}
out.println("</table>");
stmt.close();
con.close();
} catch( Exception e ) {
e.printStackTrace();
}
out.println("</body></html>");
}
}
public String getServletInfo() {
return "A servlet that performs Building queries on the " + "ArcWorld database";
}
João Correia Lopes
Faculdade de Engenharia da Universidade do Porto
Acesso a Dados Relacionais com Java
v 1.4, Abril de 2001
FEUP, João Correia Lopes, 2000/01