ZetCode

Spring Boot RowMapper

last modified July 6, 2020

Spring Boot RowMapper tutorial shows how to map rows of ResultSet to data carriers. We use Java record as a data carrier. For the tutorial, we need JDK 14 and to enable preview features.

Spring Boot is a popular application framework for creating enterprise application in Java, Kotlin, or Groovy.

Java record

Java record is a restricted form of a class. Java records eliminate a lot of boilerplate code, including constructor, getters, toString, hashCode and equals methods. They are immutable. Their purpose is to be simple data carriers.

Spring Boot RowMapper example

The following application uses a RowMapper to map a result set row to a City record.

pom.xml
src
├───main
│   ├───java
│   │   └───com
│   │       └───zetcode
│   │           │   Application.java
│   │           │   MyRunner.java
│   │           ├───mapper
│   │           │       CityMapper.java
│   │           ├───model
│   │           │       City.java
│   │           └───service
│   │                   CityService.java
│   │                   ICityService.java
│   └───resources
│           application.properties
│           data-h2.sql
│           schema-h2.sql
└───test
    └───java

This is the project structure of the Spring Boot application.

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>SpringBootRowMapper</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>jar</packaging>

    <properties>
        <java.version>14</java.version>
    </properties>

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

    <dependencies>

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

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

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <release>14</release>
                    <compilerArgs>
                        --enable-preview
                    </compilerArgs>
                </configuration>
            </plugin>

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

</project>

This is the Maven build file. The RowMapper resides in spring-boot-starter-jdbc. In the maven-compiler-plugin configuration, we enable preview features.

resources/application.properties
spring.main.banner-mode=off
spring.datasource.platform=h2
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL

In the application.properties, we turn off the Spring Boot banner and set up the H2 datasource.

resources/schema-h2.sql
CREATE TABLE cities(id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), population BIGINT);

This SQL script creates the cities table.

resources/data-h2.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);

This SQL script fills the table with data.

com/zetcode/model/City.java
package com.zetcode.model;

public record City(Long id, String name, Integer population) {

}

We have a City record. Most of the boilderplate for a typical Java model class is removed.

com/zetcode/mapper/CityMapper.java
package com.zetcode.mapper;

import com.zetcode.model.City;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class CityMapper implements RowMapper<City> {

    @Override
    public City mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new City(rs.getLong("id"), rs.getString("name"), rs.getInt("population"));
    }
}

The CityMapper maps a row of a result set to the City record.

Note: Since Java records are immutable and do not follow the Java Beans specification, we cannot use the BeanPropertyRowMapper; we have to create our own mapper.

com/zetcode/service/ICityService.java
package com.zetcode.service;

import com.zetcode.model.City;

import java.util.List;

public interface ICityService {

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

We have two contract methods.

com/zetcode/service/CityService.java
package com.zetcode.service;

import com.zetcode.mapper.CityMapper;
import com.zetcode.model.City;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CityService implements ICityService {

    @Autowired
    private JdbcTemplate jtm;

    @Override
    public List<City> findAll() {

        String sql = "SELECT * FROM cities";

        return jtm.query(sql, new CityMapper());
    }

    @Override
    public City findById(Long id) {

        String sql = "SELECT * FROM cities WHERE id = ?";

        return jtm.queryForObject(sql, new Object[]{id},
                new CityMapper());
    }
}

We have the implementations of the two contract methods, using the CityMapper.

com/zetcode/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.

com/zetcode/MyRunner.java
package com.zetcode;

import com.zetcode.service.ICityService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class MyRunner implements CommandLineRunner {

    @Autowired
    private ICityService cityService;

    @Override
    public void run(String... args) throws Exception {

        var city = cityService.findById(1L);
        System.out.println(city);

        var data = cityService.findAll();
        System.out.println(data);
    }
}

In the MyRunner, we find one city by its Id and then find all cities.

In this tutorial, we have worked with Spring Boot RowMapper.

List Spring Boot tutorials.