Download blob data in a zip file in a simple java program

Question

I'm not able to download the files using the below code as i have multiple types of extensions to download and i want to zip all the files and download. If i run the below code, it says files is corrupted. someone please help on this?

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcReadFile {
    private static final int BUFFER_SIZE = 4096;

    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@xxx:xxxx:xxx";
        String user = "xxx";
        String password = "xxx";

        String filePath = "C:/abc.vsd";

        try {
            Connection conn = DriverManager.getConnection(url, user, password);

            String sql = "SELECT DOC_BO FROM table fetch first 10 rows only";
            PreparedStatement statement = conn.prepareStatement(sql);

            ResultSet result = statement.executeQuery();
            if (result.next()) {
                Blob blob = result.getBlob("DOC_BO");
                InputStream inputStream = blob.getBinaryStream();
                OutputStream outputStream = new FileOutputStream(filePath);

                int bytesRead = -1;
                byte[] buffer = new byte[BUFFER_SIZE];
                while ((bytesRead = inputStream.read(buffer)) != -1) {
                    outputStream.write(buffer, 0, bytesRead);
                }

                inputStream.close();
                outputStream.close();
                System.out.println("File saved");
            }
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}

I don't get it yet. Now in each itereation of the loop you're creating one file `C:/abc.vsd` which replaces any existing one, is supposed to be a visio file, I assume, and is corrupted. But you want one file per iteration and eventually put all of them in one zip? — Curiosa Globunznik, Oct 30 '19 at 19:08
yes, i have different types of files. not only visio, like .txt,.xlsx,.vsd. I want ll the files to be added to a zip file and download. Is it possible — Puja K, Oct 30 '19 at 19:18
It should be, can you determine the file type from some field of `table`? And do you know, that the stored binary in the blob isn't corrupt already? — Curiosa Globunznik, Oct 30 '19 at 19:21
Is there something to download as a new file in local instead of writing into a file? — Puja K, Oct 30 '19 at 19:31
even after creating a new file with no data, it says file i scorrupted after writing the data — Puja K, Oct 30 '19 at 19:36
The question is actually too broad for this forum. There's some confusion too it seems. All the code you showed would run on/be a server, it would create a zip file that some other process should download. In order to do that, you'd have to create a front-end, e.g. in [this post](https://stackoverflow.com/questions/29413238/file-download-at-front-end-using-angular-js) that's mentioned, or make it downloadable by ftp perhaps. You should get a handle on how a correct file looks like using a hex-editor, and what the difference is to the corrupt ones. An empty file is not valid in any case. — Curiosa Globunznik, Oct 30 '19 at 20:25

score 0 · Accepted Answer · edited Jun 17 '20 at 15:22

No front end is required for this. Issue is resolved using the below code.

public class BlobDataExtract { static ZipOutputStream zos = null; static String url = "jdbc:oracle:thin:@hostname:1521:SID";

public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, "user", "password");
    String sql = "select Blob_Data,ORIG_NM from table";
    PreparedStatement stmt = conn.prepareStatement(sql);
    ResultSet rs = stmt.executeQuery();
    byte[] docBlob = null;
    String filename = null;
    FileOutputStream fos = new FileOutputStream("C:/Users/test.zip");
    zos = new ZipOutputStream(fos);
    while (rs.next()) {
        docBlob = rs.getBytes("Blob_Data");
        filename = rs.getString("ORIG_NM");
        try {
            zos.putNextEntry(new ZipEntry(filename));
            zos.write(docBlob, 0, docBlob.length);
        } catch (FileNotFoundException ex) {
            System.err.println("A file does not exist: " + ex);
        } catch (IOException ex) {
            System.err.println("I/O error: " + ex);
        }
        zos.closeEntry();
    }

}

}

Download blob data in a zip file in a simple java program

1 Answers1