Store and Retrieve File Using JDBC

« Prev

What is BLOB?

  • The BLOB data type serves as a means to store extensive binary content including images and files as well as audio and video inside databases.
  • The JDBC framework includes methods to handle binary stream data through PreparedStatement and ResultSet objects.

Database Table Example (MySQL)

CREATE TABLE files (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    data LONGBLOB
);

Storing a File in the Database

Store File (Write Binary File into BLOB)

import java.io.*;
import java.sql.*;

public class FileStore {
    public static void main(String[] args) {
        String filePath = "C:/files/sample.pdf";  // Path to file

        try (
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password");
            PreparedStatement ps = con.prepareStatement("INSERT INTO files (name, data) VALUES (?, ?)");
            FileInputStream fis = new FileInputStream(filePath)
        ) {
            File file = new File(filePath);
            ps.setString(1, file.getName());
            ps.setBinaryStream(2, fis, (int) file.length());

            int rows = ps.executeUpdate();
            System.out.println("File stored successfully. Rows affected: " + rows);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Retrieving a File from the Database

Retrieve File (Read BLOB and Save to Disk)

 import java.io.*;
import java.sql.*;

public class FileRetrieve {
    public static void main(String[] args) {
        int fileId = 1;  // ID of the file to retrieve

        try (
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "password");
            PreparedStatement ps = con.prepareStatement("SELECT name, data FROM files WHERE id = ?");
        ) {
            ps.setInt(1, fileId);
            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                String fileName = rs.getString("name");
                InputStream is = rs.getBinaryStream("data");

                FileOutputStream fos = new FileOutputStream("C:/downloads/" + fileName);
                byte[] buffer = new byte[1024];
                int bytesRead = -1;

                while ((bytesRead = is.read(buffer)) != -1) {
                    fos.write(buffer, 0, bytesRead);
                }

                fos.close();
                is.close();

                System.out.println("File retrieved and saved as: " + fileName);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Note

  • When handling files bigger than 64KB in MySQL utilize the LONGBLOB data type.
  • Ensure that the file path exists and that it can be accessed with the correct read/write permissions.
  • When handling large files it is advisable to use streaming methods instead of loading the whole file into memory.
  • Always close streams to avoid memory leaks.
« Prev

Quickly Find What You Are Looking For

OnlineTpoint is a website that is meant to offer basic knowledge, practice and learning materials. Though all the examples have been tested and verified, we cannot ensure the correctness or completeness of all the information on our website. All contents published on this website are subject to copyright and are owned by OnlineTpoint. By using this website, you agree that you have read and understood our Terms of Use, Cookie Policy and Privacy Policy.