Inserting BLOB Using Spring JdbcTemplate

1. Overview

When working with relational databases, we often need to store binary large objects (BLOBs), such as documents, images, and other media. Spring’s JdbcTemplate provides a lightweight, flexible API for performing these operations without the complexity of full ORM solutions.

In this tutorial, we’ll explore several practical approaches for inserting BLOB data using JdbcTemplate.

2. Introduction to the Problem

Storing binary data in a relational database is straightforward with the right JDBC APIs.

However, depending on file size, database vendor, and driver specifics, the insertion strategy can vary. For small or medium BLOBs, setting a byte array directly is enough. For large content, using a stream is more memory-efficient. And in some scenarios, we may need Spring’s SqlLobValue and LobHandler. In addition, we can use Spring’s SqlBinaryValue to solve the problem.

As always, we’ll explore these approaches by example. So, let’s first set up an in-memory H2 database in a Spring Boot application. Then, we’ll walk through each option using simple, focused JUnit test cases.

For simplicity, we’ll skip the database and Spring configurations in this tutorial.

So now, let’s create a SQL script create-document-table.sql containing the table creation statement:

CREATE TABLE DOCUMENT
(
    ID INT PRIMARY KEY,
    FILENAME   VARCHAR(255) NOT NULL,
    DATA       BLOB
);

As we can see, in the DOCUMENT table, the column DATA’s type is BLOB. We’ll use the DOCUMENT table throughout our examples.

To keep our test environment clean, we also define a matching teardown script drop-document-table.sql:

DROP TABLE DOCUMENT;

We’ll use these two scripts in @Sql annotations:

@Sql(value = "/com/baeldung/spring/jdbc/blob/create-document-table.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
@Sql(value = "/com/baeldung/spring/jdbc/blob/drop-document-table.sql", executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
@SpringBootTest(classes = InsertBlobUsingJdbcTemplateApplication.class)
@TestPropertySource(locations = { "classpath:com/baeldung/spring/jdbc/blob/application.properties" })
class InsertBlobUsingJdbcTemplateUnitTest {
    @Autowired
    private JdbcTemlate jdbcTemplate;
    private static final String CONTENT = "I am a very very long content.";
}

As the code shows, we put two @Sql annotations on our test class. By running our table-creation script before each test method and the drop script afterward, we ensure every test starts with a predictable, clean database schema. This keeps the test environment fully isolated and repeatable.

Also, we create the CONTENT constant to simulate the content of a file. Later, we’ll use a different approach to insert CONTENT’s value into the DOCUMENT table’s DATA column.

3. Using a byte Array

The first and most straightforward approach is to use PreparedStatement.setBytes(). In this method, we convert our content into a byte[] and directly store it in the database. This technique is ideal when the size of the BLOB is small enough to comfortably fit into memory.

Next, let’s see how it works:

byte[] bytes = CONTENT.getBytes(StandardCharsets.UTF_8);

jdbcTemplate.update(
  "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
  1,
  "bigfile.txt",
  bytes
);

byte[] stored = jdbcTemplate.queryForObject("SELECT DATA FROM DOCUMENT WHERE ID = 1", (rs, rowNum) -> rs.getBytes("data"));
assertEquals(CONTENT, new String(stored, StandardCharsets.UTF_8));

Because we pass the entire byte array at once, the database driver doesn’t need to stream the data. Instead, it simply writes the full array to the BLOB column. This results in minimal boilerplate code and works reliably across all major JDBC drivers.

As we can see, this approach is straightforward, highly readable, and suitable for many use cases, such as short text documents, small images, or any binary data under a few megabytes.

4. Using an InputStream

The second approach involves using a binary stream instead of loading all the bytes into memory. With PreparedStatement.setBinaryStream(), we hand over an InputStream to the JDBC driver, which then streams the data directly into the BLOB field.

This technique is especially useful when:

• The file is large (for example, tens or hundreds of megabytes)
• The content originates as a stream (for instance, file uploads, network streams, memory-efficient pipelines)
• We want to avoid high memory consumption

Next, let’s see this approach in action:

InputStream stream = new ByteArrayInputStream(CONTENT.getBytes(StandardCharsets.UTF_8));

jdbcTemplate.update(
  "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
  2,
  "bigfile.txt",
  stream
);

byte[] stored = jdbcTemplate.queryForObject("SELECT DATA FROM DOCUMENT WHERE ID = 2", (rs, rowNum) -> rs.getBytes("data"));
assertEquals(CONTENT, new String(stored, StandardCharsets.UTF_8));

Our test simulates this by creating an InputStream over the content and passing it to the insert operation.

Streaming is more scalable because it prevents our application from holding large arrays in memory. Instead, we pass the stream and let the database driver manage the read-and-write operations incrementally.

This option is handy when the data source itself is already a stream, for example, files uploaded via REST endpoints.

5. Using Spring’s SqlLobValue and LobHandler (Deprecated in Spring 6.2)

Before Spring 6.2, another way to insert BLOB data in a database was to use SqlLobValue together with a LobHandler. Spring has provided different LobHandler implementations, most commonly DefaultLobHandler, to abstract vendor-specific LOB operations.

Although this approach still works, both SqlLobValue and LobHandler are now deprecated in Spring 6.2, replaced by the newer SqlBinaryValue API. Still, many existing Spring applications use this pattern, and it remains useful to understand. By the way, we’ll explore the SqlBinaryValue approach later.

Next, let’s see an example of how SqlLobValue and LobHandler do the job:

byte[] bytes = CONTENT.getBytes(StandardCharsets.UTF_8);

jdbcTemplate.update(
  "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
  new Object[] { 3, "bigfile.txt", new SqlLobValue(bytes, new DefaultLobHandler()) },
  new int[] { Types.INTEGER, Types.VARCHAR, Types.BLOB }
);

byte[] stored = jdbcTemplate.queryForObject("SELECT DATA FROM DOCUMENT WHERE ID = 3", (rs, rowNum) -> rs.getBytes("DATA"));
assertEquals(CONTENT, new String(stored, StandardCharsets.UTF_8));

In the example, we pass a SqlLobValue instance as the parameter and supply the SQL types separately.

6. Using Spring’s SqlBinaryValue

Since Spring 6.2, using SqlBinaryValue to insert binary data is preferred over the deprecated SqlLobValue / LobHandler APIs.

SqlBinaryValue integrates directly with Spring’s JDBC parameter framework, meaning we wrap it inside a SqlParameterValue along with the appropriate SQL type:

byte[] bytes = CONTENT.getBytes(StandardCharsets.UTF_8);

jdbcTemplate.update(
  "INSERT INTO DOCUMENT (ID, FILENAME, DATA) VALUES (?, ?, ?)",
  4,
  "bigfile.txt",
  new SqlParameterValue(Types.BLOB, new SqlBinaryValue(bytes))
);

byte[] stored = jdbcTemplate.queryForObject("SELECT DATA FROM DOCUMENT WHERE ID = 4", (rs, rowNum) -> rs.getBytes("DATA"));
assertEquals(CONTENT, new String(stored, StandardCharsets.UTF_8));

In this example, SqlBinaryValue wraps the raw byte[] and provides the driver with a clean binary representation. We additionally wrap it in SqlParameterValue, which lets us specify the JDBC type (Types.BLOB).

7. Conclusion

In this article, we explored different practical techniques for inserting BLOB data using Spring’s JdbcTemplate. By walking through focused JUnit test cases, we demonstrated how each approach works in isolation.

By understanding these options, we can choose the right strategy for our specific database and application requirements.

As always, the complete source code for the examples is available over on GitHub.