SBD-Criacao-e-Populacao-de-Tabelas-com-DBeaver-e-JDBC

Sistemas de Bancos de Dados sbd04 Criando e Populando Tabelas Nesta atividade você trabalhará como criar e popular uma tabela em um banco de dados A criação de uma tabela em um banco pode ser feita utilizando comandos DDL embutidos na linguagem java bem como a povoamento da tabela Proponho aqui que a criação de uma nova tabela ocorra utilizando a interface gráfica do programa DBeaver por dois motivos i a tabela que criaremos é pequena e será criada apenas uma vez ii Os tipos de dados possíveis para colunas de uma tabela existem em uma variedade enorme bem como é enorme a quantidade de configurações adicionais que podem ser feitas nos campos de uma tabela não aceitar valores nulos preencher com valor default entre outros Dessa forma se quisermos utilizar um comando DDL dentro do java para criar a tabela será inevitável consultar a interface gráfica do DBeaver para ver as possibilidades de criação da tabela Entretanto caso você tenha a necessidade que um programa crie tabelas automaticamente no computador de um usuário então não há como evitar a criação de um código java responsável em criar tal tabela Para popular os dados na tabela também poderíamos utilizar um comando psql passando o arquivo de dados pelo parâmetro f com as instruções de inserção de dados em uma tabela recémcriada A mesma metodologia para criar a tabela também é utilizada para popular a tabela A principal diferença do método utilizado nesta atividade para criar e popular tabelas quando comparado à última atividade é a utilização de métodos JDBC que não necessitam retornar tuplas como resultado da execução de uma consulta na SQL Antes de embutirmos o código java para popular uma tabela faremos a execução deste código isolado das classes do tutorial isso mesmo faremos um teste de leitura dos dados a serem inseridos no banco a partir de um arquivo texto e para isso não vamos utilizar nenhuma classe prédefinida no tutorial do JDBC mas tão somente a pura linguagem java Uma vez que seu código tenha funcionado então partimos para modificálo a fim de que possa ser embutido junto das classes do tutorial JDBC Bem então vamos ao trabalho Um lembrete muito importante se você deixar todo este tutorial para ser executado no último dia permitido para envio do relatório da atividade eu receio que talvez você não vá conseguir terminar a tempo 1 Nesta atividade vamos utilizar uma nova versão do banco da Instituição Bancária IB Não adianta tentar inserir estes dados no banco das aulas anteriores visto que acontecerão erros de integridade referencial para todas as linhas da nova tabela que vamos criar e popular Portanto baixe o arquivo IB2dump Na sequência você criará um banco vazio para importar o dump para o novo BD de nome IB2 wget httpswwwdropboxcoms6zuffauqzvd4fdeIB2dump 2 Criar um banco de dados vazio IB2 createdb h localhost p 5432 U postgres IB2 3 Executar a carga do arquivo IB2dump no banco IB2 psql h localhost p 5432 U postgres IB2 f IB2dump 4 Baixar os arquivos tsv da tarefa do TEAMS para a pasta raiz do tutorial do JDBC Listar o conteúdo dos arquivos baixados com cat ou editandoos com gedit Perceba que estes arquivos possuem dezenas e até centenas de linhas separadas por tabulação A próxima tarefa compreende a leitura desses dados em um loop e execução de um comando insert into para cada linha e assim popular a tabela com estes dados Para tanto disponibilizei um código de exemplo para ler dados tabulados de um arquivo e imprimir o resultado na tela Façamos uma execução deste código para vêlo em ação Sistemas de Bancos de Dados sbd04 Criando e Populando Tabelas 5 Crie um arquivo vazio com o gedit insira o seguinte código no arquivo vazio e salveo com o nome de ReadFilejava na mesma pasta na qual o arquivo debitostsv foi salvo 1 import javaioBufferedReader 2 import javaioFileReader 3 import javaioIOException 4 import javaniofileFiles 5 import javaniofilePaths 6 import javautilstreamStream 7 public class ReadFile 8 public static void mainString args 9 String fileName debitostsv 10 processFilefileName 11 12 public static void processFileString fileName 13 try BufferedReader reader new BufferedReadernew FileReaderfileName 14 String line 15 while line readerreadLine null 16 String values linesplit 17 if valueslength 7 18 Systemoutprintln 19 for String value values 20 Systemoutprintlnvalue 21 22 Systemoutprintln 23 String insertSql INSERT INTO debito numerodebito valordebito motivodebito datadebito numeroconta nomeagencia nomecliente VALUES 24 SystemoutprintlninsertSql 25 Systemoutprintln 26 27 28 catch IOException e 29 SystemerrprintlnErro ao ler o arquivo egetMessage 30 31 32 33 Compile e execute o arquivo ReadFilejava 1 javac ReadFilejava 2 java ReadFile 34 Utilize a interface do DBeaver para criar a seguinte tabela no banco Nome da tabela debito Sistemas de Bancos de Dados sbd04 Criando e Populando Tabelas Índices Nome pkdebito Tipo PRIMARY KEY Sobre numerodebito Restrições de chave estrangeira Nome fkdebito Tipo FOREIGN KEY Sobre numeroconta nomeagencia nomecliente Referenciando contanumeroconta nomeagencia nomecliente Perceba que ao final temos apenas uma tabela sem dado algum e será necessário realizar um carga de dados na tabela 35 Após ter criado manualmente a tabela agora é necessário populála com os dados do arquivo debitotsv Crie um método na classe MyQueries para executar tal operação Utilize o arcabouço do código abaixo para este propósito 1 import javasqlSQLException 2 import javasqlConnection 3 import javasqlStatement 4 5 public class DatabaseOperations 6 public static void executeUpdateConnection con String sqlToExecute 7 try Statement stmt concreateStatement 8 SystemoutprintlnExecutando SQL sqlToExecute 9 stmtexecuteUpdatesqlToExecute 10 SystemoutprintlnComando executado com sucesso 11 catch SQLException e 12 SystemerrprintlnFalha ao executar o comando SQL 13 JDBCUtilitiesprintSQLExceptione 14 15 16 Perceba que o código aqui tem um comando de execução diferente dos anteriores A explicação está abaixo na Figura 1 em resumo o método executeQuery retorna um objeto do tipo ResultSet mas nem todos os comandos executados no SGBD vão retornar algum valor a ser tratado como é o caso dos comandos DDL e DML Para comandos que não retornam parâmetros utilizamos o método executeUpdate Nome da Coluna Tipo Modificadores numerodebito integer not null valordebito double precision not null motivodebito smallint datadebito date numeroconta integer nomeagencia character varying50 nomecliente character varying80 Sistemas de Bancos de Dados sbd04 Criando e Populando Tabelas 17 O código ReadFilejava deve ser reaproveitado para popular a tabela debito Para tanto 1 Mescleo com o código apresentado na classe ReadFilejava 2 Inclua a chamada ao método populatetable no método main da classe MyQueries 18 Modificarcriar o arquivo de propriedades de conexão para acessar o banco IB2 Recomendo que copie o arquivo de propriedades de conexão anterior ao PostgreSQL para fazer a conexão ao banco IB2 19 Salve compile e execute o MyQueries 1 Certifiquese de que o arquivo propertiespostgres2propertiesxml está configurado com os parâmetros de conexão ao banco de dados correto 2 comp MyQueries propertiespostgres2propertiesxml 20 Quem chegou até aqui sem pensar como um programador deve ter recebido um erro estes registros não estão sendo aceitos no banco por questões de integridade referencial Então para resolver o problema o sistema de vocês precisa primeiro inserir os clientes depois as contas e por fim as operações de depósitos e débitos Débitos são independentes de depósitos mas esta atividade precisa registrar ambas as operações 21 Sempre que você tentar executar a inserção destes dados mais de uma vez na tabela debito Figura 1 Captura de tela da API do tutorial do java relativo à classe Statement Sistemas de Bancos de Dados sbd04 Criando e Populando Tabelas serão exibidas mensagens de erro como esta orgpostgresqlutilPSQLException ERROR duplicate key value violates unique constraint pkdebito Execute o código e comprove comp MyQueries propertiespostgres2propertiesxml Isso ocorre porque a integridade da chave primária é policiada pelo SGBD Para evitar que esta mensagem de erro ocorra supondo que a tabela precisa ser recarregada periodicamente podemos usar um comando para truncar a tabela antes de novas inserções Insira o comando antes do loop de inserção de linhas lidas do arquivo salve e execute novamente 1 stmtexecuteUpdatetruncate table debito 2 comp MyQueries propertiespostgres2propertiesxml ATENÇÃO sistema de bancos de dados algum possui um comando truncate Este é só um artifício para facilitar a execução da atividade repetidas vezes por parte de vocês 22 Após inserir os dados das quatro novas tabelas no banco modifique a consulta da saúde financeira dos clientes para substituir a tabela empréstimos pela tabela de débitos Faça seu código exibir o relatório da saúde financeira na tela com duas casas decimais para valores monetários Não modifique os nomes dos arquivos tsv e considere que eles são carregados da pasta onde o programa java está executando não passar caminho completo para o arquivo 23 Faça um relatório sobre esta atividade e entregue juntamente com o seu código MyQueries java que popula as tabelas e mostra a saúde financeira baseada na diferença da soma dos depósitos subtraído da soma dos débitos