Wednesday 1 October 2014

BLOB

BLOB stands for Binary large object. BLOB is a large object data type in the database system. BLOB can store large volume of data like PDFs, audio and video files etc.,

How to create a BLOB Object
to create BLOB use Connection.createBlob
   Ex:
   Blob b1 = conn.createBlob();

How to write BLOB data to Database
to write BLOB to DB use PreparedStatement.setBlob
   Ex:
   PreparedStatement ps = conn.prepareStatement("INSERT INTO employee values (?, ?, ?)");
   ps.setBlob(3, b1);
   ps.execute();

How to read BLOB data from Database
to read BLOB from DB use ResultSet.getBlob
   Ex:
   Statement st = conn.createStatement();
   ResultSet rs = st.executeQuery("select c1 from t1");
   Blob b2 = rs.getBlob(3);

/* Step 1: Import sql package */
import java.sql.*;
import java.io.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException, IOException{     
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        PreparedStatement prepStmt;
        Blob b1 = conn.createBlob();
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        
        
        prepStmt = conn.prepareStatement("INSERT INTO employee values (?, ?, ?)");
        stmt.execute("CREATE TABLE employee (id int, name varchar(30), resume BLOB, PRIMARY KEY(id))");
        
        prepStmt.setInt(1, 1);
        prepStmt.setString(2, "Krishna");
        
        /* Read the file specified in the location */
        System.out.println("Enter resume location");
        String location = br.readLine();
        
        /* Logic to read the file and set the blob value*/
        BufferedReader reader = new BufferedReader(new FileReader(location));
        String line;
        StringBuilder buf = new StringBuilder();
        while ((line = reader.readLine()) != null) {
           buf.append(line);
           System.out.println(line);
 }
        b1.setBytes(1, buf.toString().getBytes());
        
        /* Set the Blob object */
        prepStmt.setBlob(3, b1);
        prepStmt.execute();
        
        String query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        System.out.println("Selecting data");
        while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
            
            /* read and print the Blob data */
            Blob b = rs.getBlob("resume");
            InputStream in = b.getBinaryStream();
            int a;
            while((a = in.read())!= -1){
                System.out.print((char)a);
            }
            
        }
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Sample Output
Loading/Registering driver
Connecting to database
Enter resume location
C:\Hari\resume.txt
A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.

There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.
Selecting data
1 Krishna
A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.Dropping table employee

lets say 'resume.txt' contains below data.
A resume is a written compilation of your education, work experience, credentials, and accomplishments and is used to apply for jobs.

There are several basic types of resumes used to apply for job openings. Depending on your personal circumstances, choose a chronological, a functional, combination, or a targeted resume.









Prevoius                                                 Next                                                 Home

No comments:

Post a Comment