Home
Answers
Viewqa
JSP-Servlet
how to read the values for text and csv files and store those values into database in multiple rows..means one value for one row
QuestionAsk Questions?
sridivya pathaneni
how to read the values for text and csv files and store those values into database in multiple rows..means one value for one row
4 Answer(s) a year and 9 months ago
Posted in : JSP-Servlet
4 Answer(s) a year and 9 months ago
Posted in : JSP-Servlet
Hai,
I need a program by using servlets.. the program is like this
i upload a text and csv file.it was stored in perticular directory now i have to read the stored(.csv or .txt) file and get the values from that file and store them into database table in multiple rows(which means one value for one row).
eg: my file containes the data as content of file ==> siva,divya,ravi
now it should stored in database like names(column)siva divya
ravione value for one row.... can any one help in this program?
Regards, P.Divya
View Answers
August 12, 2011 at 1:56 AM
hi sridivya,
Just follows the steps which I have mentioned below. I have created this project in eclipse
Setup database
Here, I used MySql database, so type the following queries in mysql
create database test;
use test
create table namelist(name varchar(20));
Required files
1. uploadform.jsp
2. Upload.java (Servlet)
3. confirm.jsp
4. UpdateDB.java (Servlet)
5. error.jsp
6. namelist.csv
Required library
1.javacsv.jar
2.mysql-connector-java-5.1.10-bin.jar
You can download these library from
[mysql driver][1]
[CSVparser][2]
Add the above library to "ContextRoot/WEB-INF/lib/" directory to use servlet
Coding
1. uploadform.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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=UTF-8">
<title>Upload file</title>
</head>
<body>
<form action="upload" enctype="multipart/form-data" method="post">
<table border="1">
<tr>
<td>CSV or TXT file:</td>
<td><input type="file" name="csvfile" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" value="submit"></td>
</tr>
</table>
</form>
</body>
</html>
2. Upload.java (Servlet)
package com.file;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class UploadServlet
*/
public class Upload extends HttpServlet {
private static final long serialVersionUID = 1L;
PrintWriter out;
private void setOut(PrintWriter out){
this.out = out;
}
private void println(String content){
out.print(content+"\n");
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
setOut(response.getWriter());
boolean done = false;
//to get the content type information from JSP Request Header
String contentType = request.getContentType();
//here we are checking the content type is not equal to Null and as well as the passed data from mulitpart/form-data is greater than or equal to 0
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
//we are taking the length of Content type data
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
//this loop converting the uploaded file into byte code
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
//for saving the file name
String 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;
//extracting the index of file
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;
if(saveFile.endsWith(".txt") || saveFile.endsWith(".csv")){
// creating a new file with the same name and writing the content in new file
FileOutputStream fileOut = new FileOutputStream(new File("../uploads/"+saveFile));
fileOut.write(dataBytes, startPos, (endPos - startPos));
fileOut.flush();
fileOut.close();
done = true;
getServletContext().setAttribute("fileName", saveFile);
request.setAttribute("fileName", saveFile);
}else{
request.setAttribute("error", "Unsupported file format");
}
}
if(done)
request.getRequestDispatcher("confirm.jsp").forward(request, response);
else
request.getRequestDispatcher("error.jsp").forward(request, response);
}
}
3. confirm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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=UTF-8">
<title>Success Page</title>
</head>
<body>
<%=request.getAttribute("fileName") %> is uploaded successfully.
<a href="updatedb">Click here to update database</a>
</body>
</html>
4. updatedb.java (Servlet)
package com.db;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.csvreader.CsvReader;
/**
* Servlet implementation class UpdateDatabase
*/
public class UpdateDatabase extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public UpdateDatabase() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
PrintWriter out = response.getWriter();
try {
String filename = (String) getServletContext().getAttribute("fileName");
out.println("FileName : "+filename);
CsvReader products = new CsvReader("../uploads/"+filename);
products.readHeaders();
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/stack", "root", "");
Statement st = con.createStatement();
while (products.readRecord())
{
String productID = products.get("ProductID");
String productName = products.get("ProductName");
String supplierID = products.get("SupplierID");
String categoryID = products.get("CategoryID");
String quantityPerUnit = products.get("QuantityPerUnit");
String unitPrice = products.get("UnitPrice");
String unitsInStock = products.get("UnitsInStock");
String unitsOnOrder = products.get("UnitsOnOrder");
String reorderLevel = products.get("ReorderLevel");
String discontinued = products.get("Discontinued");
String query = "insert into product values (";
query += productID+", '";
query += productName+"', ";
query += supplierID+", ";
query += categoryID+", '";
query += quantityPerUnit+"', ";
query += unitPrice+", ";
query += unitsInStock+", ";
query += unitsOnOrder+", ";
query += reorderLevel+", '";
query += discontinued+"')";
out.println("Query : "+query);
st.executeUpdate(query);
}
out.println("Data inserted...");
products.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}
5. error.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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=UTF-8">
<title>Fail Page</title>
</head>
<body>
<%=request.getAttribute("error") %>
Choose correct file format <br />
<a href="uploadform.jsp">Upload file</a>
</body>
</html>
6. namelist.csv
name
siva
divya
ravi
Regards,
M.Sriram
August 12, 2011 at 8:31 AM
Hi,
There was an error in the above demo. Need to change database and namelist.csv file.
Setup database
create database stack;
use stack
create table product(ProductID int, ProductName varchar(20), SupplierID int, CategoryID int, QuantityPerUnit varchar(50), UnitPrice int, UnitsInStock int, UnitsOnOrder int, ReorderLevel int, Discontinued varchar(8));
**6. namelist.csv**
ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
1,Chai,1,1,10 boxes x 20 bags,18,39,0,10,FALSE
2,Chang,1,1,24 - 12 oz bottles,19,17,40,25,FALSE
August 12, 2011 at 12:35 PM
can u tel me the file size limit for this program?
Regards, P.Divya
August 12, 2011 at 9:45 PM
Which file are you asking about? csv file?
Regards,
M.Sriram
Related Pages:
If you are facing any programming issue, such as compilation errors or not able to find the code you are looking for.
Ask your questions, our development team will try to give answers to your questions.
Tutorial Topics
