SatyaCodes

JDBC- BLOB

JDBC - BLOB

We use BLOB objects to store binary data like images, videos etc

We have following methods to Save & Retrive BLOB Objects

To Save

  • void setBinaryStream(int parameterIndex, InputStream x)

  • void setBinaryStream(int parameterIndex, InputStream x, int length)

  • void setBlob(int index, Blob x)

  • void setBlob(int index,InputStream is)

To Retrive

  • Blob getBlob(int columnIndex)

  • Blob getBlob(String columnLabel)

  • InputStream getBinaryStream(int columnIndex)

  • InputStream getBinaryStream(String column)


Steps:

1.Read Image/ video data by using InputStream

FileInputStream fis= new FileInputStream("d:\\img.jpg");

2.Create PreparedStatement Object to write insert image query

PreparedStatement ps=con.prepareStatement("insert into imgtable values(?,?)");

3.Set parameter values

ps.setInt(1, 101);
ps.setBinaryStream(2,fis);

4.Execute Query

int i=ps.executeUpdate();

5.To get image from table execute Select Quey , call on rs object

FileInputSteam fs= rs.getBinarayStream(  column");

6.Choose Location to Store new Image

FileOutputStream fos = new FileOutputStream("res/img.jpg");


Example BlobInsert Operation

public class BlobInsert {
	public static void main(String[] args) throws Exception {
 String url = "jdbc:mysql://localhost:3306/mydb";
 String u = "root";
 String p = "123456";
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection(url, u, p);
PreparedStatement ps = con.prepareStatement("insert into blobtest values(?,?,?)");

 File f = new File("res/img.gif");
 FileInputStream fis = new FileInputStream(f);

 ps.setInt(1, 100);
 ps.setString(2, "johny");
 ps.setBinaryStream(3, fis, (int) f.length());
 ps.executeUpdate();
 System.out.println("Record is Inserted");
	}
}

Example BlobInsert Operation

public class BlobRetrive {
	public static void main(String[] args) throws Exception {
 String url = "jdbc:mysql://localhost:3306/mydb";
 String u = "root";
 String p = "123456";
 Class.forName("com.mysql.jdbc.Driver");
 Connection con = DriverManager.getConnection(url, u, p);
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery("select * from blobtest");

 if (rs.next()) {
 	InputStream in = rs.getBinaryStream("img");
 FileOutputStream fos = new FileOutputStream("res/newpict.gif");

 	int bytesRead = 0;
 	byte[] buffer = new byte[4096];
 	while ((bytesRead = in.read(buffer)) != -1) {
  fos.write(buffer, 0, bytesRead);
 	}
 	System.out.println("photo is stored in newpict.gif");
 	fos.close();
 	in.close();
 	rs.close();
 	st.close();
 	con.close();
 } // if
	}// main
}// class
Search