Inserting and Retrieving Binary Data with SQL Server Database using JAX-RS RESTful Web Service

File Upload and Download using SQL Server Database

In this tutorial we are going to develop file upload and file download capability using RESTful web service using JAX-RS and Jersey storing the contents of the file into SQL Server Database using BLOB column. I have subsequently added a new post on Inserting and Retrieving Binary Data using MongoDB with JAX-RS RESTful Web Service.

A BLOB (Binary Large Object Block) is a special data type that supports large volumes of data to be stored in a database column. SQL Server supports several different types of LOBs (Large Object Blocks). The first type is CLOB (Character Large Object block) which can be used to store very large character-based files or streams. The second type of LOB that SQL Server supports is XML data, which as the name suggests is used to store very large XML documents. The third type of LOB that SQL Server supports is BLOB (Binary Large Object Block) which we are using in this tutorial. This type of LOB allows us to save image files, PDF files, word documents, Powerpoint files, Zip files, Windows executables and many other types of binary data.

As you will see, our example will be able to store PDF files, Excel files, Word Document files, Powerpoint files, Image files, or any other type of file BLOB available us provided we have ample amount of storage space.

Getting Started

In order to run this tutorial yourself, you will need the following:

Required Libraries

Copy all of the following jars to WebContent->WEB-INF->lib folder.

asm-3.1.jar
jersey-client-1.18.jar
jersey-core-1.18.jar
jersey-json-1.18.jar
jersey-multipart-1.18.jar
jersey-server-1.18.jar
jersey-servlet-1.18.jar
jsr311-api-1.1.1.jar
log4j-1.2.17.jar
mimepull-1.6.jar
sqljdbc4.jar

Complete Project Overview

I have added the project overview to give you a full view of the structure and show you all files contained in this sample project.

jax-rs filestoredb proj

RESTful Web Service End Points

#URIMethodDescription
1/rest/files/uploadPOSTUses multipart/form-data encoding type. Because of this no characters are encoded. This value is required when you are using forms that have a file upload feature as you do not want to alter the binary files in any way.
2/rest/files/download/file/{id}GETDownloads the file from the database with id via the path parameter. This web service endpoint is typically used when URI path parameters are extracted from the request URI, and the parameter names correspond to the URI path template variable names.
3/rest/files/download/details/{id}GETProvides record details from the database based in the id passed via path parameter. Using this URI will allow you to verify the data stored in SQL server for a particular record id.

Creating our SQL Server “filestore” Database Table (DDL)

CREATE TABLE tutorial.webuser.filestore (
	id varchar(10) NOT NULL,
	description varchar(50),
	file_year varchar(4),
	department varchar(10),
	filename varchar(255),
	file_blob varbinary(MAX),
	CONSTRAINT filestore_PK PRIMARY KEY (id)
);

Implement our File Download Service Class using the JAX-RS API

Implementing a RESTful service requires nothing more than creating a POJO and annotating using the javax.ws.rs.* annotations. Additionally, you will need to ensure your class is under the package you defined in your web descriptor as Jersey will use this package to scan your classes for the existence RESTful resources.

Our upload method uploadFile, takes six parameters using @FormDataParam. In this method I am using @Consumes annotation with a media type of MediaType.MULTIPART_FORM_DATA this will allow us to consume multipart form data from our HTML page. The first two parameters are used for the file handling, the first one of @FormDataParam provides an InputStream to the file being uploaded and the other @FormDataParam provides content disposition header by using the FormDataContentDisposition object in the Jersey API. The next four parameters are used in the database for ID and metadata purposes to better describe details about the file we are planning to store in the database.

In the important method used in the RestFileStoreDBExample service class is the downloadFilebyID method. You will notice that I am using @Produces annotation with a media type of MediaType.APPLICATION_OCTET_STREAM. This allow us to download the file as a binary file and download it directly in your browser. This method uses the @PathParam annotation which binds the value of a URI template parameter or a path segment containing the template parameter to a resource method parameter.

dbfilestore url structure

http://localhost:8080/RestfulFileStoreDBExample/rest/files/download/file/1001

The other point worth mentioning is the fact that we are performing some database checking by ensuring that the record actually exists in our SQL Server Database. If the record exists, we return it to the user and if the record is missing, we construct an appropriate error message and return it to the user in the HTML response.

Uploading a file to SQL Server Database

In this code snippet we will concentrate on the code responsible for saving the binary data to our SQL Server database by inserting it into our filestore table. Using this code you will be able to store PDF files, Excel files, Image files, or any type of file available to you provided you have enough space available to your database.

Using setBinaryStream we are able to take the input stream coming the file we uploaded via our HTML page and save it to our database record.

public Response uploadFile(
      @FormDataParam("file") InputStream fileInputStream,
      @FormDataParam("file") FormDataContentDisposition fileInputDetails,
      @FormDataParam("id") String  id,
      @FormDataParam("description") String  description,
      @FormDataParam("file_year") String file_year,
      @FormDataParam("department") String department) {
 
  Database dbs = new Database();
  Connection con = dbs.getConnection();
  PreparedStatement ps = null;
    
  ps = con.prepareStatement(
        "INSERT INTO tutorial.webuser.filestore (id, description,"
        + "file_year, department, filename, file_blob) VALUES (?,?,?,?,?,?)");
  ps.setString(1, id);
  ps.setString(2, description);
  ps.setString(3, file_year);
  ps.setString(4, department);
  ps.setString(5, fileInputDetails.getFileName());
  ps.setBinaryStream(6, fileInputStream);
  ps.executeUpdate();

  ...   
}

Warning

For the sake of brevity, I have not added additional checks in our uploadFile method when inserting a record. If the record ID exists in the database and you attempt to insert the same ID you will get a database exception due to foreign key contraint on the unique primary key.

Downloading a file from SQL Server Database

In this code snippet you can see that the output we are expecting to produce is an APPLICATION_OCTET_STREAM which is essentially binary data. We will be obtaining the ID from the path parameter of the request URL. Using @PathParam annotation we will inject this id from the URI into our String id variable which will be used as a parameter in our PreparedStatement to query our SQL Server database and retrieve the desired record.

From our ResultSet we will use the getBinaryStream(“file_blob”) which will return an InputStream allowing us to retrieve our binary data as a stream of bytes to output it to a ByteArrayOutputStream which will be returned to the user as an HTTP response object.

Please note that we need to use the previously stored filename when building our response header via builder.header(“Content-Disposition”, “attachment; filename=” + filename);.

@GET
@Path("/download/file/{id}")
@Produces(MediaType.APPLICATION_OCTET_STREAM)
public Response downloadFilebyID(@PathParam("id")  String id) throws IOException {
  
  Response response = null;
  Database dbs = new Database();
  Connection con = dbs.getConnection();
  PreparedStatement ps = null;
  
  ps = con.prepareStatement(
        "SELECT id, description, file_year, department, filename, file_blob "
        + "from tutorial.webuser.filestore where id = ?");
  ps.setString(1, id);
  ResultSet result = ps.executeQuery();
  if (result.next()) {
    // get filename for injecting into response header
    String filename = result.getString("filename");
    
    final InputStream in = result.getBinaryStream("file_blob");
      
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    int data = in.read();
    while (data >= 0) {
      out.write((char) data);
      data = in.read();
    }
    out.flush();
        
    ResponseBuilder builder = Response.ok(out.toByteArray());
    builder.header("Content-Disposition", "attachment; filename=" + filename);
    response = builder.build();
  }

  ...
} 

Complete Program (RestFileStoreDBExample.java)

package com.avaldes;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.ws.rs.Consumes;
import javax.ws.rs.GET;
import javax.ws.rs.POST;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.ws.rs.core.Response;
import javax.ws.rs.core.Response.ResponseBuilder;

import com.avaldes.dao.Database;
import com.sun.jersey.multipart.FormDataParam;

import org.apache.log4j.Logger;

import com.sun.jersey.core.header.FormDataContentDisposition;

@Path("/files")
public class RestFileStoreDBExample {
  static Logger logger = Logger.getLogger(RestFileStoreDBExample.class);
  private static final String api_version = "1.01A rev.10023";
  
  @POST
  @Path("/upload")
  @Consumes(MediaType.MULTIPART_FORM_DATA)
  @Produces("text/html")
  public Response uploadFile(
      @FormDataParam("file") InputStream fileInputStream,
      @FormDataParam("file") FormDataContentDisposition fileInputDetails,
      @FormDataParam("id") String  id,
      @FormDataParam("description") String  description,
      @FormDataParam("file_year") String file_year,
      @FormDataParam("department") String department) {
 
    Database dbs = new Database();
    Connection con = dbs.getConnection();
    PreparedStatement ps = null;
    
    String status = "Upload has been successful";
    
    logger.info("ID: " + id);
    logger.info("description: " + description);
    logger.info("department: " + department);
    logger.info("file_year: : " + file_year);
    logger.info("fileInputDetails: " + fileInputDetails);
    
    try {
      ps = con.prepareStatement(
          "INSERT INTO tutorial.webuser.filestore (id, description,"
          + "file_year, department, filename, file_blob) VALUES (?,?,?,?,?,?)");
      ps.setString(1, id);
      ps.setString(2, description);
      ps.setString(3, file_year);
      ps.setString(4, department);
      ps.setString(5, fileInputDetails.getFileName());
      ps.setBinaryStream(6, fileInputStream);
      ps.executeUpdate();
    } catch (SQLException e) {
      status = "Upload has failed";
      e.printStackTrace();
    } finally {
      try {
        ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      dbs.closeConnection();
    }
    
    return Response.status(200).entity(status).build();
  }
  
  @GET
  @Path("/download/file/{id}")
  @Produces(MediaType.APPLICATION_OCTET_STREAM)
  public Response downloadFilebyID(@PathParam("id")  String id) throws IOException {
    
    Response response = null;
    Database dbs = new Database();
    Connection con = dbs.getConnection();
    PreparedStatement ps = null;
    
    logger.info("Inside downloadFilebyID...");
    logger.info("ID: " + id);

    try {
      ps = con.prepareStatement(
          "SELECT id, description, file_year, department, filename, file_blob "
          + "from tutorial.webuser.filestore where id = ?");
      ps.setString(1, id);
      ResultSet result = ps.executeQuery();
      if (result.next()) {
        // get these first three details for logging purposes only
        String description = result.getString("description");
        String file_year = result.getString("file_year");
        String department = result.getString("department");
        
        // get filename for injecting into response header
        String filename = result.getString("filename");
        
        logger.info("description: " + description);
        logger.info("department: " + department);
        logger.info("file_year: : " + file_year);
        logger.info("filename: " + filename);
        
        final InputStream in = result.getBinaryStream("file_blob");
        
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        int data = in.read();
        while (data >= 0) {
          out.write((char) data);
          data = in.read();
        }
        out.flush();
          
        ResponseBuilder builder = Response.ok(out.toByteArray());
        builder.header("Content-Disposition", "attachment; filename=" + filename);
        response = builder.build();
      } else {
        logger.info("Unable to find record with ID: " + id);
        response = Response.status(404).
                entity("Unable to find record with ID: " + id).
                type("text/plain").
                build();
      }
      
    } catch (SQLException e) {
      logger.error(String.format("Inside downloadFilebyID==> Unable to get file with ID: %s", 
          id));
      
      response = Response.status(404).
              entity(" Unable to get file with ID: " + id).
              type("text/plain").
              build();
      e.printStackTrace();
    } finally {
      try {
        ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      dbs.closeConnection();
    }
    
    return response;
  }
  
  
  @GET
  @Path("/download/details/{id}")
  @Produces(MediaType.TEXT_HTML)
  public Response showFileStoreDetails(@PathParam("id")  String id) {
    
    Response response = null;
    Database dbs = new Database();
    Connection con = dbs.getConnection();
    PreparedStatement ps = null;
    
    logger.info("Inside showFileStoreDetails...");
    logger.info("ID: " + id);

    try {
      ps = con.prepareStatement(
          "SELECT id, description, file_year, department, filename "
          + "from tutorial.webuser.filestore where id = ?");
      ps.setString(1, id);
      ResultSet result = ps.executeQuery();
      if (result.next()) {
        String description = result.getString("description");
        String file_year = result.getString("file_year");
        String department = result.getString("department");
        String filename = result.getString("filename");
        StringBuffer status = new StringBuffer("Inside showHeaders: <br/><br/>");
        status.append("description : ");
        status.append(description);
        status.append("<br/>");
        status.append("department : ");
        status.append(department);
        status.append("<br/>");
        status.append("file_year : ");
        status.append(file_year);
        status.append("<br/>");
        status.append("filename : ");
        status.append(filename);
        status.append("<br/>");
        
        logger.info("description: " + description);
        logger.info("department: " + department);
        logger.info("file_year: : " + file_year);
        logger.info("filename: " + filename);
        
        response = Response.status(200).entity(status.toString()).build();
      } else {
        logger.info("Unable to find record with ID: " + id);
        response = Response.status(404).
                entity("Unable to find record with ID: " + id).
                type("text/plain").
                build();
      }
    } catch (SQLException e) {
      logger.error(String.format("Inside showFileStoreDetails==> Unable to get file with ID: %s", 
          id));
      
      response = Response.status(404).
              entity(" Unable to find record with ID: " + id).
              type("text/plain").
              build();
      e.printStackTrace();
    } finally {
      try {
        ps.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      dbs.closeConnection();
    }
    
    return response;
  } 
}

Database Class

package com.avaldes.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author Amaury Valdes
 * 
 * Note: If you are an application server like Weblogic, Websphere, JBoss or Glassfish  
 * you should try to use JNDI for Data Source and Connection Pool resources
 * instead of using this Database class as that would be the most efficient way
 * of establishing a database connection for JDBC purposes
 *
 * However, for illustration purposes and low usage web sites this class should suffice
 * 
 */

public class Database {
  String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username       = "webuser";
    String password       = "deepSpace7826$$";
    Connection connection = null;
    
  public Database() {
    try {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      } catch (ClassNotFoundException e) {
        System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
        e.printStackTrace();
        return;
      }
      
      try {
        connection = DriverManager.getConnection(database_url, username, password);
      } catch (SQLException e) {
        System.out.println("ERROR:  Unable to establish a connection with the database!");
        e.printStackTrace();
        return;
      }
  }
  
  public Connection getConnection() {
    try {
        connection = DriverManager.getConnection(database_url, username, password);
      } catch (SQLException e) {
        System.out.println("ERROR:  Unable to establish a connection with the database!");
        e.printStackTrace();
        return null;
      }
    
    return connection;
  }
  
  public void closeConnection() {
    try {
      connection.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

LOG4J Configuration File (log4j.xml)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="%-5p: %c - %m%n" />
		</layout>
	</appender>
	
	<!-- Application Loggers -->
	<logger name="com.avaldes">
		<level value="info" />
	</logger>

	<!-- Root Logger -->
	<root>
		<priority value="warn" />
		<appender-ref ref="console" />
	</root>
</log4j:configuration>

Simple HTML Web Page (index.html)

This page is very simple having only a few input fields of type text for the ID, Description, File Year and Department. The last field is of type file to allow the user to choose a file to upload to our RESTful web service. This HTML page will use the method of POST with an encoding type of enctype=”multipart/form-data” in the HTML form element.

<h1>RESTful Web Service - File Upload into Database (BLOB) Example</h1>  
<form action="/RestfulFileStoreDBExample/rest/files/upload" method="POST" enctype="multipart/form-data">
  <p>ID: <input type="text" name="id" size="10" /></p>
  <p>Description: <input type="text" name="description" size="50" /></p>
  <p>File Year: <input type="text" name="file_year" size="6" /></p>
  <p>Department: <input type="text" name="department" size="10" /></p>  
  <p>File Upload: <input type="file" name="file" size="60" /></p>  
  <input type="submit" value="Submit" />  
</form>

Querying the data in the Filestore table in the Database

SELECT id, description, file_year, department, filename, file_blob
FROM tutorial.webuser.filestore;
filestore db table

Web Deployment Descriptor (web.xml)

This is a pretty straight forward deployment descriptor file – only thing you need to add is the location of you java package in the Jersey ServletContainer entry as init-param. Please ensure you add it to the web.xml file as shown below.

<?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>com.omega.rest</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.jsp</welcome-file>
  </welcome-file-list>
  
  <servlet>
 
   <servlet-name>Jersey REST Service</servlet-name>
    <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
    <init-param>
      <param-name>com.sun.jersey.config.property.packages</param-name>
      <param-value>com.avaldes</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
  </servlet>

  <servlet-mapping>
    <servlet-name>Jersey REST Service</servlet-name>
    <url-pattern>/rest/*</url-pattern>
  </servlet-mapping>
</web-app>

Testing out the Web Services

To test out the application, simply enter all the required fields into the input text fields and choose the file to upload using the file picker. Then click on the Submit button. This will insert the fields into our SQL Server database and store the file in the database as a Binary Large Object Block (BLOB).

Download the Code

That’s It!

I hope you enjoyed this tutorial. It was certainly a lot of fun putting it together and testing it out. Please continue to share the love and like us so that we can continue bringing you quality tutorials. Happy Coding!!!

java jaxrs db filestore

Please Share Us on Social Media

Facebooktwitterredditpinterestlinkedinmail

Leave a Reply

Your email address will not be published. Required fields are marked *