Red Hat Database provides a couple ways of storing binary data, such as images and audio, that cannot be represented by the standard SQL92 data types.
First, there is Red Hat Database's Large Object (LOB) extension. LOBs are stored in a seperate table (pg_largeobject) and are referred to from normal tables by an OID value. There are two methods of working with Red Hat Database LOBs in Java. The first is the standard JDBC way; the other users PostgreSQL's extensions to the JDBC API.
The second way is to use the new bytea data type. bytea values are not stored in a seperate table as LOBs are.
Which method you choose is up to you, but you should take the following into account when making your decision.
bytea is not well suited for storing large amounts of data. A column of type bytea can hold up to 1 gigabyte of binary data, but all data is stored in the table and sent to client programs when requested. Clients would need very large amounts of RAM to work with large bytea data. The LOB extension does not have this problem as locators are used extensively and data is only sent when requested. The entire LOB does not have to be sent to clients.
The fact that LOBs are stored in a seperate table introduces some complications. Most noteable is the fact that a DELETE operation does not remove the LOB; a second DELETE has to be done on the pg_largeobject table. Also, anyone connected to the database can view and/or modify any LOB, even if they do not have permissions to view or update the row containing the reference to the LOB.
In RHDB 2, the setBytes() and getBinaryStream() methods of PreparedStatement and the getBytes() and getBinaryStream() methods of ResultSet operate on the bytea data type. In earlier versions of Red Hat Database, these methods operated on the OID associated with a LOB. The next section has more information.
Example 7-5. Using bytea
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 = con.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 could also have been done using the setBytes() method if the contents of the image were already in byte[].
Retrieving an image is even easier. (A PreparedStatement is used here, but a Statement object could also 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 bytes in some way here } rs.close(); } ps.close(); |
Here the binary data was retrieved into a byte array. Alternatively, an InputStream object along with the getBinaryStream() method of ResultSet could have been used instead.
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 |
The standard JDBC way is to use the getBytes(), setBytes(), getBinaryStream(), and setBinaryStream() methods that the ResultSet and PreparedStatement classes provide. To make sure that these methods operate on OIDs instead of bytea values (the default), you need to set the compatible property to "7.1" when obtaining a Connection object. For example:
String url = "jdbc:postgresql:foo"; java.utils.Properties props = new java.utils.Properties(); props.setProperty("user", "user"); props.setProperty("password", "secret"); props.setProperty("compatible", "7.1"); Connection con = DriverManager.getConnection(url, props); |
It is also possible to use ResultSet's getBLOB() method and PreparedStatement's setBLOB() method to interact with LOBs. However, these methods may not interact with LOBS in future releases of the JDBC driver. It is recommended that you use PostgreSQL's Large Object API if you intend to use Large Objects.
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-6 illustrates the usage of this approach.
Example 7-6. 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-7 illustrates the use of the LOB API.
Example 7-7. 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(); |