Read and Write Binary Files to Database in Java
Dear reader,
Many times we need to store and read binary files like Images, mp3, mp4 and any non-human readable files
into DB. This is required basically when you work on Case Management System or anything which requires
file storage into Database.
I have written a very simple and complete code with DB script to store and read binary file into DB.
The sequence of completing the tasks are:
1) Create table.
2) Take few files in a directory, which you want to store to DB.
3) Read the content from DB and create a duplicate file into the same directory from where you have read
and stored the file into DB.
4) Complete Screenshot for the example.
-------------------------------------------------------------
Step 1:
create table FILE_STORE(
ID integer(5) not null,
FILE_NAME varchar(100) not null,
USER_NAME varchar(100) not null,
BINARY_FILE mediumblob,
MOBILE varchar(15),
primary key (ID)
);
--MEDIUMBLOB - 16,777,215 bytes (2^24 - 1)
-------------------------------------------------------------
Step 2:
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SaveBinaryFileToDB {
static String directoryLocation="E:\\Eguard_Merged_Workspace\\TestProject\\inputFiles\\";
//static String fileName="Hint_Oracle_History.png";
static String fileName="Zoobi_Doobi.mp3";
static File file = new File(directoryLocation+fileName);
InputStream fis = null;
public static void main(String[] args) throws SQLException{
String connectionURL = "jdbc:mysql://192.168.111.111:3306/deepak_temp"; //Change IP address and Port.
Connection connection = null;
ResultSet rs = null;
PreparedStatement psmnt = null;
FileInputStream fis;
try{
fis = new FileInputStream(file);
}
catch(Exception e){
e.printStackTrace();
System.exit(0);
}
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "root");
psmnt = connection.prepareStatement
("INSERT INTO FILE_STORE(ID, FILE_NAME, USER_NAME, BINARY_FILE, MOBILE) values(?,?,?,?,?)");
psmnt.setInt(1,1);
psmnt.setString(2,fileName);
psmnt.setString(3,"DeepakModi,Enstage,Bangalore");
fis = new FileInputStream(file);
psmnt.setBinaryStream(4, (InputStream)fis, (int)(file.length()));
psmnt.setString(5,"+919916473353");
int s = psmnt.executeUpdate();
if(s>0) {
System.out.println("Binary File Uploaded successfully !");
}
else {
System.out.println("Unsucessfull to upload Binary File.");
}
}
catch (Exception ex) {
System.out.println("Found some error : "+ex);
}
finally {
connection.close();
psmnt.close();
}
}
}
-------------------------------------------------------------
Step 3:
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ReadBinaryFileFromDB {
static String directoryLocation="E:\\Eguard_Merged_Workspace\\TestProject\\inputFiles\\";
//static String fileName="Hint_Oracle_History.png";
static String fileName="Zoobi_Doobi.mp3";
static String fileNameSuffix="_Duplicate.mp3";
//static String fileNameSuffix="_Duplicate.png";
static File file = new File(directoryLocation+fileName+fileNameSuffix);
static OutputStream fos = null;
static InputStream is = null;
public static void main(String[] args) throws Exception{
String connectionURL = "jdbc:mysql://192.168.111.111:3306/deepak_temp"; //Change IP address and Port.
Connection connection = null;
ResultSet rs = null;
PreparedStatement psmnt = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "root", "root");
psmnt = connection.prepareStatement("SELECT BINARY_FILE from FILE_STORE where ID=? and FILE_NAME=?");
psmnt.setInt(1,1);
psmnt.setString(2,fileName);
rs=psmnt.executeQuery();
fos = new FileOutputStream(directoryLocation+fileName+fileNameSuffix);
if(rs.next()){
is = rs.getBinaryStream(1);
System.out.println("Length of re-generated file.."+is.available());
byte[] buf = new byte[104];
int read = 0;
while ((read = is.read(buf)) > 0) {
fos.write(buf, 0, read);
}
}
fos.close();
is.close();
}
catch (Exception ex) {
System.out.println("Found some error : "+ex);
ex.printStackTrace();
}
}
}
-------------------------------------------------------------
Screen Shot:
Attached Screenshot contains 5 markers, which is meant for below points:
1. Project Name
2. Original Binary File
3. Re-Created Binary File from DB
4. Mysql JDBC Jar
5. DB Script file
Monday, May 19, 2014
Read and Write Binary Files to Database in Java
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment