Java 中将文件或 byte[] 存储为 SQL 二进制大对象 (Store and Load)_AI阅读总结 — 包阅AI

In many applications, you might need to store files or binary data directly in a database. PostgreSQL offers a data type called BLOB (Binary Large Object) that is suitable for storing binary data such as files or byte arrays. Let us delve to understand the process to store a file or byte array as a BLOB in a SQL database (PostgreSQL) using Java, and subsequently load it back.

1. Introduction

In PostgreSQL, a BLOB (Binary Large Object) is a data type that allows you to store large amounts of binary data in a database. This type is typically used to store files, images, videos, or any data that is not inherently text. Although PostgreSQL doesn’t have a dedicated BLOB type, it offers similar functionality through the BYTEA data type. The BYTEA type can store binary strings of varying lengths, making it suitable for storing large files as binary data directly within a database column. Using BLOBs is particularly useful when you need to keep binary data closely integrated with other relational data within your database.

1.1 Setting up a database on Docker

Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run postgresql.

-- Remember to change the password –docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres-- command to stop the Postgres docker container --docker stop postgres-- command to remove the Postgres docker container --docker rm postgres

Remember to enter the password of your choice. If everything goes well the postgresql database server will be up and running on a port number – 5432 and you can connect with the Dbeaver GUI tool for connecting to the server.

1.2 Table Design

To create a table in PostgreSQL that includes a BLOB column, you can use the following SQL statement. This table is designed to store files, where each file has an associated name and the actual file data is stored as a BLOB using the BYTEA data type.

CREATE TABLE files (    id SERIAL PRIMARY KEY,    name VARCHAR(255) NOT NULL,    data BYTEA NOT NULL);

• id: An auto-incrementing primary key to uniquely identify each record.
• name: A column to store the name of the file. It is defined as VARCHAR(255), meaning it can store up to 255 characters.
• data: A BYTEA column used to store the binary data of the file.

2. Saving a File as BLOB

To save a file as a BLOB in PostgreSQL using Java, we will use the following steps:

• Connect to the PostgreSQL database.
• Create a table with a BLOB column.
• Use a PreparedStatement to insert the file into the BLOB column.

import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;public class StoreFileAsBlob {    public static void main(String[] args) {        String jdbcURL = "jdbc:postgresql://localhost:5432/yourdatabase";        String username = "yourusername";        String password = "yourpassword";        String filePath = "path/to/your/file.jpg";        String sql = "INSERT INTO files (name, data) VALUES (?, ?)";        try (Connection conn = DriverManager.getConnection(jdbcURL, username, password);             PreparedStatement pstmt = conn.prepareStatement(sql)) {            // Set the name of the file            pstmt.setString(1, new File(filePath).getName());            // Set the file as InputStream for the BLOB            InputStream inputStream = new FileInputStream(new File(filePath));            pstmt.setBinaryStream(2, inputStream);            // Execute the statement            int row = pstmt.executeUpdate();            if (row > 0) {                System.out.println("A file was inserted into the BLOB column.");            }        } catch (Exception ex) {            ex.printStackTrace();        }    }}

2.1 Code Example and Output

• Connection conn = DriverManager.getConnection(jdbcURL, username, password); – Establishes a connection to the PostgreSQL database.
• PreparedStatement pstmt = conn.prepareStatement(sql); – Prepares the SQL statement with placeholders for the file name and file data.
• pstmt.setString(1, new File(filePath).getName()); – Sets the name of the file in the first placeholder.
• pstmt.setBinaryStream(2, inputStream); – Converts the file into an InputStream and sets it in the second placeholder, which corresponds to the BLOB column.
• pstmt.executeUpdate(); – Executes the SQL statement, inserting the file into the database.

If the operation is successful, you will see the following output:

A file was inserted into the BLOB column.

3. Retrieving a BLOB from a Database

Retrieving a BLOB from the database involves reading the binary data and saving it back to a file. The process involves:

• Connect to the PostgreSQL database.
• Use a PreparedStatement to fetch the BLOB data.
• Read the BLOB data and write it to a file.

import java.io.FileOutputStream;import java.io.InputStream;import java.io.OutputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;public class RetrieveFileFromBlob {    public static void main(String[] args) {        String jdbcURL = "jdbc:postgresql://localhost:5432/yourdatabase";        String username = "yourusername";        String password = "yourpassword";        String sql = "SELECT name, data FROM files WHERE id=?";        try (Connection conn = DriverManager.getConnection(jdbcURL, username, password);             PreparedStatement pstmt = conn.prepareStatement(sql)) {            pstmt.setInt(1, 1); // Assuming we're fetching the BLOB with id 1            ResultSet rs = pstmt.executeQuery();            if (rs.next()) {                String fileName = rs.getString("name");                InputStream inputStream = rs.getBinaryStream("data");                String outputPath = "path/to/save/" + fileName;                OutputStream outputStream = new FileOutputStream(outputPath);                byte[] buffer = new byte[4096];                int bytesRead = -1;                while ((bytesRead = inputStream.read(buffer)) != -1) {                    outputStream.write(buffer, 0, bytesRead);                }                System.out.println("File saved to " + outputPath);                outputStream.close();                inputStream.close();            }        } catch (Exception ex) {            ex.printStackTrace();        }    }}

3.1 Code Example and Output

• PreparedStatement pstmt = conn.prepareStatement(sql); – Prepares the SQL statement with a placeholder for the file ID.
• pstmt.setInt(1, 1); – Sets the ID of the file to be retrieved (assuming ID 1 in this example).
• ResultSet rs = pstmt.executeQuery(); – Executes the query and fetches the result set containing the BLOB data.
• InputStream inputStream = rs.getBinaryStream("data"); – Retrieves the BLOB data as an InputStream.
• OutputStream outputStream = new FileOutputStream(outputPath); – Creates an OutputStream to write the BLOB data to a file.
• The loop while ((bytesRead = inputStream.read(buffer)) != -1) reads the BLOB data in chunks and writes it to the output file.

If the operation is successful, you will see the following output:

File saved to path/to/save/yourfile.jpg

4. Conclusion

Storing and retrieving files or binary data as BLOBs in PostgreSQL using Java is a straightforward process. This method is particularly useful when dealing with large files that need to be stored securely within a database. By using Java’s PreparedStatement for both saving and retrieving BLOBs, you can efficiently handle binary data in your applications.