Chapter 3. Working with data
- Using Spring’s JdbcTemplate
- Inserting data with SimpleJdbcInsert
- Declaring JPA repositories with Spring Data
Most applications offer more than just a pretty face. Although the user interface may provide interaction with an application, it’s the data it presents and stores that separates applications from static websites.
In the Taco Cloud application, you need to be able to maintain information about ingredients, tacos, and orders. Without a database to store this information, the application wouldn’t be able to progress much further than what you developed in chapter 2.
In this chapter, you’re going to add data persistence to the Taco Cloud application. You’ll start by using Spring support for JDBC (Java Database Connectivity) to eliminate boilerplate code. Then you’ll rework the data repositories to work with the JPA (Java Persistence API), eliminating even more code.
3.1. Reading and writing data with JDBC
For decades, relational databases and SQL have enjoyed their position as the leading choice for data persistence. Even though many alternative database types have emerged in recent years, the relational database is still a top choice for a general-purpose data store and will not likely be usurped from its position any time soon.
When it comes to working with relational data, Java developers have several options. The two most common choices are JDBC and the JPA. Spring supports both of these with abstractions, making working with either JDBC or JPA easier than it would be without Spring. In this section, we’ll focus on how Spring supports JDBC, and then we’ll look at Spring support for JPA in section 3.2.
Spring JDBC support is rooted in the JdbcTemplate class. JdbcTemplate provides a means by which developers can perform SQL operations against a relational database without all the ceremony and boilerplate typically required when working with JDBC.
To gain an appreciation of what JdbcTemplate does, let’s start by looking at an example of how to perform a simple query in Java without JdbcTemplate.
Listing 3.1. Querying a database without JdbcTemplate
@Override
public Ingredient findOne(String id) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(
"select id, name, type from Ingredient where id=?");
statement.setString(1, id);
resultSet = statement.executeQuery();
Ingredient ingredient = null;
if(resultSet.next()) {
ingredient = new Ingredient(
resultSet.getString("id"),
resultSet.getString("name"),
Ingredient.Type.valueOf(resultSet.getString("type")));
}
return ingredient;
} catch (SQLException e) {
// ??? What should be done here ???
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {}
}
}
return null;
}
I assure you that somewhere in listing 3.1 there are a couple of lines that query the database for ingredients. But I’ll bet you had a hard time spotting that query needle in the JDBC haystack. It’s surrounded by code that creates a connection, creates a statement, and cleans up by closing the connection, statement, and result set.
To make matters worse, any number of things could go wrong when creating the connection or the statement, or when performing the query. This requires that you catch a SQLException, which may or may not be helpful in figuring out what went wrong or how to address the problem.
SQLException is a checked exception, which requires handling in a catch block. But the most common problems, such as failure to create a connection to the database or a mistyped query, can’t possibly be addressed in a catch block and are likely to be rethrown for handling upstream. In contrast, consider the methods that use JdbcTemplate.
Listing 3.2. Querying a database with JdbcTemplate
private JdbcTemplate jdbc;
@Override
public Ingredient findOne(String id) {
return jdbc.queryForObject(
"select id, name, type from Ingredient where id=?",
this::mapRowToIngredient, id);
}
private Ingredient mapRowToIngredient(ResultSet rs, int rowNum)
throws SQLException {
return new Ingredient(
rs.getString("id"),
rs.getString("name"),
Ingredient.Type.valueOf(rs.getString("type")));
}
The code in listing 3.2 is clearly much simpler than the raw JDBC example in listing 3.1; there aren’t any statements or connections being created. And, after the method is finished, there isn’t any cleanup of those objects. Finally, there isn’t any handling of exceptions that can’t properly be handled in a catch block. What’s left is code that’s focused solely on performing a query (the call to JdbcTemplate’s queryForObject() method) and mapping the results to an Ingredient object (in the mapRowToIngredient() method).
The code in listing 3.2 is a snippet of what you need to do to use JdbcTemplate to persist and read data in the Taco Cloud application. Let’s take the next steps necessary to outfit the application with JDBC persistence. We’ll start by making a few tweaks to the domain objects.
3.1.1. Adapting the domain for persistence
When persisting objects to a database, it’s generally a good idea to have one field that uniquely identifies the object. Your Ingredient class already has an id field, but you need to add id fields to both Taco and Order.
Moreover, it might be useful to know when a Taco is created and when an Order is placed. You’ll also need to add a field to each object to capture the date and time that the objects are saved. The following listing shows the new id and createdAt fields needed in the Taco class.
Listing 3.3. Adding ID and timestamp fields to the Taco class
@Data
public class Taco {
private Long id;
private Date createdAt;
...
}
Because you use Lombok to automatically generate accessor methods at runtime, there’s no need to do anything more than declare the id and createdAt properties. They’ll have appropriate getter and setter methods as needed at runtime. Similar changes are required in the Order class, as shown here:
@Data
public class Order {
private Long id;
private Date placedAt;
...
}
Again, Lombok automatically generates the accessor methods, so these are the only changes required in Order. (If for some reason you choose not to use Lombok, you’ll need to write these methods yourself.)
Your domain classes are now ready for persistence. Let’s see how to use JdbcTemplate to read and write them to a database.
3.1.2. Working with JdbcTemplate
Before you can start using JdbcTemplate, you need to add it to your project classpath. This can easily be accomplished by adding Spring Boot’s JDBC starter dependency to the build:
<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>
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 these 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 tacos.Ingredient;
public interface IngredientRepository {
Iterable<Ingredient> findAll();
Ingredient findOne(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 org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import tacos.Ingredient;
@Repository
public class JdbcIngredientRepository
implements IngredientRepository {
private JdbcTemplate jdbc;
@Autowired
public JdbcIngredientRepository(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
...
}
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 via the @Autowired annotated construction. 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().
Listing 3.5. Querying the database with JdbcTemplate
@Override
public Iterable<Ingredient> findAll() {
return jdbc.query("select id, name, type from Ingredient",
this::mapRowToIngredient);
}
@Override
public Ingredient findOne(String id) {
return jdbc.queryForObject(
"select id, name, type from Ingredient where id=?",
this::mapRowToIngredient, id);
}
private Ingredient mapRowToIngredient(ResultSet rs, int rowNum)
throws SQLException {
return new Ingredient(
rs.getString("id"),
rs.getString("name"),
Ingredient.Type.valueOf(rs.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. findAll() 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.
The findById() method only expects to return a single Ingredient object, so it uses the queryForObject() method of JdbcTemplate instead of query(). queryForObject() works much like query() except that it returns a single object instead of a List of objects. In this case, it’s given the query to perform, a RowMapper, and the id of Ingredient to fetch, which is used in place of the ? in the query.
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 8’s method references and lambdas are convenient when working with JdbcTemplate as an alternative to an explicit RowMapper implementation. But if for some reason you want or need an explicit RowMapper, then the following implementation of findAll() shows how to do that:
@Override
public Ingredient findOne(String id) {
return jdbc.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. So 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) {
jdbc.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() or queryForObject(). It only requires 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("order")
public class DesignTacoController {
private final IngredientRepository ingredientRepo;
@Autowired
public DesignTacoController(IngredientRepository ingredientRepo) {
this.ingredientRepo = ingredientRepo;
}
@GetMapping
public String showDesignForm(Model model) {
List<Ingredient> ingredients = new ArrayList<>();
ingredientRepo.findAll().forEach(i -> ingredients.add(i));
Type[] types = Ingredient.Type.values();
for (Type type : types) {
model.addAttribute(type.toString().toLowerCase(),
filterByType(ingredients, type));
}
return "design";
}
...
}
Notice that the second line of the showDesignForm() method now makes a call to the injected IngredientRepository’s findAll() method. The findAll() method fetches all the ingredients from the database before filtering them into distinct types in the model.
You’re almost ready to fire up the application and try these changes out. 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.
3.1.3. Defining a schema and preloading data
Aside from the Ingredient table, you’re also going to need some tables that hold order and design information. Figure 3.1 illustrates the tables you’ll need, as well as the relationships between those tables.
Figure 3.1. The tables for the Taco Cloud schema
The tables in figure 3.1 serve the following purposes:
- Ingredient— Holds ingredient information
- Taco— Holds essential information about a taco design
- Taco_Ingredients— Contains one or more rows for each row in Taco, mapping the taco to the ingredients for that taco
- Taco_Order— Holds essential order details
- Taco_Order_Tacos— Contains one or more rows for each row in Taco_Order, mapping the order to the tacos in the order
The next listing shows the SQL that creates the tables.
Listing 3.8. Defining the Taco Cloud schema
create table if not exists Ingredient (
id varchar(4) not null,
name varchar(25) not null,
type varchar(10) not null
);
create table if not exists Taco (
id identity,
name varchar(50) not null,
createdAt timestamp not null
);
create table if not exists Taco_Ingredients (
taco bigint not null,
ingredient varchar(4) not null
);
alter table Taco_Ingredients
add foreign key (taco) references Taco(id);
alter table Taco_Ingredients
add foreign key (ingredient) references Ingredient(id);
create table if not exists Taco_Order (
id identity,
deliveryName varchar(50) not null,
deliveryStreet varchar(50) not null,
deliveryCity varchar(50) not null,
deliveryState varchar(2) not null,
deliveryZip varchar(10) not null,
ccNumber varchar(16) not null,
ccExpiration varchar(5) not null,
ccCVV varchar(3) not null,
placedAt timestamp not null
);
create table if not exists Taco_Order_Tacos (
tacoOrder bigint not null,
taco bigint not null
);
alter table Taco_Order_Tacos
add foreign key (tacoOrder) references Taco_Order(id);
alter table Taco_Order_Tacos
add foreign key (taco) references Taco(id);
The big question is where to put this schema definition. As it turns out, Spring Boot answers that question.
If there’s a file named schema.sql in the root of the application’s classpath, then the SQL in that file will be executed against the database when the application starts. Therefore, you should place the contents of listing 3.8 in your project as a file named schema.sql in the src/main/resources folder.
You also need to preload the database with some ingredient data. Fortunately, Spring Boot will also execute a file named data.sql from the root of the classpath when the application starts. Therefore, you can load the database with ingredient data using the insert statements in the next listing, placed in src/main/resources/data.sql.
Listing 3.9. Preloading the database
delete from Taco_Order_Tacos;
delete from Taco_Ingredients;
delete from Taco;
delete from Taco_Order;
delete from Ingredient;
insert into Ingredient (id, name, type)
values ('FLTO', 'Flour Tortilla', 'WRAP');
insert into Ingredient (id, name, type)
values ('COTO', 'Corn Tortilla', 'WRAP');
insert into Ingredient (id, name, type)
values ('GRBF', 'Ground Beef', 'PROTEIN');
insert into Ingredient (id, name, type)
values ('CARN', 'Carnitas', 'PROTEIN');
insert into Ingredient (id, name, type)
values ('TMTO', 'Diced Tomatoes', 'VEGGIES');
insert into Ingredient (id, name, type)
values ('LETC', 'Lettuce', 'VEGGIES');
insert into Ingredient (id, name, type)
values ('CHED', 'Cheddar', 'CHEESE');
insert into Ingredient (id, name, type)
values ('JACK', 'Monterrey Jack', 'CHEESE');
insert into Ingredient (id, name, type)
values ('SLSA', 'Salsa', 'SAUCE');
insert into Ingredient (id, name, type)
values ('SRCR', 'Sour Cream', 'SAUCE');
Even though you’ve only developed a repository for ingredient data, you can fire up the Taco Cloud application at this point and visit the design page to see JdbcIngredientRepository in action. Go ahead ... give it a try. When you get back, you’ll write the repositories for persisting Taco, Order, and data.
3.1.4. Inserting data
You’ve already had a glimpse into how to use JdbcTemplate to write data to the database. The save() method in JdbcIngredientRepository used the update() method of JdbcTemplate to save Ingredient objects to the database.
Although that was a good first example, it was perhaps a bit too simple. As you’ll soon see, saving data can be more involved than what JdbcIngredientRepository needed. Two ways to save data with JdbcTemplate include the following:
- Directly, using the update() method
- Using the SimpleJdbcInsert wrapper class
Let’s first see how to use the update() method when the persistence needs are more complex than what was required to save an Ingredient.
Saving data with JdbcTemplate
For now, the only thing that the taco and order repositories need to do is to save their respective objects. To save Taco objects, the TacoRepository declares a save() method like this:
package tacos.data;
import tacos.Taco;
public interface TacoRepository {
Taco save(Taco design);
}
Similarly, OrderRepository also declares a save() method:
package tacos.data;
import tacos.Order;
public interface OrderRepository {
Order save(Order order);
}
Seems simple enough, right? Not so quick. Saving a taco design requires that you also save the ingredients associated with that taco to the Taco_Ingredients table. Likewise, saving an order requires that you also save the tacos associated with the order to the Taco_Order_Tacos table. This makes saving tacos and orders a bit more challenging than what was required to save an ingredient.
To implement TacoRepository, you need a save() method that starts by saving the essential taco design details (for example, the name and time of creation), and then inserts one row into Taco_Ingredients for each ingredient in the Taco object. The following listing shows the complete JdbcTacoRepository class.
Listing 3.10. Implementing TacoRepository with JdbcTemplate
package tacos.data;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Arrays;
import java.util.Date;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import tacos.Ingredient;
import tacos.Taco;
@Repository
public class JdbcTacoRepository implements TacoRepository {
private JdbcTemplate jdbc;
public JdbcTacoRepository(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
@Override
public Taco save(Taco taco) {
long tacoId = saveTacoInfo(taco);
taco.setId(tacoId);
for (Ingredient ingredient : taco.getIngredients()) {
saveIngredientToTaco(ingredient, tacoId);
}
return taco;
}
private long saveTacoInfo(Taco taco) {
taco.setCreatedAt(new Date());
PreparedStatementCreator psc =
new PreparedStatementCreatorFactory(
"insert into Taco (name, createdAt) values (?, ?)",
Types.VARCHAR, Types.TIMESTAMP
).newPreparedStatementCreator(
Arrays.asList(
taco.getName(),
new Timestamp(taco.getCreatedAt().getTime())));
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbc.update(psc, keyHolder);
return keyHolder.getKey().longValue();
}
private void saveIngredientToTaco(
Ingredient ingredient, long tacoId) {
jdbc.update(
"insert into Taco_Ingredients (taco, ingredient) " +
"values (?, ?)",
tacoId, ingredient.getId());
}
}
As you can see, the save() method starts by calling the private saveTacoInfo() method, and then uses the taco ID returned from that method to call saveIngredientToTaco(), which saves each ingredient. The devil is in the details of saveTacoInfo().
When you insert a row into Taco, you need to know the ID generated by the database so that you can reference it in each of the ingredients. The update() method, used when saving ingredient data, doesn’t help you get at the generated ID, so you need a different update() method here.
The update() method you need accepts a PreparedStatementCreator and a KeyHolder. It’s the KeyHolder that will provide the generated taco ID. But in order to use it, you must also create a PreparedStatementCreator.
As you can see from listing 3.10, creating a PreparedStatementCreator is non-trivial. Start by creating a PreparedStatementCreatorFactory, giving it the SQL you want to execute, as well as the types of each query parameter. Then call newPreparedStatementCreator() on that factory, passing in the values needed in the query parameters to produce the PreparedStatementCreator.
With a PreparedStatementCreator in hand, you can call update(), passing in PreparedStatementCreator and KeyHolder (in this case, a GeneratedKeyHolder instance). Once the update() is finished, you can return the taco ID by returning keyHolder.getKey().longValue().
Back in save(), cycle through each Ingredient in Taco, calling saveIngredientToTaco(). The saveIngredientToTaco() method uses the simpler form of update() to save ingredient references to the Taco_Ingredients table.
All that’s left to do with TacoRepository is to inject it into DesignTacoController and use it when saving tacos. The following listing shows the changes necessary for injecting the repository.
Listing 3.11. Injecting and using TacoRepository
@Controller
@RequestMapping("/design")
@SessionAttributes("order")
public class DesignTacoController {
private final IngredientRepository ingredientRepo;
private TacoRepository designRepo;
@Autowired
public DesignTacoController(
IngredientRepository ingredientRepo,
TacoRepository designRepo) {
this.ingredientRepo = ingredientRepo;
this.designRepo = designRepo;
}
...
}
As you can see, the constructor takes both an IngredientRepository and a TacoRepository. It assigns both to instance variables so that they can be used in the showDesignForm() and processDesign() methods.
Speaking of the processDesign() method, its changes are a bit more extensive than the changes you made to showDesignForm(). The next listing shows the new processDesign() method.
Listing 3.12. Saving taco designs and linking them to orders
@Controller
@RequestMapping("/design")
@SessionAttributes("order")
public class DesignTacoController {
@ModelAttribute(name = "order")
public Order order() {
return new Order();
}
@ModelAttribute(name = "taco")
public Taco taco() {
return new Taco();
}
@PostMapping
public String processDesign(
@Valid Taco design, Errors errors,
@ModelAttribute Order order) {
if (errors.hasErrors()) {
return "design";
}
Taco saved = designRepo.save(design);
order.addDesign(saved);
return "redirect:/orders/current";
}
...
}
The first thing you’ll notice about the code in listing 3.12 is that DesignTacoController is now annotated with @SessionAttributes("order") and that it has a new @ModelAttribute annotated method, order(). As with the taco() method, the @ModelAttribute annotation on order() ensures that an Order object will be created in the model. But unlike the Taco object in the session, you need the order to be present across multiple requests so that you can create multiple tacos and add them to the order. The class-level @SessionAttributes annotation specifies any model objects like the order attribute that should be kept in session and available across multiple requests.
The real processing of a taco design happens in the processDesign() method, which now accepts an Order object as a parameter, in addition to Taco and Errors objects. The Order parameter is annotated with @ModelAttribute to indicate that its value should come from the model and that Spring MVC shouldn’t attempt to bind request parameters to it.
After checking for validation errors, processDesign() uses the injected TacoRepository to save the taco. It then adds the Taco object to the Order that’s kept in the session.
In fact, the Order object remains in the session and isn’t saved to the database until the user completes and submits the order form. At that point, OrderController needs to call out to an implementation of OrderRepository to save the order. Let’s write that implementation.
Inserting data with SimpleJdbcInsert
You’ll recall that saving a taco involved not only saving the taco’s name and creation time to the Taco table, but also saving a reference to the ingredients associated with the taco to the Taco_Ingredients table. And you’ll also recall that this required you to know the Taco’s ID, which you obtained using KeyHolder and PreparedStatementCreator.
When it comes to saving orders, a similar circumstance exists. You must not only save the order data to the Taco_Order table, but also references to each taco in the order to the Taco_Order_Tacos table. But rather than use the cumbersome PreparedStatementCreator, allow me to introduce you to SimpleJdbcInsert, an object that wraps JdbcTemplate to make it easier to insert data into a table.
You’ll start by creating JdbcOrderRepository, an implementation of OrderRepository. But before you write the save() method implementation, let’s focus on the constructor, where you’ll create a couple of instances of SimpleJdbcInsert for inserting values into the Taco_Order and Taco_Order_Tacos tables. The following listing shows JdbcOrderRepository (without the save() method).
Listing 3.13. Creating a SimpleJdbcInsert from a JdbcTemplate
package tacos.data;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import com.fasterxml.jackson.databind.ObjectMapper;
import tacos.Taco;
import tacos.Order;
@Repository
public class JdbcOrderRepository implements OrderRepository {
private SimpleJdbcInsert orderInserter;
private SimpleJdbcInsert orderTacoInserter;
private ObjectMapper objectMapper;
@Autowired
public JdbcOrderRepository(JdbcTemplate jdbc) {
this.orderInserter = new SimpleJdbcInsert(jdbc)
.withTableName("Taco_Order")
.usingGeneratedKeyColumns("id");
this.orderTacoInserter = new SimpleJdbcInsert(jdbc)
.withTableName("Taco_Order_Tacos");
this.objectMapper = new ObjectMapper();
}
...
}
Like JdbcTacoRepository, JdbcOrderRepository is injected with JdbcTemplate through its constructor. But instead of assigning JdbcTemplate directly to an instance variable, the constructor uses it to construct a couple of SimpleJdbcInsert instances.
The first instance, which is assigned to the orderInserter instance variable, is configured to work with the Taco_Order table and to assume that the id property will be provided or generated by the database. The second instance, assigned to orderTacoInserter, is configured to work with the Taco_Order_Tacos table but makes no claims about how any IDs will be generated in that table.
The constructor also creates an instance of Jackson’s ObjectMapper and assigns it to an instance variable. Although Jackson is intended for JSON processing, you’ll see in a moment how you’ll repurpose it to help you as you save orders and their associated tacos.
Now let’s take a look at how the save() method uses the SimpleJdbcInsert instances. The next listing shows the save() method, as well as a couple of private methods that save() delegates for the real work.
Listing 3.14. Using SimpleJdbcInsert to insert data
@Override
public Order save(Order order) {
order.setPlacedAt(new Date());
long orderId = saveOrderDetails(order);
order.setId(orderId);
List<Taco> tacos = order.getTacos();
for (Taco taco : tacos) {
saveTacoToOrder(taco, orderId);
}
return order;
}
private long saveOrderDetails(Order order) {
@SuppressWarnings("unchecked")
Map<String, Object> values =
objectMapper.convertValue(order, Map.class);
values.put("placedAt", order.getPlacedAt());
long orderId =
orderInserter
.executeAndReturnKey(values)
.longValue();
return orderId;
}
private void saveTacoToOrder(Taco taco, long orderId) {
Map<String, Object> values = new HashMap<>();
values.put("tacoOrder", orderId);
values.put("taco", taco.getId());
orderTacoInserter.execute(values);
}
The save() method doesn’t actually save anything. It defines the flow for saving an Order and its associated Taco objects, and delegates the persistence work to saveOrderDetails() and saveTacoToOrder().
SimpleJdbcInsert has a couple of useful methods for executing the insert: execute() and executeAndReturnKey(). Both accept a Map<String, Object>, where the map keys correspond to the column names in the table the data is inserted into. The map values are inserted into those columns.
It’s easy to create such a Map by copying the values from Order into entries of the Map. But Order has several properties, and those properties all share the same name with the columns that they’re going into. Because of that, in saveOrderDetails(), I’ve decided to use Jackson’s ObjectMapper and its convertValue() method to convert an Order into a Map.[1] Once the Map is created, you’ll set the placedAt entry to the value of the Order object’s placedAt property. This is necessary because ObjectMapper would otherwise convert the Date property into a long, which is incompatible with the placedAt field in the Taco_Order table.
I’ll admit that this is a hackish use of ObjectMapper, but you already have Jackson in the classpath; Spring Boot’s web starter brings it in. Also, using ObjectMapper to map an object into a Map is much easier than copying each property from the object into the Map. Feel free to replace the use of ObjectMapper with any code you prefer that builds the Map you’ll give to the inserter objects.
With a Map full of order data ready, you can now call executeAndReturnKey() on orderInserter. This saves the order information to the Taco_Order table and returns the database-generated ID as a Number object, which a call to longValue() converts to a long returned from the method.
The saveTacoToOrder() method is significantly simpler. Rather than use the ObjectMapper to convert an object to a Map, you create the Map and set the appropriate values. Once again, the map keys correspond to column names in the table. A simple call to the orderTacoInserter’s execute() method performs the insert.
Now you can inject OrderRepository into OrderController and start using it. The following listing shows the complete OrderController, including the changes to use an injected OrderRepository.
Listing 3.15. Using an OrderRepository in OrderController
package tacos.web;
import javax.validation.Valid;
import org.springframework.stereotype.Controller;
import org.springframework.validation.Errors;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.SessionAttributes;
import org.springframework.web.bind.support.SessionStatus;
import tacos.Order;
import tacos.data.OrderRepository;
@Controller
@RequestMapping("/orders")
@SessionAttributes("order")
public class OrderController {
private OrderRepository orderRepo;
public OrderController(OrderRepository orderRepo) {
this.orderRepo = orderRepo;
}
@GetMapping("/current")
public String orderForm() {
return "orderForm";
}
@PostMapping
public String processOrder(@Valid Order order, Errors errors,
SessionStatus sessionStatus) {
if (errors.hasErrors()) {
return "orderForm";
}
orderRepo.save(order);
sessionStatus.setComplete();
return "redirect:/";
}
}
Aside from injecting OrderRepository into the controller, the only significant changes in OrderController are in the processOrder() method. Here, the Order object submitted in the form (which also happens to be the same Order object maintained in session) is saved via the save() method on the injected OrderRepository.
Once the order is saved, you don’t need it hanging around in a session anymore. In fact, if you don’t clean it out, the order remains in session, including its associated tacos, and the next order will start with whatever tacos the old order contained. Therefore, the processOrder() method asks for a SessionStatus parameter and calls its setComplete() method to reset the session.
All of the JDBC persistence code is in place. Now you can fire up the Taco Cloud application and try it out. Feel free to create as many tacos and as many orders as you’d like.
You might also find it helpful to dig around in the database. Because you’re using H2 as your embedded database, and because you have Spring Boot DevTools in place, you should be able to point your browser to http://localhost:8080/h2-console to see the H2 Console. The default credentials should get you in, although you’ll need to be sure that the JDBC URL field is set to jdbc:h2:mem:testdb. Once logged in, you should be able to issue any query you like against the tables in the Taco Cloud schema.
Spring’s JdbcTemplate, along with SimpleJdbcInsert, makes working with relational databases significantly simpler than plain vanilla JDBC. But you may find that JPA makes it even easier. Let’s rewind your work and see how to use Spring Data to make data persistence even easier.
3.2. Persisting data with Spring Data JPA
The Spring Data project is a rather large umbrella project comprised of several subprojects, most of which are focused on data persistence with a variety of different database types. A few of the most popular Spring Data projects include these:
- Spring Data JPA— JPA persistence against a relational database
- Spring Data MongoDB— Persistence to a Mongo document database
- Spring Data Neo4j— Persistence to a Neo4j graph database
- Spring Data Redis— Persistence to a Redis key-value store
- Spring Data Cassandra— Persistence to a Cassandra database
One of the most interesting and useful features provided by Spring Data for all of these projects is the ability to automatically create repositories, based on a repository specification interface.
To see how Spring Data works, you’re going to start over, replacing the JDBC-based repositories from earlier in this chapter with repositories created by Spring Data JPA. But first, you need to add Spring Data JPA to the project build.
3.2.1. Adding Spring Data JPA to the project
Spring Data JPA is available to Spring Boot applications with the JPA starter. This starter dependency not only brings in Spring Data JPA, but also transitively includes Hibernate as the JPA implementation:
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
If you want to use a different JPA implementation, then you’ll need to, at least, exclude the Hibernate dependency and include the JPA library of your choice. For example, to use EclipseLink instead of Hibernate, you’ll need to alter the build as follows:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<exclusions>
<exclusion>
<artifactId>hibernate-entitymanager</artifactId>
<groupId>org.hibernate</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.eclipse.persistence</groupId>
<artifactId>eclipselink</artifactId>
<version>2.5.2</version>
</dependency>
Note that there may be other changes required, depending on your choice of JPA implementation. Consult the documentation for your chosen JPA implementation for details. Now let’s revisit your domain objects and annotate them for JPA persistence.
3.2.2. Annotating the domain as entities
As you’ll soon see, Spring Data does some amazing things when it comes to creating repositories. But unfortunately, it doesn’t help much when it comes to annotating your domain objects with JPA mapping annotations. You’ll need to open up the Ingredient, Taco, and Order classes and throw in a few annotations. First up is the Ingredient class.
Listing 3.16. Annotating Ingredient for JPA persistence
package tacos;
import javax.persistence.Entity;
import javax.persistence.Id;
import lombok.AccessLevel;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.RequiredArgsConstructor;
@Data
@RequiredArgsConstructor
@NoArgsConstructor(access=AccessLevel.PRIVATE, force=true)
@Entity
public class Ingredient {
@Id
private final String id;
private final String name;
private final Type type;
public static enum Type {
WRAP, PROTEIN, VEGGIES, CHEESE, SAUCE
}
}
In order to declare this as a JPA entity, Ingredient must be annotated with @Entity. And its id property must be annotated with @Id to designate it as the property that will uniquely identify the entity in the database.
In addition to the JPA-specific annotations, you’ll also note that you’ve added a @NoArgsConstructor annotation at the class level. JPA requires that entities have a no-arguments constructor, so Lombok’s @NoArgsConstructor does that for you. You don’t want to be able to use it, though, so you make it private by setting the access attribute to AccessLevel.PRIVATE. And because there are final properties that must be set, you also set the force attribute to true, which results in the Lombok-generated constructor setting them to null.
You also add a @RequiredArgsConstructor. The @Data implicitly adds a required arguments constructor, but when a @NoArgsConstructor is used, that constructor gets removed. An explicit @RequiredArgsConstructor ensures that you’ll still have a required arguments constructor in addition to the private no-arguments constructor.
Now let’s move on to the Taco class and see how to annotate it as a JPA entity.
Listing 3.17. Annotating Taco as an entity
package tacos;
import java.util.Date;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.PrePersist;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import lombok.Data;
@Data
@Entity
public class Taco {
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@NotNull
@Size(min=5, message="Name must be at least 5 characters long")
private String name;
private Date createdAt;
@ManyToMany(targetEntity=Ingredient.class)
@Size(min=1, message="You must choose at least 1 ingredient")
private List<Ingredient> ingredients;
@PrePersist
void createdAt() {
this.createdAt = new Date();
}
}
As with Ingredient, the Taco class is now annotated with @Entity and has its id property annotated with @Id. Because you’re relying on the database to automatically generate the ID value, you also annotate the id property with @GeneratedValue, specifying a strategy of AUTO.
To declare the relationship between a Taco and its associated Ingredient list, you annotate ingredients with @ManyToMany. A Taco can have many Ingredient objects, and an Ingredient can be a part of many Tacos.
You’ll also notice that there’s a new method, createdAt(), which is annotated with @PrePersist. You’ll use this to set the createdAt property to the current date and time before Taco is persisted. Finally, let’s annotate the Order object as an entity. The next listing shows the new Order class.
Listing 3.18. Annotating Order as a JPA entity
package tacos;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.PrePersist;
import javax.persistence.Table;
import javax.validation.constraints.Digits;
import javax.validation.constraints.Pattern;
import org.hibernate.validator.constraints.CreditCardNumber;
import org.hibernate.validator.constraints.NotBlank;
import lombok.Data;
@Data
@Entity
@Table(name="Taco_Order")
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
private Date placedAt;
...
@ManyToMany(targetEntity=Taco.class)
private List<Taco> tacos = new ArrayList<>();
public void addDesign(Taco design) {
this.tacos.add(design);
}
@PrePersist
void placedAt() {
this.placedAt = new Date();
}
}
As you can see, the changes to Order closely mirror the changes to Taco. But there’s one new annotation at the class level: @Table. This specifies that Order entities should be persisted to a table named Taco_Order in the database.
Although you could have used this annotation on any of the entities, it’s necessary with Order. Without it, JPA would default to persisting the entities to a table named Order, but order is a reserved word in SQL and would cause problems. Now that the entities are properly annotated, it’s time to write your repositories.
3.2.3. Declaring JPA repositories
In the JDBC versions of the repositories, you explicitly declared the methods you wanted the repository to provide. But with Spring Data, you can extend the CrudRepository interface instead. For example, here’s the new IngredientRepository interface:
package tacos.data;
import org.springframework.data.repository.CrudRepository;
import tacos.Ingredient;
public interface IngredientRepository
extends CrudRepository<Ingredient, String> {
}
CrudRepository declares about a dozen methods for CRUD (create, read, update, delete) operations. Notice that it’s parameterized, with the first parameter being the entity type the repository is to persist, and the second parameter being the type of the entity ID property. For IngredientRepository, the parameters should be Ingredient and String.
You can similarly define the TacoRepository like this:
package tacos.data;
import org.springframework.data.repository.CrudRepository;
import tacos.Taco;
public interface TacoRepository
extends CrudRepository<Taco, Long> {
}
The only significant differences between IngredientRepository and TacoRepository are the parameters to CrudRepository. Here, they’re set to Taco and Long to specify the Taco entity (and its ID type) as the unit of persistence for this repository interface. Finally, the same changes can be applied to OrderRepository:
package tacos.data;
import org.springframework.data.repository.CrudRepository;
import tacos.Order;
public interface OrderRepository
extends CrudRepository<Order, Long> {
}
And now you have your three repositories. You might be thinking that you need to write the implementations for all three, including the dozen methods for each implementation. But that’s the good news about Spring Data JPA—there’s no need to write an implementation! When the application starts, Spring Data JPA automatically generates an implementation on the fly. This means the repositories are ready to use from the get-go. Just inject them into the controllers like you did for the JDBC-based implementations, and you’re done.
The methods provided by CrudRepository are great for general-purpose persistence of entities. But what if you have some requirements beyond basic persistence? Let’s see how to customize the repositories to perform queries unique to your domain.
3.2.4. Customizing JPA repositories
Imagine that in addition to the basic CRUD operations provided by CrudRepository, you also need to fetch all the orders delivered to a given ZIP code. As it turns out, this can easily be addressed by adding the following method declaration to OrderRepository:
List<Order> findByDeliveryZip(String deliveryZip);
When generating the repository implementation, Spring Data examines any methods in the repository interface, parses the method name, and attempts to understand the method’s purpose in the context of the persisted object (an Order, in this case). In essence, Spring Data defines a sort of miniature domain-specific language (DSL) where persistence details are expressed in repository method signatures.
Spring Data knows that this method is intended to find Orders, because you’ve parameterized CrudRepository with Order. The method name, findByDeliveryZip(), makes it clear that this method should find all Order entities by matching their deliveryZip property with the value passed in as a parameter to the method.
The findByDeliveryZip() method is simple enough, but Spring Data can handle even more-interesting method names as well. Repository methods are composed of a verb, an optional subject, the word By, and a predicate. In the case of findByDeliveryZip(), the verb is find and the predicate is DeliveryZip; the subject isn’t specified and is implied to be an Order.
Let’s consider another, more complex example. Suppose that you need to query for all orders delivered to a given ZIP code within a given date range. In that case, the following method, when added to OrderRepository, might prove useful:
List<Order> readOrdersByDeliveryZipAndPlacedAtBetween(
String deliveryZip, Date startDate, Date endDate);
Figure 3.2 illustrates how Spring Data parses and understands the readOrdersByDeliveryZipAndPlacedAtBetween() method when generating the repository implementation. As you can see, the verb in readOrdersByDeliveryZipAndPlacedAtBetween() is read. Spring Data also understands find, read, and get as synonymous for fetching one or more entities. Alternatively, you can also use count as the verb if you only want the method to return an int with the count of matching entities.
Figure 3.2. Spring Data parses repository method signatures to determine the query that should be performed.
Although the subject of the method is optional, here it says Orders. Spring Data ignores most words in a subject, so you could name the method readPuppiesBy... and it would still find Order entities, as that is the type that CrudRepository is parameterized with.
The predicate follows the word By in the method name and is the most interesting part of the method signature. In this case, the predicate refers to two Order properties: deliveryZip and placedAt. The deliveryZip property must be equal to the value passed into the first parameter of the method. The keyword Between indicates that the value of deliveryZip must fall between the values passed into the last two parameters of the method.
In addition to an implicit Equals operation and the Between operation, Spring Data method signatures can also include any of these operators:
- IsAfter, After, IsGreaterThan, GreaterThan
- IsGreaterThanEqual, GreaterThanEqual
- IsBefore, Before, IsLessThan, LessThan
- IsLessThanEqual, LessThanEqual
- IsBetween, Between
- IsNull, Null
- IsNotNull, NotNull
- IsIn, In
- IsNotIn, NotIn
- IsStartingWith, StartingWith, StartsWith
- IsEndingWith, EndingWith, EndsWith
- IsContaining, Containing, Contains
- IsLike, Like
- IsNotLike, NotLike
- IsTrue, True
- IsFalse, False
- Is, Equals
- IsNot, Not
- IgnoringCase, IgnoresCase
As alternatives for IgnoringCase and IgnoresCase, you can place either AllIgnoringCase or AllIgnoresCase on the method to ignore case for all String comparisons. For example, consider the following method:
List<Order> findByDeliveryToAndDeliveryCityAllIgnoresCase(
String deliveryTo, String deliveryCity);
Finally, you can also place OrderBy at the end of the method name to sort the results by a specified column. For example, to order by the deliveryTo property:
List<Order> findByDeliveryCityOrderByDeliveryTo(String city);
Although the naming convention can be useful for relatively simple queries, it doesn’t take much imagination to see that method names could get out of hand for more-complex queries. In that case, feel free to name the method anything you want and annotate it with @Query to explicitly specify the query to be performed when the method is called, as this example shows:
@Query("Order o where o.deliveryCity='Seattle'")
List<Order> readOrdersDeliveredInSeattle();
In this simple usage of @Query, you ask for all orders delivered in Seattle. But you can use @Query to perform virtually any query you can dream up, even when it’s difficult or impossible to achieve the query by following the naming convention.
Summary
- Spring’s JdbcTemplate greatly simplifies working with JDBC.
- PreparedStatementCreator and KeyHolder can be used together when you need to know the value of a database-generated ID.
- For easy execution of data inserts, use SimpleJdbcInsert.
- Spring Data JPA makes JPA persistence as easy as writing a repository interface.