Store File or byte[] as SQL Blob in Java (Store and Load)

1. Overview

Files such as images, audio, video, documents, etc. can be stored in a database as Binary Large Object (BLOB). A BLOB is a SQL data type that can store large binary data as a single entity.

In this tutorial, we’ll learn how to store and retrieve BLOB data using Java Database Connectivity (JDBC) with an H2 in-memory database.

2. Example Setup

To begin, let’s add the h2 dependency to the pom.xml:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

Next, let’s create a schema named warehouses:

String sql = """
    CREATE TABLE IF NOT EXISTS warehouses (
    id INTEGER PRIMARY KEY,
    name text NOT NULL,
    capacity REAL,
    picture BLOB
);""";

Here, we create a table named warehouses with four columns. The fourth column is of type BLOB, which is suitable for saving binary objects like pictures, PDF files, etc.

Also, let’s set up the JDBC connection:

static Connection connect() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:h2:./test", "sa", "");
    return connection;
}

In this method, we create a Connection object to establish a connection with the database.

Finally, let’s initiate a create operation to create a table in the database:

try (Connection connection = connect(); Statement stmt = connection.createStatement()) {
    stmt.execute(sql);
}

In the code above, we execute our SQL query to create a table.

 3. Saving  a File as BLOB

We can store file content in the database by first converting it into a byte array and then inserting it or streaming the file content in chunks.

3.1. Converting File to a Byte Array

If we intend to store a small file, converting it to a byte array may be an efficient choice. However, this approach may not be suitable for huge files.

First, let’s write a method to convert a file to a byte array:

static byte[] convertFileToByteArray(String filePath) throws IOException {
    File file = new File(filePath);
    try (FileInputStream fileInputStream = new FileInputStream(file); 
      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
        byte[] buffer = new byte[1024];
        for (int len; (len = fileInputStream.read(buffer)) != -1; ) {
            byteArrayOutputStream.write(buffer, 0, len);
        }
        return byteArrayOutputStream.toByteArray();
    }
}

The method above accepts the file path as an argument which is passed to the File object. Next, we create FileInputStream and ByteArrayOutputStream objects to read the file and store bytes read from the file respectively.

Also, we read the file in chunks of 1024 bytes at a time. If the fileInputStream returns -1, the end of the file is reached.

Next, let’s insert a new record into the warehouses table:

boolean insertFile(int id, String name, int capacity, String picture) throws SQLException, IOException {
    String insertSql = """
        INSERT INTO warehouses(id,name,capacity,picture) VALUES(?,?,?,?)
        """;
    try (Connection conn = connect()) {
        if (conn != null) {
            PreparedStatement stmt = conn.prepareStatement(insertSql);
            stmt.setInt(1, id);
            stmt.setString(2, name);
            stmt.setDouble(3, capacity);
            stmt.setBytes(4, convertFileToByteArray(picture));
            stmt.executeUpdate();
            return true;
        }
    }
    return false;
}

Here, we establish a connection to the database and create a PreparedStatement object to set values for each column. The file is converted to a byte array and set as a BLOB data using the setBytes() method.

Let’s see a unit test to demonstrate inserting a new record using the insertFile() method:

@ParameterizedTest
@CsvSource({ "1, 'Liu', 3000", "2, 'Walmart', 5000" })
void givenBlobFile_whenInsertingTheBlobFileAsByteArray_thenSuccessful(
    int id, 
    String name, 
    int capacity
) throws SQLException, IOException {
    boolean result = jdbcConnection.insertFile(id, name, capacity, TEST_FILE_PATH);
    assertTrue(result);
}

In the code above, we add two new records to the warehouses table. Finally, we assert that the operation returns true.

3.2. Saving the File as a Stream

When dealing with large files, converting the entire file to a byte array before saving it to the database may not be efficient. In such cases, we can save the file in chunks using a streaming approach.

Here’s an example that saves a file into the database in chunks:

boolean insertFileAsStream(
    int id, 
    String name, 
    int capacity, 
    String filePath
) throws SQLException, IOException {
    String insertSql = """
        INSERT INTO warehouses(id,name,capacity,picture) VALUES(?,?,?,?)
        """;
    try (Connection conn = connect()) {
        if (conn != null) {
            PreparedStatement stmt = conn.prepareStatement(insertSql);
            stmt.setInt(1, id);
            stmt.setString(2, name);
            stmt.setDouble(3, capacity);
            File file = new File(filePath);
            try (FileInputStream fis = new FileInputStream(file)) {
                stmt.setBinaryStream(4, fis, file.length());
                stmt.executeUpdate();
                return true;
            }
        }
    }
    return false;
}

In the code above, instead of converting the picture file to a byte array, we pass it to the FileInputStream object to stream the file contents directly into the database without loading the entire file into memory. This is more efficient for large files because it reduces OutOfMemoryErrors.

4. Retrieving a BLOB from a Database

Next, let’s look at how we can read binary data from the database as an input stream and write it directly to a file output stream.

Here’s a method that retrieves a record with BLOB data from the database:

static boolean writeBlobToFile(
    String query, 
    int paramIndex, 
    int id, 
    String filePath
) throws IOException, SQLException {
    try (
        Connection connection = connect(); 
        PreparedStatement statement = connection.prepareStatement(query)
    ) {
        statement.setInt(paramIndex, id);
        try (
            ResultSet resultSet = statement.executeQuery(); 
            FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath))
        ) {
            while (resultSet.next()) {
                InputStream input = resultSet.getBinaryStream("picture");
                byte[] buffer = new byte[1024];
                int bytesRead;
                while ((bytesRead = input.read(buffer)) > 0) {
                    fileOutputStream.write(buffer, 0, bytesRead);
                }
            }
            return true;
        }
    }
}

In the code above, we retrieve a BLOB from the database and write it to a file. Also, we create a PreparedStatement object to execute a query, retrieve the BLOB as a binary stream from the ResultSet, and read each byte in chunks of 1024 bytes.

Then, we ensure the read operation returns the number of bytes read, which may be less than the buffer size.

5. Conclusion

In this article, we learned how to write binary files into the database as a byte array. We also saw how to write a large file using a stream. Furthermore, we learned how to retrieve a blob file from the database and write it to a file.