Inserting BLOB Using Spring JdbcTemplate

1. Overview

Storing binary data in relational databases requires careful consideration of file size, database vendor, and driver specifics. While setting a byte array directly works for small BLOBs, streams are more memory-efficient for larger content, and Spring’s SqlBinaryValue offers a modern approach.

Spring’s JdbcTemplate provides a flexible API for these operations, avoiding the complexity of full ORM solutions, but selecting the right strategy is crucial for performance and scalability.

Why This Matters

Ideal models assume infinite resources, but in reality, memory constraints and database limitations dictate practical approaches to BLOB storage. Inefficient handling can lead to out-of-memory errors, slow database writes, or even application crashes, costing developer time and potentially impacting user experience.

Key Insights

• Deprecated APIs: SqlLobValue and LobHandler were deprecated in Spring 6.2, replaced by SqlBinaryValue.
• Streaming vs. In-Memory: Using PreparedStatement.setBinaryStream() avoids loading the entire BLOB into memory, improving scalability.
• Vendor Specificity: LobHandler implementations abstract vendor-specific LOB operations, although SqlBinaryValue simplifies this.

Working Example

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.support.SqlBinaryValue;

import java.io.ByteArrayInputStream;
import java.nio.charset.StandardCharsets;

public class BlobInsertionExample {

    private JdbcTemplate jdbcTemplate;
    private static final String CONTENT = "I am a very very long content.";

    public void insertBlobByteArray() {
        byte[] bytes = CONTENT.getBytes(StandardCharsets.UTF_8);
        jdbcTemplate.update(
                "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
                1,
                "bigfile.txt",
                bytes
        );
    }

    public void insertBlobStream() {
        ByteArrayInputStream stream = new ByteArrayInputStream(CONTENT.getBytes(StandardCharsets.UTF_8));
        jdbcTemplate.update(
                "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
                2,
                "bigfile.txt",
                stream
        );
    }

    public void insertBlobSqlBinaryValue() {
        byte[] bytes = CONTENT.getBytes(StandardCharsets.UTF_8);
        jdbcTemplate.update(
                "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
                3,
                "bigfile.txt",
                new SqlParameterValue(java.sql.Types.BLOB, new SqlBinaryValue(bytes))
        );
    }
}

Practical Applications

• Image Storage (Instagram): Utilizing streams for uploading and storing high-resolution images efficiently.
• Pitfall: Loading large BLOBs entirely into memory with setBytes() can cause OutOfMemoryError exceptions, especially under high load.

References:

• https://www.baeldung.com/spring-jdbctemplate-insert-blob