To Upload and insert the CSV file into Database

In this tutorial, you will learn how to upload a CSV file through JSP and insert it into the database.

To Upload and insert the CSV file into Database

To Upload and insert the CSV file into Database

     

In this tutorial, you will learn how to upload a CSV file through JSP and insert it into the database. For this, we have created two jsp pages page.jsp and upload_page.jsp. The page.jsp is created for presentation where a file component is created to let the user select the file to be uploaded and a button to submit the request. The action is performed on upload_page.jsp. Before proceeding further, we need table in database. We created table named 'file' for our example.

Table structure for file

create table file (
  'file_id' int ,
  'file_data' text
);

Table "file" is created.

Here is the code of page.jsp

<%@ page language="java" %>
<HTml>
<HEAD><TITLE>Display file upload form to the user</TITLE></HEAD> 

<BODY>
<FORM ENCTYPE="multipart/form-data" ACTION=
"upload_page.jsp" METHOD=POST>
<br><br><br>
<center><table border="2" >
<tr><center><td colspan="2"><p align=
"center"><B>UPLOAD THE FILE</B><center></td></tr>
<tr><td><b>Choose the file To Upload:</b>
</td>
<td><INPUT NAME="file" TYPE="file"></td></tr>
<tr><td colspan="2">
<p align="right"><INPUT TYPE="submit" VALUE="Send File" ></p></td></tr>
<table>
</center> 
</FORM>
</BODY>
</HTML>

The action attribute of the <form> element specifies the name of the page which will be requested next on clicking the submit button. By using <input type="file">, you can browse the file and get the appropriate file for upload. Action is performed on clicking the "Send File" button. On clicking the button upload_page.jsp page is called.

Here is the code of upload_page.jsp

<%@ page import="java.io.*,java.sql.*" %>
<html>
<%
String contentType = request.getContentType();
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;

while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
System.out.println("saveFile=" + saveFile);
saveFile = saveFile.substring(saveFile.lastIndexOf("\\")+ 1,saveFile.indexOf("\""));
System.out.println("saveFile" + saveFile);
saveFile = file.substring(file.indexOf("filename=\"") + 10);
saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
saveFile = saveFile.substring(saveFile.lastIndexOf("\\")+ 1,saveFile.indexOf("\""));
int lastIndex = contentType.lastIndexOf("=");
String boundary = contentType.substring(lastIndex + 1,contentType.length());
int pos;

pos = file.indexOf("filename=\"");
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
pos = file.indexOf("\n", pos) + 1;
int boundaryLocation = file.indexOf(boundary, pos) - 4;
int startPos = ((file.substring(0, pos)).getBytes()).length;
int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;

FileOutputStream fileOut = new FileOutputStream(saveFile);
fileOut.write(dataBytes, startPos, (endPos - startPos));
%>
<b>File <% out.println(saveFile); %> has been uploaded and inserted into Database.</b>
<%Connection con=null;
Statement pst=null;
String line = null;
String value=null;
try{
StringBuilder contents = new StringBuilder();
BufferedReader input = new BufferedReader(new FileReader(saveFile));
while (( line = input.readLine()) != null){
contents.append(line);
}
value = contents.toString();
System.out.println("Value:"+value);
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection
   ("jdbc:mysql://192.168.10.59/application?user=root&password=root"); 
pst=con.createStatement();
int val = pst.executeUpdate("insert into file(file_data) values('"+value+"')");
}
catch(Exception e)
{}
}
%>
</html>

The method request.getContentType() provides the content type information from the jsp header. Then check the two conditions ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)). If the condition is true, create an object of DataInputStream and pass the request.getInputstream() method of Inputstream class to its object. A DataInputstream read primitive Java data types from an underlying input stream.
 
To get the length of the content type, the method request.getContentLength() is used. Convert the uploaded file into the byte by using while loop and pass the array of dataBytes to the variable file. To save file name, we are using substring() method. After creating sub-string of string, extract the index of file. Then create a new file of same name and write the content in the file using FileOutputStream.

The file to be inserted is to be read using buffered reader. A buffer content is created using StringBuilder contents = new StringBuilder(). The method contents.toString() is defined to convert the whole content of file into String.

To insert this file into the database, write the following:
1) Class.forName("com.mysql.jdbc.Driver") for loading driver for database.
2) con=DriverManager.getConnection("jdbc:mysql://192.168.10.59/application?user=root&password=root") for getting connection to the database and
3)
Query to insert data into the database.

Output will be displayed as:

After selecting the file, click the "Send File" button

A message has been displayed on the browser.

The file data.csv is inserted into the database.

Download Source Code