In this video we will learn how to use JDBC to read and write BLOBs with MySQL

Please SUBSCRIBE to this channel

Download Java Source Code

—

Transcript

Time – 00:00
Hi, this is Chad (Shod) with luv2code.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 SQL script that will create the table and add sample data for you. You can download it from the link below. The file is sql/table-setup.sql.

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
At the beginning I’ll set up a SQL statement. I’ll say update employees set resume equals to question mark where the email address equals john.doe@foo.com. We’re only going to make a change for this John Doe employee, set up a prepared statement, and then I go, we’re going to create a file that’s an actual handle to the local file sample_resume.pdf. Then I’ll 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 .executeUpdate. That will actually update the database with the binary data.

Time – 01:54
All right so let’s switch over to Eclipse and let’s look at a very simple demo. I have a program called WriteBlobDemo. In this program we’re going to actually read a resume from a local file system and write it as a BLOB to the database. I’ll walk through the code here. The first thing we do is we get a connection to the database. We prepare a statement. We’re going to update employees, set resume equals to question mark where email equals john.doe@foo.com. So we’re going to update the resume for John Doe.

Time – 02:22
Then I’ll move down, I’ll set up a file handle for this file sample_resume.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 actually do an execute update. This will actually store this binary file from the file system into the actual database column.

Time – 02:47
I just ran the application and here’s the output of the program. So beginning it says that it’s reading the file and gives me the full path to the file just for sanity sake, I know which file is actually reading. Then it tells me that it’s storing the resume in the database for our employee john.doe@foo.com and then it says that it completed successfully. Excellent.

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.

Time – 04:33
At the beginning I have a SQL string where I’m going to select the resume from employees were email equals john.doe@foo.com. On the next line I’ll actually execute the query and then I’ll set up a file handle for the actual output file. 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.

Time – 04:58
Then I’ll process the results, so I’ll basically move to the next row and I’ll grab an input stream on that column resume. I’ll say myRs.getBinaryStream on resume. That’s the handle to that BLOB column. Then I’ll basically take it and now this was normal Java file IO. 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, so the output is a handle to the actual output file resume_from_db.pdf. I’ll keep doing it while I read all the buffer streams and eventually I’ll have the file completed.

Time – 05:34
All right, so let’s switch back to Eclipse and let’s see this in action. This file here called ReadBloblDemo.java basically we’re going to read the resume from the database and store it as a file on the local file system. 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@foo.com. We execute the query. Then I set up a file handle for an output file name and I’ll call it resume_from_db.pdf. Then I grab that result set and I say input equals myRS.getBinaryStream. 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.

Time – 06:24
Once I have this input stream then I take that and I write that out to the output file. I’ll just keep doing that until the file is actually completely written. We’ll say “Save to file” and complete it successfully.

Time – 06:37
I just ran the application and here’s the output from it. It said reading from database. It gives the query that it was using. john.doe@foo.com. It read all the information and then it saved it to a file to give the complete path for the file that saved it, and also that it completed successfully.

Time – 06:55
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. Here’s my directory. I have this file resume_from_db.pdf. I can double click on it. I’m going to bring it up in my preview. There it is, a standard PDF file that we can view, the same information that we had in the actual database. So we are successful.

Time – 07:20
Well that wraps up our discussion. We were successful in reading and writing BLOBs to the MySQL database. Please subscribe to our channel to view more videos on Java. Click the thumbs up to like our video. Also visit our website https://luv2code.com to download the Java source code used in this video.