Saving and retrieving files is a common problem when it comes to writing programs that has a database back end. In this post I will show you how to save and retrieve image files to and from a MySQL database, using Java. This method can be used to store any file in a database.

The Database:

For this example I will create a database called ‘DB’ and a table called ‘images’ where I will store the files. I use wampserver as my database manager.

The format for the ‘images’ table is as follows:

Index    :      INT
Photo    :      MEDIUMBLOB

The data type BLOB can be used to store byte values. There are four such data types available, namely TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB. Check here to find out the maximum sizes supported by each data type.

Java Program:

The following java program reads an image file (test.jpg) and saves it in the database in a record with index as 5. Then it reads back all the records that are in the table, and stores those images back on the hard disk.

Make sure you have installed MySQL Connector/J. If you are using NetBeans, you can add MySQL JDBC Driver to you libraries folder.

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.*;
import javax.imageio.ImageIO;

public class Main {

	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/mysql";
			Connection con = DriverManager.getConnection(url, "root", "");
			System.out.println("Connected to db");

			//saving the image
			PreparedStatement psmnt = (PreparedStatement) con.prepareStatement("INSERT INTO DB.images VALUES(?,?)");
			psmnt.setInt(1, 5);
			File f = new File("test.jpg");
			psmnt.setBlob(2, new FileInputStream(f), f.length());
			psmnt.executeUpdate();
			System.out.println("Image saved in DB");

			//retrieving it

			Statement stmt = (Statement) con.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM DB.images");
			int count = 0;
			while (rs.next()) {
				InputStream is = rs.getBinaryStream("photo");
				BufferedImage image = ImageIO.read(is); //the image is read in
				//store it back again as a file
				ImageIO.write(image, "jpg", new FileOutputStream("recived" + count + ".jpg"));
				count++;
			}

			System.out.println(count+" images saved on disk");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}

If you are trying to upload a large file, sometimes you might get a ‘packet size too large’ exception. You can increase the ‘max_allowed_packet’ variable of your server. Check here to find out how to do this.

If you want to know how to save, send and receive Java Image objects over a network have a look at this. It explains how to convert an Image object to a byte array. This byte array can be saved in an SQL database in a BLOB type data field.

Happy Coding! Continue reading →