JAVA JDBC – Save, Retrieve and Update Image to MySQL Database

In this tutorial you will learn:

1. What is BLOB Type?
2. How to store Image in Database using JDBC?
3. How to retrieve Image from Database?

This tutorial explains, save and retrieve image to MySQL database using JDBC. However, it is not practical to save image in database because image size is big and resources of databases servers are very costly. The most famous way is store image inside a folder and save image path to database. However, sometimes you may want to save image to database. This tutorial is about insert and retrieve picture from MySQL Database.

What is BLOB Type?

You must know what is BLOB Type. BLOB (Binary Large Objects) Type is used to define column in table that store picture. There is no way insert picture directly in the database instead it you need to convert image into binary data type then store that binary value to database. There are 4 types of BLOB that hold binary data in different sizes. You can choose one of the type according to your picture size while creating table.

TYPE SIZE
TINYBLOB 255 Bytes
BLOB 64 KB
MEDIUMBLOB 16 MB
LONGBLOB 4 GB

Programming Example

Here, I will use a small image (below 64KB) to save into database. If you don’t have smaller image you can download this.

Programming Example

We have a database STOREDB and a Table ITEM inside it. We need to add an extra column SavePic for storing image in it. Here is a JDBC program for adding extra BLOB Type column in ITEM table.

STEP 1 : Adding Extra column SavePic with BLOB type in table Item.
package ImageTutorial;
import java.sql.*;

public class AddBlobColumn
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to Server
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();

	    //Step 3 : SQL Query
	    //Add New Column SavePic
	    String query="ALTER TABLE ITEM ADD SavePic BLOB";
	    
	    //Step 4 : Run Query
	    stmt.executeUpdate(query);
	    System.out.println("Column SavePic with BLOB Datatype is added Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Column SavePic with BLOB Datatype is added Successfully
Closing the connection.
_

 

STEP 2: Inserting Picture to Table
package ImageTutorial;
import java.sql.*;
import java.io.*;

public class InsertImage
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws Exception, IOException, SQLException
{
	Connection con = null;
	Statement stmt = null;
	FileInputStream fs=null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    	    
	    File f=new File("/home/prashant/Documents/image/mainjava.jpg");
	    fs=new FileInputStream(f);
	    
	    ps= con.prepareStatement("INSERT INTO ITEM(PRODUCT,PRICE,SavePic) VALUES(?,?,?)");
	    ps.setString(1, "MainJava Logo");
	    ps.setString(2, "900");
	    ps.setBinaryStream(3,fs,(int)f.length());
	    ps.executeUpdate();
	    System.out.println("Image Stored Successfully");
	  
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    ps.close();
	    fs.close();
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Image Stored Successfully
Closing the connection.
_

Retrieve Image from MySQL Database

Programming Example

package ImageTutorial;

import java.sql.*;
import java.io.*;

public class RetrieveImage
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws Exception, IOException, SQLException
{
	Connection con = null;
	FileOutputStream fs=null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);
	    ps= con.prepareStatement("SELECT * FROM ITEM WHERE SavePic IS NOT NULL");
	    ResultSet rset=ps.executeQuery();
	    	    
	    byte b[];
	    Blob blob;
	    int i=1;
	    while(rset.next())
	    {
	    	i++;
	    	System.out.print("ID: " + rset.getInt(1));
	    	System.out.print(" Product : "+rset.getString(2));
	    	System.out.println(" Price : "+rset.getString(3));
	    	
	    	File f=new File("/home/prashant/Documents/image/mainjava " + i + ".jpg");
		    fs=new FileOutputStream(f);
	    	blob=rset.getBlob("SavePic");
	    	b=blob.getBytes(1, (int)blob.length());
	    	fs.write(b);
	    }
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    ps.close();
	    fs.close();
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

ID: 12 Product : MainJava Logo Price : 900
Closing the connection.
_

When you visit the folder location you will see all the images are stored there from database. In my example you can see all your image in this path.

/home/prashant/Documents/image/
 

Updating Image in MySQL Database

Programming Example

package ImageTutorial;

import java.sql.*;
import java.io.*;

public class UpdateImage
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args) throws Exception, IOException, SQLException
{
	Connection con = null;
	FileInputStream fs=null;
	PreparedStatement ps=null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    	    
	    File f=new File("/home/prashant/Documents/image/updated_image.png");
	    fs=new FileInputStream(f);
	    
	    ps= con.prepareStatement("UPDATE ITEM SET SavePic=? Where ID=?");
	    
	    ps.setBinaryStream(1,fs,(int)f.length());
	    ps.setInt(2, 13);
	    ps.executeUpdate();
	    System.out.println("Image Updated Successfully");
	  
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    ps.close();
	    fs.close();
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Image Updated Successfully
Closing the connection.
_

Summary

In this chapter you learned how to insert, update and retrieve image/picture in JDBC using MySQL Database. Save image to database or insert picture to mysql is very easy if you have understand the basic programming of JDBC. However, I have tried to keed JDBC Image Programming as simple as I can. In the next chapter you will learn how to insert, update or delete files using JDBC.


Leave a Reply

Your email address will not be published. Required fields are marked *