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 :
- 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). - The parameter index in
set...methods ofjava.sql.PreparedStatementobject starts with 1. java.sql.PreparedStatementdoes not provide a direct method which executes a query and returns ajava.sql.ResultSet. You need to invokegetResult()method ofjava.sql.PreparedStatementto gain access to the result set.
- If you are not sure about the data type of the column contents while using a
get...method ofjava.sql.ResultSet, then usegetObjectmethod to fetch column contents. - A
java.sql.ResultSetobject is initially before the first row of the results returned. Callingnext()positions it to the first row. - The parameter index in
get...methods ofjava.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.