Spring Boot - 03 Spring Data

This is the third post in a series of posts to cover different aspects of Spring Boot. Please note that the entire post isn’t necessarily only written in English.

In this post, I am going to cover the basics of Spring Data, including JDBC (Java DataBase Connectivity) & JPA (Java Persistence API).

---

Debug

By using JPA instead of JDBC, data.sql is not executed & schema.sql is not required.

If schema.sql & data.sql are under main/resources, then either null or filled with SQL statements.

Commenting out the SQL lines will cause problems.

If schema.sql exists under main/resources, JPA will execute DDL based on schema.sql; Otherwise, JPA auto creates the table based on the package domain.

Solution: Add dataLoader in main application:

@Bean
public CommandLineRunner dataLoader(IngredientRepository repo) {
    return new CommandLineRunner() {
        @Override
        public void run(String... args) throws Exception {
            repo.save(new Ingredient("FLTO", "Flour Tortilla", Type.WRAP));
			repo.save(new Ingredient("SRCR", "Sour Cream", Type.SAUCE));
            ......  } };
}

In application.yml:

spring:

  jpa:
    show-sql: true

    hibernate:
      ddl-auto: none

  jdbc:
    url: jdbc:h2:mem:testdb

Using H2 Console:

• JDBC Url: jdbc:h2:mem:testdb

• Username: sa

• Password: <empty>

Hibernate Exception:

org.hibernate.exception.SQLGrammarException: could not prepare statement

Table name (e.g. Order) is a reserved keyword for H2 database.

Hibernate Exception:

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

When use H2 in-memory DB, we can use @ManytoMany.

But with an external MySQL DB, we always need a 3rd table, therefore the annotation is split to:

• @OnetoMany
• @ManytoOne

Exceptions:

// 第一种： Hibernate Exception
org.hibernate.InstantiationException: 
No default constructor for entity: : demo.taco.domain.Ingredient;

// 第二种： 
ERROR 12470 --- [restartedMain] o.s.boot.SpringApplication: Application run failed
java.lang.IllegalStateException: Failed to execute CommandLineRunner

Must add following annotations to domain.Ingredient:

// Needs a constructor with no arguments, and other constructors with args
@RequiredArgsConstructor
@NoArgsConstructor(access=AccessLevel.PRIVATE, force=true)

Maven install failed:

Failed to execute goal org.apache.maven.plugins:
maven-surefire-plugin:2.12:test (default-test) on project.

Add the following dependency:

<!-- Spring Boot 2.2.6 -->
<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-surefire-plugin</artifactId>
    <version>2.19.1</version>
</plugin>

JPA error with Entity Manager Factory:

[JPA]
org.springframework.beans.factory.BeanCreationException: 
Error creating bean with name 'entityManagerFactory' defined in class path resource[]: 
Invocation of init method failed; Unable to build Hibernate SessionFactory;

Add the following dependency:

<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.1</version>
</dependency>

Add missing annotations:

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;


@ManyToMany(targetEntity=Taco.class)
private List<Taco> tacos = new ArrayList<>();

---

JDBC

Comparison

Spring JDBC support is rooted in the JdbcTemplate class.

• Perform SQL operations against a relational DB without all the clumsy code when working with JDBC directly.

Sample query without JdbcTemplate:

@Override
// find one ingredient
public Ingredient findOne (String id) {				
    
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    
    try {
        connection = dataSource.getConnection();		  
        statement = connection.prepareStatement (	// SQL query is 👇
            "select id, name, type from Ingredient where id = ?");
        statement.setString(1, id);
        rs = statement.executeQuery();
        Ingredient ingredient = null;
        
        if (rs.next())
            ingredient = new Ingredient(rs.getString("id"), rs.getString("name"),
				Ingredient.Type.valueOf(rs.getString("type")));
        
        return ingredient;
    }
    
    catch (SQLException e) { /* some exceptions */ }
    
    finally {
        if (rs != null) {
            try { rs.close(); }
            catch (SQLException e) {}
        }
        
        if (statement != null) {
            try { statement.close(); }
            catch (SQLException e) {}
        }
        
        if (connection != null) {
            try { statement.close(); }
            catch (SQLException e) {}
        }
    }
    return null;
}

Sample query with JdbcTemplate:

No statements / connections being created & cleaned up. No try & catch

Focus on performing the query & mapping results to an Ingredient object

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")));
}

And adapt Domain for persistence: Add id to it (for DB auto-generated id)

JdbcTemplate

Dependency: Add H2 embedded database

<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>

Objective (Repository, Persistence): operations needed for Ingredeint object(s) are

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

IngredientRepository interface:

public interface IngredientRepository {
    
    Iterable<Ingredient> findAll();	// Iterable!
    
    Ingredient findOne(String id);
    
    Ingredient save(Ingredient ingredient);
    
}

IngredientRepository implementation: (use JdbcTemplate to query the database)

• Create a constructor:

  The constructor assigns JdbcTemplate to an instance variable that will be used in other methods to query & insert into the DB.

@Autowired

// Injects the RepoImpl with JdbcTemplate, via the @Autowired annotated constructor
public IngredientRepositoryImpl(JdbcTemplate jdbc) {
    this.jdbc = jdbc;
}

• Sample Implementation:

@Override
public Iterable<Ingredient> findAll() {		
    
    // query
    return jdbc.query("select id, name, type from Ingredient", this::mapRowToIngredient);
}

// 和上面的 findOne 一样
@Override
public Ingredient findById(String id) {			
    
    // queryForObject
    return jdbc.queryForObject (
        "select id, name, type from Ingredient where id=?", this::mapRowToIngredient, id);
}

// Java 8 method reference & lambdas
private Ingredient mapRowToIngredient(ResultSet rs, int rowNum) throws SQLException {
    return new Ingredient(rs.getString("id"), rs.getString("name"),
                          Ingredient.Type.valueOf(rs.getString("type")));
}

query() & queryForObject:

• findById() returns one single object, use queryForObject().
• findAll() returns a collection of objects, use query().
  • query() accepts SQL & impl of Spring’s RowMapper, to map each row in the result set to an object

Insert a Row

JdbcTemplate ‘s update() method: Any query that writes / updates data in the DB.

Because it’s not necessary to map ResultSet data to an object, hence update is much simpler than query.

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

Update Controller

@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));

    model.addAttribute("taco", new Taco());
    return "design";
}

Define DB Schema

5 张表， 2个 Many to Many

3张存实际内容，剩下两张存 Many to Many 关系

利用 H2 database (文件名必须保持一致)：

SQL will be executed when application starts

• Save DDL SQL to resources/schema.sql
• Save additional data to resources/data.sql

Insert Data with FK

Two ways to save data with JdbcTemplate:

• Directly using the update() method
• Using SimpleJdbcInsert wrapper class

One Step further to complicate the situation:

According to the DB Schema above, when we save data, we also need to update the associated tables.

With JdbcTemplate

Use PreparedStatementCreator & KeyHolder together to get the auto generated ID.

@Respository
public class JdbcTacoRepository implements TacoRepository {
    
    private JdbcTemplate jdbc;
    
    public JdbcTacoRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }
    
    @Override
    // save: 只是用来 call other methods
    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());

        // 这个是为了 KeyHolder
        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 kh = new GeneratedKeyHolder();
        
        // update() 中的两个参数， keyholder 提供 ID. 但为了使用 kh，必须先创建一个 psc
        jdbc.update(psc, kh);				
        return kh.getKey().longValue();	
        // return 的是 tacoId （long）
    }


    // update() doesn't give us the auto generated tacoID
    // so we need saveTacoInfo to get the ID
    private void saveIngredientToTaco(Ingredient ingredient, long tacoId) {

        // 注意这里的update（）
        jdbc.update("insert into Taco_ingredients (taco, ingredient)" + "values(?, ?)",
                   tacoID, ingredient.getId());
    }
}

Update Controller

@Controller
@RequestMapping("/design")
@SessionAttributes("order")				// 注意这个 annotation
public class DesignTacoController {
    
    private final IngredientRepository ingredientRepo;
    private TacoRepository tacoRepo;
    
    // Constructor! Assigns both to instance variables
    @Autowired
    public DesignTacoController(IngredientRepository ingredientRepo, TacoRepository tacoRepo){
        this.ingredientRepo = ingredeintRepo;
        this.tacoRepo = tacoRepo;
    }
    
    // ensures that an order object will be created in the model
    @ModelAttribute(name = "order")		
    public Order order() {
        return new Order();
    }
    
    @ModelAttribute(name = "taco")
    public Taco taco() {
        return new Taco();
    }
    
    @PostMapping
    public String processDesign (@Valid Taco taco, Errors errors, @ModelAttribute Order order) {
        if (error.hasErrors())
            return "design";
        
        // injected tacoRepo
        Taco saved = tacoRepo.save(taco);			
        order.addDesign(saved);
        return "redirect:/orders/current";
    }
}

@SessionAttributes & @ModelAttribute:

• The class-level @SessionAttributes specifies that, any model objects ( order & taco ) should be kept in session and available across multiple requests

• order is annotated with @ModelAttribute: order ‘s value should come from the model, and MVC shouldn’t attempt to bind request parameters to it

• The Order object isn’t saved to the DB until the user completes & submits the order form.

---

Simple JDBC Insert

Improvement of JdbcTemplate

Rewrite JdbcOrderRepository with SimpleJdbcInsert

Jackson: for JSON processing

@Repository
public class JdbcOrderRepository implements OrderRepository {
    
    // Jackson’s ObjectMapper
    private ObjectMapper objectMapper;	
    private SimpleJdbcInsert orderInserter;
    private SimpleJdbcInsert oderTacoInserter;
    			
    @Autowired
    public JdbcOrderRepository (JdbcTemplate jdbc) {
        this.orderInserter = new SimpleJdbcInserter(jdbc)
            // 这里 generate ID！
            .withTableName("Taco_Order").usingGeneratedKeyColumns("id");  
        
        this.OrderTacoInserter = new SimpleJdbcInserter(jdbc)
            .withTableName("Taco_Order_Tacos");
        
        this.objectMapper = new ObjectMapper();
    }
}

Insert data

@Override
// save() doesn't save anything, but define the flow for saing an order & associated taco object
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());

    // executeAndReturnKey()!
    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);	// execute()
}

Update Controller

@Controller
@RequestMapping("/orders")
@SessionAttributes("order")
public class OrderController {
  
  private OrderRepository orderRepo;

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

  @PostMapping
  public String processOrder(@Valid Order order, Errors errors, SessionStatus sessionStatus) {
    if (errors.hasErrors()) {
      return "orderForm";
    }
    
    orderRepo.save(order);
    sessionStatus.setComplete();
    return "redirect:/";
  }
}

---

JPA

Intro

The Spring Data Project includes:

• JPA
• MongoDB (document DB)
• Neo4j (graph DB)
• Redis
• Cassandra

The most useful feature: Auto create repositories, based on a repository-specific interface

Dependency

The starter dependency transitively includes Hibernate as the JPA implementation

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

Use a different JPA implementation: Must exclude Hibernate at first

<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>

Domain / Entity Annotation

约定： 如果不用 JPA，就用 Domain； 如果用了 JPA，那就叫 Entity

@Data
@RequiredArgsConstructor

// JPA requires that entities have a no-argument constructor
// 如果写了 @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 statis enum Type {
        WRAP, PROTEIN, VEGGIES, CHEESE, SAUCE
    }
}

The @Data implicitly adds a required argument constructor, but when a @NoArgsConstructor is used, that constructor gets removed.

An explicit @RequiredArgsConstructor ensures that you’ll still have a required arguments constructor, apart from the private no-argument constructor.

多对多关系：

@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;

    @ManyToMany(targetEntity=Ingredient.class)
    @Size(min=1, message="You must choose at least 1 ingredient")
    private List<String> ingredients;
    
	private Data createdAt;
    
    // set createdAt to the current time before Taco is persisted
    @PrePersist				
    void createdAt() {
        this.createdAt = new Date();
    }
}

关于 @Table & Serializable: 总是成对出现

@Table(name = "Taco_Order")
public class Order implements Serializable {}

为什么这里需要 @Table:

Without @Table, JPA will persist the entities to a table named Order.

But order is a reserved word in SQL. Therefore we need to specify the table.

JPA Repositories

CrudRepository

When the application starts, Spring Data JPA auto implements the CrudRepo Interface.

public interface IngredientRepository extends CrudRepository<Ingredient, String> {}

// 1st param (Ingredient): entity type to persist (object)
// 2nd param (String): ID type (is string)


public interface TacoRepository extends CrudRepositoy<Taco Long> {}

// entity type is Taco, ID type is Long

Customization

When generating the repository implementation, Spring Data parse the method name in the Repository, and attempts to understand the method’s purpose in the context of the persisted object. Spring Data defines domain-specific language (DSL) , where persistence details are expressed in repository method signatures.

Repository methods are composed of:

• verb (e.g. find = get = read, count)
• subject (optional)
• By
• predicate (e.g. DeliveryZip)
• operator (条件 / 比较)

Get all orders delivered to a given zip:

// Find all Order entities by matching deliveryZip with the param passed in the interface
// String deliveryZip is the passed parameter
List<Order> findByDeliveryZip(String deliveryZip);

Get all orders delivered to a given zip within a time range:

// You can write: getPuppies, and it still works. Because subject is optional
// start & end date must fall between the given values
List<Order> getOrdersByDeliveryZipAndPlacedAtBetween(
    String deliveryZip, Date startDate, Date endDate);

Spring Data operators:

// 排序. e.g. findByDeliveryCityOrderByDeliveryTo (String city)
OrderBy

IsTrue, True
IsFalse, False

Is , Equals
IsNot , Not

// All: Ignore all cases in all matched strings
AllIgnoringCase, AllIgnoresCase
IgnoringCase, IgnoresCase

IsAfter, After, IsGreaterThan, GreaterThan
IsBefore, Before, IsLessThan, LessThan

IsGreaterThanEqual, GreaterThanEqual
IsLessThanEqual, LessThanEqual

IsNull, Null
IsNotNull, NotNull

IsIn, In
IsNotIn, NotIn

IsBetween, Between
IsContaining, Containing, Contains

IsStartingWith, StartingWith, StartsWith
IsEndingWith, EndingWith, EndsWith

IsLike, Like
IsNotLike, NotLike

SQL Query

With @Query, we can explicitly specify the query to be performed. （遇到复杂的情况）

@Query("Order order where order.deliveryCity='Seattle'")
List<Order> getOrdersDeliveredInSeattle();

Update Controller

@Controller
@RequestMapping("/orders")

// Need @SessionAttributes
@SessionAttributes("order")
public class OrderController {

    // Final
    private final OrderRepository orderRepo;

    // Constructor. Autowired
    @Autowired
    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";
		
        // save() method, save to repo
        orderRepo.save(order);
        
        // set to complete
        sessionStatus.setComplete();

        return "redirect:/";
    }
}