Introduction to Spring Boot and JDBCTemplate: JDBC Template

As with any programming language, Java has several tools to make easy integration between the language and the database. There are several tools such as Hibernate, Eclipse Link, JPA specification, and so on. However, the ORM brings several issues, and sometimes it does not sense to use it and then use it a Java Communication layer or JDBC. This tutorial will cover a way to simplify the code with JDBC using the Spring JDBC Template.

Mapping frameworks such as ORM reduces a lot of boilerplate, reduces the duplicated code, avoids bugs, and doesn’t reinvent the wheel. However, an Object-relational impedance mismatch that brings a set of conceptual and technical difficulties is often encountered when an RDBMS is being served by an application program written in an object-oriented programming language.

A solution might use JDBC, but it increases the complexity to handle data and Java. How can an application reduce this verbosity with JDBC? Spring JDBCTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API but eliminates a lot of problems with JDBC API.

Related content by Ram N:

Starting With Spring Initializr

This sample application will use JDBCTemplate with Spring to use two databases: PostgreSQL to run the application and H2 in the test scope. For all Spring applications, you should start with the Spring Initializr. The Initializr offers a fast way to pull in all the dependencies you need for an application and does a lot of the setup for you. This example requires the JDBC API, Spring MVC, PostgreSQL driver, and H2 Database dependencies.

<dependency> 

    <groupId>org.springframework.boot</groupId> 

    <artifactId>spring-boot-starter-web</artifactId> 

</dependency> 

<dependency> 

    <groupId>org.springframework.boot</groupId> 

    <artifactId>spring-boot-starter-jdbc</artifactId> 

</dependency> 

<dependency> 

    <groupId>org.postgresql</groupId> 

    <artifactId>postgresql</artifactId> 

    <scope>runtime</scope> 

</dependency> 

<dependency> 

    <groupId>com.h2database</groupId> 

    <artifactId>h2</artifactId> 

    <scope>test</scope> 

</dependency> 

<dependency> 

    <groupId>org.springframework.boot</groupId> 

    <artifactId>spring-boot-starter-test</artifactId> 

    <scope>test</scope> 

</dependency> 

The next step is the Car entity, with five fields: id, name, city, model, and color. In this project, a POJO as an anemic model is more than enough to handle it. A rich model guarantees the object’s rules and avoids any encapsulation issue, thus, a bulletproof API. However, it only fits in complex projects. Highlighting: 'it depends' is always a good answer when we talk about software architecture.

xxxxxxxxxx

public class Car {

    private Long id;

    private String name;

    private String city;

    private String model;

    private String color;

    //...

    public static CarBuilder builder() {

        return new CarBuilder();

    }

}

To have tight integration between Java and ResultSet of JDBC, Spring JDBC has a RowMapper interface. A developer can either create a custom class or use BeanPropertyRowMapper, which reduces the boilerplate; however, the entity should have a public getter and setter; it might get an encapsulation issue besides it provides convenience rather than high performance. For best performance, consider using a custom RowMapper implementation.

xxxxxxxxxx

public class CarRowMapper implements RowMapper<Car> {

    @Override

    public Car mapRow(ResultSet resultSet, int rowNum) throws SQLException {

        Long id = resultSet.getLong("ID");

        String name = resultSet.getString("name");

        String city = resultSet.getString("city");

        String model = resultSet.getString("model");

        String color = resultSet.getString("color");

        return Car.builder().id(id).name(name)

                .city(city)

                .model(model)

                .color(color).build();

    }

}

The entity is ready; let’s talk about the Data Access Object, DAO; every time there is a vast or complex query to handle, there is a discussion about where the SQL query should go. Briefly, when the script is hardcoded, it is clearer what the script is doing, but when the command is tremendous, it becomes challenging to read and understand. Thus, we can move it to read it from properties.

x

car.query.find.by.id=SELECT * FROM CAR WHERE ID = :id

car.query.delete.by.id=DELETE FROM CAR WHERE ID =:id

car.query.update=update CAR set name = :name, city = :city, model= :model, color =:color  where id = :id

car.query.find.all=select * from CAR ORDER BY id LIMIT :limit OFFSET :offset

Once the reader is familiar with the query in the code, we’ll explore the query option in a properties file. We’ll have a class to hold this duty, and it will have tight integration with Spring Configuration.

xxxxxxxxxx

@Component

public class CarQueries {

    @Value("${car.query.find.by.id}")

    private String findById;

    @Value("${car.query.delete.by.id}")

    private String deleteById;

    @Value("${car.query.update}")

    private String update;

    @Value("${car.query.find.all}")

    private String findAll;

    //...

}

The CarDAO will do the CRUD operation once the findAll has pagination support. It uses NamedParameterJdbcTemplate to class with a basic set of JDBC operations, allowing named parameters rather than traditional ‘?’ Placeholders. To avoid connection leak, Spring has the Transactional annotation to control the transaction in each method where we defined in our code.

xxxxxxxxxx

@Repository

public class CarDAO {

    private final NamedParameterJdbcTemplate template;

    private final CarQueries queries;

    private final RowMapper<Car> rowMapper;

    private final SimpleJdbcInsert insert;

    @Autowired

    public CarDAO(NamedParameterJdbcTemplate template, CarQueries queries) {

        this.template = template;

        //this.rowMapper = new BeanPropertyRowMapper<>(Car.class);

        this.rowMapper = new CarRowMapper();

        this.queries = queries;

        this.insert = new SimpleJdbcInsert(template.getJdbcTemplate());

        this.insert.setTableName("car");

        this.insert.usingGeneratedKeyColumns("id");

    }

    @Transactional

    public Car insert(Car car) {

        //Number id = insert.executeAndReturnKey(new BeanPropertySqlParameterSource(car));

        Number id = insert.executeAndReturnKey(car.toMap());

        return findBy(id.longValue()).orElseThrow(() -> new IllegalStateException(""));

    }

    public Optional<Car> findBy(Long id) {

        String sql = queries.getFindById();

        Map<String, Object> parameters = Collections.singletonMap("id", id);

        return template.queryForStream(sql, parameters, rowMapper).findFirst();

    }

    @Transactional

    public boolean delete(Long id) {

        String sql = queries.getDeleteById();

        Map<String, Object> paramMap = Collections.singletonMap("id", id);

        return template.update(sql, paramMap) == 1;

    }

    @Transactional

    public boolean update(Car car) {

        String sql = queries.getUpdate();

        Map<String, Object> paramMap = car.toMap();

        return template.update(sql, paramMap) == 1;

    }

    public Stream<Car> findAll(Page page) {

        String sql = queries.getFindAll();

        Map<String, Object> paramMap = new HashMap<>();

        paramMap.put("limit", page.getLimit());

        paramMap.put("offset", page.getOffset());

        return template.queryForStream(sql, paramMap, rowMapper);

    }

}

The code is ready; let’s test it. Yeap, a TDD technique, has a philosophy to start with the test and then create the code. But it is not the article’s goal. In the test scope, we’ll generate a DBMS in memory with H2. Spring has several features to allow us to test smoothly. Thanks to Spring, we can use H2 in the test without impact the driver that will operate on production.

xxxxxxxxxx

@ExtendWith(SpringExtension.class)

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.MOCK)

class CarDAOTest {

    @Autowired

    private CarDAO carDAO;

    @Autowired

    private JdbcTemplate template;

    @Test

    public void shouldFindById() {

        Assertions.assertNotNull(carDAO);

        Optional<Car> car = carDAO.findBy(1L);

        Assertions.assertNotNull(car);

    }

    @Test

    public void shouldInsertCar() {

        Car car = Car.builder()

                .city("Salvador")

                .color("Red")

                .name("Fiat")

                .model("Model")

                .build();

        Car insert = carDAO.insert(car);

        Assertions.assertNotNull(insert);

        Assertions.assertNotNull(insert.getId());

    }

    @Test

    public void shouldDelete() {

        Car car = Car.builder()

                .city("Salvador")

                .color("Red")

                .name("Fiat")

                .model("Model")

                .build();

        Car insert = carDAO.insert(car);

        carDAO.delete(insert.getId());

        Optional<Car> empty = carDAO.findBy(insert.getId());

        Assertions.assertTrue(empty.isEmpty());

    }

    @Test

    public void shouldUpdate() {

        Car car = Car.builder()

                .city("Salvador")

                .color("Red")

                .name("Fiat")

                .model("Model")

                .build();

        Car insert = carDAO.insert(car);

        insert.update(Car.builder()

                .city("Salvador")

                .color("Red")

                .name("Fiat")

                .model("Update")

                .build());

        carDAO.update(insert);

    }

    @Test

    public void shouldFindAll() {

        template.execute("DELETE FROM CAR");

        List<Car> cars = new ArrayList<>();

        for (int index = 0; index < 10; index++) {

            Car car = Car.builder()

                    .city("Salvador")

                    .color("Red")

                    .name("Fiat " + index)

                    .model("Model")

                    .build();

            cars.add(carDAO.insert(car));

        }

        Page page = Page.of(1, 2);

        List<Car> result = carDAO.findAll(page).collect(Collectors.toList());

        Assertions.assertEquals(2, result.size());

        MatcherAssert.assertThat(result, Matchers.contains(cars.get(0), cars.get(1)));

        Page nextPage = page.next();

        result = carDAO.findAll(nextPage).collect(Collectors.toList());

        Assertions.assertEquals(2, result.size());

        MatcherAssert.assertThat(result, Matchers.contains(cars.get(2), cars.get(3)));

    }

}

Conclusion

In this tutorial, we introduced Spring JDBC and its operation; we talked a little about the mapping trade-offs and the place to store the queries in addition to the testing and configuration resources. Spring brings several features that increase developer productivity. In the second part, we will talk a little about Spring MVC and its works with the database.

Code: https://github.com/xgeekshq/spring-boot-jdbc-template-sample