Storing Binary Data
PostgreSQL® provides two distinct ways to store binary data. Binary data can be stored in a table using the data typeBYTEA or by using the Large Object feature which stores the binary data in a separate table in a special format and refersto that table by storing a value of type OID in your table.
In order to determine which method is appropriate you need to understand the limitations of each method. The BYTEA datatype is not well suited for storing very large amounts of binary data. While a column of type BYTEA can hold up to 1 GBof binary data, it would require a huge amount of memory to process such a large value. The Large Object method forstoring binary data is better suited to storing very large values, but it has its own limitations. Specifically deletinga row that contains a Large Object reference does not delete the Large Object. Deleting the Large Object is a separateoperation that needs to be performed. Large Objects also have some security issues since anyone connected to the databasecan view and/or modify any Large Object, even if they don’t have permissions to view/update the row containing theLarge Object reference.
Version 7.2 was the first release of the JDBC driver that supports the BYTEA data type. The introduction of this functionalityin 7.2 has introduced a change in behaviour as compared to previous releases. Since 7.2, the methodsgetBytes() ,setBytes() ,getBinaryStream() , andsetBinaryStream() operate on the BYTEA data type. In 7.1 andearlier, these methods operated on the OID data type associated with Large Objects. It is possible to revert the driverback to the old 7.1 behaviour by setting the propertycompatible on theConnection object to the value7.1.More details on connection properties are available in the section calledConnection Parameters.
To use the BYTEA data type you should simply use thegetBytes() ,setBytes() ,getBinaryStream() , orsetBinaryStream() methods.
To use the Large Object functionality you can use either theLargeObject class provided by the PostgreSQL® JDBC driver,or by using thegetBLOB() andsetBLOB() methods.
IMPORTANT
You must access Large Objects within an SQL transaction block. You can start a transaction block by calling
setAutoCommit(false).
Example 7.1, “Processing Binary Data in JDBC”contains some examples on how to process binary data using the PostgreSQL® JDBC driver.
Example 7.1. Processing Binary Data in JDBC
For example, suppose you have a table containing the file names of images and youalso want to store the image in a BYTEA column:
CREATETABLEimages(imgnametext,imgbytea);To insert an image, you would use:
Filefile=newFile("myimage.gif");try(FileInputStreamfis=newFileInputStream(file);PreparedStatementps=conn.prepareStatement("INSERT INTO images VALUES (?, ?)");){ps.setString(1,file.getName());ps.setBinaryStream(2,fis,(int)file.length());ps.executeUpdate();}Here,setBinaryStream() transfers a set number of bytes from a stream into the column of type BYTEA. This also couldhave been done using thesetBytes() method if the contents of the image was already in abyte[] .
NOTE
The length parameter to
setBinaryStreammust be correct. There is no way to indicate that the stream is of unknownlength. If you are in this situation, you must read the stream yourself into temporary storage and determine the length.Now with the correct length you may send the data from temporary storage on to the driver.
Retrieving an image is even easier. (We usePreparedStatement here, but theStatement class can equally be used.
try(PreparedStatementps=conn.prepareStatement("SELECT img FROM images WHERE imgname = ?");){ps.setString(1,"myimage.gif");try(ResultSetrs=ps.executeQuery();){while(rs.next()){byte[]imgBytes=rs.getBytes(1);// use the data in some way here}}}Here the binary data was retrieved as anbyte[]. You could have used aInputStream object instead.
Alternatively you could be storing a very large file and want to use theLargeObject API to store the file:
CREATETABLEimageslo(imgnametext,imgoidoid);To insert an image, you would use:
// All LargeObject API calls must be within a transaction blockconn.setAutoCommit(false);FileinputFile=newFile("myimage.gif");// Now insert the row into imageslotry(PreparedStatementps=conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");FileInputStreamfis=newFileInputStream(inputFile);){ps.setString(1,file.getName());ps.setBlob(2,fis,inputFile.length());ps.executeUpdate();}// Finally, commit the transaction.conn.commit();Retrieving the image from the Large Object:
// All LargeObject API calls must be within a transaction blockconn.setAutoCommit(false);try(PreparedStatementps=conn.prepareStatement("SELECT imgoid FROM imageslo WHERE imgname = ?");){ps.setString(1,"myimage.gif");try(ResultSetrs=ps.executeQuery();){while(rs.next()){// Read all data at oncebyte[]contents=rs.getBytes(1);// Read all data as InputStreamBlobblob=rs.getBlob(1);try(InputStreamis=blob.getBinaryStream();){// Process the input stream. The input stream is buffered, so you don't need to// wrap it in a BufferedInputStream}finally{blob.free();}}}}// Finally, commit the transaction.conn.commit();Updating the contents of the Large Object:
// All LargeObject API calls must be within a transaction blockconn.setAutoCommit(false);try(PreparedStatementps=conn.prepareStatement("SELECT imgoid FROM imageslo WHERE imgname = ?");){ps.setString(1,"myimage.gif");try(ResultSetrs=ps.executeQuery();){while(rs.next()){Blobblob=rs.getBlob(1);try(OutputStreamos=blob.setBinaryStream(0);){// Write data to the output stream. The output stream is buffered, so you don't need to// wrap it in a BufferedOutputStream}finally{blob.free();}}}}// Finally, commit the transaction.conn.commit();