Working with Large Objects (LOBs)

1. Working with Large Objects (LOBs)

Welcome back! So far, we've stored simple data like names and prices. But what if our bookstore needs to store a book's entire manuscript or its cover image? That's where Large Objects come in.

2. Large Objects in Databases

Databases offer two special types for big data. BLOB, or Binary Large Object, handles binary content like images, videos, and PDFs. CLOB, or Character Large Object, stores large text documents like articles, XML, or JSON files.

3. Why BLOBs and CLOBs?

Why not just use VARCHAR? For small values, VARCHAR works fine. But for a 200-page manuscript or high-resolution image, it becomes inefficient. BLOBs and CLOBs support streaming, meaning we read and write content in chunks without loading everything into memory. Think of streaming a movie. You watch as it downloads, piece by piece. Large Objects work the same way.

4. Writing a BLOB in Java

Let's write a BLOB to our database. We create an INSERT statement with placeholders - INSERT statements allow us to add new rows of data into our tables. Then we open a FileInputStream, a Java class that reads binary data from a file. We bind the book ID with setInt, and here's the key: setBinaryStream binds our file stream to the BLOB column. This method streams binary data directly to the database without loading the file into memory. Finally, the executeUpdate method runs the insert.

5. Writing a CLOB in Java

For large text, the approach is similar. We use a FileReader instead of a FileInputStream to handle text content. Then we call setInt for the book ID and setCharacterStream to bind the text stream to the CLOB column. This method works just like setBinaryStream, but is designed for character data. We finish with executeUpdate to execute the insert.

6. Reading LOBs in Java

Reading LOBs works in reverse. In Java, an InputStream reads binary data byte by byte, while a Reader reads text character by character. For BLOBs, getBinaryStream returns an InputStream. For CLOBs, getCharacterStream returns a Reader. In our example, we prepare a SELECT query and execute it. When a row exists, we call getBinaryStream to get an InputStream connected to the BLOB data. We also create a FileOutputStream, which writes binary data to a file. The transferTo method copies the entire stream from the database to a file in a single call, efficiently handling even large files.

7. Reading CLOBs with Readers

Let's look at reading CLOBs with Readers. After executing our query, we loop through the results and call getCharacterStream to get a Reader. To read from it, we create a buffer, here a character array of 30 elements. The read method fills this buffer with the first 30 characters. Calling read again fills it with the next 30, continuing from where we left off. This lets us process large text in manageable chunks.

8. Best Practices for LOBs

Here are some best practices for working with Large Objects. When possible, store file paths or URLs in the database instead of the actual files. This keeps our database lean and efficient. When we do need to store LOBs directly, always use the streaming APIs we covered. Also, avoid frequently updating very large objects, as repeated writes can slow down both our application and the database.

9. Let's practice!

Let's work with some large objects!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.