Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.mysqltutorial.org/mysql-jdbc-tutorial/writing-and-reading-mysql-blob-using-jdbc/ below:

Reading and Writing MySQL BLOB Using JDBC

Summary: in this tutorial, you will learn how to write and read MySQL BLOB data using JDBC.

This tutorial picks up where the Calling MySQL Stored Procedures from the JDBC tutorial left off.

We’ll use the candidates table in the mysqljdbc database for the demonstration.

First, connect to the MySQL server:

mysql -u root -p

Second, show the structure of the candidates table:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)  | NO   |     | NULL    |                |
| last_name  | varchar(50)  | NO   |     | NULL    |                |
| dob        | date         | NO   |     | NULL    |                |
| phone      | varchar(20)  | YES  |     | NULL    |                |
| email      | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
6 rows in set (0.03 sec)Code language: PHP (php)

Third, add a new column called resume to the candidates table:

ALTER TABLE candidates 
ADD COLUMN resume LONGBLOB
NULL AFTER email;Code language: SQL (Structured Query Language) (sql)

Finally, display the structure of the candidates table to verify the change:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(50)  | NO   |     | NULL    |                |
| last_name  | varchar(50)  | NO   |     | NULL    |                |
| dob        | date         | NO   |     | NULL    |                |
| phone      | varchar(20)  | YES  |     | NULL    |                |
| email      | varchar(100) | YES  |     | NULL    |                |
| resume     | longblob     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.05 sec)Code language: PHP (php)

We’ll read data from a PDF file and insert it into the resume column.

The following defines addResume() method that reads data from a PDF file and inserts it into the resume column of the candidates table:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;

public class Candidate {
    public static void addResume(int candidateId, String filename) {
        var sql = "UPDATE candidates SET resume = ? WHERE id=?";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql)) {

            
            var file = new File(filename);
            var input = new FileInputStream(file);

            
            stmt.setBinaryStream(1, input);
            stmt.setInt(2, candidateId);

            
            stmt.executeUpdate();

        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        }
    }
}Code language: Java (java)

The following calls the addResume() method to write binary data from C:\temp\resume.pdf file and insert it into the resume column of the candidates table for the id 1:

public class Main {
    public static void main(String[] args) {
       Candidate.addResume(1, "C:/temp/resume.pdf");
    }
}Code language: Java (java)

If you execute the program query the candidates table, you’ll see the BLOB column updated:

SELECT id, first_name, LENGTH(resume)
FROM candidates 
WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+------------+----------------+
| id | first_name | length(resume) |
+----+------------+----------------+
|  1 | Carine     |          23817 |
+----+------------+----------------+
1 row in set (0.01 sec)Code language: plaintext (plaintext)

This query retrieves the length of the resume column for the row id 1.

The following defines the getResume() method that retrieves BLOB data from the resume column and writes it to a file:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Date;
import java.sql.Statement;
import java.util.ArrayList;

public class Candidate {
    public static void getResume(int candidateId, String filename){
        
        var sql = "SELECT resume FROM candidates WHERE id=?";

        try (var conn = MySQLConnection.connect();
             var stmt = conn.prepareStatement(sql)) {
            
            stmt.setInt(1, candidateId);
            var file = new File(filename);

            try(var rs = stmt.executeQuery();
                var output = new FileOutputStream(file)){
                
                while (rs.next()) {
                    var input = rs.getBinaryStream("resume");
                    byte[] buffer = new byte[1024];
                    while (input.read(buffer) > 0) {
                        output.write(buffer);
                    }
                }
            } catch (IOException e){
                e.printStackTrace();
            }
        } catch (SQLException  e) {
            e.printStackTrace();
        }
    }
    
}Code language: Java (java)

The following shows how to retrieve BLOB data from the resume column of the candidates table and write it to a file in the C:/temp/resume_blob.pdf file:

public class Main {
    public static void main(String[] args){
        Candidate.getResume(1,"C:/temp/resume_blob.pdf");
    }
}Code language: Java (java)

If you execute the program, you’ll see a new file called resume_blob.pdf created in the c:/temp directory.

The resume_blob.pdf file will be the same as the one that we used to insert into the resume column.

Was this tutorial helpful?

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4