Working with Large Objects (LOBs)

Querying a PostgreSQL Database in Java

Miller Trujillo

Staff Software Engineer

Large Objects in Databases

BLOB: Binary Large Object (images, videos, PDFs)
CLOB: Character Large Object (long text, XML, JSON)

BLOB and CLOB representation

Why BLOBs and CLOBs?

VARCHAR works for small data
BLOB and CLOB are designed for very large content
Support streaming: read and write in chunks

Analogy - streaming a movie

Writing a BLOB in Java

String sql = "INSERT INTO covers (book_id, image) VALUES (?, ?)";


try (PreparedStatement pstmt = conn.prepareStatement(sql);
     FileInputStream fis = new FileInputStream("cover.png")) {


    pstmt.setInt(1, 101);

    pstmt.setBinaryStream(2, fis);

    pstmt.executeUpdate();

}

INSERT - adds new rows to a table
setBinaryStream() - streams binary data to a BLOB column

Writing a CLOB in Java

String sql = "INSERT INTO manuscripts (book_id, content) VALUES (?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql);

     FileReader reader = new FileReader("manuscript.txt")) {


    pstmt.setInt(1, 101);

    pstmt.setCharacterStream(2, reader);

    pstmt.executeUpdate();

}

setCharacterStream() - streams text data to a CLOB column

Reading LOBs in Java

InputStream reads bytes; Reader reads characters
BLOB: getBinaryStream() returns an InputStream
CLOB: getCharacterStream() returns a Reader

String sql = "SELECT image FROM covers WHERE book_id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setInt(1, 101);
    try (ResultSet rs = pstmt.executeQuery()) {

        if (rs.next()) {

            try (InputStream is = rs.getBinaryStream("image");

                 FileOutputStream fos = new FileOutputStream("out.png")){

                is.transferTo(fos);

}   }   }   }

Reading CLOBs with Readers

String sql = "SELECT content FROM manuscripts";
try (PreparedStatement pstmt = conn.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {

  while (rs.next()) {

    try (Reader reader = rs.getCharacterStream("content")) {

      char[] cb = new char[30];

      reader.read(cb); // Chars from 0 to 29

      reader.read(cb); // Chars from 30 to 59:
    }
  }
}

Best Practices for LOBs

📂 Prefer storing references (like URLs or paths)
⏩ Use streaming APIs (setBinaryStream(), getBinaryStream(), setCharacterStream())
🚫 Avoid frequent updates to very large objects

Large objects representation

Let's practice!

Querying a PostgreSQL Database in Java