{"id":3510,"date":"2015-05-17T05:43:55","date_gmt":"2015-05-17T13:43:55","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3510"},"modified":"2024-01-11T02:27:52","modified_gmt":"2024-01-11T09:27:52","slug":"writing-and-reading-mysql-blob-using-jdbc","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/writing-and-reading-mysql-blob-using-jdbc\/","title":{"rendered":"Writing and Reading MySQL BLOB Using JDBC"},"content":{"rendered":"\n

Summary<\/strong>:\u00a0in this tutorial, you will learn how to write and read MySQL BLOB data using JDBC.<\/p>\n\n\n\n

This tutorial picks up where the Calling MySQL Stored Procedures from the JDBC<\/a> tutorial left off.<\/p>\n\n\n\n

Adding a BLOB column to the candidates table<\/h2>\n\n\n\n

We’ll use the candidates<\/code> table in the mysqljdbc<\/code> database for the demonstration.<\/p>\n\n\n\n

First, connect to the MySQL server<\/a>:<\/p>\n\n\n

mysql -u root -p<\/code><\/span><\/pre>\n\n\n
Second, show the structure of the candidates<\/code> table:<\/p>\n\n\n
+------------+--------------+------+-----+---------+----------------+\n| Field      | Type         | Null<\/span> | Key | Default<\/span> | Extra          |\n+------------+--------------+------+-----+---------+----------------+\n| id         | int          | NO   | PRI | NULL<\/span>    | auto_increment |\n| first_name | varchar(50<\/span>)  | NO   |     | NULL<\/span>    |                |\n| last_name  | varchar(50<\/span>)  | NO   |     | NULL<\/span>    |                |\n| dob        | date         | NO   |     | NULL<\/span>    |                |\n| phone      | varchar(20<\/span>)  | YES  |     | NULL<\/span>    |                |\n| email      | varchar(100<\/span>) | YES  |     | NULL<\/span>    |                |\n+------------+--------------+------+-----+---------+----------------+\n6<\/span> rows in set (0.03<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
ALTER<\/span> TABLE<\/span> candidates \nADD<\/span> COLUMN<\/span> resume<\/span> LONGBLOB\nNULL<\/span> AFTER<\/span> email;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, display the structure of the candidates<\/code> table to verify the change:<\/p>\n\n\n
+------------+--------------+------+-----+---------+----------------+\n| Field      | Type         | Null<\/span> | Key | Default<\/span> | Extra          |\n+------------+--------------+------+-----+---------+----------------+\n| id         | int          | NO   | PRI | NULL<\/span>    | auto_increment |\n| first_name | varchar(50<\/span>)  | NO   |     | NULL<\/span>    |                |\n| last_name  | varchar(50<\/span>)  | NO   |     | NULL<\/span>    |                |\n| dob        | date         | NO   |     | NULL<\/span>    |                |\n| phone      | varchar(20<\/span>)  | YES  |     | NULL<\/span>    |                |\n| email      | varchar(100<\/span>) | YES  |     | NULL<\/span>    |                |\n| resume     | longblob     | YES  |     | NULL<\/span>    |                |\n+------------+--------------+------+-----+---------+----------------+\n7<\/span> rows in set (0.05<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
We’ll read data from a PDF file and insert it into the resume<\/code> column.<\/p>\n\n\n\n
Writing BLOB data to MySQL database<\/h2>\n\n\n\n
The following defines addResume()<\/code> method that reads data from a PDF file and inserts it into the resume<\/code> column of the candidates<\/code> table:<\/p>\n\n\n
import<\/span> java.io.File;\nimport<\/span> java.io.FileInputStream;\nimport<\/span> java.io.FileNotFoundException;\nimport<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;\nimport<\/span> java.util.ArrayList;\n\npublic<\/span> class<\/span> Candidate<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> addResume<\/span>(int<\/span> candidateId, String filename)<\/span> <\/span>{\n        var<\/span> sql = \"UPDATE candidates SET resume = ? WHERE id=?\"<\/span>;\n\n        try<\/span> (var<\/span> conn = MySQLConnection.connect();\n             var<\/span> stmt = conn.prepareStatement(sql)) {\n\n            \/\/ read the file<\/span>\n            var<\/span> file = new<\/span> File(filename);\n            var<\/span> input = new<\/span> FileInputStream(file);\n\n            \/\/ set parameters<\/span>\n            stmt.setBinaryStream(1<\/span>, input);\n            stmt.setInt(2<\/span>, candidateId);\n\n            \/\/ store the resume file in database<\/span>\n            stmt.executeUpdate();\n\n        } catch<\/span> (SQLException | FileNotFoundException e) {\n            e.printStackTrace();\n        }\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The following calls the addResume()<\/code> method to write binary data from C:\\temp\\resume.pdf<\/code> file and insert it into the resume<\/code> column of the candidates<\/code> table for the id 1:<\/p>\n\n\n
public<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span> <\/span>{\n       Candidate.addResume(1<\/span>, \"C:\/temp\/resume.pdf\"<\/span>);\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
If you execute the program query the candidates<\/code> table, you’ll see the BLOB column updated:<\/p>\n\n\n
SELECT<\/span> id<\/span>, first_name, LENGTH<\/span>(resume<\/span>)\nFROM<\/span> candidates \nWHERE<\/span> id<\/span> = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+------------+----------------+\n| id | first_name | length(resume) |\n+----+------------+----------------+\n|  1 | Carine     |          23817 |\n+----+------------+----------------+\n1 row in set (0.01 sec)<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
This query retrieves the length of the resume<\/code> column for the row id 1.<\/p>\n\n\n\n
Reading BLOB data from MySQL database<\/h2>\n\n\n\n
The following defines the getResume()<\/code> method that retrieves BLOB data from the resume<\/code> column and writes it to a file:<\/p>\n\n\n
import<\/span> java.io.File;\nimport<\/span> java.io.FileInputStream;\nimport<\/span> java.io.FileOutputStream;\nimport<\/span> java.io.FileNotFoundException;\nimport<\/span> java.io.IOException;\nimport<\/span> java.sql.SQLException;\nimport<\/span> java.sql.Date;\nimport<\/span> java.sql.Statement;\nimport<\/span> java.util.ArrayList;\n\npublic<\/span> class<\/span> Candidate<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> getResume<\/span>(int<\/span> candidateId, String filename)<\/span><\/span>{\n        \/\/ update sql<\/span>\n        var<\/span> sql = \"SELECT resume FROM candidates WHERE id=?\"<\/span>;\n\n        try<\/span> (var<\/span> conn = MySQLConnection.connect();\n             var<\/span> stmt = conn.prepareStatement(sql)) {\n            \/\/ set parameter<\/span>\n            stmt.setInt(1<\/span>, candidateId);\n            var<\/span> file = new<\/span> File(filename);\n\n            try<\/span>(var<\/span> rs = stmt.executeQuery();\n                var<\/span> output = new<\/span> FileOutputStream(file)){\n                \/\/ write binary stream into file<\/span>\n                while<\/span> (rs.next()) {\n                    var<\/span> input = rs.getBinaryStream(\"resume\"<\/span>);\n                    byte<\/span>[] buffer = new<\/span> byte<\/span>[1024<\/span>];\n                    while<\/span> (input.read(buffer) > 0<\/span>) {\n                        output.write(buffer);\n                    }\n                }\n            } catch<\/span> (IOException e){\n                e.printStackTrace();\n            }\n        } catch<\/span> (SQLException  e) {\n            e.printStackTrace();\n        }\n    }\n    \/\/ ...<\/span>\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows how to retrieve BLOB data from the resume column of the candidates<\/code> table and write it to a file in the C:\/temp\/resume_blob.pdf<\/code> file:<\/p>\n\n\n
public<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span><\/span>{\n        Candidate.getResume(1<\/span>,\"C:\/temp\/resume_blob.pdf\"<\/span>);\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
If you execute the program, you’ll see a new file called resume_blob.pdf<\/code> created in the c:\/temp<\/code> directory. <\/p>\n\n\n\n
The resume_blob.pdf<\/code> file will be the same as the one that we used to insert into the resume<\/code> column.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful? <\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Third, add a new column<\/a> called resume<\/code> to the candidates<\/code> table:<\/p>\n\n\n