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 theConnection
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 theexecuteUpdate()
method of thePreparedStatement
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 theConnection
object. Use the set* method of thePreparedStatement
object to supply values for the parameters. - After that, call the
executeQuery
method of thePreparedStatement
object to get theResultSet
object. - Finally, loop through the result set, use the
getBinaryStream()
method to get data, and use theFileOutputStream
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.