Create BLOB for PostgreSQL and Oracle 

Joined:
04/07/2009
Posts:
171

November 25, 2010 00:03:53    Last update: November 25, 2010 00:03:53
It seems that the JDBC standard way to create a BLOB is to call Connection.createBlob. However, this does not work for PostgreSQL (as of version 9.0-801 jdbc4):
Exception in thread "main" org.postgresql.util.PSQLException: Method org.postgresql.jdbc4.Jdbc4Connection.createBlob() is not yet implemented.
        at org.postgresql.Driver.notImplemented(Driver.java:753)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.createBlob(AbstractJdbc4Connection.java:47)
        at org.postgresql.jdbc4.Jdbc4Connection.createBlob(Jdbc4Connection.java:21)


The workaround is to call a PostgreSQL function to create the Blob, then use JDBC to update it:
Connection conn = jdbcTemplate.getDataSource().getConnection();
conn.setAutoCommit(false);

// step 1: create the item with empty blob
PreparedStatement pstmt = conn.prepareStatement(
    "insert into InventoryItem (id, name, image) values (?, ?, lo_creat(-1))"
);
pstmt.setString(1, "1");
pstmt.setString(2, "BigItem");
pstmt.executeUpdate();
pstmt.close();

// step 2: select item to update
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
    "select image from InventoryItem where id = '1'"
);
rs.next();
Blob blob = rs.getBlob(1);
blob.truncate(1);

OutputStream out = blob.setBinaryStream(1); // position at the beginning
InputStream in = new FileInputStream("theImage.png");
byte[] buffer = new byte[4096];
try {
    int n = in.read(buffer);
    while (n >= 0) {
	out.write(buffer, 0, n);
	n = in.read(buffer);
    }
}
catch (IOException e) {
    throw new SQLException(e);
}
finally {
    try {
	in.close();
	out.close();
    }
    catch (IOException e) {
	log.error("Failed to close streams!", e);
    }
}
rs.close();
stmt.close();
conn.commit();


Oracle Note: the Oracle way function to create an empty BLOB is EMPTY_BLOB().
stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");

Share |
| Comment  | Tags