UNIVERSIDADE FEDERAL DO PARANÁ CURSO: Ciência da Computação PERÍODO: 4o. DISCIPLINA: Técnicas Alternativas de Programação DATA: ____/____/ 2013 PROFESSOR: Andrey AULA: 08 APRESENTAÇÃO Na aula de hoje vamos apresentar e discutir o acesso à bases de dados via JDBC. Criando classes que acessam bases de dados via JDBC DESENVOLVIMENTO Um programa em Java pode ler, inserir, remover e atualizar informações que estão armazenadas em um Sistema Gerenciador de Banco de Dados (SGBD). Estes SGBD são sistemas como Oracle, Informix, MS Sql Server, Mysql. Este acesso é feito pelo JDBC(Java Database Connectivity). É uma interface de programação de aplicações (API) desenvolvida pela Sun que permite a um programa acessar um SGBD. A JDBC facilita a conexão com um banco de dados, o envio de instruções SQL e a obtenção de resultados. Conforme a figura acima, o aplicativo Java invoca as classes da JDBC que por sua vez realiza a comunicação com o driver do SGBC que acessa efetivamente a base de dados. Portanto, diferentes programas SGBD podem ter drivers distintos. A API JDBC e o gerenciador JDBC protegem o aplicativo da implementação de SGDB, ou seja, os drivers de JDBC fazem o trabalho de nível inferior de conexão e a comunicação com um SGBD específico. O Pacote java.sql É uma API para acesso a base de dados usando a linguagem Java. Esta API inclui um sistema segundo o qual drivers diferentes podem ser instalados dinamicamente para acessar fontes de dados diferentes. O pacote java.sql contém diversas classes para manipulação e controle de banco de dados, as principais são: -Classe DriverManager: Utilizada para realizar a conexão com um driver. -Interface Connection: Contém métodos para criação de instruções e manipulação de conexões e suas propriedades. -Statement: Usado para enviar comandos básicos do SQL Página 1/10 UNIVERSIDADE FEDERAL DO PARANÁ -PreparedStatement: Usado para enviar comandos preparados ou básicos do SQL. -ResultSet Para receber e atualizar os dados de uma query. Para carregar o driver dinamicamente, basta uma linha de código. Por exemplo, para utilizar a ponte JDBC-ODBC utilize a seguinte instrução: Class.forName("org.gjt.mm.mysql.Driver"); Para realizar a conexão basta agora a criação de uma instância da classe Connection indicando a URL para conexão: String URL = "jdbc:mysql://apocalipse.spet.br:3306/basepc10"; conn = Drivermanager.getConnection(URL,username, password); Onde username- Nome do usuário para Login password - Senha. Exemplo de consulta a base de dados: import java.io.*; import java.sql.*; public class Acesso { public static void main(String args [])throws IOException { Statement statement=null; Connection conn = null; ResultSet resultSet; String URL = "jdbc:mysql://apocalipse.spet.br:3306/basepc10"; String username = ""; String password = ""; BufferedReader teclado = new BufferedReader(new InputStreamReader(System.in)); System.out.println("digite o nome"); String nome = teclado.readLine(); try{ // carrega o driver Class.forName("org.gjt.mm.mysql.Driver"); // faz a conexão co a base de dados conn = DriverManager.getConnection(URL,username, password); // consegue o objeto statement statement = conn.createStatement(); // faz a consulta String query = "SELECT matricula FROM alunos WHERE nome="+nome; resultSet = statement.executeQuery(query); while (resultSet.next()) { System.out.println("matricula do "+nome+" e "+ resultSet.getString(1)); } //fecha a conexão statement.close(); conn.close(); }catch(SQLException sqlex) { System.out.println("Erro na consulta "+ sqlex); }catch(ClassNotFoundException cnfex) { System.out.println("Erro no Driver "+ cnfex); } } } Página 2/10 UNIVERSIDADE FEDERAL DO PARANÁ pode-se fazer tambem fazer inserts e updates usando os metodos da classe Statement: boolean execute(String query) e int executeUpdate(String query) Criando classes que acessam bases de dados via JDBC No desenvolvimento de sistemas orientados a objetos cada classe terá as suas responsabilidades. Algumas classes serão responsáveis por receber os dados digitados pelo usuário e mostrar os resultados, enquanto outras terão a responsabilidade de manter os dados dos seus objetos atualizados no banco de dados. Nas aulas anteriores, nós vimos que estas classes são as classes do modelo (MVC), também chamadas de classes entidade ou até classes persistentes. O principal objetivo de se ter estas classes no seu sistema é deixar o acesso à base de dados restrito à apenas algumas classes. Isto permite que qualquer alteração na base tenha pouca consequencia no restante do sistema. Normalmente, cada uma destas classes está intimamente relacionada a uma tabela da base, por ex.: class Cliente.getBairro { private String nome; private String endereco; private String telefone; private String bairro; } create table cliente ( int id; varchar(80) nome; varchar(160) endereco; varchar(20) telefone; varchar(80) bairro) No exemplo acima vemos que a tabela cliente possui campos que correspondem aos atributos da classe cliente. Vamos criar uma classe Cliente com os métodos de acesso. public class Cliente { private String nome; private String endereco; private String telefone; private String bairro; public public public public public public public public String getNome() { return nome;} void setNome(String nome) { this.nome = nome; } String getEndereco() { return endereco; } void setEndereco(String endereco) { this.endereco = endereco; } String getTelefone() { return telefone; } void setTelefone(String telefone) { this.telefone = telefone; } String getBairro() { return bairro; } void setBairro(String bairro) { this.bairro = bairro; } } Página 3/10 UNIVERSIDADE FEDERAL DO PARANÁ Então, para que um objeto da classe Cliente tenha seus dados gravados na base na base é necessário criar uma classe Repositorio com um método que faça uma inserção na tabela cliente salvando cada atributo da classe. Por exemplo: import java.sql.*; public class Repositorio { public static void insereClienteBD(Cliente cli) { String query = "INSERT INTO clientes (nome, endereco, telefone, bairro) " +"VALUES ('"+cli.getNome()+"', '"+cli.getEndereco()+"', '" +cli.getTelefone()+"', '"+cli.getBairro()+"')"; BD banco = new BD(); banco.conecta(); banco.insere(query); banco.desconecta(); } Para recuperar os atributos do objeto da base, é necessário que o id do objeto na base (ou a chave primária) seja conhecido. Então é feito um select na base recuperando os dados para o id desejado e atribuir cada valor recuperado ao atributo correspondente da base. Por exemplo: public static Cliente recuperaClienteBD(int id) { Cliente cli = new Cliente(); try{ String query = "SELECT nome, endereco, telefone, bairro FROM " + "clientes WHERE id="+id+" "; BD banco = new BD(); banco.conecta(); ResultSet res = banco.consulta(query); while(res.next()) { cli.setNome(res.getString(1)); cli.setEndereco(res.getString(2)); cli.setTelefone(res.getString(3)); cli.setBairro(res.getString(4)); } banco.desconecta(); }catch(SQLException sqlex){ System.out.println("erro na consulta "+sqlex); } return cli; } } Para isso iremos precisar de uma classe BD com um método insere(). Esta classe irá fazer a conexão com a base e executar a query. import java.io.*; import java.sql.*; public class BD { private Statement stm = null; private Connection conn = null; Página 4/10 UNIVERSIDADE FEDERAL DO PARANÁ private private private private private ResultSet res = null; String driver= "com.mysql.jdbc.Driver"; String URL = "jdbc:mysql://localhost:3306/pc10"; String username = "andrey"; String password = ""; public void conecta() { try{ Class.forName(driver); conn = DriverManager.getConnection(URL,username, password); }catch(SQLException sqlex){ System.out.println("Erro na conexão "+ sqlex); }catch(ClassNotFoundException cnfex){ System.out.println("não carregou o driver "+ cnfex); } } public void desconecta() { try{ conn.close(); }catch(SQLException sqlex){ System.out.println("Erro na conexão "+ sqlex); } } public void insere(String query) { try{ stm = conn.createStatement(); stm.execute(query); stm.close(); }catch(SQLException sqlex){ System.out.println("Erro na consulta "+ sqlex); } } public ResultSet consulta(String query) { try{ stm = conn.createStatement(); res = stm.executeQuery(query); }catch(SQLException sqlex){ System.out.println("Erro na consulta "+ sqlex); } return res; } } Classes acessando bases de dados - Classe Vector, Interface Iterator, Classes ResultSet e PreparedStatement. Para saber trazer todos os identificadores da tabela cliente: Página 5/10 UNIVERSIDADE FEDERAL DO PARANÁ public Vector recuperaTodosIdBD() { Vector todosIds = new Vector(); String query = "SELECT id FROM cliente "; ResultSet resultSet = BD.consulta(query); while (resultSet.next()) { todosIds.addElement(new Integer(resultSet.getInt(1))); } return todosIds; } Como nâo é fácil saber o número de registros que serão recuperados da tabela, existe a necessidade de usarmos uma estrutura de tamanho flexível para armazená-los. Esta estrutura é um Vector (java.util.Vector). Um Vector é uma coleção de objetos, indexados, que pode ter um número variável de objetos. Este número pode inclusive aumentar ou diminuir conforme a necessidade durante a execução do programa. Os principais métodos da classe Vector são: void add(int index, ObjectInsere o element na posição index no Vector. element) boolean add(Object o) Insere o objeto no final do Vector. void addElement(Object obj) Insere o objeto no final do Vector aumentando em 1 o seu tamanho. Object elementAt(int index) Retorna o componente da posição especificada. Object get(int index) Retorna o componente da posição especificada. int indexOf(Object elem) Procura a primeira ocorrencia do objeto no Vector. boolean isEmpty() Testa se o Vector não tem componentes. Object remove(int index) Remove o elemento da posição especificada pelo índice no Vector. boolean remove(Object o) Remove a primeira ocorrência do elemento no Vector. int size() Retorna o número de elementos do Vector. Para acessar os elemento de um Vector podemos usar a interface Iterator. Veja o seguinte código: Vector todos = cliente.recuperaTodosIdBd(conn) Iterator itr = todos.iterator(); while (itr.hasNext()) { System.out.println(itr.next()); } Página 6/10 UNIVERSIDADE FEDERAL DO PARANÁ A classe ResultSet implementa um conjunto de métodos para recuperarmos os dados de uma linha (registro) de uma consulta através de comandos SQL. Além dos apresentados abaixo, há outros métodos para outros tipos de dados: getInt(int coluna) getLong(int coluna) getFloat(int coluna) getDouble(int coluna) getString(int coluna) getDate(int coluna) getTime(int coluna) Obtém o valor de uma coluna na linha corrente como um Java int Obtém o valor de uma coluna na linha corrente como um Java long Obtém o valor de uma coluna na linha corrente como um Java float Obtém o valor de uma coluna na linha corrente como um Java double Obtém o valor de uma coluna na linha corrente como um Java String Obtém o valor de uma coluna na linha corrente como um objeto java.sql.Date Obtém o valor de uma coluna na linha corrente como um objeto java.sql.Time Ao recuperar campos de banco de dados, um nulo (null) é um valor válido. Por exemplo, se o campo for um número inteiro, um nulo fará com que o método getInt retorne um valor zero. Para testarmos se o retorno é nulo, chamaremos o método wasNull da classe ResultSet imediatamente após chamar o método getInt. Deve-se ter atenção com as conversões na obtenção das informações no banco de dados. A tabela abaixo mostra a equivalência entre os tipos SQL e Java. Desta forma, utilizaremos o método correto para leitura da base de dados: Tipo SQL CHAR VARCHAR LONGVARCHAR BIN TINYINT SMALLINT INTEGER BIGINT DOUBLE DATE Tipo Java String String String boolean Byte Short Int long double java.sql.Date Método getString() getString() getString() getBoolean() getByte() getShort() getInt() getLong() getDouble() getDate() A classe PreparedStatement é utilizada quando o mesmo comando SQL é executado diversas vezes, somente alterando o conteúdo de parâmetros. Por exemplo, para atualizar as notas de alunos do banco de dados o que irá variar é o valor da nota e o código do aluno, mas o comando é idêntico. Para executar esta atualização de um modo mais eficiente, utilizamos o PreparedStatement: Veja o exemplo: Connection com = DriverManager.getConnection(localBD); String sql = "UPDATE aluno SET nota = ? WHERE codigo = ?"; PreparedStatement ps = con.preparedStatement(sql); ps.setInt(1, 10); ps.setInt(2, 1001); /* Atualiza o aluno 1001 com a nota 10 */ int result = ps.executeUpdate(); ps.setInt(1, 9); ps.setInt(2, 1002); Página 7/10 UNIVERSIDADE FEDERAL DO PARANÁ /* Atualiza o aluno 1002 com a nota 9 */ int result = ps.executeUpdate(); ps.setNull(1, Types.FLOAT); ps.setInt(2, 1002); /* Atualiza o aluno 1002 com a nota 9 */ int result = ps.executeUpdate(); ps.close(); Assim como temos métodos para recuperar campos de um ResultSet, temos também métodos para atualizar os parâmetros necessários para executar um comando SQL através da classe PreparedStatement: setInt (int parameterIndex, int x) setLong (int parameterIndex, long x) setFloat (int parameterIndex, float x) setDouble (int parameterIndex, double x) setString (int parameterIndex, String x) setDate (int parameterIndex, Date x) setTime (int parameterIndex, Time x) setNull(int paramIndex, int sqlType) Coloca um nulo no parâmetro indicado pela posição. O segundo parâmetro refere-se ao tipo de dado SQL que receberá o conteúdo Null. Os possíveis valores são descritos na classe java.sql.Types. Para saber trazer todos os registros da tabela cliente é necessário um método da classe Repositório que faça a consulta e recupere esses registros em um Vector: public static Vector recuperaTodosClientesBD() { Vector todos = new Vector(); try{ String query = "SELECT nome, endereco, telefone, bairro "+ "FROM clientes "; BD banco = new BD(); banco.conecta(); ResultSet res = banco.consulta(query); while(res.next()) { Cliente cli = new Cliente(); cli.setNome(res.getString(1)); cli.setEndereco(res.getString(2)); cli.setTelefone(res.getString(3)); cli.setBairro(res.getString(4)); todos.addElement(cli); } banco.desconecta(); }catch(SQLException sqlex){ System.out.println("erro na consulta "+sqlex); } Página 8/10 UNIVERSIDADE FEDERAL DO PARANÁ return todos; } Para acessar os elemento de um Vector podemos usar a interface Iterator. Veja o seguinte código: Vector todos = Repositorio.recuperaTodosClientesBd(conn) Iterator itr = todos.iterator(); while (itr.hasNext()) { System.out.println((Cliente)itr.next().getNome()); } Veja o exemplo da classe PreparedStatement: Connection com = DriverManager.getConnection(localBD); String sql = "UPDATE aluno SET nota = ? WHERE codigo = ?"; PreparedStatement ps = con.preparedStatement(sql); ps.setInt(1, 10); ps.setInt(2, 1001); /* Atualiza o aluno 1001 com a nota 10 */ int result = ps.executeUpdate(); ps.close(); Transações Uma transação é um conjunto de um ou mais comandos que devem ser considerados em conjunto como uma só operação, ou seja, caso um dos comandos falhar, todo o conjunto deve ser desprezado. Portanto, a transação só é concluída com sucesso se todos os seus comando forem executados com sucesso. Transações ajudam a preservar a integridade dos dados de um SGBD. Por exemplo, um registro de venda deve atualizar a quantidade de estoque dos produtos vendidos. A transação deverá englobar a operação de inserção do registro da venda e a atualização do estoque. Se a atualização no estoque falhar, o registro da venda também deve ser descartado. Os seguintes métodos da classe Connection são utilizados para a realização de transações: void setAutoCommit(boolean) void commit() void rollback() Liga (false) ou desliga (true) a utilização de transações pelo SGDB. Confirma toda a transação, ou seja, todas as atualizações no SGBD executadas desde o início da transação serão efetivadas na base de dados. Aborta toda a transação, ou seja, todas as atualizações no SGBD executadas desde o início da transação serão descartadas. Considera-se como início de uma transação, a chamada a um dos seguites métodos: Connection conn = DriverManager.getConnection(url, username, password); conn.setAutoCommit(false); conn.commit(); conn.rollback(); Página 9/10 UNIVERSIDADE FEDERAL DO PARANÁ ATIVIDADE 1. Dada a seguinte tabela: notas (matricula, turma, bimestre, nota), fazer um programa que 2. 3. 4. 5. 6. 7. leia a matricula do aluno, a turma, o bimestre e a nota do aluno e grave estes dados na base de dados. Fazer um programa que leia a matricula do aluno e mostre as notas, turma e o bimestre de todas as notas que o aluno recebeu. Construa um método da classe Produto para inserir, remover e atualizar os dados na base. Construa um método da classe Vendedor para inserir, remover e atualizar os dados na base. Construa uma Classe BD com os métodos: public static void insere(String query), public static ResultSet consulta(String query), public static void remove(String query), public static void altera(String query) Construa um método da classe Cliente para recuperar os atributos do objeto da base usando o id do objeto na base, um método para atualizar os dados na base e um método para retornar todos os Identificadores (id) da tabela Cliente. Construa os métodos da classe Pizza para: inserir os dados do objeto na base de dados; recuperar os atributos do objeto da base usando o id do objeto na base; um método para atualizar os dados do objeto na base; um método para retornar todos os Identificadores (id) da tabela pizza e um método para retornar todos os dados da tabela pizza. BIBLIOGRAFIA BÁSICA DEITEL, H. M. e DEITEL, P. J.. Java, como Programar. Ed. Bookman. Porto Alegre. 2001. AHO, A.V.; HOPCROFT, J.E.; ULLMAN, J.D. Data Structures and Algorithms. Addison-Wesley, Reading, Massachusetts, 1983. Página 10/10