JDBC avançado

JDBC
Tópicos avançados de acesso a base de dados
relacional
A gestão de instruções SQL em JDBC
1
:
proposta de uma arquitectura
para evitar a manipulação de código SQL embutido em código JAVA.
A arquitectura tem como objectivos proporcionar:
•
a modificação da string de uma query no momento da compilação, mas sem
recorrer à concatenação de strings;
•
a modificação de uma query sem modificar o código java;
•
a separação da camada de execução de instruções SQL do código Java da
aplicação.
As instruções SQL devem ser definidas num ficheiro de “propriedades” 2. A cada uma é
dado um nome adequado. O nome é a chave e a string (query) é o valor de uma
propriedade. A query pode conter variáveis “where”, como varNome no exemplo
seguinte:
# Sample ResourceBundle properties file
query1= SELECT * from pessoa
query2= SELECT * from pessoa where nome='Maria'
query3= SELECT * from pessoa where nome='varNome'
varNome será
substituída em run-time.
Do lado da aplicação Java, a estrutura de dados que guarda a atribuição de valores a
estas variáveis é uma Hashtable.
1
Baseado em [Chitaguppi,2005], disponível em http://java.about.com/library/weekly/uc_querycache1.htm
Ver em http://java.sun.com/docs/books/tutorial/essential/attributes/properties.html
2
A arquitectura é exemplificada através de 3 classes:
1. Teste_Acesso_BD.java - Classe para testar o acesso a uma BD relacional com
recurso a um ficheiro de texto do tipo '.properties' que guarda todos os comandos
SQL, o que permite separar realmente o código JAVA do código SQL. Esta
classe funciona com duas outras auxiliares, SQLCache.java e QueryResult.java.
2. SQLCache.java - Classe que obtêm instruções SQL guardadas num ficheiro de
propriedades e que as manipula, por exemplo para verificar a correcção de uma
query.
3. QueryResult.java - Classe auxiliar para tratar os resultados de uma query.
Guarda o resultado de um ResultSet numa tabela, representada por um Vector
que integra matrizes do tipo DataElement.
Teste_Acesso_BD.java
import java.sql.*;
import java.util.*;
/**
* Classe para testar o acesso a uma BD relacional
* com recurso a um ficheiro de texto do tipo '.properties'
* que guarda todos os comandos SQL, o que permite separar
* realmente o código JAVA do código SQL
*/
public class Teste_Acesso_BD {
Connection connect;
Statement stmt;
// instância de SQLCache guardará as strings que representam comandos SQL
SQLCache cache;
/**
* Construtor que carrega as intruções SQL do ficheiro de properties
* para o atributo 'cache' (instância de SQLCache)
*/
public Teste_Acesso_BD() {
cache = new SQLCache();
cache.loadSQLStatements();
}
/**
* Carrega o driver MySQL e abre a Connection em localhost, sem utilizar
password.
*/
public void carregaDriverEAbreConnection(){
try {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
ex.printStackTrace();
}
connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?"+
"user=root&password=password");
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
ex.printStackTrace();
}
}
/**
** Abre um Statement.
*/
public void abreStatement(){
try {
stmt = connect.createStatement();
} catch(Exception e){ e.printStackTrace(); }
}
/**
* Fecha um Statement.
*/
public void fechaStatement(){
try {
stmt.close();
} catch(Exception e){ e.printStackTrace(); }
}
/**
* Fecha uma Connection.
*/
public void fechaConnection(){
try {
connect.close();
} catch(Exception e){ e.printStackTrace(); }
}
/**
* Método que executa uma query com recurso às classes
* auxiliares SQLCache e QueryResult.
*/
public QueryResult executeSelect(
String qryName, Hashtable params) throws Exception {
// instância de QueryResult guardará o resultado da query
QueryResult result = null;
try {
String query = cache.compileQuery(qryName,params);
ResultSet rs = stmt.executeQuery(query);
result = new QueryResult();
result.populateData(rs);
} catch(Exception e){ e.printStackTrace(); }
return result;
}
/**
* O método main testa a execução de uma query com parâmetros.
*/
public static void main(String args[]){
}
}
Teste_Acesso_BD testeBD = new Teste_Acesso_BD();
testeBD.carregaDriverEAbreConnection();
testeBD.abreStatement();
Hashtable params = new Hashtable();
params.put("varNome", "Antonio");
String qryName = new String("query3");
QueryResult qr;
try {
qr = testeBD.executeSelect(qryName, params);
System.out.println(qr.tabela());
} catch(Exception ex){ ex.printStackTrace();}
testeBD.fechaStatement();
testeBD.fechaConnection();
/**
* O ficheiro sql.properties tem o seguinte conteúdo para teste: *
*
*
# Sample ResourceBundle properties file
*
query1= SELECT * from pessoa
*
query2= SELECT * from pessoa where nome='Maria'
*
query3= SELECT * from pessoa where nome='varNome'
*
* ( Para conhecer os ficheiros de propriedades ver em
* http://java.sun.com/docs/books/tutorial/i18n/resbundle/propfile.html )
*
*/
SQLCache.java
import java.util.*;
import java.io.*;
/**
* Classe que obtêm instruções SQL guardadas num ficheiro de propriedades
* e que as manipula, por exemplo para verificar a correcção de uma query.
*/
public class SQLCache {
/**
* As instruções SQL obtidas a partir do ficheiro ficarão guardadas
* num objecto do tipo Properties
* (ver http://java.sun.com/j2se/1.4.2/docs/api/java/util/Properties.html
*e
http://java.sun.com/docs/books/tutorial/essential/attributes/properties.html
)
*/
private Properties props = new Properties();
/**
* Nome do ficheiro de propriedades
*/
private static final String SQL_FILE = "..\\..\\..\\..\\sql.properties";
// várias hipóteses de acesso ao ficheiro sql.properties:
// quando se usa uma classe normal:
//private static final String SQL_FILE = "..\\..\\..\\..\
\sql.properties";
// quando se acede à base a partir de um servlet:
// 1ª hipótese:
//private static final String SQL_FILE = "..\\..\\sql.properties";
// e copiar à mão o ficheiro sql.properties para "C:\Program
Files\netbeans-5.5\enterprise3\apache-tomcat-5.5.17"
// 2ª hipótese:
// posicionar na raiz de C: desde "C:\Program
Files\netbeans-5.5\enterprise3\apache-tomcat-5.5.17\webapps"
// e depois percorrer o Path até ao ficheiro de propriedades
// private static final String SQL_FILE =
"..\\..\\..\\..\\..\\..\\Users\\Jorge\\Desktop\\ISCTE\\PRede\
\PRede_2007-08\\Testes\\TesteMySQL\\sql.properties";
/**
* Carrega instruções SQL a partir do ficheiro de propriedades e
guarda-as num
objecto Properties
*/
public void loadSQLStatements() {
try {
// carrega as instruções SQL a partir de um ficheiro de propriedades
InputStream in =
getClass().getClassLoader().getResourceAsStream(SQL_FILE);
System.out.println("in: " + in);
props.load(in);
System.out.println("props" + props);
in.close();
} catch(Exception e) {
e.printStackTrace();
}
}
/**
* Método chamado antes de executar uma query, para substituir
* as variáveis na query pelos parâmetros devidos.
* Os parâmetros estão guardados numa Hastable
* (ver http://java.sun.com/j2se/1.4.2/docs/api/java/util/Hashtable.html)
*/
public String compileQuery(String qryName, Hashtable params) throws
Exception {
String qry = getSQLStatement(qryName);
for(Enumeration enu = params.keys(); enu.hasMoreElements();) {
String key = (String) enu.nextElement();
String value = (String) params.get(key);
System.out.println(key);
qry = qry.replaceAll(key,value);
}
return qry;
}
}
/**
* Método que verifica se uma instrução SQL existe
*/
public String getSQLStatement(String sqlName) throws Exception {
String sqlStm = (String) props.get(sqlName);
if(sqlStm == null) {
throw new Exception("A instrução SQL não foi encontrada.");
} else {
return sqlStm;
}
}
QueryResult.java
import java.util.*;
import java.sql.*;
/**
* Classe auxiliar para tratar os resultados de uma query.
* Guarda o resultado de um ResultSet numa tabela,
* representada por um Vector que integra matrizes do tipo DataElement.
*/
public class QueryResult {
private Vector tabela = new Vector();
/**
* Classe que guarda o resultado de um ResultSet numa tabela,
* representada por um Vector que integra matrizes do tipo DataElement
*/
public void populateData(ResultSet rs) throws SQLException {
ResultSetMetaData metaData = rs.getMetaData();
int colCnt = metaData.getColumnCount();
while(rs.next()) {
DataElement[] obj = new DataElement[colCnt];
for(int i=0;i<colCnt;i++) {
DataElement element = new DataElement();
element.dataType = metaData.getColumnType(i+1);
element.dataTypeName = metaData.getColumnTypeName(i+1);
element.colName = metaData.getColumnName(i+1);
element.value = rs.getObject(i+1);
System.out.println(element.value);
obj[i] = element;
}
tabela.addElement(obj);
}
}
/**
* Inspector para a tabela dos dados retirados do ResultSet
*/
Vector tabela(){
return tabela;
}
/**
* Classe embutida que representa cada elemento que é retirado do
ResulSet,
* qualquer que seja o seu tipo
*/
private class DataElement {
int dataType;
String dataTypeName;
Object value;
String colName;
}
}