Spring 3 MVC insert/retrieve blob in database

In this section, you will learn how to insert/retrieve blob in the database.

Spring 3 MVC insert/retrieve blob in database

In this section, you will learn how to insert/retrieve blob in 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.

Second thing is that 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 should 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 image 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 (/Spring3MvcBlob/WebContent/WEB-INF/web.xml)

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee 
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

<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 (/Spring3MvcBlob/WebContent/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: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.devmanuals" />

<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.devmanuals.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 (/Spring3MvcBlob/WebContent/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 (/Spring3MvcBlob/src/jdbc.properties)

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

FileController.java (/Spring3MvcBlob/src/net/devmanuals/controller/FileController.java)

package net.devmanuals.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.devmanuals.dao.FileDAO;
import net.devmanuals.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 (/Spring3MvcBlob/src/net/devmanuals/dao/FileDAO.java)

package net.devmanuals.dao;

import java.util.List;

import net.devmanuals.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 (/Spring3MvcBlob/src/net/devmanuals/model/File.java)

package net.devmanuals.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 (/Spring3MvcBlob/WebContent/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>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