Spring Boot CSV tutorial

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.

Application

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

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

This is the project structure.

pom.xml
<?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>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.3.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>3.9</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.

application.yml
server:
    port: 8086
    context-path: /rest

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. We have mappings for server port and context path (application name). We access our application at localhost:8086/rest/ URL. 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.

City.java
package com.zetcode.bean;

import java.io.Serializable;
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 implements Serializable {
    
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    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 void setId(Long id) {
        this.id = 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 String toString() {
        return "City{" + "id=" + id + ", name=" + name
                + ", population=" + population + '}';
    }
}

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 implements Serializable {

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.AUTO)
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.

import.sql
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.

CityRepository.java
package com.zetcode.repository;

import com.zetcode.bean.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 findOne(). This way we save a lot of boilerplate code.

ICityService.java
package com.zetcode.service;

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

public interface ICityService {

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

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

CityService.java
package com.zetcode.service;

import com.zetcode.bean.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() {

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

    @Override
    public City findById(Long id) {

        City city = repository.findOne(id);
        return 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.

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

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

City city = repository.findOne(id);

The findOne() method of the repository returns one specific city object.

MyCsvController.java
package com.zetcode.controller;

import com.zetcode.bean.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 MyCsvController {

    @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.

WriteCsvToResponse.java
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.bean.City;
import java.io.PrintWriter;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class WriteCsvToResponse {

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

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

        try {

            ColumnPositionMappingStrategy mapStrategy
                    = new ColumnPositionMappingStrategy();

            mapStrategy.setType(City.class);
            mapStrategy.generateHeader();

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

            StatefulBeanToCsv btcsv = new StatefulBeanToCsvBuilder(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 mapStrategy
                    = new ColumnPositionMappingStrategy();

            mapStrategy.setType(City.class);

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

            StatefulBeanToCsv btcsv = new StatefulBeanToCsvBuilder(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 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 btcsv = new StatefulBeanToCsvBuilder(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.

Application.java
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:8086/rest/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:8086/rest/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. You might also be interested in the related tutorials: