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.

In our design, TacoOrder and Taco are part of an aggregate in which TacoOrder is the aggregate root. In other words, Taco objects don’t exist outside of the context of a TacoOrder. So, for now, we only need to define a repository to persist TacoOrder objects and, in turn, Taco objects along with them. Such a repository can be defined in a OrderRepository interface like this:

package tacos.data;

import java.util.Optional;

import tacos.TacoOrder;

public interface OrderRepository {

  TacoOrder save(TacoOrder order);

}

Seems simple enough, right? Not so quick. When you save a TacoOrder, you also must save the Taco objects that go with it. And when you save the Taco objects, you’ll also need to save an object that represents the link between the Taco and each Ingredient that makes up the taco. The IngredientRef class defines that linking between Taco and Ingredient as follows:

package tacos;

import lombok.Data;

@Data
public class IngredientRef {

  private final String ingredient;

}

Suffice it to say that the save() method will be a bit more interesting than the corresponding method you created earlier for saving a humble Ingredient object.

Another thing that the save() method will need to do is determine what ID is assigned to the order once it has been saved. Per the schema, the id property on the Taco_Order table is an identity, meaning that the database will determine the value automatically. But if the database determines the value for you, then you will need to know what that value is so that it can be returned in the TacoOrder object returned from the save() method. Fortunately, Spring offers a helpful GeneratedKeyHolder type that can help with that. But it involves working with a prepared statement, as shown in the following implementation of the save() method:

package tacos.data;

import java.sql.Types;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Optional;

import org.springframework.asm.Type;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import tacos.IngredientRef;
import tacos.Taco;
import tacos.TacoOrder;

@Repository
public class JdbcOrderRepository implements OrderRepository {

  private JdbcOperations jdbcOperations;

  public JdbcOrderRepository(JdbcOperations jdbcOperations) {
    this.jdbcOperations = jdbcOperations;
  }

  @Override
  @Transactional
  public TacoOrder save(TacoOrder order) {
    PreparedStatementCreatorFactory pscf =
      new PreparedStatementCreatorFactory(
        "insert into Taco_Order "
        + "(delivery_name, delivery_street, delivery_city, "
        + "delivery_state, delivery_zip, cc_number, "
        + "cc_expiration, cc_cvv, placed_at) "
        + "values (?,?,?,?,?,?,?,?,?)",
        Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
        Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
        Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP
    );
    pscf.setReturnGeneratedKeys(true);

    order.setPlacedAt(new Date());
    PreparedStatementCreator psc =
      pscf.newPreparedStatementCreator(
        Arrays.asList(
          order.getDeliveryName(),
          order.getDeliveryStreet(),
          order.getDeliveryCity(),
          order.getDeliveryState(),
          order.getDeliveryZip(),
          order.getCcNumber(),
          order.getCcExpiration(),
          order.getCcCVV(),
          order.getPlacedAt()));

    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcOperations.update(psc, keyHolder);
    long orderId = keyHolder.getKey().longValue();
    order.setId(orderId);

    List<Taco> tacos = order.getTacos();
    int i=0;
    for (Taco taco : tacos) {
      saveTaco(orderId, i++, taco);
    }
    return order;
  }
}

There appears to be a lot going on in the save() method, but we can break it down into only a handful of significant steps. First, you create a PreparedStatementCreatorFactory that describes the insert query along with the types of the query’s input fields. Because you’ll later need to fetch the saved order’s ID, you also will need to call setReturnGeneratedKeys(true).

After defining the PreparedStatementCreatorFactory, you use it to create a PreparedStatementCreator, passing in the values from the TacoOrder object that will be persisted. The last field given to the PreparedStatementCreator is the date that the order is created, which you’ll also need to set on the TacoOrder object itself so that the returned TacoOrder will have that information available.

Now that you have a PreparedStatementCreator in hand, you’re ready to actually save the order data by calling the update() method on JdbcTemplate, passing in the PreparedStatementCreator and a GeneratedKeyHolder. After the order data has been saved, the GeneratedKeyHolder will contain the value of the id field as assigned by the database and should be copied into the TacoOrder object’s id property.

At this point, the order has been saved, but you need to also save the Taco objects associated with the order. You can do that by calling saveTaco() for each Taco in the order.

The saveTaco() method is quite similar to the save() method, as you can see here:

private long saveTaco(Long orderId, int orderKey, Taco taco) {
  taco.setCreatedAt(new Date());
  PreparedStatementCreatorFactory pscf =
      new PreparedStatementCreatorFactory(
    "insert into Taco "
    + "(name, created_at, taco_order, taco_order_key) "
    + "values (?, ?, ?, ?)",
    Types.VARCHAR, Types.TIMESTAMP, Type.LONG, Type.LONG
  );
  pscf.setReturnGeneratedKeys(true);

  PreparedStatementCreator psc =
    pscf.newPreparedStatementCreator(
      Arrays.asList(
        taco.getName(),
        taco.getCreatedAt(),
        orderId,
        orderKey));

  GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
  jdbcOperations.update(psc, keyHolder);
  long tacoId = keyHolder.getKey().longValue();
  taco.setId(tacoId);

  saveIngredientRefs(tacoId, taco.getIngredients());

  return tacoId;
}

Step by step, saveTaco() mirrors the structure of save(), albeit for Taco data instead of TacoOrder data. In the end, it makes a call to saveIngredientRefs() to create a row in the Ingredient_Ref table to link the Taco row to an Ingredient row. The saveIngredientRefs() method looks like this:

private void saveIngredientRefs(
  long tacoId, List<IngredientRef> ingredientRefs) {
  int key = 0;
  for (IngredientRef ingredientRef : ingredientRefs) {
    jdbcOperations.update(
      "insert into Ingredient_Ref (ingredient, taco, taco_key) "
      + "values (?, ?, ?)",
      ingredientRef.getIngredient(), tacoId, key++);
  }
}

Thankfully, the saveIngredientRefs() method is much simpler. It cycles through a list of Ingredient objects, saving each into the Ingredient_Ref table. It also has a local key variable that is used as an index to ensure that the ordering of the ingredients stays intact.

All that’s left to do with OrderRepository is to inject it into OrderController and use it when saving an order. The following listing shows the changes necessary for injecting the repository.

Listing 3.11 Injecting and using OrderRepository

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.TacoOrder;
import tacos.data.OrderRepository;

@Controller
@RequestMapping("/orders")
@SessionAttributes("tacoOrder")
public class OrderController {

  private OrderRepository orderRepo;
  
  public OrderController(OrderRepository orderRepo) {
    this.orderRepo = orderRepo;
  }

  // ...
  @PostMapping
  public String processOrder(@Valid TacoOrder order, Errors errors, SessionStatus sessionStatus) {
    if (errors.hasErrors()) {
      return "orderForm";
    }

    orderRepo.save(order);
    sessionStatus.setComplete();

    return "redirect:/";
  }
}

As you can see, the constructor takes an OrderRepository as a parameter and assigns it to an instance variable that it will use in the processOrder() method. Speaking of the processOrder() method, it has been changed to call the save() method on the OrderRepository instead of logging the TacoOrder object.

Spring’s JdbcTemplate makes working with relational databases significantly simpler than with plain vanilla JDBC. But even with JdbcTemplate, some persistence tasks are still challenging, especially when persisting nested domain objects in an aggregate. If only there were a way to work with JDBC that was even simpler.

Let’s have a look at Spring Data JDBC, which makes working with JDBC insanely easy — even when persisting aggregates.