In Red Hat Database, Large Objects (also known as LOBs) are used to hold data, such as images and audio, that are not be represented by standard SQL data types. They are referred to from normal tables by an OID value.
There are two methods to work with Red Hat Database LOBs in Java. The first is the standard JDBC way; the other uses PostgreSQL's extensions to the JDBC API. All LOB operations must be done within a transaction. A transaction can be entered by calling the setAutoCommit method of your Connection object with a parameter of false. For example:
Connection con; ... con.setAutoCommit(false); // now ok to use LOBs |
With JDBC, the standard way to access LOBs is to use the getBinaryStream() method of ResultSet objects, and the setBinaryStream() method of PreparedStatement objects. These methods make the large object appear as a Java stream, allowing you to use the java.io package to manipulate the object. Example 7-5 illustrates the usage of this approach.
Example 7-5. Using Large Objects
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 (?, ?)");
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 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. (A PreparedStatement is used here, but a Statement object could also be used.)
PreparedStatement pstmt =
con.prepareStatement("SELECT img FROM images WHERE imgname=?");
pstmt.setString(1, "myimage.gif");
ResultSet rs = pstmt.executeQuery();
if (rs != null) {
while(rs.next()) {
InputStream is = rs.getBinaryInputStream(1);
// use the stream in some way here
is.close();
}
rs.close();
}
pstmt.close(); |
Notice that the InputStream is closed before processing of the next row in the result happens. The JDBC API requires that this is done: it states that any InputStream returned must be closed when either ResultSet.next() or ResultSet.close() are called.
The standard JDBC LOB interface is limited in that LOBs can only be streamed in and out. The LOB Extension API provided by PostgreSQL allows for random access within LOBs, as if they were local files.
The org.postgresql.largeobject package provides the equivalent of the libpq library's large object API. It consists of two classes: LargeObjectManager, which deals with creating, opening and deleting LOBs, and LargeObject, which represents an individual LOB. Example 7-6 illustrates the use of the LOB API.
Example 7-6. Using Large Objects
Suppose you have a table containing the file name of an image and you have a LOB containing that image:
CREATE TABLE images (imgname text, img oid); |
To insert an image, you would use:
// All LargeObject API calls must be within a transaction
con.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj =
((org.postgresql.Connection)con).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 pstmt =
con.prepareStatement("INSERT INTO images VALUES (?, ?)");
pstmt.setString(1, file.getName());
pstmt.setInt(2, oid);
pstmt.executeUpdate();
pstmt.close();
fis.close(); |
Retrieving the image from the database:
// All LargeObject API calls must be within a transaction
con.setAutoCommit(false);
// Get the Large Object Manager to perform operations with
LargeObjectManager lobj =
((org.postgresql.Connection)con).getLargeObjectAPI();
PreparedStatement pstmt =
con.prepareStatement("SELECT img FROM images WHERE imgname=?");
pstmt.setString(1, "myimage.gif");
ResultSet rs = pstmt.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();
}
pstmt.close(); |