Read and Write Binary Files to Database in Java

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