In this section, we are going to store objects in an Ignite cache and examine application performance. First, we will create a database-backed RESTful web service to store and fetch soccer players. Next, we will add a caching layer on top of the database layer to improve the query performance:

1. Install MySQL (version 8.0.11 or higher version) from the following Oracle URL: https://dev.mysql.com/downloads/installer/. Configure it using the the steps mentioned here: https://youtu.be/UgHRay7gN1g. Include MySQL workbench.

2. Launch MySQL workbench and create a new database (schema), football:

3. In query editor, execute the use  SQL command to select our newly created database. The following is the SQL command—use football; (don't forget the semicolon ;).
4. Execute the following script to create a table, club. clubno is the primary key and cname is the club's name:

      create table club(
       clubno integer,
       cname varchar(200),
       constraint pk_club primary key (clubno)
      );

5. Now, create a table, player. playerno is the primary key, pname represents a player's name, wages is a player's weekly wages ,and the clubno column maps a player to a club:

      create table player(
       playerno integer,
       pname varchar(200),
       wages integer,
       clubno integer,
       constraint pk_player primary key (playerno),
       constraint fk_clubno foreign key (clubno) references club  
       (clubno)
      );

6. MySQL workbench should not throw an error:

7. Insert a few rows into the club table:

      insert into club values(1, 'Manchester United');
      insert into club values(2, 'Real Madrid');
      insert into club values(3, 'Manchester City');
      insert into club values(4, 'FC Barcelona');

8. Launch http://start.spring.io/ and create a Gradle project, chapter3-datagrid, with the following dependencies: web, jpa, mysql, and devtools:

9. Unzip the downloaded zip file and import it into your IDE (Eclipse/STS/IntelliJ—your favorite IDE ). The project structure in Eclipse will look like this:

10. Now, edit the application.properties to enable the mySQL data source. Edit the mySQL password before saving the application.properties. The format_sql flag will instruct Spring Boot to generate the formatted query in the Eclipse console:

      spring.datasource.url=jdbc:mysql://localhost:3306/football
      spring.datasource.username=root
      spring.datasource.password=password
      spring.jpa.properties.hibernate.dialect =   
      org.hibernate.dialect.MySQLDialect
      spring.jpa.properties.hibernate.id.new_generator_mappings = false
      spring.jpa.properties.hibernate.format_sql = true
      logging.level.org.hibernate.SQL=DEBUG
      logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

11. Now, create two entity classes, club and player. These classes will be annotated with a set of JPA (javax.persistence) annotations such as these:
    • All our domain models will be annotated with the @Entity annotation, to mark the classes as a persistent Java class.
    • The @Table annotation maps the table to this entity. You don't have to mention the table name (even the annotation) if the entity name and the table name are the same.
    • The @Id annotation defines the primary key. Additionally, we can specify the ID generation type or generator, such as auto incremental ID.
    • The @Column annotation defines the properties of the table column that will be mapped to the annotated field, such as name, length, nullable, and so on.

The following is the code snippet of the Club class:

package chapter3.datagrid.entity;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "club")
public class Club {
 @Id
 @Column
 int clubno;
 
 @Column(name = "cname")
 String name;
 
 //Getters/setters are omitted for brevity
}

The following is the code snippet of the Player class:

@Entity
@Table(name = "player")
public class Player {
 @Id
 @Column
 int playerno;
 
 @Column(name = "pname")
 String name;

 @Column
 int wages;

 @ManyToOne(optional=false)
 @JoinColumn(name="clubno",referencedColumnName="clubno")
 Club club;
//Getters/setters are omitted for brevity
}

The @ManyToOne annotation is used to map many players to a club. The @JoinColumn is used to join two tables. The name  attribute of @JoinColumn refers to the column in the source table and the referencedColumnName  refers to the column referenced in the destination table. In our case, both the tables, player and club, have same column name, clubno.

Create JPA repositories for the following:

• For Player:

      @Repository
      public interface PlayerRepository extends JpaRepository<Player,   
      Integer>{
      }

• For club:

       @Repository
      public interface ClubRepository extends JpaRepository<Club,   
      Integer>{
      }

The beauty of Spring data is if you follow the convention, you don't have to write any code to access the database. Write a controller class to access player details. 

@RestController: Represents a Spring Rest controller

@RequestMapping: Maps the path to this resource

@Get/Post/Put/DeleteMapping annotations represent the HTTP operation:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import chapter3.datagrid.entity.Player;
import chapter3.datagrid.repository.PlayerRepository;

@RestController
@RequestMapping("/rest")
public class PlayerController {

 @Autowired
 PlayerRepository playerRepository;

 @GetMapping("/players")
 public List<Player> getAllPlayers() {
 return playerRepository.findAll();
 }
 
 @PostMapping("/player")
 public Player createPlayer(@Valid @RequestBody Player Player) {
 return playerRepository.save(Player);
 }

 @GetMapping("/players/{id}")
 public Player getPlayerById(@PathVariable(value = "id") Integer id) {
 return playerRepository.findById(id).orElse(null);
 }
 
@PutMapping("/players/{id}")
 public Player updatePlayer(@PathVariable(value = "id") Integer playerId, @Valid @RequestBody Player details) {
  Player player = playerRepository.findById(playerId)
 .orElseThrow(() -> new RuntimeException("Player" + playerId + " not found"));
 
  player.setClub(details.getClub());
  player.setName(details.getName());
  player.setWages(details.getWages());

  Player updatedPlayer = playerRepository.save(player);
  return updatedPlayer;
 }

 @DeleteMapping("/players/{id}")
 public ResponseEntity<?> deletePlayer(@PathVariable(value = "id") Integer id) {
 Player Player = playerRepository.findById(id)
 .orElseThrow(() -> new RuntimeException("Player:" + id + " not found"));
    playerRepository.delete(Player);

   return ResponseEntity.ok().build();
 }
}

Now, the project structure looks as follows:

Launch the DatagridApplication.java and open postman to create a player:

Now, search Paul Pogba using his id=3:

The Spring Boot application will invoke a SELECT JOIN query to fetch the details: