Click here to Skip to main content
15,435,432 members
Articles / Programming Languages / SQL
Article
Posted 3 Feb 2015

Tagged as

Stats

38.5K views
1.1K downloads
8 bookmarked

Upload and Download Files with JDBC to SQL Servers

Rate me:
Please Sign up or sign in to vote.
3.43/5 (5 votes)
4 Feb 2015CPOL8 min read
This article presents an example to upload and download files with SQL server through JDBC in a Spring MVC application.

Introduction

This article presents an example to upload and download files with SQL server through JDBC in a Spring MVC application.

Background

JDBC is as mature as Java itself, and there are many alternatives built upon it afterwards. It is still the most important approach to access the database, simply because it is the fundamental. When other approaches do not satisfy your needs, JDBC is always there. This article presents an example to upload and download files with SQL server through JDBC in a Spring MVC application. The example used jQuery file upload plugin to upload the files to the server. I have tested the application on Firefox and Chrome. Since jQuery file upload plugin requires HTML5 support from the web browser and I do not have a recent version of IE, I did not test this application on Internet Explorer.

The Database

The SQL server used in my test is the SQL server 2014 express. But if you have an earlier version of SQL server, I believe that the example should work on it too. If you have the administrative permission to the server, you can issue the following script to create the test database.

SQL
USE master
GO
    
-- Create the [Experiment] database
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Experiment')
BEGIN
    ALTER DATABASE Experiment SET SINGLE_USER WITH ROLLBACK IMMEDIATE
END
GO
    
DROP DATABASE Experiment
GO
CREATE DATABASE [Experiment]
GO
    
-- Create the FileLoader login to the server
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'FileLoader')
DROP LOGIN [FileLoader]
GO
    
EXEC sp_addlogin @loginame = 'FileLoader', @passwd  = 'Pwd123456';
GO
    
USE Experiment
GO
    
-- Grant FileLoader the access to the Experiment database
sp_grantdbaccess 'FileLoader'
GO
    
-- Create the ID table
CREATE TABLE [dbo].[TABLE_ID](
    [TableName] [varchar](255) NOT NULL,
    [Current_ID] [int] NOT NULL,
 CONSTRAINT [PK_TABLE_ID] PRIMARY KEY CLUSTERED ([TableName] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
    
INSERT INTO TABLE_ID VALUES ('TABLE_FILES', 0)
GO
    
-- Create the file table
CREATE TABLE [dbo].[TABLE_FILES](
    [ID] [int] NOT NULL,
    [Name] [varchar](255) NOT NULL,
    [ContentType] [varchar](255) NOT NULL,
    [Size] [bigint] NOT NULL,
    [Data] [varbinary](max) NOT NULL,
    [UploadTime] [datetime] NOT NULL,
 CONSTRAINT [PK_TABLE_FILES] PRIMARY KEY NONCLUSTERED ([ID] ASC)
 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
    
-- Create the stored procedures
CREATE PROCEDURE [dbo].[File_Insert]
    @name VARCHAR(255),
    @contentType VARCHAR(255),
    @size BIGINT,
    @data VARBINARY(max),
    @fileId INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    -- Get an Id for the file
    BEGIN TRANSACTION
    BEGIN TRY
        SELECT @fileId = Current_ID + 1 FROM [TABLE_ID] WITH(XLOCK, ROWLOCK)
        WHERE [TableName] = 'TABLE_FILES'
    
        UPDATE [TABLE_ID] SET Current_ID = @fileId WHERE [TableName] = 'TABLE_FILES'
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    
        RAISERROR ('Unable to get an Id for the file', 16, 1)
        RETURN
    END CATCH
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
    
    -- Insert the file to the table
    INSERT INTO [TABLE_FILES] VALUES(@fileId, @name, @contentType, @size, @data, GETDATE())
END
GO
    
CREATE PROCEDURE [dbo].[File_Delete]
    @fileId INT
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    DELETE FROM [TABLE_FILES] WHERE ID = @fileId
END
GO
    
CREATE PROCEDURE [dbo].[File_Select]
    @fileId INT
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    SELECT ID, Name, ContentType, Size, Data
    FROM [TABLE_FILES] WHERE ID = @fileId
END
GO
    
CREATE PROCEDURE [dbo].[File_List]
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    
    SELECT ID, Name, ContentType, Size FROM [TABLE_FILES]
    ORDER BY UploadTime DESC
END
GO
    
-- Grant FileLoader the execution permissons to the SPs
GRANT EXECUTE ON OBJECT::[dbo].[File_Insert] TO FileLoader
GRANT EXECUTE ON OBJECT::[dbo].[File_Delete] TO FileLoader
GRANT EXECUTE ON OBJECT::[dbo].[File_Select] TO FileLoader
GRANT EXECUTE ON OBJECT::[dbo].[File_List] TO FileLoader
    
-- Check the initial data
SELECT * FROM TABLE_ID
SELECT * FROM TABLE_FILES
GO
    
-- Bring the database on-line
ALTER DATABASE [Experiment] SET MULTI_USER
GO

If the script runs successfully, you should have a database named [Experiment] in your server.

Image 1

The [Experiment] database should have the following components:

  • Tables [Table_files] and [Table_id]. The [Table_files] will be used to save the uploaded files and the [Table_id] will be used to generate the Ids for the files;
  • Stored procedures [File_Delete], [File_Insert], [File_List], and [File_Select];
  • The database user [FileLoader] with the password "Pwd123456" has already been granted the permission to execute the stored procedures. We will be using this database user to access the database from our Spring MVC application.

The stored procedure [File_Insert] will be used to save the files to the database. Let us take some detailed look at it.

SQL
CREATE PROCEDURE [dbo].[File_Insert]
    @name VARCHAR(255),
    @contentType VARCHAR(255),
    @size BIGINT,
    @data VARBINARY(max),
    @fileId INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    -- Get an Id for the file
    BEGIN TRANSACTION
    BEGIN TRY
        SELECT @fileId = Current_ID + 1 FROM [TABLE_ID] WITH(XLOCK, ROWLOCK)
        WHERE [TableName] = 'TABLE_FILES'
    
        UPDATE [TABLE_ID] SET Current_ID = @fileId WHERE [TableName] = 'TABLE_FILES'
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    
        RAISERROR ('Unable to get an Id for the file', 16, 1)
        RETURN
    END CATCH
    
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;
    
    -- Insert the file to the table
    INSERT INTO [TABLE_FILES] VALUES(@fileId, @name, @contentType, @size, @data, GETDATE())
END
  • You may notice that obtaining the file id and saving the file are in two different transactions. By doing this, we can quickly release the lock on the [Table_id] after we finish obtaining the file id, so other concurrent operations are not blocked by the insert operation on the [Table_files];
  • When reading the file id from the [Table_id], we used the query hint "XLOCK". This is to avoid the possible deadlocks if the stored procedure is called at the same time by other concurrent processes to upload other files.

If you now issue the following command to the database, you can check the data in the two tables.

SQL
-- Check the initial data
SELECT * FROM TABLE_ID
SELECT * FROM TABLE_FILES
GO

Image 2

The MVC Application

The attached Spring MVC application is a Maven project. The following is its "pom.xml" file.

XML
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
    http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.song.example</groupId>
    <artifactId>JDBCExample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    
    <properties>
        <spring.version>4.0.7.RELEASE</spring.version>
        <jackson.version>2.4.3</jackson.version>
        <tomcat.version>7.0.55</tomcat.version>
    </properties>
          
    <dependencies>
        <!-- Minimal dependencies for Spring MVC -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</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>
        
        <!-- Multi-part file support -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        
        <!-- Jackson dependencies -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>${jackson.version}</version>
        </dependency>
        <dependency>
              <groupId>com.fasterxml.jackson.core</groupId>
              <artifactId>jackson-databind</artifactId>
              <version>${jackson.version}</version>
        </dependency>
         
        <!-- Sevlet jars for compilation, provided by Tomcat -->
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-servlet-api</artifactId>
            <version>${tomcat.version}</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>
      
    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                <source>1.7</source>
                <target>1.7</target>
                </configuration>
            </plugin>
                      
            <plugin>
                <artifactId>maven-war-plugin</artifactId>
                <version>2.4</version>
                <configuration>
                <warSourceDirectory>WebContent</warSourceDirectory>
                <failOnMissingWebXml>true</failOnMissingWebXml>
                </configuration>
            </plugin>
        </plugins>
    </build>
    
</project>
  • The dependencies on "org.springframework" are the minimal set of dependencies to create a Spring MVC application;
  • The dependency on "commons-fileupload" is to enable MVC application to process the files uploaded;
  • The dependencies on "com.fasterxml.jackson.core" are used to help the application to perform Json operations.

The easiest way to work on this project is to use an IDE. If you do not know how to import the project into Eclipse, you can take a look at this article. The following is the project in the project explorer in the Eclipse.

Image 3

The web application is configured in the "web.xml" and the "mvc-dispatcher-servlet.xml" files.

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"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
        http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    id="WebApp_ID" version="3.0">
    
    <display-name>Spring Web Example</display-name>
    <welcome-file-list>
        <welcome-file>mvc/mainpage</welcome-file>
    </welcome-file-list>
      
    <filter>
        <filter-name>nocachefilter</filter-name>
        <filter-class>com.song.web.filter.NocacheFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>nocachefilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
    
    <servlet>
        <servlet-name>mvc-dispatcher</servlet-name>
          <servlet-class>
              org.springframework.web.servlet.DispatcherServlet
          </servlet-class>
          <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>mvc-dispatcher</servlet-name>
        <url-pattern>/mvc/*</url-pattern>
    </servlet-mapping>
    
    <env-entry>  
        <env-entry-name>DBConnectionInformation</env-entry-name>
        <env-entry-type>java.lang.String</env-entry-type>
        <env-entry-value>
            jdbc:sqlserver://localhost;DatabaseName=Experiment|FileLoader|Pwd123456
        </env-entry-value>    
    </env-entry> 
    
    <context-param>
        <param-name>BaseUrl</param-name>
          <param-value>
            http://localhost:8080/JDBCExample
          </param-value>
    </context-param>
</web-app>
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:p="http://www.springframework.org/schema/p" 
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans 
        http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
        http://www.springframework.org/schema/context 
        http://www.springframework.org/schema/context/spring-context-3.2.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd">
     
     <context:component-scan base-package="com.song.web.controller" />
     <mvc:annotation-driven />
     
    <bean id="viewResolver"
        class="org.springframework.web.servlet.view.InternalResourceViewResolver" >
        <property name="prefix"><value>/views/</value></property>
    </bean>
    
    <bean id="multipartResolver"
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="maxUploadSize" value="1048576"/>
    </bean>
     
</beans>
  • The information to connect to the database is configured in the "DBConnectionInformation" entry;
  • The "BaseUrl" "context-param" is the base URL of the web application. If you want to deploy the application to a Tomcat server, you will need to change it to match the domain and port of the web server;
  • The "multipartResolver" set the maximum size for the file uploaded. In my example, It is set to 1 megabytes.

The Database Access Classes

The classes in the "com.song.database" package are used for connecting to the database to save and retrieve the files. The "DBConnector" class is implemented as the following to obtain a "Connection" to the database.

Java
package com.song.database;
    
import java.sql.Connection;
import java.sql.DriverManager;
    
import javax.naming.InitialContext;
    
public class DBConnector {
    private final static class ConnInformation {
        public String DBUrl;
        public String UserName;
        public String Password;
        
        private ConnInformation(String dBUrl,
                String userName, String password) {
            DBUrl = dBUrl;
            UserName = userName;
            Password = password;
        }
        
        private static ConnInformation instance = null;
        private static synchronized ConnInformation getInstance() throws Throwable{
            if (instance == null) {
                try {
                    InitialContext initialContext = new javax.naming.InitialContext();
                    String contextString = (String) initialContext
                            .lookup("java:comp/env/DBConnectionInformation");
                    
                    String[] info = contextString.split("\\|");
                    instance = new ConnInformation(info[0], info[1], info[2]);
                } catch (Throwable e) {
                    instance = null;
                    throw e;
                }
            }
            
            return instance;
        }
    }
    
    public static Connection getAConnection(boolean autocommit) throws Throwable {
        ConnInformation connInfo = ConnInformation.getInstance();
        
        Connection connection = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            connection = DriverManager
                    .getConnection(connInfo.DBUrl, connInfo.UserName, connInfo.Password);
            
            connection.setAutoCommit(autocommit);
        } catch (Throwable e) {throw e;}
        
        return connection;
    }
    
    public static Connection getAConnection() throws Throwable {        
        return getAConnection(true);
    }
}
  • The static class "ConnInformation" keeps the information required to connect to the database from the "web.xml" file;
  • The overloaded methods "getAConnection" return a connection to the database using the Microsoft JDBC driver for SQL server;

The name of the method "setAutoCommit" on the connection object is somewhat confusing. When it is set to false, it issues a command "SET IMPLICIT_TRANSACTIONS ON" to the database. From the database perspective, if "IMPLICIT_TRANSACTIONS" is "ON", and if a connection is not currently in a transaction, executing almost any SQL query starts a transaction, including a simple select query. This can lead to a few complexities.

  • If you never intend to start a transaction but simply issue a select query to the database, a transaction will start if you "setAutoCommit" to false on your connection object. You will need to remember to commit or rollback this unnecessary transaction. If not, your select query may possibly block other people's database query until your session times out and the database server choose to rollback your transaction;
  • According to the ACID principle, a single-statement query is always a transaction. If you "setAutoCommit" to false on your connection object, even you simply insert a single row into a single table, you need to remember to commit it, otherwise there is no guarantee that your data will be saved to the database.

Since JDBC does not implement transactions, the transaction mechanism is implemented in the database server. I believe that the best place to control the transactions is the place where transaction is implemented. In this example, I use stored procedure to save and delete files from the database. The transaction is controlled in the stored procedures, so I always "setAutoCommit" to true in this example. Built upon the "DBConnector" class, the "DBOperation" class calls the stored procedures to work on the files.

Java
package com.song.database;
    
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
    
import com.song.model.File;
    
public class DBOperation {
    
    public static File saveAFile(String name, String contentType,
            int size, InputStream stream) throws Throwable {
    
        int fileId = 0;
        String sql = "{call File_Insert(?,?,?,?,?)}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
                stmt.registerOutParameter(5, java.sql.Types.INTEGER);
                stmt.setString(1, name);
                stmt.setString(2, contentType);
                stmt.setInt(3, size);
                stmt.setBinaryStream(4, stream);
                
                stmt.execute();
                
                fileId = stmt.getInt(5);
            }
        }
        
        return new File(fileId, name, contentType, size);
    }
    
    public static void deleteAFile(int fileId) throws Throwable {
        
        String sql = "{call File_Delete(?)}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
                stmt.setInt(1, fileId);
                stmt.execute();
            }
        }
    }
    
    public static File selectAFile(int fileId) throws Throwable {
        File file = null;
        
        String sql = "{call File_Select(?)}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
                stmt.setInt(1, fileId);
                
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    fileId = rs.getInt("ID");
                    String name = rs.getString("Name");
                    String contentType = rs.getString("ContentType");
                    int size = rs.getInt("Size");
                    byte[] data = rs.getBytes("Data");
                    
                    file = new File(fileId, name, contentType, size);
                    file.setData(data);
                }
            }
        }
        
        return file;
    }
    
    public static List<File> ListAllFile() throws Throwable {
        List<File> files = new ArrayList<File>();
        
        String sql = "{call File_List}";
        try (Connection con = DBConnector.getAConnection()) {
            try (CallableStatement stmt = con.prepareCall(sql)) {
                ResultSet rs = stmt.executeQuery();
                while (rs.next()) {
                    int fileId = rs.getInt("ID");
                    String name = rs.getString("Name");
                    String contentType = rs.getString("ContentType");
                    int size = rs.getInt("Size");
                    
                    File file = new File(fileId, name, contentType, size);
                    files.add(file);
                }
            }
        }
        
        return files;
    }
}

The MVC Model Classes

The classes in the "com.song.model" and "com.song.model.rest" packages are the models of the MVC application. The "RestResponseStatusCode", "RestResponseStatus", and the "RestResponse" enum/classes represent a REST response.

Java
package com.song.model.rest;

public enum RestResponseStatusCode { SUCCESS, FAIL }
Java
package com.song.model.rest;

public final class RestResponseStatus {
    public RestResponseStatusCode code;
    public String msg;
    
    private RestResponseStatus(RestResponseStatusCode code, String msg) {
        this.code = code;
        this.msg = msg;
    }
    
    public static RestResponseStatus success(String msg) {
        msg = (msg == null)? "": msg.trim();
        
        if (msg.length() == 0) {
            msg = "The operation is successful";
        }
        
        return new RestResponseStatus(RestResponseStatusCode.SUCCESS, msg);
    }
    
    public static RestResponseStatus fail(String msg) {
        msg = (msg == null)? "": msg.trim();
        
        if (msg.length() == 0) {
            msg = "Error occured while doing the operation";
        }
        
        return new RestResponseStatus(RestResponseStatusCode.FAIL, msg);
    }
}
Java
package com.song.model.rest;

public final class RestResponse {
    public RestResponseStatus status;
    public Object payload;
}

The "payload" object in the "RestResponse" class can be any object. Of course, in the example, it is the "File" class.

Java
package com.song.model;

public final class File {
    public int fileId;
    public String name;
    public String contentType;
    public int size;
    public byte[] data;
    
    public File(int fileId, String name, String contentType, int size) {
        this.fileId = fileId;
        this.name = name;
        this.contentType = contentType;
        this.size = size;
    }
    
    public void setData(byte[] data) {
        this.data = data;
    }
}

The MVC Controller

The "FileController" class in the "com.song.web.controller" package is the application's controller.

Java
package com.song.web.controller;
    
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
    
import javax.servlet.http.HttpServletResponse;
    
import org.springframework.stereotype.Controller;
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.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;
    
import com.song.database.DBOperation;
import com.song.model.File;
import com.song.model.rest.RestResponse;
import com.song.model.rest.RestResponseStatus;
    
    
@Controller
public class FileController {
    @RequestMapping(value = "/mainpage",
            method = RequestMethod.GET)
    public ModelAndView mainpage() {
        RestResponse response = new RestResponse();
        List<File> files = null;
        
        try {
            files = DBOperation.ListAllFile();
        }catch(Throwable e) {
            response.status = RestResponseStatus
                    .fail("Failed to load the existing files");
        }
        
        if (response.status == null) {
            response.status = RestResponseStatus.success(null);
            response.payload = files;
        }
        
        ModelAndView modelView = new ModelAndView();
        modelView.addObject("data", response);
        modelView.setViewName("homepage.jsp");
        
        return modelView;
    }
    
    @RequestMapping(value = "/api/uploadfile",
            method = RequestMethod.POST)
    public ModelAndView uploadfile(MultipartHttpServletRequest request)
            throws Throwable {
        
        MultipartFile multipartFile = request.getFile("uploadFile");
        int size = (int) multipartFile.getSize();
        String contentType = multipartFile.getContentType();
        String name = multipartFile.getOriginalFilename();
        InputStream stream = multipartFile.getInputStream();
        
        final File file = DBOperation.saveAFile(name, contentType, size, stream);
        List<File> files = new ArrayList<File>();
        files.add(file);
        
        ModelAndView modelView = new ModelAndView();
        modelView.addObject("files", files);
        modelView.setViewName("files.jsp");
    
        return modelView;
    }
    
    @ResponseBody
    @RequestMapping(value = "/api/deletefile",
            method = RequestMethod.POST)
    public RestResponse deletefile(int fileId) throws Throwable {
        DBOperation.deleteAFile(fileId);
        
        RestResponse response = new RestResponse();
        response.status = RestResponseStatus.success(null);
        return response;
    }
    
    @RequestMapping(value = "/api/downloadfile",
            method = RequestMethod.GET)
    public void downloadfile(@RequestParam("fileId") int fileId,
            HttpServletResponse response) throws Throwable {
        
        File file = DBOperation.selectAFile(fileId);
        response.setContentType(file.contentType);
        response.addHeader("Content-Disposition",
                "attachment; filename=\"" + file.name + "\"");
        response.setContentLength(file.size);
        response.getOutputStream().write(file.data);
        
        response.flushBuffer();
    }
    
}
  • The "mainpage" method loads the application's main web page;
  • The "uploadfile" method receives the files uploaded from the web browser and saves them to the database;
  • The "deletefile" method deletes a file;
  • The "downloadfile" method sends the file data to the web browser for download.

The MVC Views

The "homepage.jsp" file is the application's main page.

<%@page import="com.song.model.rest.RestResponseStatusCode"%>
<%@page import="com.song.model.rest.RestResponseStatus"%>
<%@page import="java.util.List"%>
<%@page import="com.song.database.DBOperation"%>
<%@page import="com.song.model.File"%>
<%@page import="com.song.model.rest.RestResponse"%>
<%@page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    
<%String baseUrl = getServletContext().getInitParameter("BaseUrl");%>
<%RestResponse data = (RestResponse)request.getAttribute("data");%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>jQuery File Upload Example</title>
    
<link rel="shortcut icon" href="<%=baseUrl%>/styles/favicon.png" />
    
<link rel="stylesheet" type="text/css"
    href="<%=baseUrl%>/external/jquery-ui-1.11.2/jquery-ui.min.css" />
<link rel="stylesheet"
    type="text/css" href="<%=baseUrl%>/styles/site.css" />
    
<script src="<%=baseUrl%>/external/jquery/jquery-2.1.3.min.js"></script>
<script src="<%=baseUrl%>/external/jquery-ui-1.11.2/jquery-ui.min.js"></script>
<script src="<%=baseUrl%>/external/jquery-file-upload-9.8.1/jquery.iframe-transport.js"></script>
<script src="<%=baseUrl%>/external/jquery-file-upload-9.8.1/jquery.fileupload.js"></script>
    
<script src="<%=baseUrl%>/scripts/file-processor.js"></script>
    
<script>
$(function () {    
    fileProcessor.initiate('<%=baseUrl%>', $('#fileupload'), $('table>tbody'));
});
    
</script>
</head>
<body>
<%
RestResponseStatus status = data.status;
    
if (status.code == RestResponseStatusCode.SUCCESS) {
    @SuppressWarnings("unchecked")    
    List<File> files = (List<File>)data.payload;
    request.setAttribute("files", files);
}
%>
<div>
<input id="fileupload" type="file" name="uploadFile"
    data-url="<%=baseUrl%>/mvc/api/uploadfile" multiple />
<div style="margin-top: 5px">
<%if (status.code == RestResponseStatusCode.FAIL) { %>
<div>Failed to get the list of the files from the database</div>
<%} %>
<table>
<tbody>
<%if (status.code == RestResponseStatusCode.SUCCESS) { %>
    <jsp:include page="files.jsp" />
<%} %>
</tbody>
</table>
</div>
</div>
</body> 
</html>

The list of the uploaded files is shown in the web page using the "files.jsp" file.

<%@page import="java.util.List"%>
<%@page import="com.song.model.File"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%
    @SuppressWarnings("unchecked")
    List<File> files = (List<File>)request.getAttribute("files");
%>
<%for (File file: files) { %>
<tr>
<td>
    <label class="linklabel"
        onclick="return fileProcessor.downloadfile(<%=file.fileId%>)">
        <%=file.name%>
    </label>
</td>
<td><%=file.size%></td>
<td>
    <label class="linklabel"
        onclick="return fileProcessor.deletefile(this, <%=file.fileId%>)">
        Delete
    </label>
</td>
</tr>
<%} %>

The Javascript to perform the file upload is implemented in the "file-processor.js" file.

JavaScript
var fileProcessor = function() {
    var context = {
            baseurl: null,
            file: null,
            tbody: null
    };
    
    return {
        initiate: function(baseurl, $file, $tbody) {
            context.baseurl = baseurl;
            context.file = $file;
            context.tbody = $tbody;
            
            context.file.fileupload({
                dataType: 'text',
                add: function(e, data) {
                    var size = parseInt(data.files[0].size);
                    var name = data.files[0].name;
                    
                    var $td = $('<td colspan="3"></td>');
                    var $tr = $('<tr>');
                    $tr.append($td).prependTo(context.tbody);
                    
                    // Stop uploading if larger than 1 megabytes
                    if (size > 1048576) {
                        var text = name + ' is larger than 1 Megabytes';
                        $td.html(text);
                        var $lblCancel = $('<label style="margin-left: 10px" class="linklabel">')
                            .html('Cancel');
                        $lblCancel.click(function() {
                            $(this).closest('tr').remove();
                        }).appendTo($td);
    
                        return;
                    }
                    
                    // Upload the file
                    $td.html('Uploading ' + name + ', ' + size + ' bytes');
                    data.context = $td;
                    data.submit();
                },
                progress: function(e, data) {
                    // The progress data is only for the upload, not including the
                    // time to save into the database, so it is not a valid number to show
                    // data.context.html(parseInt(data.loaded / data.total * 100, 10)
                    //        + '% - completed');
                },
                fail: function(e, data) {
                    var name = data.files[0].name;
                    data.context.html('Failed to upload ' + name);
                    var $lblCancel = $('<label style="margin-left: 10px" class="linklabel">')
                        .html('Cancel');
                    $lblCancel.click(function() {
                        $(this).closest('tr').remove();
                    });
                    data.context.append($lblCancel);
                    
                },
                done: function(e, data) {
                    data.context.parent().html($(data.jqXHR.responseText).html());
                },
                // Global events
                progressall: function(e, data) {},
                start: function() {},
                stop: function() {}
            }); 
        },
        downloadfile: function(id) {
            var url = context.baseurl + '/mvc/api/downloadfile?fileId=' + id;
            
            var hiddenIFrameID = 'hiddenDownloader';
            var iframe = document.getElementById(hiddenIFrameID);
            if (iframe === null) {
                iframe = document.createElement('iframe');
                iframe.id = hiddenIFrameID;
                iframe.style.display = 'none';
                document.body.appendChild(iframe);
            }
    
            iframe.contentWindow.location.replace(url);
        },
        deletefile: function(ctl, id) {
            var url = context.baseurl + '/mvc/api/deletefile';
            
            var ajax = $.ajax({
                cache: false, type: "POST", data: {fileId: id}, url: url});
            
            ajax.done(function() {
                $(ctl).closest('tr').remove();
            }).fail(function() {
                alert('Error occured while deleting the file');
            });
        }
    };
}();

The JDBC Driver

We do not need the JDBC driver to compile the application, but if we want to run the application we will need it. Since we are using SQL server, we can go to the Microsoft website to download the driver. If you use Tomcat to run the web application, you can put the downloaded jar file in the "lib" folder in the Tomcat.

Run the application

You can issue an "mvn clean install" to build the application and deploy it to a web server or run it directly within the Eclipse. To run the application in Eclipse, you can refer to this link. You need to make sure that the web server has the JDBC driver ready. You also need to check the "web.xml" file to see if the "BaseUrl" is correctly configured. Of course, your SQL server needs to be up and running and you have successfully created the test database.

Image 4

When the application first starts, it shows you the file upload button in the browser. You can click it to upload your files. You can try to upload multiple files in a batch.

Image 5

You can click the "Delete" link to delete the uploaded files. You can also click the file names to download the files to your browser. The following is the gold fish picture that I uploaded to the server and downloaded back to my browser.

Image 6

 

Points of Interest

  • This article presented an example to upload and download files with SQL server through JDBC in a Spring MVC application;
  • JDBC is a mature subject, but you may constantly need to revisit it, if other approaches fail, JDBC should always work;
  • The example used the jQuery file upload plugin to upload the files to the server, which allows uploading multiple files in the Ajax way without reloading the web page;
  • I hope you like my postings and I hope this article can help you one way or the other.

History

First Revision - 2/3/2015.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
QuestionGreat Tutorial Pin
Member 1220506314-Dec-15 23:17
MemberMember 1220506314-Dec-15 23:17 
AnswerRe: Great Tutorial Pin
Dr. Song Li15-Dec-15 5:51
MemberDr. Song Li15-Dec-15 5:51 
You can save the uploaded binary to a file on your disk. Be sure to give the file a name that is unique in your directory and save the location of the file in a database column. You should be fine.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.