Spring MVC Excel File Upload to Oracle Database Example

April 4, 2018 Sushil Singh

Spring MVC Excel File Upload to Oracle Database Example

File Uploading is a very common task in any web application. Today we will learn about Spring File upload, specifically Spring MVC File Upload to upload Excel file and save its data into Oracle DB tables.

Spring uses MultipartResolver interface to handle the file uploads in web application. Below are two of the implementation :

StandardServletMultipartResolver – Servlet 3.0 multipart request parsing.
CommonsMultipartResolver – Classic commons-fileupload.jar

Tools used in this article :

Spring 4.3.7.RELEASE
Maven 3
Tomcat 8.5

1. Project Structure

2. Project Dependency

First of all we need to add project dependency in our pom.xml

pom.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.learningsolo.spring</groupId>
<artifactId>springFileUpload</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>FileUpload</name>
<description>FileUpload</description>

<properties>
<jdk.version>1.8</jdk.version>
<spring.version>4.3.7.RELEASE</spring.version>
<jstl.version>1.2</jstl.version>
<servletapi.version>3.1.0</servletapi.version>
<logback.version>1.1.3</logback.version>
<jcl.slf4j.version>1.7.12</jcl.slf4j.version>
</properties>

<dependencies>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
<exclusions>
<exclusion>
<groupId>commons-logging</groupId>
<artifactId>commons-logging</artifactId>
</exclusion>
</exclusions>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.2</version>
</dependency>

<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>${jstl.version}</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>

<!-- compile only, deployed container will provide this -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>${servletapi.version}</version>
<scope>provided</scope>
</dependency>

<!-- Logging -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jcl-over-slf4j</artifactId>
<version>${jcl.slf4j.version}</version>
</dependency>

<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>${logback.version}</version>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
</dependencies>

<build>
<plugins>

<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.3</version>
<configuration>
<source>${jdk.version}</source>
<target>${jdk.version}</target>
</configuration>
</plugin>

<plugin>
<artifactId>maven-war-plugin</artifactId>
<version>3.0.0</version>
<configuration>
<warSourceDirectory>WebContent</warSourceDirectory>
</configuration>
</plugin>
</plugins>
</build>

</project>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

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

<groupId>com.learningsolo.spring</groupId>

<artifactId>springFileUpload</artifactId>

<version>0.0.1-SNAPSHOT</version>

<name>FileUpload</name>

<description>FileUpload</description>

<jdk.version>1.8</jdk.version>

<spring.version>4.3.7.RELEASE</spring.version>

<jstl.version>1.2</jstl.version>

<servletapi.version>3.1.0</servletapi.version>

<logback.version>1.1.3</logback.version>

<jcl.slf4j.version>1.7.12</jcl.slf4j.version>

</properties>

<groupId>org.springframework</groupId>

<artifactId>spring-webmvc</artifactId>

<version>${spring.version}</version>

<groupId>commons-logging</groupId>

<artifactId>commons-logging</artifactId>

</exclusion>

</exclusions>

</dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-web</artifactId>

<version>${spring.version}</version>

</dependency>

<groupId>commons-fileupload</groupId>

<artifactId>commons-fileupload</artifactId>

</dependency>

<version>${jstl.version}</version>

</dependency>

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml-schemas</artifactId>

</dependency>

<groupId>javax.servlet</groupId>

<artifactId>javax.servlet-api</artifactId>

<version>${servletapi.version}</version>

<scope>provided</scope>

</dependency>

<version>${jcl.slf4j.version}</version>

</dependency>

<groupId>ch.qos.logback</groupId>

<artifactId>logback-classic</artifactId>

<version>${logback.version}</version>

</dependency>

<groupId>org.springframework</groupId>

<artifactId>spring-jdbc</artifactId>

<version>${spring.version}</version>

</dependency>

</dependencies>

<build>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-compiler-plugin</artifactId>

<source>${jdk.version}</source>

<target>${jdk.version}</target>

</configuration>

</plugin>

<artifactId>maven-war-plugin</artifactId>

<warSourceDirectory>WebContent</warSourceDirectory>

</configuration>

</plugin>

</plugins>

</build>

</project>

3. Spring MVC configuration

To enable file upload we will add MultiPart bean in our spring mvc configuration file springDispatcherContext.xml org.springframework.web.multipart.commons.CommonsMultipartResolver

Our final Spring configuration file looks like below.

springDispatcherContext.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:security="http://www.springframework.org/schema/security"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
	<mvc:annotation-driven />
	<context:component-scan base-package="com.learningsolo.spring*" />
	<mvc:resources mapping="/resources/**" location="/resources/" />

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

	<!-- File Upload Configuration Bean Details -->
	<bean id="multipartResolver"
		class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<property name="maxUploadSize" value="1000000000" />
	</bean>

	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
		<property name="url"
			value="jdbc:oracle:thin:@corp.learningsolo.com:1521/MYDB" />
		<property name="username" value="PIMS" />
		<property name="password" value="pims$123" />
	</bean>

	<bean id="uploadService" class="com.learningsolo.spring.FileUploadService"
		autowire="byType"></bean>
	<bean id="fileUploadDao" class="com.learningsolo.spring.FileUploadDao"
		autowire="byType"></bean>
</beans>

<?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:security="http://www.springframework.org/schema/security"

xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"

xmlns:mvc="http://www.springframework.org/schema/mvc"

xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

<mvc:annotation-driven />

<context:component-scan base-package="com.learningsolo.spring*" />

<mvc:resources mapping="/resources/**" location="/resources/" />

<bean id="jspViewResolver"

class="org.springframework.web.servlet.view.InternalResourceViewResolver">

<property name="viewClass"

value="org.springframework.web.servlet.view.JstlView" />

</bean>

<bean id="multipartResolver"

class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

</bean>

<bean id="dataSource"

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="url"

value="jdbc:oracle:thin:@corp.learningsolo.com:1521/MYDB" />

</bean>

<bean id="uploadService" class="com.learningsolo.spring.FileUploadService"

autowire="byType"></bean>

<bean id="fileUploadDao" class="com.learningsolo.spring.FileUploadDao"

autowire="byType"></bean>

</beans>

Notice I am setting max upload file size for CommonsMultiPartResolver using “maxUploadSize”

4. Spring File Upload Controller Class

Controller class should have handler method to return view and also to process file upload. Controller class is responsible for mapping the request from url and executing it in the java layer.

FileUploadController.java

package com.learningsolo.spring;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

import org.springframework.beans.factory.annotation.Autowired;
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.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

@Controller
public class FileUploadController {

	@Autowired
	FileUploadService uploadService;

	@RequestMapping(value = "/welcome", method = RequestMethod.GET)
	public ModelAndView index() {
		return new ModelAndView("upload");
	}

	@RequestMapping(value = "/upload", method = RequestMethod.POST)
	public ModelAndView singleFileUpload(@RequestParam("file") MultipartFile file,
			RedirectAttributes redirectAttributes) {

		if (file.isEmpty()) {
			redirectAttributes.addFlashAttribute("message", "Please select a file to upload");
			return new ModelAndView("upload","message", "Please select a file to upload");
		}

		try {

			// Get the file and save it somewhere
			byte[] bytes = file.getBytes();
			Path path = Paths.get("C://temp//" + file.getOriginalFilename());
			Files.write(path, bytes);

			uploadService.uploadFileData("C://temp//"+path.getFileName());

			redirectAttributes.addFlashAttribute("message",
					"You successfully uploaded '" + file.getOriginalFilename() + "'");

		} catch (IOException e) {
			redirectAttributes.addFlashAttribute("message",
					"Failure occured during upload '" + file.getOriginalFilename() + "'");
			e.printStackTrace();
		}

		return new ModelAndView("upload","message", "You successfully uploaded '" + file.getOriginalFilename() + "'");
	}
}

package com.learningsolo.spring;

import java.io.IOException;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import org.springframework.beans.factory.annotation.Autowired;

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.multipart.MultipartFile;

import org.springframework.web.servlet.ModelAndView;

import org.springframework.web.servlet.mvc.support.RedirectAttributes;

@Controller

public class FileUploadController {

@Autowired

FileUploadService uploadService;

@RequestMapping(value = "/welcome", method = RequestMethod.GET)

public ModelAndView index() {

return new ModelAndView("upload");

}

@RequestMapping(value = "/upload", method = RequestMethod.POST)

public ModelAndView singleFileUpload(@RequestParam("file") MultipartFile file,

RedirectAttributes redirectAttributes) {

if (file.isEmpty()) {

redirectAttributes.addFlashAttribute("message", "Please select a file to upload");

return new ModelAndView("upload","message", "Please select a file to upload");

}

try {

// Get the file and save it somewhere

byte[] bytes = file.getBytes();

Path path = Paths.get("C://temp//" + file.getOriginalFilename());

Files.write(path, bytes);

uploadService.uploadFileData("C://temp//"+path.getFileName());

redirectAttributes.addFlashAttribute("message",

"You successfully uploaded '" + file.getOriginalFilename() + "'");

} catch (IOException e) {

redirectAttributes.addFlashAttribute("message",

"Failure occured during upload '" + file.getOriginalFilename() + "'");

e.printStackTrace();

}

return new ModelAndView("upload","message", "You successfully uploaded '" + file.getOriginalFilename() + "'");

}

5. Spring View – upload.jsp

upload.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

	<h1>Spring MVC file upload example</h1>

	<form method="POST" action="${pageContext.request.contextPath}/upload"
		enctype="multipart/form-data">
		<input type="file" name="file" /> <input type="submit" value="Submit" />
	</form>

	<h1>Upload Status</h1>

	<h2>Message : ${message}</h2>

</body>
</html>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="ISO-8859-1"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<title>Insert title here</title>

</head>

<body>

<h1>Spring MVC file upload example</h1>

<form method="POST" action="${pageContext.request.contextPath}/upload"

enctype="multipart/form-data">

</form>

<h1>Upload Status</h1>

<h2>Message : ${message}</h2>

</body>

</html>

6. Web.xml

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	id="WebApp_ID" version="3.1">
	<display-name>SpringFileUpload</display-name>

	<!-- <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> 
		</listener> -->
	<servlet>
		<servlet-name>springdispatcher_srr</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/springDispatcherContext.xml</param-value>
		</init-param>
		<load-on-startup>0</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>springdispatcher_srr</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>

	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
</web-app>

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns="http://xmlns.jcp.org/xml/ns/javaee"

xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"

id="WebApp_ID" version="3.1">

<display-name>SpringFileUpload</display-name>

<!-- <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>

</listener> -->

<servlet-name>springdispatcher_srr</servlet-name>

<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>

<init-param>

<param-name>contextConfigLocation</param-name>

<param-value>/WEB-INF/springDispatcherContext.xml</param-value>

</init-param>

<load-on-startup>0</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>springdispatcher_srr</servlet-name>

<url-pattern>/</url-pattern>

</servlet-mapping>

<welcome-file-list>

<welcome-file>index.html</welcome-file>

</welcome-file-list>

</web-app>

7. Service Layer

This layer will process excel file and read data from file and construct java object to save into DB. Here we have used apache POI to parse Excel data and read from excel sheet.

FileUploadService.java

package com.learningsolo.spring;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;

public class FileUploadService {

	@Autowired
	FileUploadDao fileUploadDao;

	public String uploadFileData(String inputFilePath){
		Workbook workbook = null;
		Sheet sheet = null;
		try
		{

			workbook = getWorkBook(new File(inputFilePath));
			sheet = workbook.getSheetAt(0);

			/*Build the header portion of the Output File*/
			String headerDetails= "EmployeeId,EmployeeName,Address,Country";
			String headerNames[] = headerDetails.split(",");

			/*Read and process each Row*/
			ArrayList<ExcelTemplateVO> employeeList = new ArrayList<>();
			Iterator<Row> rowIterator = sheet.iterator();

			while(rowIterator.hasNext())
			{
				Row row = rowIterator.next();
				//Read and process each column in row
				ExcelTemplateVO excelTemplateVO = new ExcelTemplateVO();
				int count=0;
				while(count<headerNames.length){
					String methodName = "set"+headerNames[count];
					String inputCellValue = getCellValueBasedOnCellType(row,count++);
					setValueIntoObject(excelTemplateVO, ExcelTemplateVO.class, methodName, "java.lang.String", inputCellValue);
				}

				employeeList.add(excelTemplateVO);
			}
			fileUploadDao.saveFileDataInDB(employeeList);

		}
		catch(Exception ex){
			ex.printStackTrace();
		}

		return "Success";
	}

	public static Workbook getWorkBook(File fileName)
	{
		Workbook workbook = null;
		try {
			String myFileName=fileName.getName();
			String extension = myFileName.substring(myFileName.lastIndexOf("."));
			if(extension.equalsIgnoreCase(".xls")){
				workbook = new HSSFWorkbook(new FileInputStream(fileName));
			}
			else if(extension.equalsIgnoreCase(".xlsx")){
				workbook = new XSSFWorkbook(new FileInputStream(fileName));
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return workbook;
	}

	public static String getCellValueBasedOnCellType(Row rowData,int columnPosition)
	{
		String cellValue=null;
		Cell cell = rowData.getCell(columnPosition);
		if(cell!=null){
			if(cell.getCellType()==Cell.CELL_TYPE_STRING)
			{
				String inputCellValue=cell.getStringCellValue();
				if(inputCellValue.endsWith(".0")){
					inputCellValue=inputCellValue.substring(0, inputCellValue.length()-2);
				}
				cellValue=inputCellValue;
			}
			else if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC)
			{
				Double doubleVal = new Double(cell.getNumericCellValue());
				cellValue= Integer.toString(doubleVal.intValue());
			}
		}
		return cellValue;
	}

	private static <T> void setValueIntoObject(Object obj, Class<T> clazz, String methodNameForField, String dataType, String inputCellValue)
			throws SecurityException, NoSuchMethodException, ClassNotFoundException, NumberFormatException, IllegalArgumentException, IllegalAccessException, InvocationTargetException{

		Method meth = clazz.getMethod(methodNameForField, Class.forName(dataType));
		T t = clazz.cast(obj);

		if("java.lang.Double".equalsIgnoreCase(dataType))
		{
			meth.invoke(t, Double.parseDouble(inputCellValue));
		}else if(!"java.lang.Integer".equalsIgnoreCase(dataType))
		{
			meth.invoke(t, inputCellValue);
		}
		else
		{
			meth.invoke(t, Integer.parseInt(inputCellValue));
		}
	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

package com.learningsolo.spring;

import java.io.File;

import java.io.FileInputStream;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.util.ArrayList;

import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.beans.factory.annotation.Autowired;

public class FileUploadService {

@Autowired

FileUploadDao fileUploadDao;

public String uploadFileData(String inputFilePath){

Workbook workbook = null;

Sheet sheet = null;

try

{

workbook = getWorkBook(new File(inputFilePath));

sheet = workbook.getSheetAt(0);

/*Build the header portion of the Output File*/

String headerDetails= "EmployeeId,EmployeeName,Address,Country";

String headerNames[] = headerDetails.split(",");

/*Read and process each Row*/

ArrayList<ExcelTemplateVO> employeeList = new ArrayList<>();

Iterator<Row> rowIterator = sheet.iterator();

while(rowIterator.hasNext())

{

Row row = rowIterator.next();

//Read and process each column in row

ExcelTemplateVO excelTemplateVO = new ExcelTemplateVO();

int count=0;

while(count<headerNames.length){

String methodName = "set"+headerNames[count];

String inputCellValue = getCellValueBasedOnCellType(row,count++);

setValueIntoObject(excelTemplateVO, ExcelTemplateVO.class, methodName, "java.lang.String", inputCellValue);

}

employeeList.add(excelTemplateVO);

}

fileUploadDao.saveFileDataInDB(employeeList);

}

catch(Exception ex){

ex.printStackTrace();

}

return "Success";

}

public static Workbook getWorkBook(File fileName)

{

Workbook workbook = null;

try {

String myFileName=fileName.getName();

String extension = myFileName.substring(myFileName.lastIndexOf("."));

if(extension.equalsIgnoreCase(".xls")){

workbook = new HSSFWorkbook(new FileInputStream(fileName));

}

else if(extension.equalsIgnoreCase(".xlsx")){

workbook = new XSSFWorkbook(new FileInputStream(fileName));

}

catch(Exception ex)

{

ex.printStackTrace();

}

return workbook;

}

public static String getCellValueBasedOnCellType(Row rowData,int columnPosition)

{

String cellValue=null;

Cell cell = rowData.getCell(columnPosition);

if(cell!=null){

if(cell.getCellType()==Cell.CELL_TYPE_STRING)

{

String inputCellValue=cell.getStringCellValue();

if(inputCellValue.endsWith(".0")){

inputCellValue=inputCellValue.substring(0, inputCellValue.length()-2);

}

cellValue=inputCellValue;

}

else if (cell.getCellType()==Cell.CELL_TYPE_NUMERIC)

{

Double doubleVal = new Double(cell.getNumericCellValue());

cellValue= Integer.toString(doubleVal.intValue());

}

return cellValue;

}

private static <T> void setValueIntoObject(Object obj, Class<T> clazz, String methodNameForField, String dataType, String inputCellValue)

throws SecurityException, NoSuchMethodException, ClassNotFoundException, NumberFormatException, IllegalArgumentException, IllegalAccessException, InvocationTargetException{

Method meth = clazz.getMethod(methodNameForField, Class.forName(dataType));

T t = clazz.cast(obj);

if("java.lang.Double".equalsIgnoreCase(dataType))

{

meth.invoke(t, Double.parseDouble(inputCellValue));

}else if(!"java.lang.Integer".equalsIgnoreCase(dataType))

{

meth.invoke(t, inputCellValue);

}

else

{

meth.invoke(t, Integer.parseInt(inputCellValue));

}

8. DAO Layer to persist data into DB

DAO layer is responsible for persisting file data into DB tables in same format as in input file.

FileUploadDao.java

package com.learningsolo.spring;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class FileUploadDao extends BaseDAO{

	public boolean saveFileDataInDB(List<ExcelTemplateVO> employeeList){
		String sql = "insert into EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, ADDRESS, COUNTRY) "
				+ " VALUES (:employeeId, :employeeName, :address, :country)";
		try {
			List<Map<String, String>> batchUpdateParams = new ArrayList<>();

			for(ExcelTemplateVO vo : employeeList){
				Map<String, String> paramMap = new HashMap<>();
				paramMap.put("employeeId", vo.getEmployeeId());
				paramMap.put("employeeName", vo.getEmployeeName());
				paramMap.put("address", vo.getAddress());
				paramMap.put("country", vo.getCountry());
				batchUpdateParams.add(paramMap);
			}

			getNamedParamJdbcTemplate().batchUpdate(sql, batchUpdateParams.toArray(new Map[batchUpdateParams.size()] ));

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;

	}

}

package com.learningsolo.spring;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

public class FileUploadDao extends BaseDAO{

public boolean saveFileDataInDB(List<ExcelTemplateVO> employeeList){

String sql = "insert into EMPLOYEE (EMPLOYEEID, EMPLOYEENAME, ADDRESS, COUNTRY) "

+ " VALUES (:employeeId, :employeeName, :address, :country)";

try {

List<Map<String, String>> batchUpdateParams = new ArrayList<>();

for(ExcelTemplateVO vo : employeeList){

Map<String, String> paramMap = new HashMap<>();

paramMap.put("employeeId", vo.getEmployeeId());

paramMap.put("employeeName", vo.getEmployeeName());

paramMap.put("address", vo.getAddress());

paramMap.put("country", vo.getCountry());

batchUpdateParams.add(paramMap);

}

getNamedParamJdbcTemplate().batchUpdate(sql, batchUpdateParams.toArray(new Map[batchUpdateParams.size()] ));

} catch (Exception e) {

e.printStackTrace();

return false;

}

return true;

}

9. Sample Input Excel file

Below is the input excel file which is parsed and processed by our SpringFileUpload project. It read all the rows one by one and then process it and saves to oracle Database.

10. Spring File upload Result

Type below url in browser and open welcome page to upload file.

http://localhost:8080/springFileUpload/welcome

Upload success:

Download Sample from GitHub.com

0 Shares

9 thoughts on “Spring MVC Excel File Upload to Oracle Database Example”

Sairam

November 9, 2018 at 4:12 pm

where is basedao,Where is ExcelTemplatev? In the Above Example?
- Sushil Singh
  
  November 19, 2018 at 4:03 pm
  
  Hi Sairam,
  You can download working sample of the project from our github repository link provided in the post. Post is covering only the concepts but github repo has working sample.
  https://github.com/sushilsingh94/Spring-Examples/tree/master/SpringFileUploadToDB
Anshu (@DeveloperAnshu)

January 11, 2019 at 12:26 am

Thank you
yuditya

July 12, 2019 at 9:34 am

why i keep getting request method ‘post’ not supported
- Sushil Singh
  
  July 13, 2019 at 9:13 pm
  
  Can you please elaborate more on your issue? Please give more details on the issue that you are facing.
Sathya K

July 25, 2019 at 8:50 pm

I have downloaded the project but am not able to run it. can you please help me to get the result.
- Sushil Singh
  
  September 4, 2019 at 11:25 am
  
  You should import this project as Existing maven Project into your eclipse and add a tomcat server to run on.
- Sushil Singh
  
  August 5, 2020 at 3:30 pm
  
  What is the error you are getting?
harish

November 30, 2019 at 1:58 pm

Thank you sir