Using Large Objects

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.

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.

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();