PostgreSQL 7.1.2 Documentation
Prev	Chapter 8. JDBC Interface	Next

8.5. Using Large Objects

In Postgres, Large Objects (also known as BLOBs) are used to hold data in the database that cannot be stored in a normal SQL table. They are stored in a separate table in a special format, and are referred to from your own tables by an OID value.

Important: For Postgres, you must access Large Objects within an SQL transaction. You would open a transaction by using the setAutoCommit() method with an input parameter of false:
Connection mycon;
...
mycon.setAutoCommit(false);
... // now use Large Objects

There are two methods of using Large Objects. The first is the standard JDBC way, and is documented here. The other, uses PostgreSQL extensions to the API, which presents the libpq large object API to Java, providing even better access to large objects than the standard. Internally, the driver uses the extension to provide large object support.

In JDBC, the standard way to access Large Objects is using the getBinaryStream() method in ResultSet, and setBinaryStream() method in PreparedStatement. These methods make the large object appear as a Java stream, allowing you to use the java.io package, and others, to manipulate the object. Example 8-2 illustrates the usage of this approach.

Example 8-2. Using the JDBC Large Object Interface

For example, suppose you have a table containing the file name of an image and you have a large object containing that image:

CREATE TABLE images (imgname text, imgoid oid);

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 (?, ?)"); (1)
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close();

(1): The question marks must appear literally. The actual data is substituted by the next lines.

Here, setBinaryStream transfers a set number of bytes from a stream into a Large Object, and stores the OID into the field holding a reference to it. Notice that the creation of the Large Object itself in the database happens transparently.

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

PreparedStatement ps = con.prepareStatement("SELECT oid FROM images WHERE name=?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while(rs.next()) {
        InputStream is = rs.getBinaryInputStream(1);
        // use the stream in some way here
        is.close();
    }
    rs.close();
}
ps.close();

Here you can see how the Large Object is retrieved as an InputStream. You will also notice that we close the stream before processing the next row in the result. This is part of the JDBC specification, which states that any InputStream returned is closed when ResultSet.next() or ResultSet.close() is called.

Prev	Home	Next
Performing Updates	Up	PostgreSQL Extensions to the JDBC API