Blobs and JDBC

A blob is a database data type for storing raw, binary data. It stands for "binary large object". In this blog post, I'm going to show you how to use this data type to insert and retrieve a photo using JDBC.

To insert the photo, start by creating an InputStream object to the photo you want to insert. For example, if the photo resides in a file, create a FileInputStream object.

File file = new File("photo.jpg");
InputStream in = new FileInputStream(file);

Then, create a PreparedStatement object for your INSERT statement. The PreparedStatement should contain a parameter for where the binary data should go, just as if you were inserting "normal" data, like a string or an integer.

Connection conn = ...
PreparedStatement stmt = conn.prepareStatement("INSERT INTO test (photo) VALUES (?)");

To set the binary data, pass the InputStream object into the setBlob() method, and then execute the statement.

stmt.setBlob(1, in);
stmt.execute();

To retrieve blob data from the database, call the getBlob() method on the ResultSet object that is returned from the SELECT statement. This will return a Blob object. Then, invoke the Blob.getBinaryStream() method to get an InputStream to the binary data.

Connection conn = ...
PreparedStatement stmt = conn.prepareStatement("SELECT photo FROM test");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
  Blob blob = rs.getBlob(1);
  InputStream in = blob.getBinaryStream();
  ...
}