In this tutorial, we’ll learn about using MySQL BLOB using JDBC. Blob stands for Binary Large Object, basically, a BLOB is an object data type used for storing large files such as audio, video, images, etc. In SQL server, BLOB can store up to 4 gigabytes. Although BLOBs are not universal to databases, MySQL supports BLOB. There are four data types of BLOB in MySQL: TINYBLOB, BLOB, MEDIUMBLOB, and LARGEBLOB.
Also read: MySQL INT Datatype
Let’s see a brief description of all four BLOB datatype:
- Tiny Blob can store 0 to 255 bytes.
- Blob can store from 0 to 65535 bytes.
- Medium Blob can store 0 to 16777215 bytes.
- Large Blob can store 0 to 4294967295 bytes.
The syntax of BLOB datatype using CREATE TABLE:
CREATE TABLE tablename(
field1 datatype,
field2 BLOB,
field3 TINYBLOB,
field4 LARGEBLOB,
field5 MEDIUMBLOB
);Code language: SQL (Structured Query Language) (sql)The syntax of BLOB datatype using ALTER TABLE:
ALTER TABLE tablename ADD fieldname BLOB;You can use as many BLOB fields as you want in a table.
Example of MySQL BLOB
In this section, we will see an example of how to create a BLOB variable using MySQL. Let’s create a student table with fields such as first name, last name, contact number, standard, section, class teacher, and certificate. The certificate will be a BLOB data type.
Let’s see the code:
CREATE TABLE student (
fname varchar(30),
lname varchar(30),
contact int(10),
standard int(2),
section varchar(2),
teacher varchar(30),
certificate BLOB);Code language: SQL (Structured Query Language) (sql)Using the above SQL table, we’ll be moving forward with our next section.
How to read and write MySQL BLOB data using JDBC
In this section, we’ll go through how we can read and write over MySQL BLOBS using JDBC, to follow along with this section execution of the student table is necessary.
Write MySQL BLOB using JDBC
We can write a MySQL BLOB using JDBC, for that will take a simple example for better understanding. Let’s take the student table that we have just created. We know that the certificate field is a BLOB datatype, which means it can store files like audio, video, images, text file, etc. So in the certificate column, we’ll insert .pdf files. For this section’s understanding, you can use any PDF file.
Let’s see the code for the same:
import java.io.*;
import java.sql.*;
public class WriteStudentBlobExample {
public static void main(String[] args) throws Exception {
Connection myConn = null;
PreparedStatement myStmt = null;
FileInputStream file = null;
try {
// 1. Create connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db", "student", "std");
// 2. Create Statement
myStmt = myConn.prepareStatement("INSERT INTO student (fname, lname, contact, standard, section, teacher, certificate)"
+"values(?, ?, ?, ?, ?, ?, ?)");
myStmt.setString(1, "Ridhima");
myStmt.setString(2, "Singh");
myStmt.setInt(3, 9876543210);
myStmt.setInt(4, 5);
myStmt.setString(5, "B");
myStmt.setString(6, "Radha");
// 3. Create Object for Certificate
File certificate = new File("certficate1.pdf");
input = new FileInputStream(certificate);
myStmt.setBinaryStream(7, certificate);
System.out.println("Reading input file: " + certificate.getAbsolutePath());
// 4. Execute statement
System.out.println("\nStoring resume in database: " + theFile);
System.out.println(sql);
myStmt.executeUpdate();
System.out.println("\nCompleted successfully!");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
if (input != null) {
input.close();
}
myConn.close();
}
}
}
Code language: Java (java)Read MySQL BLOB using JDBC
In the last section we saw how to write a BLOB using JDBC in MySQL, now we’ll learn how to read a MySQL BLOB field using JDBC. In this example, we’ll take a student table only, we’ll be writing a query that will return a certificate PDF of a student named Ridhima (we created this student in the last section). Let’s now see the step-by-step code for the same:
import java.io.*;
import java.sql.*;
public class ReadStudentBlobExample {
public static void main(String[] args) throws Exception {
Connection myConn = null;
Statement myStmt = null;
ResultSet myRs = null;
InputStream input = null;
FileOutputStream output = null;
try {
// 1. Create connection to database
myConn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/db", "student", "std");
// 2. Create and execute statement
myStmt = myConn.createStatement();
String sql = "SELECT certificate FROM student WHERE fname = 'Ridhima'";
myRs = myStmt.executeQuery(sql);
// 3. Get File
File certificate = new File("certificate.pdf");
output = new FileOutputStream(certificate);
//4. Execute result set
if (myRs.next()) {
input = myRs.getBinaryStream("certificate");
System.out.println("Reading certificate from database...");
System.out.println(sql);
byte[] buffer = new byte[1024];
while (input.read(buffer) > 0) {
output.write(buffer);
}
System.out.println("Saved to location: " + certificate.getAbsolutePath());
System.out.println("Completed successfully!");
}
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (input != null) {
input.close();
}
if (output != null) {
output.close();
}
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
}
Code language: Java (java)Conclusion
In this tutorial, we learned a lot about JDBC MySQL BLOB, various types of MySQL BLOB, and created a BLOB. We learned to use Java programming language to write or read a BLOB. We saw several examples of creating a BLOB, and reading and writing it as well. This tutorial will help you to get started with MySQL BLOB and you can incorporate them into your applications using Java, Spring boot, etc.
See Also: The official Documentation for more details.