Integrando Java com Banco de Dados Relacional (JDBC)
Programação 3: Orientação a Objetos e Java
Interface Java-Banco de Dados
Tabela: ResultSet
interface ResultSet {
boolean next() throws SQLException;
double getDouble(int col) throws SQLException;
double getDouble(String col) throws SQLException;
int getInt(String col) throws SQLException;
...
void close() throws SQLException;
String getCursorName() throws SQLException;
ResultSetMetaData getMetaData() throws SQLException;
}
Comandos SQL: Statement
interface Statement {
ResultSet executeQuery(String sql) throws SQLException;
int executeUpdate(String sql) throws SQLException;
void close() throws SQLException;
boolean execute(String sql) throws SQLException;
boolean getMoreResults() throws SQLException;
ResultSet getResultSet() throws SQLException;
void setCursorName(String name) throws SQLException;
}
Sessão com SGBD: Connection
interface Connection {
Statement createStatement() throws SQLException;
boolean isClosed() throws SQLException;
void close() throws SQLException;
PreparedStatement prepareStatement(String sql) throws SQLException;
CallableStatement prepareCall(String sql) throws SQLException
DatabaseMetaData getMetaData() throws SQLException;
void commit() throws SQLException;
void rollback() throws SQLException;
void setAutoCommit(boolean autoCommit) throws SQLException;
}
Abrindo uma Sessão
Connection con;
Class.forName("connect.microsoft.MicrosoftDriver");
con = DriverManager.getConnection( "jdbc:ff-microsoft://150.161.2.118:1433/if101", "login", "passwd");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection( "jdbc:odbc:di2-if101", "login", "passwd");
Banco SQL
import java.sql.*;
class BancoSQL {
private Connection con;
private Statement stmt;
private ResultSet rset;
void cadastro(int num, double saldo) throws SQLException {
stmt.executeUpdate("INSERT INTO CONTAS VALUES ( " + num + " , " + saldo + ")");
}
void debito(int num, double val) throws SQLException {
stmt.executeUpdate("UPDATE CONTAS SET saldo = saldo - " + val + " WHERE numero=" + num);
}
double saldo(int num) throws CNEException, SQLException {
rset = stmt.executeQuery("SELECT * FROM CONTAS WHERE numero=" + num);
if (rset.next()) return rset.getDouble("saldo");
else throw new CNEException();
}
double saldo_total() throws SQLException {
rset = stmt.executeQuery("SELECT SUM(saldo) FROM CONTAS");
if (rset.next()) return rset.getDouble(1);
else return 0;
}
BancoSQL() {
try {
Class.forName("connect.microsoft.MicrosoftDriver");
con = DriverManager.getConnection("jdbc:ff-microsoft:" + "//150.161.2.118:1433/if101", "phmb", "phmb");
stmt = con.createStatement();
}
catch(SQLException e) {
System.out.println(e.getMessage());
}
}
Banco Persistente
import java.sql.*;
class BancoPersistente extends Persistente {
void cadastro(int num, double saldo) throws SQLException {
Conta c = new Conta(num,saldo);
c.armazena();
}
void debito(int num, double val) throws SQLException {
Conta c = new Conta();
c.recupera(num);
c.credito(val);
c.armazena();
}
double saldo(int num) throws CNEException, SQLException {
Conta c = new Conta();
c.recupera(num);
if (c.numero() == num)
return c.saldo();
else throw new CNEException();
}
double num_contas() throws SQLException {
int num = 0;
Conta c;
c = new Conta();
c.reset();
while ( !c.eoi() ) {
num = num + 1;
c.next();
}
return num;
}
}
Banco Genérico
class BancoGenerico {
private DepositoDeContas contas;
void cadastro(Conta c) throws CEException {
if (contas.procura(c.numero()) == null)
contas.insere(c);
else throw new CEException();
}
void debito(int num, double val) throws CNEException {
Conta c = contas.procura(num);
if (c == null) throw new CNEException();
else c.debito(val);
}
double saldo(int num) throws CNEException {
Conta c = contas.procura(num);
if (c == null) throw new CNEException();
else return c.saldo();
}
int num_clientes() {
int num = 0; Conta c;
contas.reset();
while ( !contas.eoi() ) {
num = num + 1;
c = contas.next();
}
return num;
}
BancoGenerico (DepositoDeContas dep) {
contas = dep;
}
}
Depósito de Contas
interface DepositoDeContas {
void insere(Conta c);
Conta procura(int n);
void reset();
Conta next();
boolean eoi();
}
Depósito de Contas com Arrays
class DCArray implements DepositoDeContas {
private Conta[] contas;
private int rd, wr;
void insere(Conta c) {
contas[wr] = c;
wr = (wr + 1) % 1000;
}
Conta procura(int num) {
int i;
Conta c = null;
for (i = 0; i < 1000 ; ++i) {
if (contas[i] != null)
if (contas[i].numero() == num)
c = contas[i];
}
return c;
}
void reset() {
rd = 0;
}
Conta next() {
Conta c = contas[rd];
if (c != null) rd = (rd + 1) % 1000;
return c;
}
boolean eoi() {
return ((rd == 1000) || (contas[rd] == null));
}
DCArray () {
rd = 0; wr = 0;
contas = new Conta[1000];
}
}
Depósito de Contas com BD
import java.sql.*;
class DCArrayDB implements DepositoDeContas {
private static final int tamCache = 1000;
private Conta[] contas;
private int rd, wr;
private Connection con;
private Statement stmt;
private ResultSet rset;
void insere(Conta c) {
this.insertSQL(c);
this.updateSQL(contas[wr]);
contas[wr] = c;
wr = (wr + 1) % tamCache;
}
Conta procura(int num) {
int i;
Conta c = null;
for (i = 0; i < tamCache ; ++i) {
if (contas[i] != null)
if (contas[i].numero() == num) c = contas[i];
}
if (c == null) {
c = this.selectSQL(num);
this.updateSQL(contas[wr]);
contas[wr] = c;
wr = (wr + 1) % tamCache;
}
return c;
}
private void updateSQL (Conta c) {
if (c != null) {
try {
stmt.executeUpdate("UPDATE CONTAS SET saldo = " + c.saldo() + " WHERE numero = " + c.numero());
}
catch (SQLException e) {
System.out.println("Erro => updateSQL() : "+e);
}
}
}
private void insertSQL (Conta c) {
if (c != null) {
try {
stmt.executeUpdate("INSERT INTO CONTAS VALUES (" + c.numero() + ", " + c.saldo() + ")");
}
catch (SQLException e) {
System.out.println("Erro => insertSQL+e);
}
}
}
private Conta selectSQL (int num) {
try {
ResultSet rs = stmt.executeQuery("SELECT * FROM CONTAS WHERE numero = " + num);
if (rs.next()) {
return new Conta(rs.getInt("numero"),
rs.getDouble("saldo"));
}
else return null;
}
catch (SQLException e) {
System.out.println("Erro => selectSQL() : "+e);
}
}
DCArrayDB () {
rd = 0;
wr = 0;
contas = new Conta[tamCache];
try {
Class.forName("connect.microsoft.MicrosoftDriver");
con = DriverManager.getConnection("jdbc:ff-microsoft://150.161.2.118:1433/if101", "phmb", " phmb");
stmt = con.createStatement();
this.reset();
}
catch(SQLException e) {System.out.println(e);}
}
DCArrayDB () {
rd = 0;
wr = 0;
contas = new Conta[tamCache];
try {
Class.forName("connect.microsoft.MicrosoftDriver");
con = DriverManager.getConnection("jdbc:ff-microsoft://150.161.2.118:1433/if101", "phmb", " phmb");
stmt = con.createStatement();
this.reset();
}
catch(SQLException e) {System.out.println(e);}
}
private void loadarray() {
int j, i;
for (i = 0; i < tamCache; i++) updateSQL(contas[i]);
i = 0;
rset = stmt.executeQuery("SELECT * FROM CONTAS");
while ( (i < tamCache) && (rset.next()) ) {
contas[i] = new Conta(rset.getInt("numero"),
rset.getDouble("saldo"));
i = i + 1;
}
if (i == tamCache || i == 0) wr = 0;
else wr = i-1;
for (j = i; j < tamCache; j++) contas[j] = null;
}
}
GYPIS
 |
Paulo Borba(phmb@di.ufpe.br) |