Java JDBC Tutorial – Part 10: BLOB - Reading and Writing BLOB with MySQL

Sharing buttons:

hi this is Shawn would love to koat.com

welcome back to another tutorial on Java

JDBC in this video we're going to learn

how to read and write blobs

for this tutorial we're going to use the

employees table I have a sequel script

that will create the table and add

sample data for you you can download it

from the link below the file is sequel

slash table set up that SQL

so what exactly is a blob well a blob is

basically binary data that we store in

the database you normally use blobs to

keep track of documents images audio or

any other binary object that you have

note that not all databases have support

for blobs in this example we're going to

make use of my sequel and they have blob


on this slide we will learn how to

create a blob column so when we create a

table in my sequel we add a column with

the blob data type

so in this slide I want to learn how to

write a blob to a database so for this

example I'm going to add a resume for an

employee I'm going to read a local PDF

file and I'll take that file and update

the database with the binary data that's

the actual content of the PDF file so

let's walk through the code so at the

beginning I'll set up a sequel statement

I'll say update employees set resume

equals to question mark where the email

address equals John Doe ate food comm so

we're only going to make the change for

this John Doe employee set up a prepared

statement and then I go through and I

create a file that's an actual handle to

the local file sample resume PDF then I

set on my statement I'll set the binary

stream that first parameter for the

resume comma the input that's the input

stream I have for that file then I

actually call my statement execute

update and I don't actually update the

database with the binary data all right

so let's switch over to eclipse and

let's look at a very simple demo I have

a program called write blob demo and in

this program we're going to actually

read a resume from the local file system

and write it as a blob to the database

so I'll walk through the code here so

the first thing we do is we get a

connection to the database we prepare a

statement and so we're going to update

employees set resume equals two question

mark we're email equals John Doe at phu

kham so we're going to update the resume

for John Doe then I'll move down I'll

set up a file handle for this file

sample resume dot PDF I'll set up an

input stream on that file and then I'll

set that as the parameter for our binary

stream for our prepared statement then

I'll move through and I'll I should do

an execute update and this will actually

store this binary file from the file

system into the actual database column

ok so I just ran the application and

here's the output of the program so at

the beginning it says that it's reading

the file that gives me the full path to

the file just for sanity saying I know

which file is actually reading then it

tells me that it's storing the resume in

the database for employee John at John

Doe at phu kham and then it says that it

completed successfully excellent so now

let's move over to our my sequel tool

and verify the

so I'll take a look at the employees

that are in the database right now so

I'll run this query select star from

employees and I'll get a list of the

actual employees we'll notice here for

John Doe there's an entry here for

resume there's a blob entry

note all the other entries are null okay

so we have a blob entry here so I can

select this entry I can right click I

can say open value and editor and it's

going to show me this blob and so again

it's a binary large object so we see all

this binary data actually should being

displayed as hex instead of looking at


in this fashion there's a there's a tab

here called image and I can select this

tab and now it'll actually show me this

image or the actual data as a PDF that I

can view so this is the actual PDF

document for this employee John Doe so

this looks really good so we know that

we were successful in storing the blob

in the actual database now that we know

how to write blobs in the database let's

learn how to read a blob from the

database so in this example I'm going to

read the employees resume from the

database as a blob and then store it as

a file on my local file system so let's

walk through the code at the beginning I

have the sequel string where I'm going

to select the resume from employees

where email equals John Doe at phu kham

on the next line I'll actually execute

the query and then I'll set up a file

handle for the actual output file so I'm

going to give this an explicit name

resume from DB PDF just so I know it's a

unique file name I'll set up an output

stream for that file handle then I'll

process the result set so I'll basically

move to the next row and I'll grab an

input stream on that column resume so

I'll say my RS get binary stream on

resume so that's a handle to that blob

column then I'll basically take it and

now this is normal Java file i/o I'll

just set up a buffer

I'll read in those binary bytes with a

buffer size of 1024 and then I'll write

it to the output file so that handle to

the output is a handle to the actual

output file resume from

EBI PDF and I'll keep doing it while I

read all the buffer streams and

eventually I'll have the file complete

all right so let's switch back to clips

and let's see this in action I have this

file here called read blob demo Java

basically we're going to read the resume

from the database and store it as a file

on the local file system so we can walk

through the code here standard thing of

getting a connection to a database

select resume from employees where email

equals John Doe egg foo comm we execute

the query then I set up a file handle

for an output file name and I'll call it

resume from DB dot PDF then I grab that

result set and I say input equals my RS

get binary strings so I'm basically

reading that blob data from the database

and then I'll use this to store it to

the actual local file system

so once I have this input string then I

take that and I write that out to the

output file and I just keep doing that

until the file is actually completely

written will say save the file and

complete it successfully okay so I just

ran the application and here's the

output from it it said reading from

database gives the query that it was

using John Doe it phu kham it read all

the information and then it saved it to

a file they give the complete path for

the file that saved it and also that it

completed successfully ok so now what I

can do is actually go to my file system

and verify that it created that file and

take a look at it so here's my directory

I have this file resume from DB PDF I

can double click on it and I'll bring it

up in my preview and there it is a

standard PDF file that we can view the

same information that we had in the

actual database so we're successful well

that wraps up our discussion we were

successful in reading and writing blobs

to the my sequel database please

subscribe to our channel to view more

videos on Java click the thumbs up to

like our video also visit our website

love to cocom to download the java

source code used in this video