ViralPatel.net

Working with BLOB / CLOB data types in database is sometime a trivial task. I found particularly when working with Hibernate 3 to store and retrieve BLOB objects we need certain things to be taken care of. Let us see a tutorial where we will using Spring 3 MVC and Hibernate 3 to store and retrieve blob objects in database.

Our Goal

Our goal is to create a Document Manager application in Spring 3 MVC and Hibernate. Following is the functionality.

1. A form is displayed on main page with fields such as Document name, description and browse button to select document from file system.
2. User can select any document from local drive and upload the same using Save document functionality.
3. All the documents saved are added in a database table.
4. List of all the documents present in database is displayed on the main page.
5. Each document in the list have two buttons: Delete and Download.
6. Any document can be downloaded by clicking on download button.
7. Any document can be deleted by clicking on delete button.

Here is the final screen shot of Document manager application.

Step 1: Create Database Table

For Document Manager application, we will use MySQL database. Create a table documents in MySQL database docdb. This is very preliminary example and thus we have minimum columns to represent a document. Feel free to extend this example and create a more complex application.

CREATE DATABASE `docdb`;

USE `docdb`;

CREATE TABLE `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `filename` varchar(200) NOT NULL,
  `content` mediumblob NOT NULL, /* for ORACLE enter BLOB*/
  `content_type` varchar(255) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);
Code language: SQL (Structured Query Language) (sql)

Step 2: Create Maven Project in Eclipse

The document manager application will use Maven for build and dependency management. For this we will use the Maven Dynamic Web Project in Eclipse as the base architecture of our application. Or directly download the below source code: Maven Dynamic Web Project (6.7 KB) Once you have imported / created the Maven web project in Eclipse. Copy following content into Maven’s pom.xml file. These are the dependencies we will use in our Document manager application. File: /pom.xml

<?xml version="1.0" encoding="UTF-8"?><project>
  <modelVersion>4.0.0</modelVersion>
  <groupId>MavenWeb</groupId>
  <artifactId>MavenWeb</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <description></description>
  <build>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>1.5</source>
          <target>1.5</target>
        </configuration>
      </plugin>
      <plugin>
        <artifactId>maven-war-plugin</artifactId>
        <version>2.0</version>
      </plugin>
    </plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>servlet-api</artifactId>
      <version>2.5</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>${org.springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${org.springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${org.springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${org.springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>${org.springframework.version}</version>
    </dependency>
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.1.2</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>3.3.2.ga</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.10</version>
    </dependency>
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>20030825.184428</version>
    </dependency>
    <dependency>
      <groupId>commons-pool</groupId>
      <artifactId>commons-pool</artifactId>
      <version>1.5.4</version>
    </dependency>
    <dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.2.1</version>
    </dependency>
    <dependency>
      <groupId>commons-io</groupId>
      <artifactId>commons-io</artifactId>
      <version>1.3</version>
    </dependency>
  </dependencies>
  <properties>
    <org.springframework.version>3.0.2.RELEASE</org.springframework.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>
Code language: HTML, XML (xml)

Unzip the source code to your hard drive and import the project in Eclipse. Once the project is imported in Eclipse, we will create package structure for Java source. First rename the project to DocumentManager and create following packages under src/main/java folder.

1. net.viralpatel.docmanager.controller – This package will contain Spring Controller classes for Document Manager application.
2. net.viralpatel.docmanager.model – This package will contain form object for Document manager application. Document model will be a simple POJO class with different attributes such as document name, description, filename etc.
3. net.viralpatel.docmanager.dao – This is the DAO layer of Document manager application. It consists of DocumentDao class which will use Hibernate API to interact with database.
4. The src/main/resources folder will have hibernate configuration file: hibernate.cfg.xml.
5. The WEB-INF folder will have jsp/documents.jsp file to render document list and add form and jdbc.properties file containing database connection configuration. Also it contains spring-servlet.xml which will define all the Controller class and web.xml which contain spring configuration.

Entity class – The Hibernate model class

Let us start with the coding of Document manager application. First we will create a model object or hibernate POJO class to store document information. Also this class will be an Entity class and will be linked with DOCUMENTS table in database. Create a java class Document.java under net.viralpatel.docmanager.model package and copy following code into it. File: /src/main/java/net/viralpatel/docmanager/model/Document.java

package net.viralpatel.docmanager.model;

import java.sql.Blob;
import java.sql.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;

@Entity
@Table(name="documents")
public class Document {
 
 @Id
 @GeneratedValue
 @Column(name="id")
 private Integer id;

 @Column(name="name")
 private String name;

 @Column(name="description")
 private String description;

 @Column(name="filename")
 private String filename;

 @Column(name="content")
 @Lob
 private Blob content;
 
 @Column(name="content_type")
 private String contentType;
 
 @Column(name="created")
 private Date created;
 
 //Getter and Setter methods
}
Code language: Java (java)

The first thing you’ll notice is that the import statements import from javax.persistence rather than a Hibernate or Spring package. Using Hibernate with Spring, the standard JPA annotations work just as well and that’s what I’m using here.

• First we’ve annotated the class with @Entity which tells Hibernate that this class represents an object that we can persist.
• The @Table(name = "documents") annotation tells Hibernate which table to map properties in this class to documents table. The first property in this class on line 20 is our object ID which will be unique for all events persisted. This is why we’ve annotated it with @Id.
• The @GeneratedValue annotation says that this value will be determined by the datasource, not by the code.
• The @Column(name = "filename") annotation is used to map this property to the FILENAME column in the DOCUMENTS table.

The Data Access (DAO) Layer

The DAO layer of Document Manager application consist of a class DocumentDAO. Ideal solution will be to create an interface (DocumentDAO) and its corresponding implementation class DocumentDAOImpl. But for sake of simplicity we will create just normal DAO class DocumentDAO.java. File: src/main/java/net/viralpatel/docmanager/dao/DocumentDAO.java

package net.viralpatel.docmanager.dao;

import java.util.List;

import net.viralpatel.docmanager.model.Document;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class DocumentDAO {
 
 @Autowired
 private SessionFactory sessionFactory;
 
 @Transactional
 public void save(Document document) {
  Session session = sessionFactory.getCurrentSession();
  session.save(document);
 }
 
 @Transactional
 public List<Document> list() {
  Session session = sessionFactory.getCurrentSession();
  List<Document> documents = null;
  try {
   documents = (List<Document>)session.createQuery("from Document").list();

  } catch (HibernateException e) {
   e.printStackTrace();
  }
  return documents;
 }
 
 @Transactional
 public Document get(Integer id) {
  Session session = sessionFactory.getCurrentSession();
  return (Document)session.get(Document.class, id);
 }

 @Transactional
 public void remove(Integer id) {
  Session session = sessionFactory.getCurrentSession();
  
  Document document = (Document)session.get(Document.class, id);
  
  session.delete(document);
 }
}
Code language: Java (java)

Methods:

• list() Method – This method gets the list of all documents stored in documents table and return a List of Document objects.
• save() Method – This method is used to store a new document (including BLOB) into database.
• get() Method – This method returns Document entry for a given ID from database. Used in download functionality to download a stored document from database.
• remove() Method – This method is used to delete a document with specific ID from database.

Note that we have used two Spring annotations @Repository and @Autowired. Classes marked with annotations are candidates for auto-detection by Spring when using annotation-based configuration and classpath scanning. The @Component annotation is the main stereotype that indicates that an annotated class is a “component”. The @Repository annotation is yet another stereotype that was introduced in Spring 2.0. This annotation is used to indicate that a class functions as a repository and needs to have exception translation applied transparently on it. The benefit of exception translation is that the service layer only has to deal with exceptions from Spring’s DataAccessException hierarchy, even when using plain JPA in the DAO classes. Another annotation used in DocumentDAO is @Autowired. This is used to autowire the dependency of the DocumentDAO on the SessionFactory. Also note that we have used @Transactional annotation on each method. Ideally the DAO layer is access from a Service layer and transaction management needs to be specified at Service layer. But again for sake of simplicity we will not include service layer in our example and directly call the DAO layer from Spring Controller. Again, feel free to change this implementation and add your own service layer in between. For more information about A layered application with Services in Spring MVC and Hibernate read this tutorial. Spring MVC Hibernate Maven example

Adding Spring MVC Support to Webapplication

Let us add Spring MVC support to our web application. Update the web.xml file and add servlet mapping for org.springframework.web.servlet.DispatcherServlet. Also note that we have mapped url / with springServlet so all the request are handled by spring. File: /src/webapp/WEB-INF/web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns="http://java.sun.com/xml/ns/javaee"
 xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
 id="WebApp_ID" version="2.5">
 <display-name>DocumentManager</display-name>
 <welcome-file-list>
  <welcome-file>index.html</welcome-file>
 </welcome-file-list>
 <servlet>
  <servlet-name>spring</servlet-name>
  <servlet-class>
   org.springframework.web.servlet.DispatcherServlet
  </servlet-class>
  <load-on-startup>1</load-on-startup>
 </servlet>
 <servlet-mapping>
  <servlet-name>spring</servlet-name>
  <url-pattern>*.html</url-pattern>
 </servlet-mapping>
</web-app>
Code language: HTML, XML (xml)

Once the web.xml is configured, let us add spring-servlet.xml and jdbc.properties files in /src/main/webapp/WEB-INF folder. File: /src/main/webapp/WEB-INF/jdbc.properties

jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/docdb
jdbc.username=root
jdbc.password=password
Code language: HTML, XML (xml)

The jdbc.properties file contains database connection information such as database url, username, password, driver class. You may want to edit the driverclass and dialect to other DB if you are not using MySQL. File: /src/main/webapp/WEB-INF/spring-servlet.xml

<?xml  version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:aop="http://www.springframework.org/schema/aop"
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:jee="http://www.springframework.org/schema/jee"
 xmlns:lang="http://www.springframework.org/schema/lang"
 xmlns:p="http://www.springframework.org/schema/p"
 xmlns:tx="http://www.springframework.org/schema/tx"
 xmlns:util="http://www.springframework.org/schema/util"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
  http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
  http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
  http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
  http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

 <context:annotation-config />
 <context:component-scan base-package="net.viralpatel.docmanager" />

 <bean id="jspViewResolver"
  class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  <property name="viewClass"
   value="org.springframework.web.servlet.view.JstlView" />
  <property name="prefix" value="/WEB-INF/jsp/" />
  <property name="suffix" value=".jsp" />
 </bean>

 <bean id="propertyConfigurer"
  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
  p:location="/WEB-INF/jdbc.properties" />

 <bean id="dataSource"
  class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
  p:driverClassName="${jdbc.driverClassName}"
  p:url="${jdbc.databaseurl}" p:username="${jdbc.username}"
  p:password="${jdbc.password}" />


 <bean id="sessionFactory"
  class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="configLocation">
   <value>classpath:hibernate.cfg.xml</value>
  </property>
  <property name="configurationClass">
   <value>org.hibernate.cfg.AnnotationConfiguration</value>
  </property>
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">${jdbc.dialect}</prop>
    <prop key="hibernate.show_sql">true</prop>
    <prop key="hibernate.connection.SetBigStringTryClob">true</prop>
    <prop key="hibernate.jdbc.batch_size">0</prop>
   </props>
  </property>
 </bean>
 <bean id="multipartResolver"
  class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

  <!-- one of the properties available; the maximum file size in bytes -->
  <property name="maxUploadSize" value="10000000" />
 </bean>
 <tx:annotation-driven />

 <bean id="transactionManager"
  class="org.springframework.orm.hibernate3.HibernateTransactionManager">
  <property name="sessionFactory" ref="sessionFactory" />
 </bean>
</beans>
Code language: HTML, XML (xml)

The spring-servlet.xml file contains different spring mappings such as transaction manager, hibernate session factory bean, data source etc.

• jspViewResolver bean – This bean defined view resolver for spring mvc. For this bean we also set prefix as “/WEB-INF/jsp/” and suffix as “.jsp”. Thus spring automatically resolves the JSP from WEB-INF/jsp folder and assigned suffix .jsp to it.
• propertyConfigurer bean – This bean is used to load database property file jdbc.properties. The database connection details are stored in this file which is used in hibernate connection settings.
• dataSource bean – This is the java datasource used to connect to document manager database. We provide jdbc driver class, username, password etc in configuration.
• sessionFactory bean – This is Hibernate configuration where we define different hibernate settings. hibernate.cfg.xml is set a config file which contains entity class mappings. Also note that in sessionFactory we have specified few hibernate properties such as hibernate.connection.SetBigStringTryClob and hibernate.jdbc.batch_size. These are used to configure BLOB / CLOB settings in hibernate.
• multipartResolver bean – We use Spring MVCs CommonsMultipartResolver. This resolver will resolve multipart form data such as file uploads from the request and make available File object to spring controller. Note that we have specified property maxUploadSize with value 10000000. This is the maximum limit of filesize which can be uploaded in our example.
• transactionManager bean – We use hibernate transaction manager to manage the transactions of our document manager application.

File: /src/main/resources/hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

 
<hibernate-configuration>
    <session-factory>
        <mapping class="net.viralpatel.docmanager.model.Document" />
    </session-factory>
     
</hibernate-configuration>
Code language: HTML, XML (xml)

The Controller – Spring MVC controller class

We are almost done with our application. Just add following Spring controller class DocumentController.java to net.viralpatel.docmanager.controller package. File: /src/main/java/net/viralpatel/docmanager/controller/DocumentController.java

package net.viralpatel.docmanager.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.viralpatel.docmanager.dao.DocumentDAO;
import net.viralpatel.docmanager.model.Document;

import org.apache.commons.io.IOUtils;
import org.hibernate.Hibernate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

@Controller
public class DocumentController {
 
 @Autowired
 private DocumentDAO documentDao;
 
 @RequestMapping("/index")
 public String index(Map<String, Object> map) {
  try {
   map.put("document", new Document());
   map.put("documentList", documentDao.list());
  }catch(Exception e) {
   e.printStackTrace();
  }

  return "documents";
 }

 @RequestMapping(value = "/save", method = RequestMethod.POST)
 public String save(
   @ModelAttribute("document") Document document,
   @RequestParam("file") MultipartFile file) {
  
  
  System.out.println("Name:" + document.getName());
  System.out.println("Desc:" + document.getDescription());
  System.out.println("File:" + file.getName());
  System.out.println("ContentType:" + file.getContentType());
  
  try {
   Blob blob = Hibernate.createBlob(file.getInputStream());

   document.setFilename(file.getOriginalFilename());
   document.setContent(blob);
   document.setContentType(file.getContentType());
  } catch (IOException e) {
   e.printStackTrace();
  }
  
  try {
   documentDao.save(document);
  } catch(Exception e) {
   e.printStackTrace();
  }
  
  return "redirect:/index.html";
 }

 @RequestMapping("/download/{documentId}")
 public String download(@PathVariable("documentId")
   Integer documentId, HttpServletResponse response) {
  
  Document doc = documentDao.get(documentId);
  try {
   response.setHeader("Content-Disposition", "inline;filename=\"" +doc.getFilename()+ "\"");
   OutputStream out = response.getOutputStream();
   response.setContentType(doc.getContentType());
   IOUtils.copy(doc.getContent().getBinaryStream(), out);
   out.flush();
   out.close();
  
  } catch (IOException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  
  
  return null;
 }

 @RequestMapping("/remove/{documentId}")
 public String remove(@PathVariable("documentId")
   Integer documentId) {
  
  documentDao.remove(documentId);
  
  return "redirect:/index.html";
 }
}
Code language: Java (java)

The spring controller defines four methods to manipulate document manager application.

• index method – This method uses list() method of DocumentDAO to fetch the list of all documents from database. Note that we have mapped request “/index” to this method. Thus Spring will automatically calls this method whenever it encounters this url in request.
• save method – This method adds a new document to document list. The document details are fetched in Document object using @ModelAttribute annotation. Also note that the request “/save” is mapped with this method. The request method should also be POST. Once the document is added in document list, we redirect to /index.html page which in turn calls index() method to display document list to user. One more thing to note here is @RequestParam. We are mapping MultipartFile object using @RequestParam(“file”) annotation. Spring automatically detects “file” data from request and map it with MultipartFile object. This object is later converted to BLOB object and set in the Document content.
  Related: Forms in Spring MVC
• download method – This method is used to download a selected testcase. Note that we are fetching the document content from database using DAO class and thn set the Data stream in Response. Also note that we are using response.setHeader() method to set "Content-Disposition". This will raise a Save As dialog box in browser whenever user tries to download a document.
• remove method – This methods removes a document from the document list. Similar to save() this method also redirects user to /index.html page once the document is removed. One thing to note in this method is the way we have mapped request url using @RequestMapping annotation. The url “/remove/{documentId}” is mapped thus whenever user send a request /remove/12.html, the remove method will try to delete document with ID:12.

Finally add following JSP file to WEB-INF/jsp folder. File: /src/main/webapp/WEB-INF/jsp/documents.jsp

<%@taglib uri="http://www.springframework.org/tags" prefix="spring"%>
<%@taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
 <title>Document Manager - viralpatel.net</title>
</head>
<body>

<h2>Document Manager</h2>

<h3>Add new document</h3>
<form:form method="post" action="save.html" commandName="document" enctype="multipart/form-data">
 <form:errors path="*" cssClass="error"/>
 <table>
 <tr>
  <td><form:label path="name">Name</form:label></td>
  <td><form:input path="name" /></td> 
 </tr>
 <tr>
  <td><form:label path="description">Description</form:label></td>
  <td><form:textarea path="description" /></td>
 </tr>
 <tr>
  <td><form:label path="content">Document</form:label></td>
  <td><input type="file" name="file" id="file"></input></td>
 </tr>
 <tr>
  <td colspan="2">
   <input type="submit" value="Add Document"/>
  </td>
 </tr>
</table> 
</form:form>

<br/>
<h3>Document List</h3>
<c:if  test="${!empty documentList}">
<table class="data">
<tr>
 <th>Name</th>
 <th>Description</th>
 <th>&nbsp;</th>
</tr>
<c:forEach items="${documentList}" var="document">
 <tr>
  <td width="100px">${document.name}</td>
  <td width="250px">${document.description}</td>
  <td width="20px">
   <a href="${pageContext.request.contextPath}/download/${document.id}.html"><img 
    src="${pageContext.request.contextPath}/img/save_icon.gif" border="0" 
    title="Download this document"/></a> 
  
   <a href="${pageContext.request.contextPath}/remove/${document.id}.html"
    onclick="return confirm('Are you sure you want to delete this document?')"><img 
    src="${pageContext.request.contextPath}/img/delete_icon.gif" border="0" 
    title="Delete this document"/></a> 
  </td>
 </tr>
</c:forEach>
</table>
</c:if>
</body>
</html>
Code language: HTML, XML (xml)

Download Source code

Click here to download full source code of Document manager application (16 KB)

That’s All folks

Compile and execute the Document manager application in Eclipse. Open URL http://localhost:8080/DocumentManager/index.html