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

---

---

In this video tutorial we will learn how to use JDBC to read and write BLOB with MySQL.

---

Transcript

Time - 00:00

Time - 00:28
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 MySQL and they have BLOB support.

Time - 00:48
On this slide we will learn how to create a BLOB column. So when we create a table in MySQL we add a column with the BLOB data type. On this slide I want to learn how to write a BLOB to a database. 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 PDF file. Let's walk through the code.

Time - 01:17

Time - 01:54

Time - 02:22

Time - 02:47

Time - 03:10
Now let's move over to MySQL tool and verify this. I'll take a look at the employees there in the database right now. l’ll run this query select * from employees and I'll get a list of the actual employees. Notice here for John Doe there’s an entry here for a resume, there's a BLOB entry. Note all the other entries are null. So we have a BLOB entry here. I can select this entry. I can right click. I can say, “Open Value in Editor” and it’s going to show me this BLOB. Again, it's a binary large object so we see all this binary data. Actually it should be displayed as hex.

Time - 03:50
Instead of looking at it in this fashion there's a tab here called “Image.” 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. This is the actual PDF document for this employee, John Doe. This looks really good. We know that we were successful in storing the BLOB in the actual database.

Time - 04:18
Now that we know how to write BLOBs in the database let's learn how to read a BLOB from the database. In this example I'm going to read the employee’s resume from the database as a BLOB and then store it as a file on my local files system. Let's walk through the code.

[snip] .... see the Transcripts tab for details.

[wyndhamkeith]

Hello! After executing the writeBlobDemo
I did this
right click resume under John Doe, marked as BLOB,
open VALUE editor,

I can only see the Binary & Text tab only...

while yours IMAGE is available

[OmarAbdelaziz__]

Thank you man, Thank you for everything you give.
don't stop spreading knowledge please

[kush]

All I see here is clutch programming. I'm a doing an adaptation of SpringMVC(with proper structure).
Keep up the clutchness and the good work.

[rajatambastha]

Hi, if a method is returning a XSSFWorkbook and I don't have access to a physical space (system storage) to save that file. I need to save that file into database, how can I do that?
Thanks

[clopes]

MySQL Workbench version 8.0 .17 does not seem to have the Image tab in the editor.

[MasterSpencer]

Amazing video! Thank you so much, was exactly what I was looking for. So professional and well organized

[RealPlgue]

Thank You. Really Helped a lot, I've tried a lot of methods but this one what worked for me.

[shubhamscreation]

Sir pdf file is inserted into database but in mysql workbench i am not able to read it i mean it is not in human readable form not able to see it in image form what to do

[violetanderson]

Very very very useful! Thanks a bunch!

[clopes]

In this tutorial, I had to use the absolute path in `File theFile = new File("sample_resume.pdf")` using \\ between each directory as an escape sequence. Is there a way to set the path somewhere in the code to make it look cleaner? Where does the program start its path search from when it runs?
EDIT: i copied the 'sample_resume.pdf' into the project folder, which fixed the ability to use a relative path. :)

[adamellgood]

Hey, great video! This has really helped me with understanding the concept of uploading and retrieving files from a database. Thankyou

I am trying to make it graphical where a user logged in can select the file they wish to upload to the database.

I imagine I would have to allow the user to identify their self through a username into a text field. I imagine I need the user to identify the absolute file path? That way on a button, the user will write their file to their record in the database.

How can I go about doing this? Any chance of some guidance and/or source code?

Thanks again, Adam.

[SilveraStarbreeze]

Another great video! Is it possible to open a .pdf file directly (I don't want to save it to disk) from blob in mysql, by clicking on a specific row in java jTable? Thank you! :D

[CybersecurityandCigars]

I appreciate the tutorial. Would I be able to tailor reading and writing BLOBs to SQLite using your general format? I've always been a bit slow with Java. Thank you.

[GiuseppeCanto]

Hi, thank you for sharing this content with a lesson so smoothly.

[silentass]

I got the error, " Invalid escape sequence (valid ones are \b \t \n \f \r \" \' \\ )"
when specifying the complete path to the sample_resume file... can u tell me how to resolve this?

[angelodebruyne]

You've got the best tutorials! Really good work :)

[filipnedovic]

Program works the same with direct writing to file from ResultSet with getBytes function -> out.write(rs.getBytes("resume"));,
without this peace of code:
InputStream in = rs.getBinaryStream("reusme");

byte[] buffer = new byte[1024];

if(in.read(buffer)>0) {
out.write(buffer);
}

Q: Is there a specific reason why you do this with peace of code above?

[ruqaiyakurawadwala]

hey, how to read the blob from the mysql database and and then display it in a jframe? is it possible?

[ayaan]

I am getting the following error in write BLOB 
Exception in thread "main" Method com/mysql/jdbc/
is abstract
        at
Statement.java)

[njunior]

Professor, I'm also getting the ' sample_resume.pdf (No such file or directory)' exception

Is this what I have to do to fix it in the Java code?

File file = new

That's the path to my Mac desktop...