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.
USE master
GO
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
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
sp_grantdbaccess 'FileLoader'
GO
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 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 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
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 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 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
SELECT * FROM TABLE_ID
SELECT * FROM TABLE_FILES
GO
ALTER DATABASE [Experiment] SET MULTI_USER
GO
If the script runs successfully, you should have a database named [Experiment] in your server.
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.
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
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 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.
SELECT * FROM TABLE_ID
SELECT * FROM TABLE_FILES
GO
The MVC Application
The attached Spring MVC application is a Maven project. The following is its "pom.xml" file.
<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>
<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>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<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>
<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.
The web application is configured in the "web.xml" and the "mvc-dispatcher-servlet.xml" files.
="1.0"="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>
="1.0"="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.
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.
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.
package com.song.model.rest;
public enum RestResponseStatusCode { SUCCESS, FAIL }
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);
}
}
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.
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.
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.
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);
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;
}
$td.html('Uploading ' + name + ', ' + size + ' bytes');
data.context = $td;
data.submit();
},
progress: function(e, data) {
},
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());
},
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.
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.
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.
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.
