Adding Database Support to the Service

For our next example, we are going to add a database to our service. Let’s add two new endpoints that use a database instead of a cache to store and retrieve a key/value pair. We’ll also use our custom exception handling that we just implemented for the new endpoints. In addition to the necessary swagger updates and resource class, we’ll need to write database setup scripts, create the JPA repository and ORM Hibernate classes to interact with the database, and create a service class to direct the interaction. Then we’ll write some integration tests using a local database instance.

Add New Database Endpoints

First we need to modify our swagger.json to add the contract for the new endpoints:

POST /database

GET /database/{key}

    "/database/{key}": {
      "summary": "Get Database values.",
      "description": "These methods allows apps and services to GET session variables for this user. It will *not* accept requests from browsers (i.e. User-Agent       contains 'Mozilla') in order to prevent potential XSS threats.",
      "parameters": [
        {
          "name": "key",
          "in": "path",
          "description": "The identifier for the database key.",
          "required": true,
          "schema": {
            "type": "string"
          }
        }
      ],
      "get": {
        "summary": "Get a database value for this key.",
        "description": "This method allows apps and services to retrieve session variables for this user. It will *not* accept requests from browsers (i.e. User-Agent       contains 'Mozilla') in order to prevent potential XSS threats.",
        "operationId": "getDatabaseKey",
        "responses": {
          "200": {
            "description": "Retrieved key value pair was successfully.",
            "content": {
              "application/json": {
                "schema": {
                  "type": "string"
                }
              }
            }
          },
          "401": {
            "description": "Key Required"
          },
          "403": {
            "description": "Not Allowed"
          }
        },
        "tags": [
          "Database"
        ]
      }
    },
    "/database": {
      "post": {
        "summary": "Saves a key/value pair for this database value.",
        "description": "This method allows apps and services set session variables for this user. It will *not* accept requests from browsers (i.e. User-Agent contains 'Mozilla') in order to prevent potential XSS threats.",
        "operationId": "setDatabaseKey",
        "requestBody": {
          "content": {
            "application/json": {
              "schema": {
                "type": "object",
                "properties": {
                  "key": {
                    "type": "string"
                  },
                  "value": {
                    "type": "string"
                  }
                },
                "required": [
                  "key",
                  "value"
                ]
              }
            }
          }
        },
        "responses": {
          "202": {
            "description": "Key value pair was saved successfully."
          },
          "400": {
            "description": "Key already exists"
          },
          "401": {
            "description": "Key Required"
          },
          "403": {
            "description": "Not Allowed"
          },
          "404": {
            "description": "Key Not Found"
          }
        },
        "tags": [
          "Database"
        ]
      }
    }

Add the following dependencies to your service pom to pull in the necessary libraries needed for the next section:

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

Create the Repository, Model and Service Classes

Before we create the resource for the new endpoints, we need to create the classes needed to interact with the database that the resource will use.

Create a new package called database with three sub-packages, model, repository and service.

Create the class StarterDbEntity in the model package; we’ll write a corresponding SQL script to go with this when we set up our test database later.

package gov.va.mobile.starter.v1.service.database.model;

import java.math.BigDecimal;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.SequenceGenerator;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "STARTER_DB_TABLE", schema = "STARTER_SERVICE")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class StarterDbEntity {
  @Id
  @Column(name = "ID", nullable = false, unique = true)
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "starter_db_SQS")
  @SequenceGenerator(name = "starter_db_SQS", sequenceName = "STARTER_DB_TABLE_SQS")
  private BigDecimal id;

  @Column(name = "KEY", nullable = false)
  private String key;

  @Column(name = "VALUE")
  private String value;
}

Create StarterDbRepository in the repository package:

package gov.va.mobile.starter.v1.service.database.repository;

import java.math.BigDecimal;
import gov.va.mobile.starter.v1.service.database.model.StarterDbEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * JPA Repository used to retrieve {@link StarterDbEntity} entity objects.
 *
 * @see JpaRepository
 */
@Repository
public interface StarterDbRepository extends JpaRepository<StarterDbEntity, BigDecimal> {
  StarterDbEntity findByKey(String key);

  void deleteByKey(String key);
}

Add StarterDbService to the service package. Note that we are throwing our new custom exceptions when saving our token value.

package gov.va.mobile.starter.v1.service.database.service;

import java.util.Optional;

import gov.va.mobile.starter.v1.service.database.repository.StarterDbRepository;
import gov.va.mobile.starter.v1.service.database.model.StarterDbEntity;
import gov.va.mobile.starter.v1.service.exception.TokenExistsException;
import gov.va.mobile.starter.v1.service.exception.TokenRequiredException;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;
import gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;

/**
 * The service layer that interacts with the repository for storing, retrieving and updating policies. Handles validation and respond back.
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class StarterDbService {
  private final StarterDbRepository repository;

  protected void delete(final String key) {
    repository.deleteByKey(key);
  }

  public void save(final SetDatabaseKeyRequest obj) {
    if (StringUtils.isEmpty(obj.getKey())) {
      throw new TokenRequiredException(String.format("Empty Key: %s", obj.getKey()));
    }
    StarterDbEntity starterDbEntity = new StarterDbEntity();
    starterDbEntity.setKey(obj.getKey());
    starterDbEntity.setValue(obj.getValue());
    try {
      repository.save(starterDbEntity);
    } catch (DataIntegrityViolationException exc) {
      throw new TokenExistsException(exc, String.format("Row already exists with key: %s", obj.getKey()));
    }
  }

  public Optional<String> getValueFromKey(final String key) {
    StarterDbEntity obj = repository.findByKey(key);
    return Optional.of(obj.getValue());
  }
}

Create the Database Resource

Now we can create the resource for the new endpoints called DatabaseResource:

package gov.va.mobile.starter.v1.service.resource;

import gov.va.mobile.service.security.HeaderNames;
import gov.va.mobile.service.security.web.jwt.JWTRestricted;
import gov.va.mobile.starter.v1.service.database.service.StarterDbService;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestHeader;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;

import java.util.Optional;

@RestController
@JWTRestricted(checkResource = true)
@RequestMapping(path = "/database")
public class DatabaseResource {

  private final StarterDbService starterDbService;

  public DatabaseResource(final StarterDbService starterDbService) {
    this.starterDbService = starterDbService;
  }

  @PostMapping
  public void setDatabaseValue(@RequestBody final SetDatabaseKeyRequest body,
                               @RequestHeader(HeaderNames.VAMF_JWT_HEADER) final String jwt)  {
    starterDbService.save(body);
  }

  @GetMapping(value = "/{key}", produces = MediaType.APPLICATION_JSON_VALUE)
  public ResponseEntity<String> getDatabaseValue(@PathVariable final String key,
                                                 @RequestHeader(HeaderNames.VAMF_JWT_HEADER) final String jwt)  {
    Optional<String> response = starterDbService.getValueFromKey(key);
    return ResponseEntity.status(HttpStatus.OK).body("\"" + response.orElseThrow() + "\"");
  }
}

At this point, you could compile your code to verify that it is syntactically correct, but the dependencies you added above will not allow your service to stand up until you wire up the underlying database. Don’t worry, we’ll tackle that in the next section.