Spring Boot CSV

last modified July 6, 2020

In this tutorial, we are going to serve content in CSV in a Spring Boot RESTful application. We use Opencsv library.

Spring is a popular Java application framework for creating enterprise applications. Spring Boot is the next step in evolution of Spring framework. It helps create stand-alone, production-grade Spring based applications with minimal effort. It abandons XML configurations and uses heavily the convention over configuration principle.

CSV (Comma Separated Values) is simple data format where values are (mostly) separated by a comma character and each line represents one record. The data is stored in a plain text file. It is very popular as import and export format used in spreadsheets and databases. Opencsv is an open source, simple CSV parser library for Java.

Hibernate is an object-relational mapping framework for the Java language. It provides a framework for mapping an object-oriented domain model to a relational database. Object-relational mapping (ORM) is a programming technique for converting data between incompatible type systems in object-oriented programming languages.

Spring Data Spring Data JPA is part of the umbrella Spring Data project that makes it easier to implement JPA based repositories. Spring Data JPA uses JPA to store data in a relational database. It can create repository implementations automatically, at runtime, from a repository interface.

A RESTFul application follows the REST architectural style, which is used for designing networked applications. RESTful applications generate HTTP requests which perform CRUD (Create/Read/Update/Delete) operations on resources.

Spring Boot CSV example

Our application is a Spring Boot RESTful application which returns data from an H2 database in a CSV format.

pom.xml
src
├── main
│   ├── java
│   │   └── com
│   │       └── zetcode
│   │           ├── Application.java
│   │           ├── model
│   │           │   └── City.java
│   │           ├── controller
│   │           │   └── MyCityController.java
│   │           ├── repository
│   │           │   └── CityRepository.java
│   │           ├── service
│   │           │   ├── CityService.java
│   │           │   └── ICityService.java
│   │           └── util
│   │               └── WriteCsvToResponse.java
│   └── resources
│       ├── application.yml
│       └── import.sql
└── test
    └── java

This is the project structure.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zetcode</groupId>
    <artifactId>SpringBootCSV</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>13</maven.compiler.source>
        <maven.compiler.target>13</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>5.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

This is the Maven build file. The h2 dependency adds a driver for H2 database. The opencsv dependency adds a driver for the Opencsv library.

Spring Boot starters are a set of convenient dependency descriptors we can include in our application. They greatly simplify Maven configuration. The spring-boot-starter-parent provides some common configurations for a Spring Boot application. The spring-boot-starter-web is a starter for building web, including RESTful, applications using Spring MVC. It uses Tomcat as the default embedded container. The spring-boot-starter-data-jpa is a starter for using Spring Data JPA with Hibernate.

The spring-boot-maven-plugin provides Spring Boot support in Maven, allowing us to package executable JAR or WAR archives. Its spring-boot:run goal runs the Spring Boot application.

spring:
    main:
        banner-mode: "off"
    jpa:
        database: h2
        hibernate:
            dialect: org.hibernate.dialect.H2Dialect
            ddl-auto: create-drop

logging:
    level:
        org:
            springframework: ERROR

The application.yml file contains various configuration settings of a Spring Boot application. With the banner-mode property we turn off the Spring banner.

The JPA database value specifies the target database to operate on. We specify the Hibernate dialect, org.hibernate.dialect.H2Dialect in our case. The ddl-auto is the data definition language mode; the create-drop option automatically creates and drops the database schema.

The H2 database is run in memory. Also, we set the logging level for spring framework to ERROR. The application.yml file is located in the in the src/main/resources directory.

package com.zetcode.model;

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "cities")
public class City {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private int population;

    public City() {
    }

    public City(Long id, String name, int population) {

        this.id = id;
        this.name = name;
        this.population = population;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 79 * hash + Objects.hashCode(this.id);
        hash = 79 * hash + Objects.hashCode(this.name);
        hash = 79 * hash + this.population;
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final City other = (City) obj;
        if (this.population != other.population) {
            return false;
        }
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return Objects.equals(this.id, other.id);
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("City{");
        sb.append("id=").append(id);
        sb.append(", name='").append(name).append('\'');
        sb.append(", population=").append(population);
        sb.append('}');
        return sb.toString();
    }
}

This is the City entity. Each entity must have at least two things defined: @Entity annotation and an Id field with @Id annotation. We have set the ddl-auto option to create-drop which means that Hibernate will create the table schema from this entity.

@Entity
@Table(name = "cities")
public class City {

The @Entity annotation specifies that the class is an entity and is mapped to a database table. The @Table entity specifies the name of the database table to be used for mapping.

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

The @Id annotation specifies the primary key of an entity and the @GeneratedValue provides for the specification of generation strategies for the values of primary keys.

INSERT INTO cities(name, population) VALUES('Bratislava', 432000);
INSERT INTO cities(name, population) VALUES('Budapest', 1759000);
INSERT INTO cities(name, population) VALUES('Prague', 1280000);
INSERT INTO cities(name, population) VALUES('Warsaw', 1748000);
INSERT INTO cities(name, population) VALUES('Los Angeles', 3971000);
INSERT INTO cities(name, population) VALUES('New York', 8550000);
INSERT INTO cities(name, population) VALUES('Edinburgh', 464000);
INSERT INTO cities(name, population) VALUES('Berlin', 3671000);

The schema is automatically created by Hibernate; later, the import.sql file is executed to fill the table with data.

package com.zetcode.repository;

import com.zetcode.model.City;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CityRepository extends CrudRepository<City, Long> {

}

By extending from the Spring CrudRepository, we will have some methods for our data repository implemented, including findAll() and findById(). This way we save a lot of boilerplate code.

package com.zetcode.service;

import com.zetcode.model.City;
import java.util.List;

public interface ICityService {

    List<City> findAll();
    City findById(Long id);
}

ICityService provides contract methods to get all cities and get a city by its Id from the data source.

package com.zetcode.service;

import com.zetcode.model.City;
import com.zetcode.repository.CityRepository;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class CityService implements ICityService {

    @Autowired
    private CityRepository repository;

    @Override
    public List<City> findAll() {

        return  (List<City>) repository.findAll();
    }

    @Override
    public City findById(Long id) {

        return repository.findById(id).orElse(new City());
    }
}

CityService contains the implementation of the findAll() and findById() methods. We use repository to retrieve data from the database.

@Autowired
private CityRepository repository;

CityRepository is injected.

return  (List<City>) repository.findAll();

The findAll() method of the repository returns the list of cities.

return repository.findById(id).orElse(new City());

The findById() method of the repository returns one specific city object. If no city is found, we send an empty city object.

package com.zetcode.controller;

import com.zetcode.model.City;
import com.zetcode.service.ICityService;
import com.zetcode.util.WriteCsvToResponse;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class MyCityController {

    @Autowired
    ICityService cityService;

    @RequestMapping(value = "/cities", produces = "text/csv")
    public void findCities(HttpServletResponse response) throws IOException {

        List<City> cities = (List<City>) cityService.findAll();

        WriteCsvToResponse.writeCities(response.getWriter(), cities);
    }

    @RequestMapping(value = "/cities/{cityId}", produces = "text/csv")
    public void findCity(@PathVariable Long cityId, HttpServletResponse response) throws IOException {

        City city = cityService.findById(cityId);
        WriteCsvToResponse.writeCity(response.getWriter(), city);
    }
}

This is the controller class for the Spring Boot RESTful application. The @RestController annotation creates a RESTful controller. While the traditional MVC controller uses ModelAndView, the RESTful controller simply returns the object and the object data is written directly to the HTTP response (usually) in JSON or XML format. In our case, we chose CSV format.

@Autowired
private ICityService cityService;

We inject a ICityService into the countryService field.

@RequestMapping(value = "/cities", produces = "text/csv")
public void findCities(HttpServletResponse response) throws IOException {

...
}

The @RequestMapping annotation is used to map web requests to Spring controller methods. The produces option sets the media type, which is text/csv in our case. We map a request with the /cities path to the controller's findCities() method. The default request is a GET request.

List<City> cities = (List<City>) cityService.findAll();

WriteCsvToResponse.writeCities(response.getWriter(), cities);

We call the cityService's findAll() to get all the cities. We write the CSV data into the HttpServletResponse object. The mapping of Java beans to CSV data is delegated to the WriteCsvToResponse class.

@RequestMapping(value = "/cities/{cityId}", produces = "text/csv")
public void findCity(@PathVariable Long cityId, HttpServletResponse response) throws IOException {

    City city = cityService.findById(cityId);
    WriteCsvToResponse.writeCity(response.getWriter(), city);
}

In the second method, we have an URL path that contains the Id of the city to be retrieved; we use the @PathVariable annotation to bind the URL template variable to the method cityId parameter.

package com.zetcode.util;

import com.opencsv.CSVWriter;
import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.opencsv.exceptions.CsvException;
import com.zetcode.model.City;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.PrintWriter;
import java.util.List;

public class WriteCsvToResponse {

    private static final Logger LOGGER = LoggerFactory.getLogger(WriteCsvToResponse.class);

    public static void writeCities(PrintWriter writer, List<City> cities) {

        try {

            ColumnPositionMappingStrategy<City> mapStrategy
                    = new ColumnPositionMappingStrategy<>();

            mapStrategy.setType(City.class);

            String[] columns = new String[]{"id", "name", "population"};
            mapStrategy.setColumnMapping(columns);

            StatefulBeanToCsv<City> btcsv = new StatefulBeanToCsvBuilder<City>(writer)
                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
                    .withMappingStrategy(mapStrategy)
                    .withSeparator(',')
                    .build();

            btcsv.write(cities);

        } catch (CsvException ex) {

            LOGGER.error("Error mapping Bean to CSV", ex);
        }
    }

    public static void writeCity(PrintWriter writer, City city) {

        try {

            ColumnPositionMappingStrategy<City> mapStrategy
                    = new ColumnPositionMappingStrategy<>();

            mapStrategy.setType(City.class);

            String[] columns = new String[]{"id", "name", "population"};
            mapStrategy.setColumnMapping(columns);

            StatefulBeanToCsv<City> btcsv = new StatefulBeanToCsvBuilder<City>(writer)
                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
                    .withMappingStrategy(mapStrategy)
                    .withSeparator(',')
                    .build();

            btcsv.write(city);

        } catch (CsvException ex) {

            LOGGER.error("Error mapping Bean to CSV", ex);
        }
    }
}

In WriteCsvToResponse, we use Opencsv library to convert Java beans to CSV and write the final output into the HttpServletResponse.

ColumnPositionMappingStrategy<City> mapStrategy
    = new ColumnPositionMappingStrategy<>();

mapStrategy.setType(City.class);

A MappingStrategy defines how Java attributes are mapped to CSV column names. ColumnPositionMappingStrategy uses column position to do the mapping.

String[] columns = new String[]{"id", "name", "population"};
mapStrategy.setColumnMapping(columns);

We set the column names.

StatefulBeanToCsv<City> btcsv = new StatefulBeanToCsvBuilder<City>(writer)
    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
    .withMappingStrategy(mapStrategy)
    .withSeparator(',')
    .build();

StatefulBeanToCsv class writes beans out in CSV format to writer keeping state information and making an intelligent guess at the mapping strategy to be applied.

btcsv.write(city);

Finally, the beans are written.

package com.zetcode;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

The Application sets up the Spring Boot application. The @SpringBootApplication enables auto-configuration and component scanning.

$ mvn spring-boot:run

With mvn spring-boot:run command, we run the application. The application is deployed on embedded Tomcat server.

$ curl localhost:8080/cities
1,Bratislava,432000
2,Budapest,1759000
3,Prague,1280000
4,Warsaw,1748000
5,Los Angeles,3971000
6,New York,8550000
7,Edinburgh,464000
8,Berlin,3671000

With the curl command, we get all cities.

$ curl localhost:8080/cities/1
1,Bratislava,432000

Here we get one city identified by its Id.

In this tutorial, we have returned data to the client in CSV format from a Spring Boot RESTful application. We used Opencsv library.

List all Spring Boot tutorials.