Introduction

Databases aren't great for storing binary large objects, aka BLOBs. By large meaning several MBs of size. If that is needed, then it's likely much more performant to just use the filesystem or cloud storage and only store references in the database for structure.

Smaller objects are typically fine to store in the database. To that end, this article will demonstrate how to manage BLOBs using JPA and Hibernate along with CockroachDB. In CockroachDB, the BLOB type is an alias for the BYTES data type. As mentioned in the docs, it's recommended to keep values under 1 MB to ensure adequate performance. Above that threshold, write amplification and other considerations may cause significant performance degradation.

Mapping BLOBs in JPA

When using Hibernate, you typically use the @Lob annotation and java.sql.Blob which maps to the SQL BLOB data type.

@Entity
@Table(name = "attachment")
public class Attachment {   
    @Column(name = "content")
    @Basic(fetch = FetchType.LAZY)
    @Lob
    private Blob content;
    ...
}

You can also use a byte[] array or a String, but it's generally more performant to use a streaming approach using the Blob type.

Using BLOB Mappings

You need to use Hibernate’s BlobProxy class to create a Blob. As you can see in the example below, it's pretty straightforward:

// Stores a BLOB represented by the inputStream
Blob content = BlobProxy.generateProxy(inputStream, contentLength);

Attachment attachment = new Attachment();
attachment.setContent(content);
attachment.setName(name);
attachment.setDescription(description);

attachmentRepository.save(attachment);

That's about it, very simple.

To read the BLOB back again, it recommended to use the streaming approach:

// Lookup attachment by ID and stream the blob to the outputStream
Attachment attachment = attachmentRepository.getReferenceById(id);
try (InputStream in = new BufferedInputStream(
        attachment.getContent().getBinaryStream())) {
    FileCopyUtils.copy(in, outputStream);
} catch (SQLException | IOException e) {
    throw new DataRetrievalFailureException("Error reading attachment data", e);
}

If you would provide a REST endpoint for downloading BLOB attachments, then it could look like this when implemented using Spring Boot:

@GetMapping("/download/{id}")
public ResponseEntity<StreamingResponseBody> downloadAttachment(@PathVariable("id") Long id) {
    Attachment attachment = attachmentService.findById(id);
    StreamingResponseBody responseBody =
            outputStream -> attachmentService.streamAttachment(attachment, outputStream);
    return ResponseEntity.ok()
            .header(HttpHeaders.CONTENT_TYPE, attachment.getContentType())
            .header(HttpHeaders.CONTENT_DISPOSITION, "inline")
            .header("Cache-Control", "no-cache, no-store, must-revalidate")
            .header("Pragma", "no-cache")
            .header("Expires", "0")
            .body(responseBody);
}

StreamingResponseBody is used for asynchronous request processing where the application can write directly to the response OutputStream.

Demo Project

This demo project is a runnable Spring Boot application that provides a REST API for querying and uploading attachments with BLOB content.

To build:

git clone git@github.com:kai-niemi/roach-spring-boot-v3
cd roach-spring-boot-v3
chmod +x mvnw
./mvnw clean install

To run:

cockroach sql --insecure --host=localhost -e "CREATE database spring_boot_demo"

java -jar spring-boot-blob/target/spring-boot-blob.jar

Check that the service is up at http://localhost:8090.

Upload an image file using cURL:

curl http://localhost:8090/attachment/form \
-H "Content-Type: multipart/form-data" \
-v \
-F "content=@spring-boot-blob/src/test/resources/test.jpg" \
-F "fileName=test.jpg" \
-F "description=test.jpg"

Source Code

The code for this article is available on GitHub.

Conclusion

This article provides a guide on how to manage binary large objects (BLOBs) using JPA and Hibernate with CockroachDB. It demonstrates the @Lob annotation, java.sql.Blob type, and a runnable Spring Boot example.