Java Tutorial: Saving and Retrieving of BLOB object in MySQL using Spring 3 MVC and Hibernate

In this example we will show step by step how to save and retrieve Blob Object in Mysql DB using Spring MVC + Hibernate

Project Structure will look like below

Below is the Content for pom.xml

To Upload a file we need to add commons-fileupload dependency in pom.xml and dependencies for Spring MVC and Hibernate.

<?xml version="1.0" encoding="UTF-8"?><project>
<modelVersion>4.0.0</modelVersion>
<groupId>MavenWeb</groupId>
<artifactId>MavenWebBlob</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<description></description>
<build>
    <finalName>MavenWebBlob</finalName>
    <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>javax.servlet</groupId>
        <artifactId>jsp-api</artifactId>
        <version>2.0</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${spring.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.6.10.Final</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>
    <spring.version>3.1.2.RELEASE</spring.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
</project>

Below is the Content for Table creation for Files.

CREATE DATABASE `filedb`;

USE `filedb`;

CREATE TABLE `files` (
`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`)
);

Below is the Content for FileUploadController.java

FileUploadController defines four methods to manipulate File manager application.

package com.mahesh.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 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;

import com.mahesh.dao.FileUploadDAO;
import com.mahesh.model.File;

@Controller
public class FileUploadController {

    @Autowired
    private FileUploadDAO fileDao;

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

        return "files";
    }

    @RequestMapping(value = "/save", method = RequestMethod.POST)
    public String save(
            @ModelAttribute("file") File file,
            @RequestParam("file") MultipartFile mulipartFile) {


        System.out.println("Name:" + file.getName());
        System.out.println("Desc:" + file.getDescription());
        System.out.println("File:" + mulipartFile.getName());
        System.out.println("ContentType:" + mulipartFile.getContentType());

        try {
            Blob blob = Hibernate.createBlob(mulipartFile.getInputStream());

            file.setFilename(mulipartFile.getOriginalFilename());
            file.setContent(blob);
            file.setContentType(mulipartFile.getContentType());
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            fileDao.save(file);
        } catch(Exception e) {
            e.printStackTrace();
        }

        return "redirect:/index.html";
    }

    @RequestMapping("/download/{fileId}")
    public String download(@PathVariable("fileId")
            Integer fileId, HttpServletResponse response) {

        File doc = fileDao.get(fileId);
        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/{fileId}")
    public String remove(@PathVariable("fileId")
            Integer fileId) {

        fileDao.remove(fileId);

        return "redirect:/index.html";
    }

}

Below is the Content for File.java

create a model object or hibernate POJO class File.java to store file information. Also this class will be an Entity class and will be linked with FILES table in database.Create a java class File.java under com.mahesh.model package

package com.mahesh.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="files")
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;
    }
}

Below is the Content for FileUploadDAO.java

The DAO layer of File Manager application consist of a class FileUploadDAO Which responsible for
all DB operations through HibernateTemplate.

package com.mahesh.dao;

import java.util.List;

import org.hibernate.HibernateException;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.mahesh.model.File;

@Repository
public class FileUploadDAO {

    private HibernateTemplate template;

    public HibernateTemplate getTemplate() {
        return template;
    }

    public void setTemplate(HibernateTemplate template) {
        this.template = template;
    }

    @Transactional
    public void save(File file) {
        template.save(file);
    }

    @Transactional
    public List<File> list() {
        List<File> files = null;
        try {
            files = (List<File>)template.find("from File");

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

    @Transactional
    public File get(Integer id) {
        return (File)template.get(File.class, id);
    }

    @Transactional
    public void remove(Integer id) {
        File file = (File)template.get(File.class, id);

        template.delete(file);
    }
}

Below is the Content for 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="com.mahesh.model.File" />
    </session-factory>

</hibernate-configuration>

Below is the Content for jdbc.properties

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.

jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/filedb
jdbc.username=root
jdbc.password=root

Below is the Content for spring-servlet.xml

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

<?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-3.0.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

    <context:annotation-config />
    <context:component-scan base-package="com.mahesh" />

    <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>
                <prop key="hibernate.jdbc.batch_size">0</prop>
            </props>
        </property>
    </bean>

    <bean id="template" class="org.springframework.orm.hibernate3.HibernateTemplate">
        <property name="sessionFactory" ref="sessionFactory"></property>
    </bean>

    <bean id="fileDao" class="com.mahesh.dao.FileUploadDAO">
        <property name="template" ref="template"></property>
    </bean>


    <bean id="multipartResolver"
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

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

Below is the Content for web.xml

Update the web.xml file and add servlet mapping for DispatcherServlet. Also note that we have given name as spring so it will look for file named spring-servlet.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>

Finally add the Content for files.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>File Upload Manager - cmkspring.blogspot.in</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: #F29A87;
            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>

<h2>File Manager</h2>

<h3>Add new File</h3>
<form:form method="post" action="save.html" commandName="file" 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">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/>
<h3>List of Uploaded Files</h3>
<c:if test="${!empty fileList}">
<table class="data">
<tr>
    <th>Name</th>
    <th>Description</th>
    <th> </th>
</tr>
<c:forEach items="${fileList}" var="file">
    <tr>
        <td width="100px">${file.name}</td>
        <td width="250px">${file.description}</td>
        <td width="20px">
            <a href="${pageContext.request.contextPath}/download/${file.id}.html"><img
                src="${pageContext.request.contextPath}/img/save_icon.gif" border="0"
                title="Download this file"/></a>

            <a href="${pageContext.request.contextPath}/remove/${file.id}.html"
                onclick="return confirm('Are you sure you want to delete this file?')"><img
                src="${pageContext.request.contextPath}/img/delete_icon.gif" border="0"
                title="Delete this file"/></a>
        </td>
    </tr>
</c:forEach>
</table>
</c:if>

</body>
</html>

Run the Application

generate war file through the maven command mvn clean install and deploy in tomcat Server.

http://localhost:8080/MavenWebBlob/

Java Tutorial

Wednesday 26 February 2014

Saving and Retrieving of BLOB object in MySQL using Spring 3 MVC and Hibernate