Download data from mysql db to csv file using java
Hi
Friends !
In
Previous blog We discussed Extract data from mysql db to Csv file using Java
and inside We provide a path where file is stored but in real scenario it is
not possible that we create an application and instruct to user that brother
when you use this application then for run it properly you have to make a
folder at specific location where your obtained data from database and that
data will be stored in it and you can
reuse this data where you want. In previous scenario we can say that we have
discussed that how we obtain our data from database and store it inside CSV for
further use. In this Blog I will discussed that how you can obtain data from
data base and stored it inside CSV file and that after download CSV file .
In
this case also we create three files for completing this Scenario
1.exportCsv.html
: which perform a form action and contains a button and after submit button our
Java Servlet will be called.
2.ExportData.java
:It is a Servlet class and it established a connection from server and client
and it also perform operation of downloading CSV file.
3.
result.html :It is our final file and it displays a message that Downloading
has been completed.
Now
we will write codes in side these files and discussed about codes and about its
working but before it we will create a table from which we could be download
data.
So
at first create a table and insert some values inside that table I have already
create a table inside mySql database stored some values inside that table here
Now
we take each file and write codes inside that file
exportCsv.html
<body>
<div>Click for downloading file</div>
<form action="ExportDataExample" method="post">
<input type="submit" value="Export" />
</form>
</body>
ExportData.java
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
*
* @author dheeraj.kumar
*/
public class ExportDataCsv extends HttpServlet {
/**
* Processes requests for both HTTP GET and POST
* methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
try {
// Db_Connection dbconn = new Db_Connection();
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "root");
// if (request.getParameter("payslipDetail") != null) {
stmt = conn.createStatement();
StringBuilder fw = new StringBuilder();
String sql = "Select * from export_test";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
fw.append("empid");
fw.append(',');
fw.append("empname");
fw.append(',');
fw.append("Designation");
fw.append(',');
fw.append("Salary");
fw.append('\n');
while (rs.next()) {
fw.append(rs.getString(1));
fw.append(',');
fw.append(rs.getString(2));
fw.append(',');
fw.append(rs.getString(3));
fw.append(',');
fw.append(rs.getString(4));
fw.append('\n');
}
response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment; filename=\"Details.csv\"");
try {
OutputStream outputStream = response.getOutputStream();
String outputResult = fw.toString();
outputStream.write(outputResult.getBytes());
outputStream.flush();
outputStream.close();
} catch (Exception e) {
System.out.println(e.toString());
}
stmt.close();
response.sendRedirect("result.html");
//}
} catch (Exception ex) {
ex.printStackTrace();
}
}
//
/**
* Handles the HTTP GET method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP POST method.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
*
* @return a String containing servlet description
*/
@Override
public String getServletInfo() {
return "Short description";
}//
}
result.html
Data was exported on specified location
More Links :
Comments
Post a Comment