3.1.2 Working with JdbcTemplate

Before you can start using JdbcTemplate, you need to add it to your project classpath. You can do this easily by adding Spring Boot’s JDBC starter dependency to the build as follows:

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

You’re also going to need a database where your data will be stored. For development purposes, an embedded database will be just fine. I favor the H2 embedded database, so I’ve added the following dependency to the build:

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

By default, the database name is randomly generated. But that makes it hard to determine the database URL if, for some reason, you need to connect to the database using the H2 console (which Spring Boot DevTools enables at http://localhost:8080/h2-console. So, it’s a good idea to pin down the database name by setting a couple of properties in application.properties, as shown next:

spring.datasource.generate-unique-name=false
spring.datasource.name=tacocloud

Or, if you prefer, rename application.properties to application.yml and add the properties in YAML format like so:

spring:
  datasource:
    generate-unique-name: false
    name: tacocloud

The choice between properties file format and YAML format is up to you. Spring Boot is happy to work with either. Given the structure and increased readability of YAML, we’ll use YAML for configuration properties throughout the rest of the book.

By setting the spring.datasource.generate-unique-name property to false, we’re telling Spring to not generate a unique random value for the database name. Instead, it should use the value set to the spring.datasource.name property. In this case, the database name will be "tacocloud". Consequently, the database URL will be "jdbc:h2:mem:tacocloud", which you can specify in the JDBC URL for the H2 console connection.

Later, you’ll see how to configure the application to use an external database. But for now, let’s move on to writing a repository that fetches and saves Ingredient data.

DEFINING JDBC REPOSITORIES

Your Ingredient repository needs to perform the following operations:

• Query for all ingredients into a collection of Ingredient objects
• Query for a single Ingredient by its id
• Save an Ingredient object

The following IngredientRepository interface defines those three operations as method declarations:

package tacos.data;

import java.util.Optional;

import tacos.Ingredient;

public interface IngredientRepository {

  Iterable<Ingredient> findAll();

  Optional<Ingredient> findById(String id);

  Ingredient save(Ingredient ingredient);

}

Although the interface captures the essence of what you need an ingredient repository to do, you’ll still need to write an implementation of IngredientRepository that uses JdbcTemplate to query the database. The code shown next is the first step in writing that implementation.

Listing 3.4 Beginning an ingredient repository with JdbcTemplate

package tacos.data;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import tacos.Ingredient;

@Repository
public class JdbcIngredientRepository implements IngredientRepository {

  private JdbcTemplate jdbcTemplate;

  public JdbcIngredientRepository(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

  // ...

}

As you can see, JdbcIngredientRepository is annotated with @Repository. This annotation is one of a handful of stereotype annotations that Spring defines, including @Controller and @Component. By annotating JdbcIngredientRepository with @Repository, you declare that it should be automatically discovered by Spring component scanning and instantiated as a bean in the Spring application context.

When Spring creates the JdbcIngredientRepository bean, it injects it with JdbcTemplate. That’s because when there’s only one constructor, Spring implicitly applies autowiring of dependencies through that constructor’s parameters. If there is more than one constructor, or if you just want autowiring to be explicitly stated, then you can annotate the constructor with @Autowired as follows:

@Autowired
public JdbcIngredientRepository(JdbcTemplate jdbcTemplate) {
  this.jdbcTemplate = jdbcTemplate;
}

The constructor assigns JdbcTemplate to an instance variable that will be used in other methods to query and insert into the database. Speaking of those other methods, let’s take a look at the implementations of findAll() and findById(), shown in the code sample.

Listing 3.5 Querying the database with JdbcTemplate

@Override
public Iterable<Ingredient> findAll() {
  return jdbcTemplate.query(
    "select id, name, type from Ingredient",
    this::mapRowToIngredient);
}
​
@Override
public Optional<Ingredient> findById(String id) {
  List<Ingredient> results = jdbcTemplate.query(
    "select id, name, type from Ingredient where id=?",
    this::mapRowToIngredient,
    id);
  return results.size() == 0 ?
    Optional.empty() :
    Optional.of(results.get(0));
}
​
private Ingredient mapRowToIngredient(ResultSet row, int rowNum)
    throws SQLException {
  return new Ingredient(
    row.getString("id"),
    row.getString("name"),
    Ingredient.Type.valueOf(row.getString("type")));
}

Both findAll() and findById() use JdbcTemplate in a similar way. The findAll() method, expecting to return a collection of objects, uses JdbcTemplate’s query() method. The query() method accepts the SQL for the query as well as an implementation of Spring’s RowMapper for the purpose of mapping each row in the result set to an object. query() also accepts as its final argument(s) a list of any parameters required in the query. But, in this case, there aren’t any required parameters.

In contrast, the findById() method will need to include a where clause in its query to compare the value of the id column with the value of the id parameter passed into the method. Therefore, the call to query() includes, as its final parameter, the id parameter. When the query is performed, the ? will be replaced with this value.

As shown in listing 3.5, the RowMapper parameter for both findAll() and findById() is given as a method reference to the mapRowToIngredient() method. Java’s method references and lambdas are convenient when working with JdbcTemplate as an alternative to an explicit RowMapper implementation. If for some reason you want or need an explicit RowMapper, then the following implementation of findById() shows how to do that:

@Override
public Ingredient findById(String id) {
  return jdbcTemplate.queryForObject(
    "select id, name, type from Ingredient where id=?",
    new RowMapper<Ingredient>() {
      public Ingredient mapRow(ResultSet rs, int rowNum)
          throws SQLException {
        return new Ingredient(
          rs.getString("id"),
          rs.getString("name"),
          Ingredient.Type.valueOf(rs.getString("type")));
      };
    }, id);
}

Reading data from a database is only part of the story. At some point, data must be written to the database so that it can be read. Let’s see about implementing the save() method.

INSERTING A ROW

JdbcTemplate’s update() method can be used for any query that writes or updates data in the database. And, as shown in the following listing, it can be used to insert data into the database.

Listing 3.6 Inserting data with JdbcTemplate

@Override
public Ingredient save(Ingredient ingredient) {
  jdbcTemplate.update(
    "insert into Ingredient (id, name, type) values (?, ?, ?)",
    ingredient.getId(),
    ingredient.getName(),
    ingredient.getType().toString());
  return ingredient;
}

Because it isn’t necessary to map ResultSet data to an object, the update() method is much simpler than query(). It requires only a String containing the SQL to perform as well as values to assign to any query parameters. In this case, the query has three parameters, which correspond to the final three parameters of the save() method, providing the ingredient’s ID, name, and type.

With JdbcIngredientRepository complete, you can now inject it into DesignTacoController and use it to provide a list of Ingredient objects instead of using hardcoded values (as you did in chapter 2). The changes to DesignTacoController are shown next.

Listing 3.7 Injecting and using a repository in the controller

@Controller
@RequestMapping("/design")
@SessionAttributes("tacoOrder")
public class DesignTacoController {

  private final IngredientRepository ingredientRepo;

  @Autowired
  public DesignTacoController(
      IngredientRepository ingredientRepo) {
    this.ingredientRepo = ingredientRepo;
  }

  @ModelAttribute
  public void addIngredientsToModel(Model model) {
    Iterable<Ingredient> ingredients = ingredientRepo.findAll();
    Type[] types = Ingredient.Type.values();
    for (Type type : types) {
      model.addAttribute(type.toString().toLowerCase(),
        filterByType(ingredients, type));
    }
  }

  // ...
}

The addIngredientsToModel() method uses the injected IngredientRepository’s findAll() method to fetch all ingredients from the database. It then filters them into distinct ingredient types before adding them to the model.

Now that we have an IngredientRepository from which to fetch Ingredient objects, we can also simplify the IngredientByIdConverter that we created in chapter 2, replacing its hardcoded Map of Ingredient objects with a simple call to the IngredientRepository.findById() method, as shown next.

Listing 3.8 Simplifying IngredientByIdConverter

package tacos.web;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.convert.converter.Converter;
import org.springframework.stereotype.Component;

import tacos.Ingredient;
import tacos.data.IngredientRepository;

@Component
public class IngredientByIdConverter implements Converter<String, Ingredient> {

  private IngredientRepository ingredientRepo;

  @Autowired
  public IngredientByIdConverter(IngredientRepository ingredientRepo) {
    this.ingredientRepo = ingredientRepo;
  }

  @Override
  public Ingredient convert(String id) {
    return ingredientRepo.findById(id).orElse(null);
  }

}

You’re almost ready to fire up the application and try out these changes. But before you can start reading data from the Ingredient table referenced in the queries, you should probably create that table and populate it with some ingredient data.