Download DB values as CSV file with JNDI Connection Pool Method

Context.xml

<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"

               maxActive="100" maxIdle="30" maxWait="10000"

               username="root" password="" driverClassName="com.mysql.jdbc.Driver"

               url="jdbc:mysql://localhost:3306/cdcol"/>

 //This should be added in the servers context,xml file. For example if you are using apache server then the context.xml will be found in C:\apache-tomcat-6.0.26\conf\Context.xml

web.xml

  <resource-ref>

      <description>DB Connection</description>

      <res-ref-name>jdbc/TestDB</res-ref-name>

      <res-type>javax.sql.DataSource</res-type>

      <res-auth>Container</res-auth>

  </resource-ref>

//This should be added in the web.xml of the local project. (Not in server's web.xml).

Program:

import java.io.ByteArrayInputStream;

import java.io.IOException;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.servlet.ServletException;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.sql.DataSource;

public class UploadFile extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

ServletOutputStream output = null;

ByteArrayInputStream fileToDownload = null;

StringBuffer data = new StringBuffer("First Name, Last Name, Mobile Number, Company Name");

data.append("\n");

Connection con = null;

Statement stmt;

    try

     {

        Context ctx=new InitialContext();

        Context envContext = (Context)ctx.lookup("java:comp/env");

        DataSource ds=(DataSource)envContext.lookup("jdbc/TestDB");//TestDB is the Database Name

        con=ds.getConnection();

        stmt = con.createStatement();

        ResultSet rs = stmt.executeQuery("Select * from <table_name> where <condition>");

        while(rs.next())  //This is for four columns

        {

        data.append(rs.getString(1));

        data.append(',');

        data.append(rs.getString(2));

        data.append(',');

        data.append(rs.getString(3));

        data.append(',');

        data.append(rs.getString(4));

        data.append('\n');

        }

     }

    catch(Exception e)

     { //Print the stack trace }

    fileToDownload = new ByteArrayInputStream(data.toString().getBytes());

    output = response.getOutputStream();

    response.setContentType("application/csv");

    response.setHeader("Content-Disposition", "attachment; filename=data.csv");//data.csv - Download file name

    response.setContentLength(fileToDownload.available());

    int c;

    while ((c = fileToDownload.read()) != -1)

    {

    output.write(c);

    }

    output.flush();

    output.close();

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

// The code can be done in post also

}

}