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 controller 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 controller for the new endpoints, we need to create the classes needed to interact with the database that the controller 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 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;

import java.math.BigDecimal;

/**
 * Entity class representing the "STARTER_DB_TABLE" table in the "STARTER_SERVICE" schema.
 * <p>
 * This class is annotated with JPA and Lombok annotations to simplify database interactions
 * and reduce boilerplate code.
 */
@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 gov.va.mobile.starter.v1.service.database.model.StarterDbEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;

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

    /**
     * Finds an entity by its key.
     */
    StarterDbEntity findByKey(String key);

    /**
     * Deletes an entity by its 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 gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;
import gov.va.mobile.starter.v1.service.database.model.StarterDbEntity;
import gov.va.mobile.starter.v1.service.database.repository.StarterDbRepository;
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 java.util.Optional;

/**
 * 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;

    /**
     * Deletes a database entry by its key.
     */
    protected void delete(final String key) {
        repository.deleteByKey(key);
    }

    /**
     * Saves a new database key-value pair.
     *
     * @param obj the {@link SetDatabaseKeyRequest} object containing the key-value pair to save.
     * @throws TokenRequiredException if the key is empty.
     * @throws TokenExistsException if a row with the same key already exists in the database.
     */
    public void save(final SetDatabaseKeyRequest obj) {
        if (StringUtils.isEmpty(obj.getKey())) {
            throw new TokenRequiredException(String.format("Empty Key: %s", obj.getKey()));
        }
        final StarterDbEntity starterDbEntity = new StarterDbEntity();
        starterDbEntity.setKey(obj.getKey());
        starterDbEntity.setValue(obj.getValue());
        try {
            repository.save(starterDbEntity);
        } catch (final DataIntegrityViolationException exc) {
            throw new TokenExistsException(exc, String.format("Row already exists with key: %s", obj.getKey()));
        }
    }

    /**
     * Retrieves the value associated with the given key from the database.
     *
     * @param key the key to search for in the database.
     * @return an {@link Optional} containing the value if the key exists, or an empty {@link Optional} if not.
     */
    public Optional<String> getValueFromKey(final String key) {
        final StarterDbEntity obj = repository.findByKey(key);
        return Optional.of(obj.getValue());
    }
}

Create the Database Controller

Now we can create the controller for the new endpoints called DatabaseController:

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

import gov.va.mobile.security.HeaderNames;
import gov.va.mobile.security.web.jwt.JwtRestricted;
import gov.va.mobile.starter.v1.model.SetDatabaseKeyRequest;
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 java.util.Optional;

/**
 * REST controller for managing database key-value pairs.
 */
@RestController
@JwtRestricted(checkResource = true)
@RequestMapping(path = "/database")
public class DatabaseController {

    private final StarterDbService starterDbService;

    /**
     * Constructs a new {@code DatabaseController} with the specified service.
     */
    public DatabaseController(final StarterDbService starterDbService) {
        this.starterDbService = starterDbService;
    }

    /**
     * Sets a key-value pair in the database.
     *
     * @param body
     *         the request body containing the key-value pair to save.
     * @param jwt
     *         the JWT token provided in the request header.
     */
    @PostMapping
    public void setDatabaseValue(@RequestBody final SetDatabaseKeyRequest body,
                                 @RequestHeader(HeaderNames.VAMF_JWT_HEADER) final String jwt) {
        starterDbService.save(body);
    }

    /**
     * Retrieves the value associated with the given key from the database.
     *
     * @param key
     *         the key to search for in the database.
     * @param jwt
     *         the JWT token provided in the request header.
     *
     * @return a {@link ResponseEntity} containing the value associated with the key.
     */
    @GetMapping(value = "/{key}", produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<String> getDatabaseValue(@PathVariable final String key,
                                                   @RequestHeader(HeaderNames.VAMF_JWT_HEADER) final String jwt) {
        final 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.