How to get download csv file using java servlet ?

Rose_Taylor
Rose_Taylor used Ask the Experts™
on
I need to download the data file from database with csv format using java/servlet.I would like to download the csv file when the download button.

Here with attahed the source code , please let know the code should add here.

Please correct the source the code here.What are the changes want to do here ?
csv.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Try using the following:
    public static void resultSetToCsv(ResultSet rs, Writer out) {
	try {
	    final String LINEFEED = "\r\n";
	    ResultSetMetaData metaData = rs.getMetaData();
	    int numberOfColumns = metaData.getColumnCount();

	    // Get the column names
	    String sep = "";

	    for (int column = 0; column < numberOfColumns; column++) {
		out.write(sep);
		out.write(metaData.getColumnLabel(column + 1));
		sep = ",";
	    }

	    out.write(LINEFEED);

	    // Get all rows.
	    while (rs.next()) {
		sep = "";

		for (int i = 1; i <= numberOfColumns; i++) {
		    out.write(sep);
		    out.write(rs.getObject(i).toString());
		    sep = ",";
		}

		out.write(LINEFEED);
	    }
	} catch (Exception e) {
	    e.printStackTrace();
	}
    }

Open in new window

Top Expert 2016

Commented:
You probably want in addition:
response.setContentType("text/csv");
// Or
//response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=data.csv"));

Open in new window

Author

Commented:
Could you help me for integrate your code.Please.

How to pass the arguments to your function ?


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

    
    String connectionURL = "jdbc:mysql://localhost/csv";
    String url=request.getParameter("WEB_URL");
    String Content=new String("");
    Statement stmt=null;
    Connection con=null;
    try
    {
        
    String filename="SearchCases.csv";
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    con=DriverManager.getConnection(connectionURL,"root","root");
    stmt=con.createStatement();
    //stmt.setMaxRows(20);
    String qry = "select * from user_managecase";
        ResultSet rst= stmt.executeQuery(qry);
    
    while(rst.next())
    {

    Content=rst.getString("USER_TYPE");
    
    }
    //ServletOutputStream out = null;
    //out.println(Content);
    
    byte requestBytes[] = Content.getBytes();
    ByteArrayInputStream bis = new ByteArrayInputStream(requestBytes);
    response.reset();
    response.setContentType("application/text");
    response.setHeader("Content-disposition","attachment; filename=" +filename);
    byte[] buf = new byte[1024];
    int len;
    while ((len = bis.read(buf)) > 0){
    response.getOutputStream().write(buf, 0, len);
    }
    
    }
    catch(Exception e){
    e.printStackTrace();
    }
    finally
    {
        bis.close();
        response.getOutputStream().flush();
    }
}protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    
    String connectionURL = "jdbc:mysql://localhost/csv";
    String url=request.getParameter("WEB_URL");
    String Content=new String("");
    Statement stmt=null;
    Connection con=null;
    try
    {
        
    String filename="SearchCases.csv";
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    con=DriverManager.getConnection(connectionURL,"root","root");
    stmt=con.createStatement();
    //stmt.setMaxRows(20);
    String qry = "select * from user_managecase";
        ResultSet rst= stmt.executeQuery(qry);
    
    while(rst.next())
    {

    Content=rst.getString("USER_TYPE");
    
    }
    //ServletOutputStream out = null;
    //out.println(Content);
    
    byte requestBytes[] = Content.getBytes();
    ByteArrayInputStream bis = new ByteArrayInputStream(requestBytes);
    response.reset();
    response.setContentType("application/text");
    response.setHeader("Content-disposition","attachment; filename=" +filename);
    byte[] buf = new byte[1024];
    int len;
    while ((len = bis.read(buf)) > 0){
    response.getOutputStream().write(buf, 0, len);
    }
    
    }
    catch(Exception e){
    e.printStackTrace();
    }
    finally
    {
        bis.close();
        response.getOutputStream().flush();
    }
}

Your code:

 public static void resultSetToCsv(ResultSet rs, Writer out) {
	try {
	    final String LINEFEED = "\r\n";
	    ResultSetMetaData metaData = rs.getMetaData();
	    int numberOfColumns = metaData.getColumnCount();

	    // Get the column names
	    String sep = "";

	    for (int column = 0; column < numberOfColumns; column++) {
		out.write(sep);
		out.write(metaData.getColumnLabel(column + 1));
		sep = ",";
	    }

	    out.write(LINEFEED);

	    // Get all rows.
	    while (rs.next()) {
		sep = "";

		for (int i = 1; i <= numberOfColumns; i++) {
		    out.write(sep);
		    out.write(rs.getObject(i).toString());
		    sep = ",";
		}

		out.write(LINEFEED);
	    }
	} catch (Exception e) {
	    e.printStackTrace();
	}
    }

Open in new window

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Top Expert 2016
Commented:
Try the following:
   protected void doGet(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {
        String connectionURL = "jdbc:mysql://localhost/csv";
        String url = request.getParameter("WEB_URL");
        Statement stmt = null;
        Connection con = null;

        try {
            String filename = "SearchCases.csv";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection(connectionURL, "root", "root");
            stmt = con.createStatement();

            String qry = "select * from user_managecase";
            ResultSet rst = stmt.executeQuery(qry);
            response.setContentType("text/csv");
            response.setHeader("Content-disposition", "attachment; filename=" + filename);
	    resultSetToCsv(rst, response.getWriter());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            con.close();
        }
    }

Open in new window

Author

Commented:
Excellent CEHJ :)..

I known about you, last 6 months i have been  wathing your reply...It is Always perfect answer....

Thank you very much .
Top Expert 2016

Commented:
:) Glad to help

Author

Commented:
If any null values in database, it returning

java.lang.NullPointerException


Where to get resolve the nullpointerexception.


Top Expert 2016

Commented:
Good point. Try instead
out.write("" + rs.getObject(i));

Open in new window

Author

Commented:
Sorry , still same error here.

 // Get all rows.
        while (rs.next()) {
        sep = "";

        for (int i = 1; i <= numberOfColumns; i++) {
            out.write(sep);
            out.write("" + rs.getObject(i).toString());
            sep = ",";
        }

public static void resultSetToCsv(ResultSet rs, Writer out) {
	try {
	    final String LINEFEED = "\r\n";
	    ResultSetMetaData metaData = rs.getMetaData();
	    int numberOfColumns = metaData.getColumnCount();

	    // Get the column names
	    String sep = "";

	    for (int column = 0; column < numberOfColumns; column++) {
		out.write(sep);
		out.write(metaData.getColumnLabel(column + 1));
		sep = ",";
	    }

	    out.write(LINEFEED);

	    // Get all rows.
	    while (rs.next()) {
		sep = "";

		for (int i = 1; i <= numberOfColumns; i++) {
		    out.write(sep);
		    out.write("" + rs.getObject(i).toString());
		    sep = ",";
		}

		out.write(LINEFEED);
	    }
	} catch (Exception e) {
	    e.printStackTrace();
	}

Open in new window

Top Expert 2016

Commented:
>> out.write("" + rs.getObject(i).toString());

You didn't use the code i posted ;)
Top Expert 2016

Commented:
How are you getting on?
Mick BarryJava Developer
Top Expert 2010

Commented:
there are lots of existing packages that will do that for you, far easier than reinventing the wheel :)

Author

Commented:
It is working great.Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial