Janos Pasztor

Building an API-driven software: Database Access

In our previous episode we discussed how the backend is set up with Spring Boot. However, we did not do anything beyond simply responding to requests. Now it is time for us to create a database and actually create a blog.

Preparations

Like previously, we will continue developing our project in Spring Boot with the help of IntelliJ IDEA as a development enviroment.

If you haven’t coded the previous part, grab the code from GitHub and continue from there.

The end result of this episode is also available for download.

Cleaning up

In the previous episode we have created a few test controllers and other files that we need to clean up before we continue. These are the following:

  • BookController
  • CreateBookRequest
  • ExceptionTestController
  • TestController
  • TestResponse

In other words there will be three classes left:

  • ApiException
  • BlogApplication
  • ErrorController

Designing a model

Before we jump into all the nitty-gritty details of setting up the actual database schema, it is always a good idea how we imagine our data model purely from a business perspective.

In our case, since we are designing a blog we will need, at the very least, a BlogPost entity. Let’s define this entity in code as follows:

package at.pasztor.backend;

public class BlogPost {
    public final String slug;
    public final String title;
    public final String content;

    public BlogPost(
            String slug,
            String title,
            String content
    ) {
        this.slug = slug;
        this.title = title;
        this.content = content;
    }
}

This will be our very basic data model we will be working with. Now, this blog post needs to be created, edited, deleted and we will need to be able to list the blog posts.

Creating the API

Now that we have the blog post entity, let’s also create a controller for it, the BlogPostController:

package at.pasztor.backend;


import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/posts")
public class BlogPostController {
    @RequestMapping(
            method = RequestMethod.POST,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost create(
            @RequestBody
            BlogPostCreateRequest request
    ) {
        return null;
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost get(
            @PathVariable
            String slug
    ) {
        return null;
    }

    @RequestMapping(
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public List<BlogPost> list() {
        return null;
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.PATCH,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost update(
            @PathVariable
            String slug,
            @RequestBody
            BlogPostUpdateRequest request
    ) {
        return null;
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.DELETE,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public void delete(
            @PathVariable
            String slug
    ) {

    }
}

These are the basic methods for for Create, Read, Update and Delete, or if you want to be brief, CRUD. Nothing really surprising, but it is worth noting that if you look closely both the class and the methods have @RequestMapping annotations. These annotations will combine and the path of the request will be, for example, /posts/{slug}.

Now on to the two request objects. The BlogPostCreateRequest class will look like this:

package at.pasztor.backend;

public class BlogPostCreateRequest {
    public final String slug;
    public final String title;
    public final String content;

    public BlogPostCreateRequest(
            @JsonProperty(required = true)
            String slug,
            @JsonProperty(required = true)
            String title,
            @JsonProperty(required = true)
            String content
    ) {
        this.slug = slug;
        this.title = title;
        this.content = content;
    }
}

Pretty straight forward, but pay attention to the @JsonProperty annotations. By default the required field is false, which would lead to the field being null if the user does not pass a value for them. To avoid this we declare them as required, so a missing field will cause a HTTP 400 (Bad Request) error.

Now on to the update request. Here we do not mark the fields as required as we want the user to be able to update only some fields:

package at.pasztor.backend;

import org.springframework.lang.Nullable;

public class BlogPostUpdateRequest {
    @Nullable
    public final String title;
    @Nullable
    public final String content;

    public BlogPostUpdateRequest(
            @Nullable
            String title,
            @Nullable
            String content
    ) {
        this.title = title;
        this.content = content;
    }
}

The @Nullable annotation here helps with IntelliJ warning us about potentially missing null checks.

So far so good, we have the basic API skeleton ready.

Tip: This may be a good point to move your API-related classes into a separate package.

Starting the storage layer

Now we need to create a storage layer. Preferably, we will follow the clean code principles and leave our options open to use different storage systems later, so we will put them behind an interface:

package at.pasztor.backend.post.storage;

import at.pasztor.backend.post.entity.BlogPost;
import java.util.List;

public interface BlogPostStorage {
    void store(BlogPost post);
    BlogPost getBySlug(String slug) throws ApiException;
    void delete(BlogPost post);
    List<BlogPost> list();
}

This interface will allow us to create different database connectors later. This should cover all our basic needs for now, but we may end up writing custom queries later.

To access databases, for now we are going to use the Java Persistence API, an ORM that does a great job at mapping database engine data types to Java data types. We can edit our pom.xml to that effect:

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

Now, for our choice of database. As a first try I recommend setting up the H2 database, which is an in-memory database engine that can automatically run with your application. To do that we have to pull in the H2 database as a dependency as well:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.199</version>
</dependency>

As a final step for the setup we will need to configure the H2 database engine. Later on we will need to change this as for production we want a different database. To add the configuration we will create a file src/main/resources/application.properties with the following content:

# JDBC URL
spring.datasource.url=jdbc:h2:mem:db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE
# We are using the H2 driver
spring.datasource.driver-class-name=org.h2.Driver
# We use the H2 dialect
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.H2Dialect

# Create the database schema automatically
spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.hbm2ddl.auto=update

If you want to learn more about database initialization take a look at the Spring Boot documentation

This configuration does two things: first of all, with our application the H2 database server will be started, and second, the database schema will be initialized automatically from our entities.

Annotating our entities

The next step is to change the BlogPost entity and add the annotations required for JPA to work.

package at.pasztor.backend.post.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity(name = "posts")
@Table(name = "posts")
public class BlogPost {
    @Id
    @Column(nullable = false)
    public final String slug;
    @Column(nullable = false)
    public final String title;
    @Column(nullable = false, length = 65535)
    public final String content;

    public BlogPost(
            String slug,
            String title,
            String content
    ) {
        this.slug = slug;
        this.title = title;
        this.content = content;
    }
}

These annotations tell JPA how to create the table structure and also how to access the database. There is one more thing we have to do, which may be a bit weird: we have to create a constructor with no parameters.

Wait, what? Yes. JPA creates an instance using the Java reflection API, and then forcefully changes the fields on the BlogPost class. This does not affect any of our other code, but we need to create the constructor for JPA nevertheless. Luckily, we can create this constructor declared as private, so we won’t mistakenly use it.

package at.pasztor.backend.post.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity(name = "posts")
@Table(name = "posts")
public class BlogPost {
    @Id
    @Column(nullable = false)
    public final String slug;
    @Column(nullable = false)
    public final String title;
    @Column(nullable = false, length = 65535)
    public final String content;

    //Only for JPA
    @SuppressWarnings("unused")
    private BlogPost() {
        slug = "";
        title = "";
        content = "";
    }

    public BlogPost(
            String slug,
            String title,
            String content
    ) {
        this.slug = slug;
        this.title = title;
        this.content = content;
    }
}

Implementing the database connector

JPA, thankfully, makes it very easy to use it. We simply have to declare an interface we’ll call JpaBlogPostRepository and it will extend JPAs CrudRepository. JPA will then provide the implementation, we don’t have to worry about that.

package at.pasztor.backend.post.storage;

import at.pasztor.backend.post.entity.BlogPost;
import org.springframework.data.repository.CrudRepository;

public interface JpaBlogPostRepository extends CrudRepository<BlogPost, String> {
}

Note on the parameters: CrudRepository requires two type-parameters. The first is the type of the entity being stored. The second is the type of the ID field annotated with @Id.

Now we can create an implementation for our storage interface:

package at.pasztor.backend.post.storage;

import at.pasztor.backend.ApiException;
import at.pasztor.backend.post.entity.BlogPost;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Service
public class JpaBlogPostStorage implements BlogPostStorage {
    private final JpaBlogPostRepository repository;

    @Autowired
    public JpaBlogPostStorage(JpaBlogPostRepository repository) {
        this.repository = repository;
    }

    @Override
    public void store(BlogPost post) {
        repository.save(post);
    }

    @Override
    public BlogPost getBySlug(String slug) throws ApiException {
        Optional<BlogPost> post = repository.findById(slug);
        if (!post.isPresent()) {
            throw new ApiException(HttpStatus.NOT_FOUND, ApiException.ErrorCode.NOT_FOUND, "The blog post you requested was not found.");
        }
        return post.get();
    }

    @Override
    public void delete(BlogPost post) {
        repository.delete(post);
    }

    @Override
    public List<BlogPost> list() {
        Iterable<BlogPost> posts = repository.findAll();
        ArrayList<BlogPost> postList = new ArrayList<>();
        posts.forEach(postList::add);
        return postList;
    }
}

Notice the @Service annotation. This annotation will tell Spring Boot to automatically create a copy of this class and inject it to other classes, such as constructors, as needed. These are also referred to as beans or components.

Connecting the API

Finally it’s time to connect up our API. Remember the BlogPostController? Now it’s time to update it:

package at.pasztor.backend.post.api;

import at.pasztor.backend.ApiException;
import at.pasztor.backend.post.entity.BlogPost;
import at.pasztor.backend.post.storage.BlogPostStorage;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/posts")
public class BlogPostController {
    private final BlogPostStorage storage;

    @Autowired
    public BlogPostController(BlogPostStorage storage) {
        this.storage = storage;
    }

    @RequestMapping(
            method = RequestMethod.POST,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost create(
            @RequestBody
            BlogPostCreateRequest request
    ) {
        BlogPost post = new BlogPost(
                request.slug,
                request.title,
                request.content
        );
        storage.store(post);
        return post;
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost get(
            @PathVariable
            String slug
    ) throws ApiException {
        return storage.getBySlug(slug);
    }

    @RequestMapping(
            method = RequestMethod.GET,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public List<BlogPost> list() {
        return storage.list();
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.PATCH,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public BlogPost update(
            @PathVariable
            String slug,
            @RequestBody
            BlogPostUpdateRequest request
    ) throws ApiException {
        BlogPost post = storage.getBySlug(slug);

        String title = request.title==null?post.title:request.title;
        String content = request.content==null?post.content:request.content;

        post = new BlogPost(
                post.slug,
                title,
                content
        );
        storage.store(post);
        return post;
    }

    @RequestMapping(
            value = "/{slug}",
            method = RequestMethod.DELETE,
            consumes = MediaType.APPLICATION_JSON_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE
    )
    public void delete(
            @PathVariable
            String slug
    ) throws ApiException {
        storage.delete(storage.getBySlug(slug));
    }
}

Pretty straight forward, right?

Custom queries

What if you wanted to add custom queries to your repository? For example, how about a title search function?

package at.pasztor.backend.post.storage;

import at.pasztor.backend.post.entity.BlogPost;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

public interface JpaBlogPostRepository extends CrudRepository<BlogPost, String> {
    @Query("SELECT p FROM posts p WHERE p.title LIKE CONCAT('%', CONCAT(?1, '%'))")
    Iterator<BlogPost> getByTitle(String title);
}

Typical question

How do you generate IDs?

In a classic approach record identifiers would be generated by the database. However, not all database types are capable of doing that, and more importantly, having sequential or semi-sequential identifiers makes it easy for would-be attackers to predict record IDs. In my applications instead I use UUID identifiers, which are basically impossible to predict. These UUIDs are generated in my Java application instead of the database.

How do you do foreign keys?

I prefer not to rely on the database to handle foreign keys because I may have the need to move one entity to a different kind of database. If I had joins or foreign keys this would be quite hard.

Next up

In our next installment we will take a look at adding OpenApi to our blog API. Stay tuned.

Janos Pasztor

I'm a DevOps engineer with a strong background in both backend development and operations, with a history of hosting and delivering content.

I run an active DevOps and development community on Discord, come in and say hi!

Join the community

Discord

Subscribe

Facebook Facebook Twitter Twitter GitHub GitHub
YouTube YouTube RSS Atom Feed
Do you want more? Click the buttons below!