How to insert image into database using Java / How to retrieve image data from database in Java

Scenario
In practical applications, when you create a profile (of a User or a Company say), you are required to maintain a picture or an icon to uniquely identify the entity so that every time the user or company visits the application, it finds the same picture which was last uploaded over the application.
For Example, when a Facebook user uploads his picture, it is added to his photos list and every time he logs in, he can see the picture in that list.
This can be done when you save the picture somewhere and retrieve it from there every time its required. A database is a suitable option in many cases to store and retrieve images.

How ?

Database operations in java are performed using JDBC where in you need to retrieve a database connection and then create a Statement or PreparedStatement using which SQL queries are executed. Visit this to know more about JDBC concepts.
You need to have a database and a table in which you want to store the image content. Create both these using SQL queries to create database and table. Make sure that the column in which you want to store the image content is of the type BLOB or BINARY in the database table.

Storing Image

Code to store the image in a database table column follows. The database name is codippa, table name is users and the column in which image will be stored is named as profile. The data type of this column in database is BLOB :

 
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.PreparedStatement;

    public static void main(String[] args) throws SQLException, IOException {
		Connection connection = null;
		PreparedStatement statement = null;
		FileInputStream imageInputStream = null;
		try {
                    // retrieve connection  
		    connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/codippa", "root", "root");
                    // get statement object  
		    statement = connection.prepareStatement("insert into users(Type,Description,Status,ImageData) values('Bug','Test','Active',?)");
                    // create an input stream pointing to the image file to store
		    imageInputStream = new FileInputStream(new File("d:\\download.png"));
                    // inform the statement that first parameter in the query is of binary type
		    statement.setBinaryStream(1, imageInputStream);
                    // execute query
		    statement.execute();
		} catch (SQLException sqe) {
			throw sqe;
		} finally {
                        // close resources 
			if (connection != null) {
			      connection.close();
			}
			if(imageInputStream!=null){
			     imageInputStream.close();
			}
		}
	}

After the image is stored in the database table column as blob data, it will look as below :

Details :

Database queries in JDBC are executed using either a java.sql.Statement or java.sql.PreparedStatement which are retrieved through a database connection. Hence, at first step we create a database connection and using this, get a java.sql.PreparedStatement object.Visit here for more understanding of the concepts.

At the time of initialization of statement object, we pass the insert query. Notice a ‘?’ at the last position where values are being set. This is used to tell the statement that we will be providing the value corresponding to this column later. The value is provided using one of the various set... methods of java.sql.PreparedStatement object which takes parameter index (starting with 1) as the first argument and the value to set as the second argument.
An image is binary data. Binary data can be read using input streams in java. Hence for reading an image, set up an input stream in the form of java.io.FileInputStream. This input stream is passed to java.sql.PreparedStatement‘s setBinaryStream() method which reads the file till its end is reached.
Finally, the query is executed using execute() method of java.sql.PreparedStatement object.

Retrieving Image

To retrieve an image stored in a database table column, we first need to get the column data using simple Select query just as we do for fetching data from other columns, that is using a Select query of the form Select ColumnName from TableName where [WHERE CLAUSE]

The query is executed using executeQuery method of java.sql.Statement object or using execute() method of java.sql.PreparedStatement object. Complete code for image retrieval is given below.
In the given example, we shall be using java.sql.Statement object for the sake of variation since we used java.sql.PreparedStatement in the above example.

    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    public static void main(String[] args) throws SQLException, IOException {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
                InputStream imageStream = null;
                OutputStream out = null;
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/codippa", "root", "root");
		        statement = connection.createStatement();
                        rs = statement.executeQuery("select ImageData from users where Id=2");
			while(rs.next()){
                                //get data of first column from result set
				imageStream = rs.getBinaryStream(1);
                                // initialize output stream with the file to create
				out = new FileOutputStream(new File("d:\\fromDb.png"));
				int c = 0;
                                //write the contents from the input stream to the output stream
				while ((c = imageStream.read()) > -1) {
					out.write(c);
				}
		     }
		} catch (SQLException sqe) {
			throw sqe;
		} finally {
                        // close resources
			if (connection != null) {
				connection.close();
			}
			if(imageStream != null){
				imageStream.close();
			}
                        if(out != null){
				outclose();
			}
			if(statement != null){
				statement.close();
			}
		}
	}

Details

After executing database query using executeQuery()method of java.sql.Statement,we need to assign it to a java.sql.ResultSetobject if we have to iterate over the result returned. In each iteration (inside loop body), the result set points to a row of the results returned and value of each column can be retrieved using various get...methods of java.sql.ResultSetobject. All get...methods take either the column index or the column name as parameter. There are various get methods according to the data type which the column holds such as getString,getInt,getBooleanetc.
In our case since the column data is binary, we use the getBinaryStream method of java.sql.ResultSet object and assign it to an input stream. Using this input stream, we can easily write its contents to a file. Many ways to write a file in java here.
Also, since there is only one column in the result, we passed 1 as the column index (which means the first column) to getBinaryStream method.

Let’s tweak in :

1. If you want to retrieve the contents of image and store it in a variable, then the type of that variable should be byte[] (byte array).
2. The parameter index in set... methods of java.sql.PreparedStatementobject starts with 1.
3. java.sql.PreparedStatement does not provide a direct method which executes a query and returns a java.sql.ResultSet. You need to invoke getResult() method of java.sql.PreparedStatement to gain access to the result set.

1. If you are not sure about the data type of the column contents while using a get... method of java.sql.ResultSet, then use getObject method to fetch column contents.
2. A java.sql.ResultSet object is initially before the first row of the results returned. Calling next() positions it to the first row.
3. The parameter index in get... methods of java.sql.ResultSetobject starts with 1.

Liked this post…Great !!! Don’t hesitate to click the clap button below to show your appreciation. You can also spread by sharing this article.