Write and Read MySQL BLOB/binary files Using JDBC

In previous couple of posts How to store file in MySQL database using JDBC and How to read files/CLOB Data from MySQL database using JDBC we learnt how to save text file or CLOB(character large object) data in MYSQL database and how read it back from database.In this post we will learn how to Write and Reade MySQL BLOB/binary files Using JDBC

Create a folder InputFiles in project home directory and dump all binary files(.doc,PDF,images,audio,video etc..)

Project structure in eclipse:

SQL Query to create storebinaryfile_table in MySQL database (jdbcdb schema)
DB.sql

CREATE TABLE `storebinaryfile_table` (
  `file_id` int(11) NOT NULL auto_increment,
  `file_name` varchar(45) NOT NULL,
  `file_size_in_kb` bigint(20) NOT NULL,
  `file_extension` char(30) NOT NULL,
  `file_content` longblob NOT NULL,
  PRIMARY KEY  (`file_id`)
) ;

CREATE TABLE `storebinaryfile_table` (

`file_id` int(11) NOT NULL auto_increment,

`file_name` varchar(45) NOT NULL,

`file_size_in_kb` bigint(20) NOT NULL,

`file_extension` char(30) NOT NULL,

`file_content` longblob NOT NULL,

PRIMARY KEY (`file_id`)

) ;

DBUtil.java class which is responsible to connect with MySQL database.

package com.infotech.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

SaveBinaryFilesInDBClientTest.java class uses to save four binary files(GST.pdf,java collection .jpg,JDBC Introduction.docx and JDBC Introduction.pptx) in MYSQL database.

package com.infotech.client;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import com.infotech.util.DBUtil;
/**
 * @author KK JavaTutorials
 *JDBC program to write or save binary data/BLOB data in database
 */
public class SaveBinaryFilesInDBClientTest {
	
	public static void main(String[] args) throws SQLException   {
		saveBinaryFilesInDatabase();
	}
	private static void saveBinaryFilesInDatabase() throws SQLException {
		String SQL="INSERT INTO storebinaryfile_table (file_name,file_size_in_kb,file_extension,file_content)VALUES(?,?,?,?)";
		Path dir = Paths.get("InputFiles");
		try(Stream<Path> list = Files.list(dir);Connection connection = DBUtil.getConnection();
				PreparedStatement ps = connection.prepareStatement(SQL)) {
			/*
			 *Below line belongs to JDK 1.8 API so make sure you are running this code on JDK 1.8.
			 *And you IDE pointing to compiler version 1.8
			 */
			List<Path> pathList = list.collect(Collectors.toList());
			System.out.println("Following files are saved in database..");
			for (Path path : pathList) {
				System.out.println(path.getFileName());
				File file = path.toFile();
				String fileName = file.getName();
				long fileLength = file.length();
				long fileLengthInKb=fileLength/1024;
				
				ps.setString(1, fileName);
				ps.setLong(2, fileLengthInKb);
				
				ps.setString(3, fileName.substring(fileName.lastIndexOf(".")+1));
				
				FileInputStream fis = new FileInputStream(file);
				ps.setBinaryStream(4, fis, fileLength);
				
				ps.addBatch();
			}
			System.out.println("----------------------------------------");
			int[] executeBatch = ps.executeBatch();
			for (int i : executeBatch) {
				System.out.println(i);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.List;

import java.util.stream.Collectors;

import java.util.stream.Stream;

import com.infotech.util.DBUtil;

/**

* @author KK JavaTutorials

*JDBC program to write or save binary data/BLOB data in database

public class SaveBinaryFilesInDBClientTest {

public static void main(String[] args) throws SQLException {

saveBinaryFilesInDatabase();

}

private static void saveBinaryFilesInDatabase() throws SQLException {

String SQL="INSERT INTO storebinaryfile_table (file_name,file_size_in_kb,file_extension,file_content)VALUES(?,?,?,?)";

Path dir = Paths.get("InputFiles");

try(Stream<Path> list = Files.list(dir);Connection connection = DBUtil.getConnection();

PreparedStatement ps = connection.prepareStatement(SQL)) {

*Below line belongs to JDK 1.8 API so make sure you are running this code on JDK 1.8.

*And you IDE pointing to compiler version 1.8

List<Path> pathList = list.collect(Collectors.toList());

System.out.println("Following files are saved in database..");

for (Path path : pathList) {

System.out.println(path.getFileName());

File file = path.toFile();

String fileName = file.getName();

long fileLength = file.length();

long fileLengthInKb=fileLength/1024;

ps.setString(1, fileName);

ps.setLong(2, fileLengthInKb);

ps.setString(3, fileName.substring(fileName.lastIndexOf(".")+1));

FileInputStream fis = new FileInputStream(file);

ps.setBinaryStream(4, fis, fileLength);

ps.addBatch();

}

System.out.println("----------------------------------------");

int[] executeBatch = ps.executeBatch();

for (int i : executeBatch) {

System.out.println(i);

}

} catch (IOException e) {

e.printStackTrace();

}

After running SaveBinaryFilesInDBClientTest.java program you will look below output on eclipse console:

Following files are saved in database..
GST.pdf
java collection .jpg
JDBC Introduction.docx
JDBC Introduction.pptx
----------------------------------------
1
1
1
1

Following files are saved in database..

GST.pdf

java collection .jpg

JDBC Introduction.docx

JDBC Introduction.pptx

----------------------------------------

If you query storebinaryfile_table in jdbcdb schema of MYSQL database.You will find all binary files are saved as below screen shot.

Now lets read these binary files from MySQL database and write into DownLoadFiles folder in project home directory.

DownloadBinaryFilesFromDBClientTest.java class uses to read all binary files from MYSQL database and writes in DownLoadFiles folder in project home directory .

NOTE:You have to create DownLoadFiles folder manually in project home directory(You can modify this program to automate folder creation) .

package com.infotech.client;

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.infotech.util.DBUtil;
/**
 * @author KK JavaTutorials
 *JDBC program to read binary data/BLOB data from database and write into local disk.
 */
public class DownloadBinaryFilesFromDBClientTest {
	
	public static void main(String[] args) throws SQLException   {
		downloadBinaryFilesFromDatabase();
	}
	
	private static void downloadBinaryFilesFromDatabase() throws SQLException {

		String SQL="SELECT *FROM storebinaryfile_table";
		try(Connection connection = DBUtil.getConnection();
				PreparedStatement ps = connection.prepareStatement(SQL);ResultSet 
				rs = ps.executeQuery()) {
			System.out.println("Following flies are downloaded from database..");
			while (rs.next()) {
				int fileId = rs.getInt("file_id");
				String fileName = rs.getString("file_name");
				long fileSizeInKb = rs.getLong("file_size_in_kb");
				String fileExtension = rs.getString("file_extension");
				System.out.println("File Id:"+fileId);
				System.out.println("File Name:"+fileName);
				System.out.println("File Size In KB:"+fileSizeInKb);
				System.out.println("File Extension:"+fileExtension);
				
				Blob blob = rs.getBlob("file_content");
				InputStream inputStream = blob.getBinaryStream();
				
				System.out.println("-----------------------------------");
				Files.copy(inputStream, Paths.get("DownLoadFiles/"+fileName));
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

package com.infotech.client;

import java.io.IOException;

import java.io.InputStream;

import java.nio.file.Files;

import java.nio.file.Paths;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.infotech.util.DBUtil;

/**

* @author KK JavaTutorials

*JDBC program to read binary data/BLOB data from database and write into local disk.

public class DownloadBinaryFilesFromDBClientTest {

public static void main(String[] args) throws SQLException {

downloadBinaryFilesFromDatabase();

}

private static void downloadBinaryFilesFromDatabase() throws SQLException {

String SQL="SELECT *FROM storebinaryfile_table";

try(Connection connection = DBUtil.getConnection();

PreparedStatement ps = connection.prepareStatement(SQL);ResultSet

rs = ps.executeQuery()) {

System.out.println("Following flies are downloaded from database..");

while (rs.next()) {

int fileId = rs.getInt("file_id");

String fileName = rs.getString("file_name");

long fileSizeInKb = rs.getLong("file_size_in_kb");

String fileExtension = rs.getString("file_extension");

System.out.println("File Id:"+fileId);

System.out.println("File Name:"+fileName);

System.out.println("File Size In KB:"+fileSizeInKb);

System.out.println("File Extension:"+fileExtension);

Blob blob = rs.getBlob("file_content");

InputStream inputStream = blob.getBinaryStream();

System.out.println("-----------------------------------");

Files.copy(inputStream, Paths.get("DownLoadFiles/"+fileName));

}

} catch (IOException e) {

e.printStackTrace();

}

After running Above client program you will look below output on eclipse console:

Following flies are downloaded from database..
File Id:1
File Name:GST.pdf
File Size In KB:760
File Extension:pdf
-----------------------------------
File Id:2
File Name:java collection .jpg
File Size In KB:51
File Extension:jpg
-----------------------------------
File Id:3
File Name:JDBC Introduction.docx
File Size In KB:69
File Extension:docx
-----------------------------------
File Id:4
File Name:JDBC Introduction.pptx
File Size In KB:107
File Extension:pptx
-----------------------------------

Following flies are downloaded from database..

File Id:1

File Name:GST.pdf

File Size In KB:760

File Extension:pdf

-----------------------------------

File Id:2

File Name:java collection .jpg

File Size In KB:51

File Extension:jpg

-----------------------------------

File Id:3

File Name:JDBC Introduction.docx

File Size In KB:69

File Extension:docx

-----------------------------------

File Id:4

File Name:JDBC Introduction.pptx

File Size In KB:107

File Extension:pptx

-----------------------------------

Now just refresh your project and expand DownLoadFiles folder you will see all binary files are downloaded from database and written in this folder as given in below screen shot.

That’s all about Writing and Reading MySQL BLOB/binary files Using JDBC

You May Also Like:

How to store file in MySQL database using JDBC
How to read files/CLOB data from MySQL database using JDBC
ResultSetMetaData in JDBC example
How to get primary key value (auto-generated keys) from inserted queries using JDBC?

If you have any feedback or suggestion please feel free to drop in blow comment box.

Leave a Reply Cancel reply