Spring Boot CSV tutorial
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.