fbpx

Load BLOB images from SQL and display in JSP page using Java Servlet

Load BLOB images from SQL and display in JSP page using Java Servlet

In this article let discuss on

Load BLOB images from SQL and display in JSP page using Java Servlet

we will guide you how to write code for displaying images stored in database on a JSP page within Java web application. Suppose that the images are stored in the database in BLOB format (Binary Large Object), and your application needs to display the images on web pages without saving the images somewhere on the server’s disk.

Generally, here are the key steps to implement:

  1. Retrieve the image data from the database as an array of bytes, by using JDBC. If you are using Hibernate, this can be done transparently by the framework.
  2. Encode the image’s binary data to String representation in Base64 format.
  3. Display the image on a JSP page using <IMG> tag with image source is the base64 string.

Now, let’s see how to integrate these steps into a typical request-response workflow of a Java web application based on Servlet and JSP. The database we use for the example is MySQL.

Lets see how to integrate request-response of  a Java web application using Servlet and JSP.

The database we use for the example is MySQL.

1. The Database

Suppose that we have a table named user_tbl that has a column called image which is of type LONGBLOB (this type can store files up to 4GB):

user_tbl blob jdbc 

So our ultimate goal is to retrieve image data from this column and display the image on a web page.

2. The Java Model Class

Java model class for the table needs to have a field to store the base64 string representation of the image.

For example:

package live.wecancode.blobdemo;

public class User {

private String username;
private String email;
private String profileImg;

public String getProfileImg() {
return profileImg;
}

public void setProfileImg(String profileImg) {
this.profileImg= profileImg;
}
// other fields...  getters and setters…
}

The field’s setter setProfileImg() will be called by a DAO class that retrieves the image binary data and converts it to a base64 string.

The field’s getter getProfile() will be called by a JSTL tag in the JSP page in order to show the image.

If you are using Hibernate, the profileImg column must be mapped similar to the below sample code.

For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
package live.wecancode.blobdemo;
 
public class User {
 
private String username;
private String email;
private String profileImg;
private byte[] image;   
 
@Column(name="image")
public byte[] getImage() {
return this.image;
}
    public String getProfileImg() {
return profileImg;
}
 
public void setProfileImg(String profileImg) {
this.profileImg= profileImg;
}
// other fields...  getters and setters…
}

3. The DAO Class

In the DAO class, we write some additional code for retrieving the image’s binary data and converting it to a base64 string. Given a ResultSet object named result, the following code reads the image column into a byte array, and then converts it to a String in base64 format:

Blob blob = result.getBlob("image");

InputStream inputStream = blob.getBinaryStream();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
byte[] buffer = new byte[4096];
int bytesRead = -1;

while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}

byte[] imageBytes = outputStream.toByteArray();

String base64Image = Base64.getEncoder().encodeToString(imageBytes);

inputStream.close();
outputStream.close();

The Base64 class can be found in the java.util package.

And the following is sample code of a DAO class – UserDAO:

package live.wecancode.blobdemo.dao;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Base64;

public class UserDAO {
String databaseURL = "jdbc:mysql://localhost:3306/userdb";
String user = "root";
String password = "pass";

public User get(int id) throws SQLException, IOException {
User user = null;

String sql = "SELECT * FROM user_tbl WHERE user_id = ?";

try (Connection connection = DriverManager.getConnection(databaseURL, user, password)) {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, id);
ResultSet result = statement.executeQuery();

if (result.next()) {
user = new User();
String username = result.getString("username");
String email = result.getString("email");
Blob blob = result.getBlob("image");

InputStream inputStream = blob.getBinaryStream();
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
byte[] buffer = new byte[4096];
int bytesRead = -1;

while ((bytesRead = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, bytesRead);
}

byte[] imageBytes = outputStream.toByteArray();
String base64Image = Base64.getEncoder().encodeToString(imageBytes);

inputStream.close();
outputStream.close();

user.setUsername(username);
book.setEmail(email);
book.setProfileImg(base64Image);
}

} catch (SQLException | IOException ex) {
ex.printStackTrace();
throw ex;
}

return user;
}
}

As you can see, the get(int id) method finds a row in the user_tbl table that associates with the specified id. If the row is found, a new User object is created and populated its information: such as username, email and profileImg.

4. The Servlet Class

In the Servlet class, it receives request from the user with the specified userID, then it calls the DAO to retrieve the User object, which is then stored as a request attribute. Finally the servlet forwards the processing to a destination JSP page.

Here is sample code of the servlet class:

package live.wecancode.blobdemo.servlets;

import java.io.IOException;
import java.sql.SQLException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/viewuser")
public class GetUserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

public GetUserServlet() {
super();
}

protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
int userId = Integer.parseInt(request.getParameter("id"));
UserDAO dao = new UserDAO();

try {
User user = dao.get(userId);

request.setAttribute("user", user);

String page = "/index.jsp";
RequestDispatcher requestDispatcher = request.getRequestDispatcher(page);
requestDispatcher.forward(request, response);
} catch (SQLException ex) {
throw new ServletException(ex);
}

}
}

5. Display Image in the JSP Page

To display the image represented by a base64 String, use the <IMG> tag with the following syntax:

For PNG image type:

<img src=”data:image/png;base64,[base_64_String_goes_here]” />

For JPG/JPEG image type:

<img src=”data:image/jpg;base64,[base_64_String_goes_here]” />

And here is code of a sample JSP page that uses JSTL tags mixed with HTML tags:

<!--  userdetail.jsp -->
&lt;%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %&gt;
&lt;%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%&gt;

</pre>
<h2><c:out value="${user.username}"></c:out></h2>
<h3><c:out value="${user.email}"></c:out></h3>
<img src="data:;base64,${user.profileImg}" width="240" height="300" />
<!-- Use this if the image type is PNG

<img src="data:image/png;base64,${user.profileImg}" width="240" height="300"/>

-->
<pre>

6. Test Image Display

Suppose that the user_tbl table contains some rows with image data. To test if the image gets displayed in the JSP page or not, call the Java Servlet by typing the following URL in a web browser:

http://localhost:8080/Userdetail/viewuser?id=1

Balamurugan T

Balamurugan T

August 05, 2021

Java & C#.NET Developer with 10++ years of IT experience.

Planning to learn ReactJS or Angular or Flutter.!

JDBC Clob datatype, store and read data with java

What is JDBC Clob data type? how to store and read data from it? 

CLOB stands for Character Large Object in general.

SQL Clob is a built-in datatype and is used to store huge amount of characters data. Using this datatype, you can store data up to 2,147,483,647 characters.

The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).

MYSQL database provides support for this datatype using four variables.

  • TINYTEXT: A CLOB type with a maximum of 28-1 (255) characters.
  • TEXT: A CLOB type with a maximum of 216-1 (65535) characters.
  • MEDIUMTEXT: A CLOB type with a maximum of 224-1 (16777215) characters.
  • LONGTEXT: A CLOB type with a maximum of 232-1 (4294967295 ) characters.

Storing Clob datatype in to table in a database

To store Clob datatype to database, using JDBC program follow the steps given below

Step 1: Connect to the database

You can connect to a database using the getConnection() method of the DriverManager class.

Connect to the MySQL database by passing the MySQL URL which is jdbc:mysql://localhost/sampleDB (where sampleDB is the database name), username and password as parameters to the getConnection() method.

String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
Step 2: Create a Prepared statement

Create a PreparedStatement object using the prepareStatement() method of the Connection interface. To this method pass the insert query (with place holders) as a parameter.

PreparedStatement pstmt = con.prepareStatement("INSERT INTO Technologies(Name,
Type, Article ) VALUES (?, ?, ?)");
Step 3: Set values to the place holders

Set the values to the place holders using the setter methods of the PreparedStatement interface. Chose the methods according to the datatype of the column. For Example if the column is of VARCHAR type use setString() method and if it is of INT type you can use setInt() method.

And if it is of Clob type you can set value to it using the setCharacterStream() or setClob() methods. To these methods pass an integer variable representing the parameter index and an object of the Reader class as parameters.

pstmt.setString(1, "JavaFX");
pstmt.setString(2, "Java Library");
FileReader reader = new FileReader("E:\\images\\javafx.txt");
pstmt.setClob(3, reader);
pstmt.execute();
Step 4: Execute the statement

Execute the above created PreparedStatement object using the execute() method of the PreparedStatement interface.

Retrieving blob from database

The getClob() method of the ResultSet interface accepts an integer representing the index of the column (or, a String value representing the name of the column) and retrieves the value at the specified column and returns it in the form of a Clob object.

while(rs.next()) {
   System.out.println(rs.getString("Name"));
   System.out.println(rs.getString("Type"));
   Clob clob = rs.getClob("Article");
}

The getCharacterStream() method of the Clob Interface retrieves the contents of the current Clob object and returns as a Reader object.

Using the getClob() method you can get the contents of the Clob as a Reader object and create text file with the retrieved contents, using the write() method of the FileOutputStream object.

Reader r = clob.getCharacterStream();
char cbuf[] = new char[r.read()];
r.read(cbuf);
FileOutputStream outPutStream = new
FileOutputStream("E:\\images\\clob_output"+i+".txt");
outPutStream.write(cbuf.toString().getBytes());
Example : Creates table with Clob field | Inserts large text data from a file to it. Retrieves the text back and stores it in another text file.
import java.io.FileReader;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class ClobExample {
   public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a table
      Statement stmt = con.createStatement();
      stmt.execute("CREATE TABLE Technologies( Name VARCHAR(255), Type VARCHAR(255), Article LONGTEXT)");
      System.out.println("Table Created......");

      //Inserting values
      String query = "INSERT INTO Technologies(Name, Type, Article ) VALUES (?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "JavaFX");
      pstmt.setString(2, "Java Library");
      FileReader reader = new FileReader("E:\\images\\javafx.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "CoffeeScript");
      pstmt.setString(2, "Scripting Language");
      reader = new FileReader("E:\\images\\coffeescript.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      pstmt.setString(1, "Cassandra");
      pstmt.setString(2, "NoSQL Database");
      reader = new FileReader("E:\\images\\cassandra.txt");
      pstmt.setClob(3, reader);
      pstmt.execute();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Technologies");
      int j = 0;
      System.out.println("Contents of the table are: ");
      while(rs.next()) {
         System.out.println(rs.getString("Name"));
         Clob clob = rs.getClob("Article");
         Reader r = clob.getCharacterStream();
         String filePath = "E:\\Data\\clob_output"+j+".txt";
         FileWriter writer = new FileWriter(filePath);
         int i;
         while ((i=r.read())!=-1) {
            writer.write(i);
         }
         writer.close();
         System.out.println(filePath);
         j++;
      }
   }
}
balamt

balamt

August 04, 2021

Java & C#.NET Developer with 10++ years of IT experience.

Planning to learn ReactJS or Angular or Flutter.!