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
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
