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));
      });
  }
}

Why not use StringBuilder? CSV is just a text format right? or build your own CSV class whose toString() method outputs a String in the format you need? — mal
– mal, Commented Sep 30, 2017 at 14:08
Just updated my post - but not sure where to start with your suggestion - you able to put it in an answer - @bot_bot — The Old County
– The Old County, Commented Sep 30, 2017 at 14:21

mal · Accepted Answer · 2017-09-30 14:38:07Z

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.

answered Sep 30, 2017 at 14:38

mal

3,3025 gold badges44 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeronimo Backes Over a year ago

That is a bad solution because the address, especially the street is bound to contain characters such as commas, quotes or line endings. These need to be properly escaped in order to generate a parseable CSV output. Use a CSV library for that instead.

mal Over a year ago

Yes I agree, it was just an example it illustrate how a solution could work.

Andreas · Accepted Answer · 2017-09-30 15:36:39Z

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;
}

Jeronimo Backes · Accepted Answer · 2017-10-01 10:57:43Z

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.

Collectives™ on Stack Overflow

Java creating an api to expose a csv instead of json

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related