Q:

Downloading very large results from query via Java Webapp

I have been mulling over a bit of an issue, and I was wondering if anyone had any advice. 
I have a web application that I need to add a section for reporting to.  It will have a t-sql query constructed based on user gui inputs, execute, and allow the user to download the file to their computer through their browser as a csv.  On it's own none of this is overly complicated.  I have done these things before.  There is one issue I have not grappled with before however.  The results may end up as multiple GBs worth of data.  What would I do differently (in terms of code) from normal to prevent it from overloading the browser or being tremendously slow?  It clearly cannot all be held in memory at once?  How do I prevent that from happening? 

A:

You can write the csv-data directly to the OutputStream of the incoming request:
package example;

import javax.servlet. ServletException;
import javax.servlet.http. HttpServlet;
import javax.servlet.http. HttpServletRequest;
import javax.servlet.http. HttpServletResponse;

import java.io. IOException;
import java.io. OutputStreamWriter;
import java.io. Writer;
import java.nio.charset. StandardCharsets;

public class MyServlet extends HttpServlet {

    private final CSVCreator csvCreator;

    private MyServlet(CSVCreator csvCreator) {
        this.csvCreator = csvCreator;
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // be nice to your consumer, tell them what they get
        resp.setHeader("Content-Type", "text/comma-separated-values; charset=UTF-8");

        try (Writer writer = new OutputStreamWriter(resp.getOutputStream(), StandardCharsets. UTF_8)) {
            this.csvCreator.createCSV("some params you need to create the correct csv", writer);
        }
    }
}

You will have to change your csv-generation to be able to write to an Writer ... for example:
package example;

import java.io. IOException;

public class CSVCreator {

    public void createCSV(String someParamsYouNeedToCreateTheCSV, Appendable appendable) throws IOException {
        appendable.append("Column1,Column2,Column3\n");
        appendable.append("Row1,Row1,Row1\n");
        appendable.append("Row2,Row2,Row2\n");
    }
}

You could also, for example, use apache-commons-csv to write the csv to the given Appendable (see: https://javadoc.io/doc/org.apache.commons/commons-csv/latest/org/apache/commons/csv/CSVPrinter.html )
package example;

import org.apache.commons.csv. CSVFormat;
import org.apache.commons.csv. CSVPrinter;

import java.io. IOException;

public class CSVCreator {

    public void createCSV(String someParamsYouNeedToCreateTheCSV, Appendable appendable) throws IOException {
        try (CSVPrinter printer = new CSVPrinter(appendable, CSVFormat. DEFAULT)) {
            printer.printRecord("Column1", "Column2", "Column3");
            printer.printRecord("Row1", "Row1", "Row1");
            printer.printRecord("Row2", "Row2", "Row2");
        }
    }
}

EDIT:
You can create a CSV-File from a ResultSet like this:
package example;

import org.apache.commons.csv. CSVFormat;
import org.apache.commons.csv. CSVPrinter;

import java.io. IOException;
import java.sql. ResultSet;
import java.sql. ResultSetMetaData;
import java.sql. SQLException;

public class ResultSetToCSV {

    public void createCSV(ResultSet rs, Appendable appendable) throws IOException, SQLException {
        final ResultSetMetaData metaData = rs.getMetaData();
        final int columnCount = metaData.getColumnCount();

        try (CSVPrinter printer = new CSVPrinter(appendable, CSVFormat. DEFAULT)) {
            printHeaders(metaData, columnCount, printer);
            printValues(rs, columnCount, printer);
        }
    }

    private void printHeaders(ResultSetMetaData metaData, int columnCount, CSVPrinter printer) throws IOException, SQLException {
        for (int i = 1; i <= columnCount; i++) {
            printer.print(metaData.getColumnLabel(i));
        }

        printer.println();
    }

    private void printValues(ResultSet rs, int columnCount, CSVPrinter printer) throws IOException, SQLException {
        while (rs.next()) {
            printValue(rs, columnCount, printer);
        }
    }

    private void printValue(ResultSet rs, int columnCount, CSVPrinter printer) throws IOException, SQLException {
        for (int i = 1; i <= columnCount; i++) {
            printer.print(rs.getString(i));
        }

        printer.println();
    }
}

