Spring 3.2 MVC insert and retrieve blob from the database

In this section, you will learn about inserting and retrieving blob from the database.

Spring 3.2 MVC insert and retrieve blob from the database

Spring 3.2 MVC insert and retrieve blob from the database

In this section, you will learn about inserting and retrieving blob from the database.

In the given below example, you can upload a blob ,like .doc .txt .pdf etc, from your local hard drive to the database. After this you will see the uploaded content in the same page below the form for upload.

In the same page. you can delete and download the content of database table directly by clicking on the image-link for delete and download.

Application Flow

When you run the application, the first page which appear to you will be:

Click on the Upload Form hyperlink or image, both act as hyperlink to the upload form, which is  :

When you upload blob correctly, it will show you the uploaded file/document in the same page :

When you click on delete icon :

When you click on download image :

Project Structure

Jar File used

Database Query

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,
	`content_type` varchar(255) NOT NULL,
	`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
);

CODES

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"
version="2.5">
<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>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>

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:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<context:property-placeholder location="classpath:jdbc.properties" />
<context:component-scan base-package="net.roseindia" />

<tx:annotation-driven transaction-manager="hibernateTransactionManager" />

<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="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${database.driver}" />
<property name="url" value="${database.url}" />
<property name="username" value="${database.user}" />
<property name="password" value="${database.password}" />
</bean>

<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="annotatedClasses">
<list>
<value>net.roseindia.model.File</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
</bean>

<bean id="hibernateTransactionManager"
class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</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>
</beans>

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Spring 3 MVC insert/fetch Blob from database</title>
</head>
<body bgcolor="#DCDCDC">
<h2><font color="red">Spring 3 MVC insert/fetch Blob from
database</font></h2>
<hr>
<h2><a href="index.html">Upload Form</a></h2>
<a href="index.html"> <img src="img/Upload-icon.png" /> </a>

<hr>
</body>
</html>

jdbc.properties

database.driver=com.mysql.jdbc.Driver
database.url=jdbc:mysql://localhost:3306/docdb
database.user=root
database.password=root
hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
hibernate.show_sql=true

FileController.java

package net.roseindia.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.roseindia.dao.FileDAO;
import net.roseindia.model.File;

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 FileController {

@Autowired
private FileDAO documentDao;

@RequestMapping("/index")
public String index(Map<String, Object> map) {
try {
map.put("document", new File());
map.put("documentList", documentDao.list());
} catch (Exception e) {
e.printStackTrace();
}

return "documents";
}

@RequestMapping(value = "/save", method = RequestMethod.POST)
public String save(@ModelAttribute("document") File 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) {

File 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";
}

}

FileDAO.java

package net.roseindia.dao;

import java.util.List;

import net.roseindia.model.File;

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 FileDAO {

@Autowired
private SessionFactory sessionFactory;

@Transactional
public void save(File document) {
Session session = sessionFactory.getCurrentSession();
session.save(document);
}

@Transactional
public List<File> list() {
Session session = sessionFactory.getCurrentSession();
List<File> documents = null;
try {
documents = (List<File>) session.createQuery("from File").list();

} catch (HibernateException e) {
e.printStackTrace();
}
return documents;
}

@Transactional
public File get(Integer id) {
Session session = sessionFactory.getCurrentSession();
return (File) session.get(File.class, id);
}

@Transactional
public void remove(Integer id) {
Session session = sessionFactory.getCurrentSession();

File document = (File) session.get(File.class, id);

session.delete(document);
}
}

File.java

package net.roseindia.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 File {

@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;

public Integer getId() {
return id;
}

public String getName() {
return name;
}

public String getDescription() {
return description;
}

public String getFilename() {
return filename;
}

public Blob getContent() {
return content;
}

public Date getCreated() {
return created;
}

public void setId(Integer id) {
this.id = id;
}

public void setName(String name) {
this.name = name;
}

public void setDescription(String description) {
this.description = description;
}

public void setFilename(String filename) {
this.filename = filename;
}

public void setContent(Blob content) {
this.content = content;
}

public void setCreated(Date created) {
this.created = created;
}

public String getContentType() {
return contentType;
}

public void setContentType(String contentType) {
this.contentType = contentType;
}
}

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>Upload Blob to database</title>
<style type="text/css">
html,body,div,span,applet,object,iframe,h1,h2,h3,h4,h5,h6,p,blockquote,pre,a,abbr,acronym,address,big,cite,code,del,dfn,em,font,img,ins,kbd,q,s,samp,small,strike,strong,sub,sup,tt,var,b,u,i,center,dl,dt,dd,ol,ul,li,fieldset,form,label,legend,table,caption,tbody,tfoot,thead,tr,th,td,input
{
font-size: 100%;
}

body {
font-family: sans-serif;
font-size: 12px;
}

.data,.data td {
border-collapse: collapse;
width: 550px;
border: 1px solid #aaa;
padding: 2px;
}

.data th {
background-color: #7CFC00;
color: black;
font-weight: bold;
}

h1,h2,h3 {
font-family: Trebuchet MS, Liberation Sans, DejaVu Sans, sans-serif;
font-weight: bold;
}

h1 {
font-size: 170%;
}

h2 {
font-size: 140%;
}

h3 {
font-size: 120%;
}
</style>
</head>
<body bgcolor="#DCDCDC">

<h2><font color="red">Upload Files/Documents in Database</font></h2>
<hr>
<h3>Upload new File</h3>
<form:form method="post" action="save.html" commandName="document"
enctype="multipart/form-data">
<form:errors path="*" cssClass="error" />
<table border="1" bordercolor="lime">
<tr>
<td><form:label path="name">File name</form:label></td>
<td><form:input path="name" /></td>
</tr>
<tr>
<td><form:label path="description">File detail</form:label></td>
<td><form:textarea path="description" /></td>
</tr>
<tr>
<td><form:label path="content">File</form:label></td>
<td><input type="file" name="file" id="file"></input></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value="Upload File" /></td>
</tr>
</table>
</form:form>
<br />
<hr>
<h3>Uploaded Files/Documents</h3>
<c:if test="${!empty documentList}">
<table class="data">
<tr>
<th>Filename</th>
<th>FileDetail</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>
<br />
<hr>
</body>
</html>

Download Source Code

Tutorials

  1. Spring 3.2
  2. New Features of Spring Framework 3.2
  3. Spring MVC framework Introduction
  4. Special Bean Types of WebApplicationContext
  5. Spring MVC framework Features
  6. Processing Sequence of DispatcherServlet
  7. @Controller annotation for defining new controller
  8. @RequestMapping annotation for mapping requests
  9. Spring 3.2 Asynchronous Request Processing
  10. Defining handler methods using @RequestMapping annotation
  11. Requests Intercepting through a HandlerInterceptor
  12. View Resolving through ViewResolver interface
  13. Chaining of Multiple view resolvers
  14. Redirecting and forwarding to views
  15. ContentNegotiatingViewResolver
  16. Locales in Spring MVC
  17. Themes and Theme resolvers in Spring MVC
  18. Multipart support for file upload in Spring MVC
  19. @ExceptionHandler & @ResponseStatus annotation
  20. Customizing the Default Error Page
  21. Form Tag library configuration
  22. The form tag
  23. The input tag
  24. The password tag
  25. ControllerClassNameHandlerMapping class for handling convention mapping
  26. Modification in conventional ModelAndView
  27. Autogenerated logical view name through RequestToViewNameTranslator
  28. The checkbox tag
  29. Support for ETag
  30. Servlet container initialization through code
  31. MVC Java Config or the MVC XML Namespace
  32. Customizing the MVC Java config or XML Namespace
  33. Interceptors Configuration using Java or XML
  34. Content Negotiation Configuration
  35. Spring 3.2 MVC Hello World Example
  36. View resolvers
  37. View Controllers Configuration
  38. Static Resources Configuration
  39. mvc:default-servlet-handler
  40. The checkboxes tag