How to insert and retrieve an image from MySQL database using Java

In this tutorial, we are going to see How to insert and retrieve an image from a MySQL database using Java. Usually images are stored in directories and we store the path to the images in database tables. But in some scenarios, we need to insert images into database tables in binary format.
 

Prerequisites :

• Download and install JDK on your system.
• Download and install MySql server.
• Download connector(mysql-connector-java-5.1.49.zip) to use MySQL with Java.

 
The first thing to do is to set up our database.

Create a database called “test”. In “test” database, add a table called “image”. The image table will take the following three fields.

• id
• size
• image

 
You can create this using a MySQL client like PHPMyAdmin.

 
Or you can create it on MySQL command prompt using the following SQL script:

CREATE TABLE `image` (
  `id` varchar(45) DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `image` longblob
);

In MySQL, when we use the blob type to store data, it supports only 5 KB as image capacity. It depends on the DBMS. According to some DBMS, the blob object type supports a large capacity.
 

 

Insert the image in the database:

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

public class InsertImg
{
	public static void main(String[] args)
	{
		//URL to Connect to the database
		String url = "jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false";
		//User
		String user = "root";
		//Password
		String password = "";
		try{
		   //create the database connection string
		   Connection con = DriverManager.getConnection(url, user, password);
		   //object to create the request
		   Statement st = con.createStatement();
		   //the image to insert
		   File file = new File("test.png");
		   FileInputStream input = new FileInputStream(file);
		 
		   //create the request
		   PreparedStatement ps =
		   con.prepareStatement("insert into Image values(?,?,?)");
		 
		   //id
		   ps.setString(1,"img001");
		   //size
		   ps.setInt(2,99);
		   //image
		   ps.setBinaryStream(3,(InputStream)input,(int)file.length());
		   //execute the request
		   ps.executeUpdate();
		   System.out.println("Image successfully inserted!");

		   //close the preparedStatement
		   ps.close();
		   //close the connection
		   con.close(); 
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
}

Compile and run the program:
You can execute the above code in command line (CMD):

> javac InsertImg.java
> java -cp .;lib/mysql-connector-java-5.1.49.jar InsertImg

 

 
Download mysql-connector-java-5.1.49.jar file and put it in the lib folder of your project.

 
Result on PHPmyAdmin:

 

Retrieve the image from the database:

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

public class SelectImg
{
	public static void main(String[] args)
	{
		//URL to Connect to the database
		String url = "jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false";
		//User
		String user = "root";
		//Password
		String password = "";
		try{
		   //create the database connection string
		   Connection con = DriverManager.getConnection(url, user, password);
		   //object to create the request
		   Statement st = con.createStatement();
		   //execute the request
		   ResultSet rs = st.executeQuery("SELECT image FROM Image");
		   int i = 0;
		   while (rs.next()) {
		     InputStream input = rs.getBinaryStream(1);
		     //create the image in the current directory
		     OutputStream output = new FileOutputStream(new File("test"+i+".jpg"));
		     i++;
		     int b = 0;
		     while ((b = input.read()) > -1) {
		         output.write(b);
		     }
		     //close the OutputStream
		     output.close();
		     //close the InputStream
		     input.close();
		   }
		   //close the connection
		   con.close(); 
		}catch (Exception e){
			System.out.println(e.getMessage());
		}
	}
}

Compile and run the program:

> javac SelectImg.java
> java -cp .;lib/mysql-connector-java-5.1.49.jar SelectImg

 

 

Display the image stored in the database in a JFrame:

import java.awt.*;
import java.sql.*;
import javax.swing.*;

public class ShowMyImage extends JFrame 
{
	public ShowMyImage() 
	{
		super("Display an image from a MySQL DB");
		setSize(300, 300);
		//get the connection
		Connection con = getConnection();
		try { 
			//creation and execution of the request
			PreparedStatement statement = con.prepareStatement("SELECT image FROM Image WHERE id = 'img001'");
			ResultSet res = statement.executeQuery();

			//get image as byte
			byte[] image = null;
			while (res.next()) {
				image = res.getBytes("image");
			}
			//create the image 
			Image img = Toolkit.getDefaultToolkit().createImage(image);
			ImageIcon icone = new ImageIcon(img);
			JLabel l = new JLabel();
			l.setIcon(icone);
			add(l);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		setVisible(true);
	}

	public Connection getConnection() {
		String url = "jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false";
		//User
		String user = "root";
		//Password
		String password = "";
		//initiate the connection
		Connection con = null;
		
		try {
			//create the database connection string
			con = DriverManager.getConnection(url, user, password);

		} catch (Exception e) {
			System.out.println("Connexion error: "+ e);
		}
		return con;
	}

	public static void main(String[] args) {
		new ShowMyImage();
	}
}

Compile and run the program:

> javac ShowMyImage.java
> java -cp .;lib/mysql-connector-java-5.1.49.jar ShowMyImage

Output:

 
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More