Failed to update field of all records with path of files using JDBC

0

I'm migrating pdf files stored in the database to file systems. In the code as it can be seen below, I use an algorithm that generates the names of the files and saves them in a directory generated through the date of inclusion of the document.

So far so good, because the files are being saved correctly. But I also need to store the path of these files in the database's path column. The problem that is occuring is that I am only able to update only the first record and in the wrong way, because it saves with the path of the last generated file.

Since all the records have the empty path field, how can I update all records by associating the correct path for all files?

public class MigracaoDados {

    public static void main(String[] args) throws Exception {

        ResultSet rs = null;

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.102:1521:xe","teste","123456");
            System.out.println("Conectado!");
            Statement statement = connection.createStatement();

            String sql = "SELECT id, dthinclusao, path, documentoarquivo FROM processodocumento";
            rs = statement.executeQuery(sql);

            while (rs.next()) {    

                //obtendo o arquivo no banco
                int id = rs.findColumn("id");
                Blob blob = rs.getBlob("documentoarquivo");

                //stream para leitura
                InputStream is = blob.getBinaryStream();

                String data = rs.getString("dthinclusao");
                String s_ano = data.substring(0,4);
                String s_mes = data.substring(5,7);
                String s_dia = data.substring(8,10);
                System.out.println(s_dia + "/" + s_mes + "/" + s_ano);
                System.out.println("---------------");

                //criacao do diretorio
                File novoDiretorio = new File("/home/phoenix/Documentos/workspace/files/" + "/"
                        + s_ano + "/"
                        + s_mes + "/"
                        + s_dia + "/");
                novoDiretorio.mkdirs();

                //geracao aleatoria dos nomes dos arquivos
                String descricaoArquivo = UUID.randomUUID().toString();

                //criacao do arquivo
                File file = new File(novoDiretorio, descricaoArquivo + ".pdf");
                System.out.println("Path: " + file);

                //atualizar campo path de acordo o nome do arquivo gerado
                String sqlUpdt = "UPDATE processodocumento SET path=? WHERE id = ?";            
                PreparedStatement stmt = connection.prepareStatement(sqlUpdt);
                String path = file.toString();

                stmt.setString(1, path);
                stmt.setInt(2, id);
                stmt.executeUpdate();

                //stream para escrita
                FileOutputStream fos = new FileOutputStream(file);    

                int b = 0;
                while ((b = is.read()) != -1)
                {
                    //efetuar a escrita no sistema de arquivos
                    fos.write(b);
                }
                fos.close();
                stmt.close();
            }
            statement.close();
            System.out.println("Todos os arquivos foram gravados no sistema de arquivos!");
            System.out.println("Desconectado!");

        } catch (SQLException e)
        {
            e.getMessage (); e.printStackTrace();
            System.out.println(e);
        }
    }
}
    
asked by anonymous 12.08.2017 / 16:50

1 answer

0

Solved, I just replaced this line

int id = rs.findColumn("id");  

by that other

int id = rs.getInt("id");
    
13.08.2017 / 01:11