Java creating an api to expose a csv instead of json

Question

I am working on a Java api - using Spring Boot - I would like to create a controller that exports data from the db - into a csv that a user can download.

This is an example of what I have making Json responses.

// get chart
@SuppressWarnings("unchecked")
public Object getChart() {

    // build clean object
    JSONObject contents = new JSONObject();

    //inverse bubble chart
    JSONObject chart = new JSONObject();
    chart.put("label", "250 applicants");
    chart.put("value", 120);

    contents.put("chart", chart);
    contents.put("number", "0202 000 000");

    JSONObject json = new JSONObject();

    json.put("contents", contents);

    return json;
}

I've seen this example -- but its being called from a reactjs framework - so not sure how I would fetch the HttpServletResponse?

Create and Download CSV file Java(Servlet)

would I invoke the api as usual?

//api/getMyCsv
@SuppressWarnings("unchecked")
@RequestMapping(value = {"/api/getMyC"}, method = RequestMethod.GET)
@CrossOrigin(origins = {"*"})
public ResponseEntity<?> getHome(
        //HttpServletRequest request
        ) throws Exception {


            JSONObject chart = getChart();

            JSONArray data = new JSONArray();
                            data.add(chart);

             //create empty response
             JSONObject response = new JSONObject();

             //create success response
             response.put("data", data);
             response.put("status", "success");
             response.put("msg", "fetched csv");


            return new ResponseEntity<>(response, HttpStatus.OK);
}

so with the react - using axios

export function fetchCsv(data) {
  let url = "http://www.example.com/api/getMyC";
  return function (dispatch) {     
   axios.get(url, {
      params: data
    })
      .then(function (response) {
      response = response.data.data;
        dispatch(alertSuccess(response));
      })
      .catch(function (error) {
        dispatch(alertFail(error));
      });
  }
}

Answer 1

CSV is just comma separated values right?
So, you can represent a row of data for example, as a class. Take an address:

30 my street, my town, my country

if I wanted to represent that data as a class, and later as CSV data I'd make a class something like this:

public class AddressCSV{

   private String street;
   private String town;
   private String country;

   public AddressCSV(String street, String town, String country){
      this.street = street;
      this.town = town;
      this.country = country;
   }

   // getters and setters here

  // here you could have a method called generateCSV() for example

  // or you could override the toString() method like this

  @Override
  public String toString(){
     return street + "," + town + "," + country + "\n";  // Add the '\n' if you need a new line 
  }
}

Then you use it the same way as your JSONObject, except instead of returning the whole object you do return address.toString();

This is a very simple example of course. Checkout the StringBuilder class if your have a lot of things to build.

Overriding the toString() method means you can do things like pass your object like System.out.printline(address) and it will print the CSV.

Answer 2

The Spring way to help (un)marshaling data is to implement and register an HttpMessageConverter.

You could implement one that specifically handles MediaType text/csv, and supports whatever object types you decide to implement, e.g.

• List<List<?>> - Row is a list of values, auto-converted to string.

• List<Object[]> - Row is an array of values, auto-converted to string.

• List<String[]> - Row is an array of string values.

• List<?> - Row is an bean object, field names added as header row.
  You may even implement your own field annotations to control column order and header row values.
  You could also make it understand JAXB and/or JSON annotations.

With such a converter in place, it's now easy to write Controller methods returning CSV:

@GetMapping(path="/api/getMyC", produces="text/csv")
public List<String[]> getMyCAsCsv() {
    List<Object[]> csv = Arrays.asList(
        new Object[] { "First Name", "Last Name", "Age" },
        new Object[] { "John"      , "Doe"      , 33    },
        new Object[] { "Jane"      , "Smith"    , 29    }
    );
    return csv;
}

Answer 3

DO NOT try doing basic String trickery because the address, especially the street is bound to contain characters such as commas, quotes or line endings. This can easily mess up the CSV output. These characters need to be properly escaped in order to generate a parseable CSV output. Use a CSV library for that instead.

Example with univocity-parsers:

ResultSet rs = queryDataFromYourDb();
StringWriter output = new StringWriter(); //writing to a String to make things easy

CsvRoutines csvRoutine = new CsvRoutines();
csvRoutine.write(rs, output);

System.out.println(output.toString());

For performance, can write to an OutputStream directly and add more stuff to it after dumping your ResultSet into CSV. In such case you will want to keep the output open for writing. In this case call:

csvRoutine.setKeepResourcesOpen(true);

Before writing the ResultSet. You'll have to close the resultSet after writing.

Disclaimer: I'm the author of this library. It's open-source and free.