Core Java >> JDBC

How to insert or update Blob data in database using JDBC

A Blob is not a normal data type in SQL which can be used as one used VARCHAR or NUMBER. A Blob is intended for storing binary data.

This data can be an image, video, song, word document, spreadsheet etc. When you open a JPEG image file in notepad or wordpad, only junk characters are shown and there is nothing much which can be read directly. Some form of encoders are required to read the binary data present in a Blob data type column. Usually Blob is used to store huge amount of data which doesn’t mean that it always has to be image or video. Even Strings can be converted to bytes (using string.toBytes() function) and stored as Blob.

Here are the methods which can be used to insert or update Blob data column in database (be it Oracle, MySQL or DB2)

Update Blob Data Method 1

String query = "Update table tab1 set blob_column=?";

and then using the prepared statement:

Connection connection = getConnection();

PreparedStatement pstmt = connection.prepareStatement(query);

pstmt.setBlob(1,blobData);

pstmt.executeUpdate();

Update Blob Data Method 2

String query = "Update table tab1 set blob_column=?";

and then using the prepared statement:

Connection connection = getConnection();

PreparedStatement pstmt = connection.prepareStatement(query);

pstmt.setBinaryStream(1,blobData.getBinaryStream(),(int)blobData.length);

pstmt.executeUpdate();

Update Blob Data Method 3

String query = "Select blob_column from tab1 where id='1'";
and then using the prepared statement:

Connection connection = getConnection();

PreparedStatement pstmt = connection.prepareStatement(query);
ResultSet rset = pstmt.executeQuery();
rset.next();
java.sql.Blob blobData = rset.getBlob("blob_column");
OutputStream out = blobData.setBinaryStream(1);
query = "Update table tab1 set blob_column=? where id='1'";
pstmt.setBinaryStream(1,blobData.getBinaryStream(),(int)blobData.length);

pstmt.executeUpdate();

Note: bolbData is an object of type java.sql.Blob. A Blob type variable can be created using the binary stream.

Usually there is some file which is to be written into the Blob column in database. An image or video file can be written by using a BinaryStream as shown below:

FileInputStream f = new FileInputStream(new File(""));
byte[] b=null;
f.read(b);
OutputStream out = new ByteArrayOutputStream();

Now one can use this OutputStream in order to write into Blob as shown in method 2 with the following modification:
stmt.setBinaryStream(1,out,fileLength);

Do share your views and experiences for update Blob data columns in a database.

Database Tutorials
How to insert or update Blob in Database using JDBC
Tips to increase Java application performance
Database Interview Questions for Java Developers

Comments

Leave Comment