Sistemas de Bancos de Dados - Recuperação e Modificação de Valores - Exercícios

Sistemas de Bancos de Dados SBD05 Recuperando e Modicando Valores Anderson Santos FACOM April 15 2025 1 Introdução As atividades realizadas anteriormente eram referentes à criação de tabelas inserção e recuper ação de dados destas tabelas Nas atividades desta aula você vai explorar um pouco mais de recuperação de dados porém você vai recuperar os dados para depois modicálos e devolver as modicações para o banco de dados sempre que possível A possibilidade de uma modicação de dados ser bem ou mal sucedida depende das capaci dades do SGBD ao qual você está conectado conjugadas com as opções de acesso que você mesmo explicita no momento em que congura a conexão ao banco de dados Você será conduzido a explorar estas possibilidades de congurações de um SBD Também vai explorar as diferentes possibilidades de atualização de dados por exemplo uma linha por vez ou várias linhas por vez Para esta aula utilizaremos o banco de dados IB2 criado e modicado na aula anterior no PostgreSQL e no SQLite É recomendado o uso de ferramentas de administração de banco de dados modernas como pgAdmin4 ou DBeaver para edições grácas Porém elas não são obrigatórias para a execução da tarefa 2 Recuperando Dados Agregados SubTarefa 1 Utilizando uma interface como pgAdmin4 ou DBeaver conectese ao banco de dados IB2 e elabore a seguinte consulta SQL Retorne os nomes de todos os clientes do banco com suas respectivas somas de depósitos débitos e empréstimos se existirem O resultado das somas deve ser agrupado pelo nome do cliente 3 Recuperando Dados com JDBC e Acessando Colunas Vamos criar um método Java para buscar dados simples da tabela CONTA e demonstrar formas de acessar os valores do ResultSet Usaremos as práticas modernas de trywithresources e PreparedStatement SubTarefa 2 Crie ou adapte o seguinte método em seu programa Java MyQueriesjava 1 import javasql 2 3 public static void recuperarContas Connection con 4 String query SELECT numeroconta nomeagencia saldo FROM CONTA Ajuste colunas 5 6 try with resources garante o fechamento automatico de pstmt e rs 7 try PreparedStatement pstmt con prepareStatement query 8 ResultSet rs pstmtexecuteQuery 9 1 10 SystemoutprintlnContas da Instituicao Bancaria 11 Systemoutprintln 12 13 while rsnext 14 Acesso por nome da coluna MAIS RECOMENDADO e robusto 15 int numeroConta rsgetIntnumeroconta 16 String nomeAgencia rsgetStringnomeagencia 17 double saldo rsgetDoublesaldo Exemplo com double 18 19 SystemoutprintfConta d Agencia s Saldo 2fn 20 numeroConta nomeAgencia saldo 21 22 Acesso por indice numerico fragil a mudancas na query SQL 23 int numeroContaIdx rsgetInt 1 24 String nomeAgenciaIdx rsgetString 2 25 Systemoutprintf Via Indice Conta d Agencia sn 26 numeroContaIdx nomeAgenciaIdx 27 28 Systemoutprintln 29 30 catch SQLException e 31 Idealmente use um metodo robusto para tratarlogar a excecao 32 SystemerrprintlnSQL State egetSQLState 33 SystemerrprintlnError Code egetErrorCode 34 SystemerrprintlnMessage egetMessage 35 JDBCUtilities printSQLException e Se voce tiver essa classe 36 37 38 Listing 1 Método para buscar e exibir dados da tabela CONTA Execute seu programa ajuste o comando comp conforme necessário para compilar e executar incluindo o classpath para o driver JDBC do PostgreSQL e SQLite SubTarefa 3 Modique o método acima ou crie um novo para executar a query da SubTarefa 1 e exibir os resultados Use 1 Nomes das colunas ou aliases rsgetStringnomecliente rsgetDoubletotaldepositos Esta é a forma preferida 2 Índices numéricos rsgetString1 rsgetDouble2 Mostre como funciona mas ex plique por que é menos robusto 4 Explorando Capacidades do Banco de Dados e ResultSet Podemos usar metadados para descobrir as capacidades do SGBD e do driver JDBC em relação aos ResultSets SubTarefa 4 Inclua este código em seu programa MyQueries executeo e explique os resultados com base na documentação JDBC sobre Holdability se um cursor permanece aberto após um commit para o PostgreSQL e SQLite 1 import javasql DatabaseMetaData 2 import javasqlResultSet 3 import javasqlSQLException 4 import javasqlConnection 5 6 public static void verificarHoldability Connection conn throws SQLException 7 DatabaseMetaData dbMetaData conngetMetaData 8 9 Systemoutprintln Verificando Holdability 2 10 SystemoutprintlnValor de ResultSet HOLDCURSORSOVERCOMMIT ResultSet HOLDCURSORSOVERCOMMIT 11 SystemoutprintlnValor de ResultSet CLOSECURSORSATCOMMIT ResultSet CLOSECURSORSATCOMMIT 12 SystemoutprintlnHoldability padrao do ResultSet via DriverDB dbMetaData getResultSetHoldability 13 14 boolean supportsHold dbMetaData supportsResultSetHoldability ResultSet HOLDCURSORSOVERCOMMIT 15 SystemoutprintlnSuporta HOLDCURSORSOVERCOMMIT supportsHold 16 17 boolean supportsClose dbMetaData supportsResultSetHoldability ResultSet CLOSECURSORSATCOMMIT 18 SystemoutprintlnSuporta CLOSECURSORSATCOMMIT supportsClose 19 Systemoutprintln 20 Listing 2 Vericando suporte à Holdability do ResultSet SubTarefa 5 O método supportsResultSetConcurrency verica se o banco de dados suporta um tipo especíco de ResultSet scrollable forwardonly em combinação com um tipo de concorrência readonly updatable Figure 1 Documentação do método supportsResultSetConcurrency Expanda o código da SubTarefa 4 ou crie um novo método para vericar e imprimir se o banco de dados suporta as seguintes seis combinações para o PostgreSQL e SQLite 1 TYPEFORWARDONLY com CONCURREADONLY 2 TYPEFORWARDONLY com CONCURUPDATABLE 3 TYPESCROLLINSENSITIVE com CONCURREADONLY 4 TYPESCROLLINSENSITIVE com CONCURUPDATABLE 5 TYPESCROLLSENSITIVE com CONCURREADONLY 6 TYPESCROLLSENSITIVE com CONCURUPDATABLE Exemplo de como vericar uma combinação 1 boolean supported dbMetaData supportsResultSetConcurrency 2 ResultSetTYPESCROLLSENSITIVE 3 ResultSet CONCURUPDATABLE 4 SystemoutprintlnSuporta SCROLLSENSITIVE com CONCURUPDATABLE supported Listing 3 Vericando uma combinação de Tipo e Concorrência 3 Nota de Contexto Embora o JDBC dena ResultSets atualizáveis CONCURUPDATABLE seu uso na prática moderna é menos comum Frequentemente modicações são feitas através de comandos SQL UPDATE INSERT DELETE explícitos via PreparedStatement ou por meio de frameworks ORM ObjectRelational Mapping como JPAHibernate ou outras bibliotecas de acesso a dados No entanto é importante conhecer essa capacidade do JDBC 5 Modicando Dados via ResultSet Atualizável Vamos explorar a atualização de dados diretamente através de um ResultSet congurado como atualizável SubTarefa 6 Analise o código abaixo Ele tenta aplicar um aumento percentual a um valor Note que ele foi escrito intencionalmente com um erro Identique o erro com base nos resultados da SubTarefa 5 Um detalhe atualize o campo saldo com diferenças entre depósitos e débitos de cada conta para ver o programa funcionar 1 public static void tentativaModificarSaldoComErro Connection con throws SQLException 2 String query SELECT numeroconta saldo FROM CONTA WHERE nomeagencia Exemplo com filtro 3 4 Usando try with resources para Statement 5 try PreparedStatement pstmt con prepareStatement query 6 ResultSetTYPESCROLLSENSITIVE 7 ResultSet CONCURREADONLY 8 9 pstmtsetString 1 Agencia Central Exemplo de parametro 10 ResultSet uprs pstmtexecuteQuery 11 12 Systemoutprintln Atualizar saldos 13 while uprsnext 14 double saldoAtual uprsgetDoublesaldo 15 double novoSaldo saldoAtual 1005 Aumento de 05 16 17 SystemoutprintfConta d Saldo atual 2f atualizado para 2f n 18 uprsgetIntnumeroconta saldoAtual novoSaldo 19 uprsupdateDoublesaldo novoSaldo 20 uprsupdateRow 21 22 Systemoutprintln 23 24 catch SQLException e 25 SystemerrprintlnErro SQL esperado ao tentar atualizar 26 SystemerrprintlnSQL State egetSQLState 27 SystemerrprintlnMessage egetMessage 28 29 Listing 4 Tentativa de modicar dados com ResultSet contém erro SubTarefa 7 Crie um novo método a partir da SubTarefa 6 por exemplo aplicarJurosPoupanca que atualize a tabela DEPOSITO aplicando juros de 05 ao valor SubTarefa 8 Aqui vamos simular que um valor foi lido do teclado e será aplicado no cálculo de atualização de valores de uma tabela Modique a função da SubTarefa 7 para que a porcentagem de juros ou o multiplicador ex 105 para 5 1 import javautilScanner 2 import javautil InputMismatchException 3 4 4 dentro do seu metodo 5 Scanner in new ScannerSystemin 6 double multiplicador 10 Valor padrao 7 boolean inputValido false 8 9 while inputValido 10 SystemoutprintDigite o multiplicador como um numero real Ex 05 1005 11 try 12 multiplicador innextDouble 13 if multiplicador 0 14 SystemoutprintlnMultiplicador nao pode ser negativo Tente novamente 15 else 16 inputValido true 17 18 catch InputMismatchException e 19 SystemoutprintlnEntrada invalida Por favor digite um numero use virgula ou ponto como separador decimal conforme seu sistema 20 innext Consome a entrada invalida 21 22 23 Agora use a variavel multiplicador no calculo do novo saldovalor 24 Ex double novoSaldo saldoAtual multiplicador 25 Nao se esqueca de fechar o Scanner se ele nao for mais usado inclose 26 Mas cuidado ao fechar Systemin se precisar ler mais tarde Listing 5 Snippet para ler valor double do teclado 6 Operações em Lote Batch Updates Operações em batch permitem agrupar vários comandos SQL geralmente INSERT ou UPDATE e enviálos ao banco de dados de uma só vez o que pode ser signicativamente mais eciente do que executar cada comando individualmente É essencial controlar a transação manualmente nesses casos SubTarefa 9 Modique o código da populateTable da tarefa sbd04 que insira várias linhas na tabela DEBITO usando batch updates e PreparedStatement Siga os passos 1 Desative o autocommit connsetAutoCommitfalse 2 Prepare um PreparedStatement para o INSERT INTO DEBITO VALUES 3 Nos loops aninhados ou cada conjunto de dados a inserir Dena os parâmetros do PreparedStatement pstmtsetInt1 pstmtsetDouble2 Adicione o comando ao batch pstmtaddBatch 4 Execute o batch int updateCounts pstmtexecuteBatch 5 Faça o commit da transação conncommit 6 Inclua tratamento de exceção trycatch e garanta que connrollback seja chamado no catch em caso de erro 7 Use um bloco nally ou trywithresources para o PreparedStatement para garantir que os recursos sejam liberados e reative o autocommit se necessário fora desta operação especíca connsetAutoCommittrue 5 8 Lembremse que se tentarmos inserir os mesmos dados mais de um vez será gerado erro de integridade referencial 7 Inserindo Dados via ResultSet Atualizável Outra forma de inserir dados é usando um ResultSet atualizável SubTarefa 10 Modique o código da SubTarefa 9 para inserir as seguintes tuplas na tabela DEBITO do banco de dados IB2 usando ResultSetCONCURUPDATABLE Use os nomes das colunas e trate o tipo Date corretamente Table 1 Dados para Inserção na Tabela DEBITO Número Débito Valor Motivo Data Débito Número Conta AgênciaCliente Exemplo 2000 15000 1 20140123 46248 UFU Carla Soares Sousa 2001 20000 2 20140123 26892 Glória Carolina Soares Souza 2002 50000 3 20140123 70044 Cidade Jardim Eurides Alves da Silva Os nomes da agência e cliente são apenas referência contextual 1 import javasqlDate 2 import javasql 3 4 public static void insertRowConnection con throws SQLException 5 try Statement stmt con createStatement 6 ResultSet uprs stmtexecuteQuerySELECT FROM 7 8 uprs moveToInsertRow posiciona no ponto de inserção da tabela 9 uprsupdateInt 10 uprsupdateDatedatadebito DatevalueOfdatadebito 11 12 uprsinsertRow insere a linha na tabela 13 uprsbeforeFirst posiciona se novamente na posição anterior ao primeiro registro 14 15 catch SQLException e 16 JDBCTutorialUtilities printSQLException e 17 18 Listing 6 Inserindo uma linha via ResultSet Atualizável 8 Contexto Moderno e Próximas Etapas Embora o JDBC seja a API fundamental para acesso a bancos de dados relacionais em Java e seu entendimento seja crucial muitas aplicações empresariais modernas utilizam camadas de ab stração sobre o JDBC para simplicar o desenvolvimento reduzir código repetitivo boilerplate e adicionar funcionalidades Algumas alternativascomplementos comuns incluem JPA Java Persistence API com implementações como Hibernate Um padrão para Mapeamento ObjetoRelacional ORM que permite trabalhar com objetos Java direta mente delegando a persistência SQL gerado ao framework Spring Data JDBC Spring Data JPA Parte do ecossistema Spring oferece ab strações ainda mais altas para criar repositórios de dados com implementações automáticas de operações CRUD Create Read Update Delete e consultas 6 MyBatis Um framework de mapeamento SQL que foca em dar ao desenvolvedor controle total sobre o SQL enquanto simplica o mapeamento dos resultados para objetos Java e viceversa JOOQ Uma biblioteca que permite escrever SQL typesafe diretamente em Java Compreender o JDBC fornece a base para entender como essas ferramentas funcionam inter namente e para solucionar problemas de conectividade ou performance em níveis mais baixos 9 Relatório Final SubTarefa 11 Crie um relatório sobre os resultados e conclusões de todas as práticas desta atividade Discuta as diferentes formas de recuperação e modicação de dados suas vantagens desvantagens e quando cada uma poderia ser mais apropriada Inclua suas observações sobre as capacidades do seu SGBD PostgreSQL e SQLite em relação aos tipos e concorrências de ResultSet Envie via TEAMS o relatório e os códigosfonte em Java da atividade devidamente comen tados É essencial o envio de ambos relatório e códigos para avaliação Considerando que esta atividade é composta de diferentes implementações não encadeadas será obrigatório a construção de um menu de execução para a chamada de cada parte da tarefa Quando o programa entrar em execução deve entrar em um loop que solicita um número inteiro associado à execução de uma das atividades listadas em um menu de opções O loop será encerrado quando um usuário digitar a tecla ESC q ou Q quit ou senão qualquer coisa exceto os números do menu A falta deste menu por si só representará uma penalidade de quatro pontos A falta de funcionalidades no menu será penalizada considerandose o nível de diculdade da subtarefa 7