SQLite Java: Write and Read BLOB

Summary: this tutorial shows you step by step how to update and select the BLOB from an SQLite database.

For the demonstration, we will use the materials table that we created in the creating table tutorial.

Suppose we have to store a picture for each material in the materials table. To do this, we add a column whose data type is BLOB to the materials table.

The following ALTER TABLE statement adds the picture column to the materials table.

ALTER TABLE materials ADD COLUMN picture blob;Code language: SQL (Structured Query Language) (sql)

Insert BLOB data into SQLite database

First, query data from the materials table to view its content:

SELECT
	id,
	description,
	picture
FROM
	materials;Code language: SQL (Structured Query Language) (sql)

The picture column is NULL.

Second, prepare a picture file and place it in a folder e.g., C:\temp as follows:

To update the picture column with the data from the picture file:

First, prepare an UPDATE statement.
Next, connect to the SQLite database to get the Connection object.
Then, create a PreparedStatement object from the Connection object.
After that, supply the values to the corresponding parameters using the set* methods of the PreparedStatement object.
Finally, execute the UPDATE statement by calling the executeUpdate() method of the PreparedStatement object.

Notice that the SQLiteJDBC driver does not implement the setBinaryStream() method, therefore, you must use the setBytes method instead.

The following readFile method reads a file and returns an array of bytes that we can pass to the setBytes method.

   /**
     * Read the file and returns the byte array
     * @param file
     * @return the bytes of the file
     */
    private byte[] readFile(String file) {
        ByteArrayOutputStream bos = null;
        try {
            File f = new File(file);
            FileInputStream fis = new FileInputStream(f);
            byte[] buffer = new byte[1024];
            bos = new ByteArrayOutputStream();
            for (int len; (len = fis.read(buffer)) != -1;) {
                bos.write(buffer, 0, len);
            }
        } catch (FileNotFoundException e) {
            System.err.println(e.getMessage());
        } catch (IOException e2) {
            System.err.println(e2.getMessage());
        }
        return bos != null ? bos.toByteArray() : null;
    }Code language: Java (java)

To connect to the test.db database, you use the connect() method as follows:

    /**
     * Connect to the test.db database
     *
     * @return the Connection object
     */
    private Connection connect() {
        // SQLite connection string
        String url = "jdbc:sqlite:C://sqlite/db/test.db";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }Code language: Java (java)

The following updatePicture() method updates a picture specified by the file name for a particular material.

/**
     * Update picture for a specific material
     *
     * @param materialId
     * @param filename
     */
    public void updatePicture(int materialId, String filename) {
        // update sql
        String updateSQL = "UPDATE materials "
                + "SET picture = ? "
                + "WHERE id=?";

        try (Connection conn = connect();
                PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {

            // set parameters
            pstmt.setBytes(1, readFile(filename));
            pstmt.setInt(2, materialId);

            pstmt.executeUpdate();
            System.out.println("Stored the file in the BLOB column.");

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }Code language: Java (java)

To update the picture for the material id 1, you use the following code:

package net.sqlitetutorial;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author sqlitetutorial.net
 */
public class BLOBApp {
    private Connection connect() {
        //...
    }
 
    private byte[] readFile(String file) {
       //...
    }
   
    public void updatePicture(int materialId, String filename) {
        //...
    }

     /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        BLOBApp app = new BLOBApp();
        app.updatePicture(1, "c:\\temp\\HP_Laptop.jpg");
    }
}
Code language: Java (java)

Note that we didn’t repeat the body of the methods that we already metioned.

Let’s execute the program and check the materials table again.

It works as expected.

Query BLOB data from SQLite database

The following steps show you how to query BLOB data from the SQLite database:

First, prepare a SELECT statement.
Next, create a Connection object by connecting to the SQLite database.
Then, create an instance of the PreparedStatement class from the Connection object. Use the set* method of the PreparedStatement object to supply values for the parameters.
After that, call the executeQuery method of the PreparedStatement object to get the ResultSet object.
Finally, loop through the result set, use the getBinaryStream() method to get data, and use the FileOutputStream object to save data into a file.

The following readPicture method selects the BLOB data of a specific material and saves it to a file.

/**
     * read the picture file and insert into the material master table
     *
     * @param materialId
     * @param filename
     */
    public void readPicture(int materialId, String filename) {
        // update sql
        String selectSQL = "SELECT picture FROM materials WHERE id=?";
        ResultSet rs = null;
        FileOutputStream fos = null;
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = connect();
            pstmt = conn.prepareStatement(selectSQL);
            pstmt.setInt(1, materialId);
            rs = pstmt.executeQuery();

            // write binary stream into file
            File file = new File(filename);
            fos = new FileOutputStream(file);

            System.out.println("Writing BLOB to file " + file.getAbsolutePath());
            while (rs.next()) {
                InputStream input = rs.getBinaryStream("picture");
                byte[] buffer = new byte[1024];
                while (input.read(buffer) > 0) {
                    fos.write(buffer);
                }
            }
        } catch (SQLException | IOException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }

                if (conn != null) {
                    conn.close();
                }
                if (fos != null) {
                    fos.close();
                }

            } catch (SQLException | IOException e) {
                System.out.println(e.getMessage());
            }
        }
    }Code language: Java (java)

For example, we can use the readPicture() method to get the BLOB data that we updated for the material id 1 and save it into a file named HP_Laptop_From_BLOB.jpg file.

app.readPicture(1, "c:\\temp\\HP_Laptop_BLOB.jpg");Code language: Java (java)

We execute the program and check the c:\\temp folder:

In this tutorial, you have learned how to update and select BLOB data in the SQLite database using SQLite JDBC driver.

Was this tutorial helpful ?