Export data from mysql db to csv file using java

Hi friends !

Today I am discussing about simple but very important scenario. When we create any application then during creation of application we have face many scenarios like

1.Taking information from user and storing these information inside database : Generally such type of Operation is performed by user using form application

2.Taking Data from Database and Displaying on Page : We can perform such type of Operation in various ways like using JSP,JSTL tags or you can perform such operations using JQUERY also.

3.Taking Data from Database and download it in Excel or CSV format : In this we want our data from database and store it inside Excel or text files for further use.

And we have chosen third scenario from discussion for performing this Operation we have to need three files

1. exportCsv.html :- this contains a button for clicking and performing exporting data from database Operation
2. ExportData.java :- It is a servlet class which defines connectivity from database and exporting data inside csv file and save exported on specified location (because it is servlet then it automatically provides a connection between server and user).
3. result.html :- After clicking on button and calling servlet class and performing all Operations result.html display final result that your data has been downloaded at specified location.

Note : we have also need a table from which we export our data and obtain inside csv file for this purpose we will create a table and stored some information inside that table and discussing this scenario already I have created a table and stored some information inside table.

At first create a table inside database and store some information like given below

Now we provide codes of specified pages and discussed

exportCsv.html

Click for downloading file

<form action =”ExportData.java” method=”post”>

<input type=”submit” value=”Export”>

</form>

ExportData.java

 import java.io.FileWriter;
 import java.io.IOException;
 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;
/**
 *
 * @ Export Data from database and stored inside csv file
 */
 public class ExportDataExample extends HttpServlet {
 protected void processRequest(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
  response.setContentType(“text / html; charset = UTF - 8”);
  // Location for storing file
  String filename = ”C: /Users/Techonology / test.csv”;
  try (PrintWriter out = response.getWriter()) {
   FileWriter fw = new FileWriter(filename);
   // Establish Connection with Databse
   Class.forName(“com.mysql.jdbc.Driver”);
   Connection con = DriverManager.getConnection(“jdbc: mysql: //localhost:3306/mysql”, “root”, “root”);
    // take data from database
    String sql = ”Select * from export_test”; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql);
    //Create header inside Csv file
    fw.append(“empid”); fw.append(‘, ’); fw.append(“empname”); fw.append(‘, ’); fw.append(“Designation”); fw.append(‘, ’); fw.append(“Salary”); fw.append(‘\n’);
    // extract data from databse
    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’);
    }
    fw.flush(); fw.close(); con.close(); response.sendRedirect(“result.html”);
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }
  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {
   processRequest(request, response);
  }
  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {
   processRequest(request, response);
  }
  @Override
  public String getServletInfo() {
   return“ Short description”;
  }
 }

finally,

result.html

Data was exported on specified location