How do I move zip file to blob column in Java?

Question

I have a Java program that creates a number of xml file and then zips them up and saves them to the file system. Later in the program I want to put that same zip file into a blob column of my oracle database. Problem is I'm not sure how to do that. I don't need to read it or do anything with the data, just move it to the database for persistent, central storage.

Thanks!

Answer 1

I think you're looking to use the setBytes() method :

So an example for a table representing e-mails where the body is a stream of bytes would look like:

      PreparedStatement ps = con.prepareStatement(
        "INSERT INTO Image (ID, Subject, Body) VALUES (2,?,?)");
      ps.setString(1, subject);
      byte[] bodyIn = {(byte)0xC9, (byte)0xCB, (byte)0xBB,
        (byte)0xCC, (byte)0xCE, (byte)0xB9,
        (byte)0xC8, (byte)0xCA, (byte)0xBC,
        (byte)0xCC, (byte)0xCE, (byte)0xB9,
        (byte)0xC9, (byte)0xCB, (byte)0xBB};
      ps.setBytes(2, bodyIn);
      int count = ps.executeUpdate();
      ps.close();

I'm assuming you can easily convert call getBytes() for your Zipped XML object.

Answer 2

There are several ways you can do this, but PreparedStatement.setBinaryStream is probably the best way.

public void saveFileToDatabase(File file) {
  InputStream inputStream = new FileInputStream(file);

  Connection conn = ...;
  PreparedStatement pS = conn.prepareStatement(...);
  ...
  pS.setBinaryStream(index, inputStream, (int) file.length());
  ...
  pS.executeUpdate();
}

(Note that for simplicity I didn't include any of the necessary try/catch stuff for closing the Connection, PreparedStatement and InputStream, but you would need to do that.)

Done this way, the data will be streamed from the file to the database without having to be loaded in to memory all at once.