8.6. Storing Binary Data

Example 8-4. Binary Data Examples

For example, suppose you have a table containing the file name of an image and you also want to store the image in a bytea column:

CREATE TABLE images (imgname text, img bytea);

To insert an image, you would use:

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close();

Here, setBinaryStream() transfers a set number of bytes from a stream into the column of type bytea. This also could have been done using the setBytes() method if the contents of the image was already in a byte[].

Retrieving an image is even easier. (We use PreparedStatement here, but the Statement class can equally be used.)

PreparedStatement ps = con.prepareStatement("SELECT img FROM images WHERE imgname=?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while(rs.next()) {
        byte[] imgBytes = rs.getBytes(1);
        // use the stream in some way here
    }
    rs.close();
}
ps.close();

Here the binary data was retrieved as an byte[]. You could have used a InputStream object instead.

Alternatively you could be storing a very large file and want to use the LargeObject API to store the file:

CREATE TABLE imagesLO (imgname text, imgOID OID);

To insert an image, you would use:

// All LargeObject API calls must be within a transaction
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.Connection)conn).getLargeObjectAPI();

//create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

//open the large object for write
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
	obj.write(buf, 0, s);
	tl += s;
}

// Close the large object
obj.close();

//Now insert the row into imagesLO
PreparedStatement ps = conn.prepareStatement("INSERT INTO imagesLO VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close();

Retrieving the image from the Large Object:

// All LargeObject API calls must be within a transaction
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.Connection)conn).getLargeObjectAPI();

PreparedStatement ps = con.prepareStatement("SELECT imgOID FROM imagesLO WHERE imgname=?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while(rs.next()) {
	//open the large object for reading
	int oid = rs.getInt(1);
	LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

	//read the data
	byte buf[] = new byte[obj.size()];
	obj.read(buf, 0, obj.size());
	//do something with the data read here

	// Close the object
	obj.close();
    }
    rs.close();
}
ps.close();