Insert Mysql Blob data


 

Insert Mysql Blob data

In this section, we will discuss about how to insert a blob data into a database table.

In this section, we will discuss about how to insert a blob data into a database table.

Insert Mysql Blob data  

In this section, we will discuss about how to insert a blob data into a database table. A Blob stores a binary large object in the database table's row. Blob object contains a logical pointer which points to the Blob data, data is not directly stored in the row of the database table. Blob object  is valid for the duration of the transaction. The "getBlob()" and  "setBlob()" method of ResultSet ,CallableStatement, and PreparedStatement interface , is used for accessing Blob value. Blob was added to java in jdk1.2 .

The Method defined in " java.sql.Blob " interface is as follows--

Return Type    Method                                         Description

InputStream        getBinaryStream()                       Retrieves the blob value designated
                                                                                 by this blob value as a stream

byte[]                 getBytes(long pos, int length)         Retrieve all or part of the blob value that
                                                                                 this blob represents as an array of bytes.

long                    length()                                          Returns the no of bytes in the Blob
                                                                                 value designated by this Blob object

OutputStream     setBinaryStream(long pos)           Retrieves a stream that can be used
                                                                                 to write to the Blob value 

int                       setBytes(long pos, byte[] bytes)    Write the given array of bytes to the Blob
                                                                                 value that this Blob object represent
                                                                                 starting at  position pos, and returns the no
                                                                                 of bytes written 

void                    truncate(long len)                            Truncates the blob value that this blob
                                                                                  object represents to be len bytes in len

 

Blob data type can further be classified into four types--

  • TINYBLOB: The maximum length is 255 characters (8 bits)
  • BLOB: The maximum length is 16,535 characters (16 bits)
  • MEDIUMBLOB: The maximum length is 16,777,216 characters (24 bits)
  • LONGBLOB: The maximum length is 4,294,967,295 characters (32 bits).

Creating Database table:

CREATE TABLE `article` ( 
             `ID` int(11) NOT NULL, 
              `Subject` varchar(256) NOT NULL, 
              `Body` longtext, 
              PRIMARY KEY (`ID`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

insertblob.java

import java.sql.*;
import java.io.*;
 
 class insertblob
{
public static void main(String[] args) throws SQLException {
  
  Connection con=null;
  ResultSet rs=null;
  PreparedStatement psmt=null;
  FileInputStream fis;
  String url="jdbc:mysql://192.168.10.13:3306/ankdb";
  try{
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  con=DriverManager.getConnection(url,"root","root");

  File image=new File("C:/ankit_jdbc/Child.jpg");

  psmt=con.prepareStatement("insert into inimage(name,city,image)"
  +
"values(?,?,?)");
  psmt.setString(1,"Nitika");
  psmt.setString(2,"Delhi");
  
  fis=new FileInputStream(image);
  
  psmt.setBinaryStream(3, (InputStream)fis, (int)(image.length()));
  int s = psmt.executeUpdate();

  if(s>0) {
  System.out.println("Image Uploaded successfully !");
   }
  else {
  System.out.println("unsucessfull to upload image.");
  }
  con.close();
  psmt.close();
  }catch(Exception ex){
  System.out.println("Error in connection : "+ex);
  }
 }
}

 Output--

inimage table

 

CMD O/P

C:\Program Files\Java\jdk1.6.0_18\bin>java insertimage 
Image Uploaded successfully !

 Download this Code

Ads