Spring Boot with PostgreSQL, Flyway, and JSONB

Spring Boot with PostgreSQL, Flyway, and JSONB

In this tutorial, you are going to learn more about PostgreSQL and how to integrate it with a Spring Boot application. You will learn how to install a simple PostgreSQL instance using Docker and how to connect a Spring Boot application to it. After that, you’ll create a simple database schema and add some data to it. Next, I’ll show you how to create SQL files to deliver database changes, which are more suitable for enterprise applications. To finish, you will learn how to use PostgreSQL JSONB data structure and use PostgreSQL as a NoSQL database.

In this tutorial, you are going to learn more about PostgreSQL and how to integrate it with a Spring Boot application. You will learn how to install a simple PostgreSQL instance using Docker and how to connect a Spring Boot application to it. After that, you’ll create a simple database schema and add some data to it. Next, I’ll show you how to create SQL files to deliver database changes, which are more suitable for enterprise applications. To finish, you will learn how to use PostgreSQL JSONB data structure and use PostgreSQL as a NoSQL database.

Let’s dig in!

Get Started with PostgreSQL via Docker

Initially proposed in the 70s, the RDBMS (Relational Database Management System) has grown in popularity through the years as computing processing power and storage capacity has increased. A critical characteristic of RDBMS is the support for ACID transactions (Atomicity, Consistency, Isolation, Durability) which guarantee data consistency even in a concurrent environment without the developer needing to be fully aware.

PostgreSQL is one of the most famous RDBMS around. Technically speaking it is also one of the most advanced relational database systems available. Why is that? Postgres means Post Ingres or the successor of Ingres, an older database that paved the way to the more famous Microsoft SQL Server and other products.

You will need PostgreSQL installed to complete the tutorial. To install and test PostgreSQL, I recommend using Docker:

docker pull postgres:11
docker run --name dev-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:11
# CREATE db coursedb
docker exec dev-postgres psql -U postgres -c"CREATE DATABASE coursedb" postgres


NOTE: You will need Docker installed for these commands to work.

The command above should run in any Linux, Windows, or MacOS distribution that has an instance of Docker installed. The first line pulls PostgreSQL version 11; the second line initiates a new instance of it with the name dev-postgres, running on port 5432. The last line executes a DDL command to create the database coursedb into the instance. Keep in mind, you are not creating a volume for the storage data if the container is deleted, all its data will be deleted as well.

Create a Spring Boot App with PostgreSQL

First, you need to create a new project with the required dependencies. You can use Spring Initialzr for this.

Configure your project as shown in the image above:

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

Download the file and unzip it. Then, simply run the command below:

./mvnw spring-boot:run


NOTE: Depending on your operating system, you might need to change ./mvnw to mvnw.

Running mvnw will download Maven, all dependencies, and run the application goal (spring-boot:run). It will likely fail because you do not have a PostgreSQL database configured. Let’s fix this.

Add Database Configuration for PostgreSQL

You now have a system that has database dependencies but does not know where to connect. First, change your main class (probably com.okta.developer.postgresql.PostgresqlApplication) and add the annotation @EnableTransactionManagement to it, like this:

package com.okta.developer.postgresql;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableTransactionManagement
public class PostgresqlApplication {

    public static void main(String[] args) {
        SpringApplication.run(PostgresqlApplication.class, args);
    }
}


Configuration annotations in Spring allow you to set up your application using type safe code. This means if something becomes inconsistent with your configuration, it will show compilation errors. The feature is a nice evolution from the older XML-based configuration Spring used to have. Previously, it was possible to get runtime errors since XML and your code was not linked.

Update src/main/resources/application.properties to define your database connection properties:

spring.datasource.url=jdbc:postgresql://192.168.99.100:5432/coursedb
spring.datasource.username=postgres
spring.datasource.password=mysecretpassword
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create


Here’s a quick explanation of each property:

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

What happens if you start up the application again?

./mvnw spring-boot:run


Now the application starts and you should be able to open [http://localhost:8080](http://localhost:8080 "http://localhost:8080") in your browser. Nothing nice happens as you haven’t written any code for the UI yet. You may see an error stack on initialization: don’t worry, it is simply Hibernate telling the JDBC driver does not support a feature (method createClob).

Create JPA Entities and Data Access Objects

OK, now let’s do some real work.

First of all, let’s look at this tutorial’s schema. It has two entities:

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

Before changing the code, add two new dependencies in your pom.xml file:

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>


Project Lombok adds a series of helpful features that simplify a developer’s life like getter/setter and constructor autogeneration. If you are using an IDE to develop this tutorial, you must install a plugin to avoid compilation problems.

Spring Data REST is a handy dependency that creates an HTTP interface for your repositories. You are going to use the HTTP interface to test your code.

To help your entities, you’ll create a superclass that handles the primary key in the same way for every entity we create. PostgreSQL has support to UUID, so you are going to use it instead of the commonly used auto-increment integer. UUID is helpful to avoid a normal attack in which the hacker tries to increase or decrease an entity ID to discover new information.

Create the class EntityWithUUID in package com.okta.developer.postgresql.entities:

package com.okta.developer.postgresql.entities;

import org.hibernate.annotations.Type;

import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import java.util.UUID;

@MappedSuperclass
public class EntityWithUUID {
    @Id @Type(type = "pg-uuid")
    private UUID id;

    public EntityWithUUID() {
        this.id = UUID.randomUUID();
    }
}


There are some excellent annotations here:

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

Now, create the two entities in the com.okta.developer.postgresql.entities package:

Teacher.java

package com.okta.developer.postgresql.entities;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Entity;

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher extends EntityWithUUID {
    private String name;
    private String pictureURL;
    private String email;
}


Course.java

package com.okta.developer.postgresql.entities;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Course extends EntityWithUUID {

    private String name;
    private int workload;
    private short rate;
    @ManyToOne
    @JoinColumn(foreignKey = @ForeignKey(name = "fk_course_teacher"))
    private Teacher teacher;
}


The annotations have a significant influence in these classes:

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

So far, so good. Now, create a DAO (Data Access Object, also called a Repository by Spring) class for each entity in the com.okta.developer.postgresql.dao package:

CourseDAO.java

package com.okta.developer.postgresql.dao;

import com.okta.developer.postgresql.entities.Course;
import org.springframework.data.repository.CrudRepository;
import java.util.UUID;

public interface CourseDAO extends CrudRepository<Course, UUID> {}


TeacherDAO.java

package com.okta.developer.postgresql.dao;

import com.okta.developer.postgresql.entities.Teacher;
import org.springframework.data.repository.CrudRepository;
import java.util.UUID;

public interface TeacherDAO extends CrudRepository<Teacher, UUID> {}


You now have DAOs for each entity. You may be asking, where is the implementation for them? Spring Data has some intelligent ways to handle this. Since your interfaces extend CrudRepository, Spring automatically generates the concrete class with all CRUD-related operations available!

To fill some data, create a new service class called DataFillerService. It will be responsible for inserting data in the database as soon as the application starts.

DataFillerService.java

package com.okta.developer.postgresql.service;

import com.okta.developer.postgresql.dao.CourseDAO;
import com.okta.developer.postgresql.dao.TeacherDAO;
import com.okta.developer.postgresql.entities.Course;
import com.okta.developer.postgresql.entities.Teacher;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.PostConstruct;

@Service
public class DataFillerService {
    private final CourseDAO courseDAO;
    private final TeacherDAO teacherDAO;
    public DataFillerService(CourseDAO courseDAO, TeacherDAO teacherDAO) {
        this.courseDAO = courseDAO;
        this.teacherDAO = teacherDAO;
    }
    @PostConstruct
    @Transactional
    public void fillData(){
        Teacher pj = new Teacher(
                "Professor Jirafales",
                "https://upload.wikimedia.org/wikipedia/commons/thumb/d/d1/Ruben2017.jpg/245px-Ruben2017.jpg",
                "[email protected]_.com"
        );
        Teacher px = new Teacher(
                "Professor X",
                "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcS9uI1Cb-nQ2uJOph4_t96KRvLSMjczAKnHLJYi1nqWXagvqWc4",
                "[email protected]_.com"

        );
        teacherDAO.save(pj);
        teacherDAO.save(px);
        // create courses
        Course mathematics = new Course("Mathematics", 20, (short) 10, pj);
        Course spanish = new Course("Spanish", 20, (short) 10, pj);
        Course dealingWithUnknown = new Course("Dealing with unknown", 10, (short) 100, pj);
        Course handlingYourMentalPower = new Course("Handling your mental power", 50, (short) 100, pj);
        Course introductionToPsychology = new Course("Introduction to psychology", 90, (short) 100, pj);
        courseDAO.save(mathematics);
        courseDAO.save(spanish);
        courseDAO.save(dealingWithUnknown);
        courseDAO.save(handlingYourMentalPower);
        courseDAO.save(introductionToPsychology);
    }
}


The method fillData is automatically called by Spring Context as soon the application context finishes loading. It uses the @Transaction annotations to indicate the entire method must run inside a transaction. That way, if any instruction fails, the entire method will be rolled back. As you can see, CourseDAO and TeacherDAO has some CRUD methods available.

Now, you can test if your application is working:

./mvnw spring-boot:run


The application creates several REST endpoints to access your DAO’s method. Try some commands:

curl http://localhost:8080/courses
curl http://localhost:8080/teachers


Secure Spring Data REST with OAuth 2.0

You shouldn’t expose your database structure without proper authentication. Let’s solve this by creating an OAuth 2.0 Resource Server. A resource server is a service working in the infrastructure that has no login page, and it is used for server-to-server communications. In other words: it needs credentials but does not handle how they are acquired.

First, you’ll need to add a dependency on the Okta Spring Boot starter:

<dependency>
    <groupId>com.okta.spring</groupId>
    <artifactId>okta-spring-boot-starter</artifactId>
    <version>1.1.0</version>
</dependency>


Then add a WebSecurityConfigurerAdapter to PostgresqlApplication:

package com.okta.developer.postgresql;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableTransactionManagement
public class PostgresqlApplication {

    public static void main(String[] args) {
        SpringApplication.run(PostgresqlApplication.class, args);
    }

    @Configuration
    static class OktaOAuth2WebSecurityConfigurerAdapter extends WebSecurityConfigurerAdapter {

        @Override
        protected void configure(HttpSecurity http) throws Exception {
            http
                .authorizeRequests().anyRequest().authenticated()
                .and()
                .oauth2ResourceServer().jwt();
        }
    }
}


The OktaOAuth2WebSecurityConfigurerAdapter class defines the security configuration.

  • Project Type: Maven Project
  • Group: com.okta.developer
  • Artifact: postgresql
  • Dependencies: Web, JPA, PostgreSQL

Finally, add two new properties in your application.properties file:

okta.oauth2.issuer=https://{yourOktaDomain}/oauth2/default
okta.oauth2.clientId={yourClientId}


These properties are going to tell Spring and Okta where to find the OAuth 2.0 issuer and identify the client ID for this application…

… Issuer, client ID, what am I talking about? To test the application, you need to create a free-forever account on Okta and set up a few things.

In the Okta dashboard, create an application of type Service, which indicates a resource server that does not have a login page or any way to obtain new tokens.

Click Next, type the name of your service, then click Done. You will be presented with a screen similar to the one below. Copy and paste the Client ID in to your application.properties file.

Now, start your application as before:

./mvnw spring-boot:run


Run curl (the -v attribute will return the response headers):

> curl -v http://localhost:8080/courses
< HTTP/1.1 401
< Set-Cookie: JSESSIONID=6AA0A042FB1D69CC1CB9747820FDF5E1; Path=/; HttpOnly
< WWW-Authenticate: Bearer
< X-Content-Type-Options: nosniff
< X-XSS-Protection: 1; mode=block
< Cache-Control: no-cache, no-store, max-age=0, must-revalidate
< Pragma: no-cache
< Expires: 0
< X-Frame-Options: DENY
< Content-Length: 0
< Date: Thu, 20 Dec 2018 04:46:21 GMT


The server returns a 401 HTTP code, which means you are unauthorized. Now, let’s create a valid token. An easy way to achieve a token is to generate one using OpenID Connect .

First, you’ll need to create a new web application in Okta to use this service:

Set the Login redirect URIs field to [https://oidcdebugger.com/debug](https://oidcdebugger.com/debug "https://oidcdebugger.com/debug") and Grant Type Allowed to Hybrid. Click Done and copy the client ID for the next step.

Now, on the OpenID Connect Debugger website, fill the form in like the screenshot below (do not forget to fill in the client ID for your recently created Okta web application):

Submit the form to start the authentication process. You’ll receive an Okta login form if you are not logged in or you’ll see the screen below with your custom token.

The token will be valid for one hour so you can do a lot of testing with your API. It’s simple to use the token, just copy it and modify the curl command to use it as follows:

export TOKEN={YOUR_TOKEN}
curl -v -H "Authorization: Bearer ${TOKEN}" http://localhost:8080/teachers


Pretty sweet, eh?!

Versioning Schema Changes in PostgreSQL with Flyway

Hibernate DDL creation is a nice feature for PoCs or small projects. For more significant projects that have a complex deployment workflow and features like version rollback in case of a significant issue, the solution is not sufficient.

There are several tools to handle database migrations, and one of the most popular is Flyway, which works flawlessly with Spring Boot. Briefly, Flyway looks for SQL scripts on your project’s resource path and runs all scripts not previously executed in a defined order. Flyway stores what files were executed into a particular table called SCHEMA_VERSION.

First, add Flyway as a dependency in your pom.xml. When Spring Boot detects Flyway on the classpath, it will run it on startup:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>


By default, Flyway looks at files in the format V$X__$DESCRIPTION.sql, where $X is the migration version name, in folder src/main/resources/db/migration. Create two files: one for the DDL and another for sample data:

V1__ddl.sql

CREATE TABLE course
  (
     id         UUID NOT NULL,
     NAME       VARCHAR(255),
     rate       INT2 NOT NULL,
     workload   INT4 NOT NULL,
     teacher_id UUID,
     PRIMARY KEY (id)
  );

CREATE TABLE teacher
  (
     id         UUID NOT NULL,
     email      VARCHAR(255),
     NAME       VARCHAR(255),
     pictureurl VARCHAR(255),
     PRIMARY KEY (id)
  );

ALTER TABLE course
  ADD CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher;


V2__data.sql

INSERT INTO teacher
            (id,
             email,
             NAME, 
             pictureurl)
VALUES      ( '531e4cdd-bb78-4769-a0c7-cb948a9f1238',
              '[email protected]_.com',
              'Profesor Jirafales',
              'https://upload.wikimedia.org/wikipedia/commons/thumb/d/d1/Ruben2017.jpg/245px-Ruben2017.jpg');

INSERT INTO teacher 
            (id,
             email,
             NAME, 
             pictureurl)
VALUES      ('6924b3ad-a7e7-4a9a-8773-58f89ef88509',
             '[email protected]_.com',
             'Professor X', 
             'https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcS9uI1Cb-nQ2uJOph4_t96KRvLSMjczAKnHLJYi1nqWXagvqWc4');

INSERT INTO course 
            (id,
             NAME,
             rate, 
             teacher_id, 
             workload)
VALUES      ('aeebbc96-52be-43fa-8c01-61b9fbca8fd7',
             'Mathematics',
             10,
             '531e4cdd-bb78-4769-a0c7-cb948a9f1238',
             20);

INSERT INTO course 
            (id,
             NAME,
             rate, 
             teacher_id, 
             workload)
VALUES      ('a6e54dad-a5a6-46c6-92b0-d61a78abb142',
            'Spanish',
             10, 
             '531e4cdd-bb78-4769-a0c7-cb948a9f1238',
             1)  ;

INSERT INTO course 
            (id,
             NAME,
             rate, 
             teacher_id, 
             workload)
VALUES      ('13710917-7469-4bd7-91cc-af8df36213c9',
             'Dealing with unknown',
             100, 
             '6924b3ad-a7e7-4a9a-8773-58f89ef88509',
             10) ;

INSERT INTO course 
            (id,
             NAME,
             rate, 
             teacher_id, 
             workload)
VALUES      ('c5e24451-86d3-4f20-ae06-55810c3cf350',
             'Handling your mental power',
             50, 
             '6924b3ad-a7e7-4a9a-8773-58f89ef88509',
             1000) ;

INSERT INTO course 
            (id,
             NAME,
             rate, 
             teacher_id,
             workload)
VALUES      ('d5063295-f1f6-44d3-8e3a-5ba5d8fb46eb',
             'Introduction to psychology',
             100, 
             '6924b3ad-a7e7-4a9a-8773-58f89ef88509',
             90);


Before starting the project again, delete the class DataFillerService since these SQL files will now import all data.

In application.properties change the ddl-auto configuration to validate:

spring.jpa.hibernate.ddl-auto=validate


This causes Hibernate to validate the schema to see if it matches with what’s defined in Java. If no match is found, the application will not start.

Delete and create a new PostgreSQL database instance, since the first instance was created using JPA.

docker rm -f dev-postgres
docker run --name dev-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:11
docker exec dev-postgres psql -U postgres -c"CREATE DATABASE coursedb" postgres


Now, start Spring Boot again:

./mvnw spring-boot:run


You will see the same data exposed, but if you have a look into the logs, will notice Flyway runs the database migration:

2018-11-25 23:50:12.941  INFO 30256 --- [           main] o.f.core.internal.command.DbValidate     : Successfully validated 2 migrations (execution time 00:00.037s)
2018-11-25 23:50:12.960  INFO 30256 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: "public"."flyway_schema_history"
2018-11-25 23:50:13.010  INFO 30256 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema "public": << Empty Schema >>
2018-11-25 23:50:13.013  INFO 30256 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 1 - ddl
2018-11-25 23:50:13.046  INFO 30256 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema "public" to version 2 - data
2018-11-25 23:50:13.066  INFO 30256 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 2 migrations to schema "public" (execution time 00:00.109s)


Scratch the NoSQL Surface with Spring Boot and PostgreSQL’s JSONB Data Structure

NoSQL databases are, as the name says, databases that do not store their data with relationships. There are many types of NoSQL databases: graph databases, key-value stores, document, etc. Generally speaking, one of the most significant advantages of this kind of database is the lack of schema enforcement (you can mix a different kind of data), different levels of data consistency and better performance in some cases.

PostgreSQL adopted some data types to handle JSON data inside its data structures. This datatype is called JSONB, and this section shows how it is possible to use it with Spring Boot and without changing your current schema.

First, create a new migration file V3__teacher_reviews.sql.

ALTER TABLE teacher
  ADD COLUMN reviews jsonb


To properly work with this custom datatype, you need to add a dependency that correctly handles it. Add the following to pom.xml:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.3.4</version>
</dependency>


The column reviews stores a JSON array with all reviews a teacher received. First, you need to change the superclass to define the new datatype correctly:

EntityWithUUID.java

import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

import javax.persistence.Id;
import javax.persistence.MappedSuperclass;
import java.util.UUID;

@MappedSuperclass
@TypeDefs({
    @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class EntityWithUUID {
    @Id
    @Type(type = "pg-uuid")
    private UUID id;

    public EntityWithUUID() {
        this.id = UUID.randomUUID();
    }
}


Now, create a class called Review in the entities package:

package com.okta.developer.postgresql.entities;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.time.LocalDate;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Review implements Serializable {
    private String author;
    private String review;
    private LocalDate date;
}


This class needs to implement Serializable as it is stored in JSON format. Now, change Teacher class to have a reviews property:

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.Type;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import java.util.List;

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher extends EntityWithUUID {

    private String name;
    private String pictureURL;
    private String email;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    @Basic(fetch = FetchType.LAZY)
    private List<Review> reviews;
}


Now you have set a list of data type jsonb but also added a fetch = LAZY property. The property tells Hibernate NOT to retrieve the attribute value unless asked. Without the property, every call to a teacher instance will process JSON and create review objects. Hibernate needs a Maven plugin to work with lazy attributes properly. Edit the pom.xml file and add the following snippet in the <plugins> section.

<plugin>
    <groupId>org.hibernate.orm.tooling</groupId>
    <artifactId>hibernate-enhance-maven-plugin</artifactId>
    <version>5.4.1.Final</version>
    <executions>
        <execution>
            <configuration>
                <failOnError>true</failOnError>
                <enableLazyInitialization>true</enableLazyInitialization>
            </configuration>
            <goals>
                <goal>enhance</goal>
            </goals>
        </execution>
    </executions>
</plugin>


OK, you are almost there. You just need to add some methods to add reviews to a teacher. I decided to take a more MVC approach here and create a Service and a Controller.

TeacherService.java

package com.okta.developer.postgresql.service;

import com.okta.developer.postgresql.entities.Review;
import javax.validation.constraints.NotNull;

public interface TeacherService {
    /**
     *
     * @param teacherID
     * @param review
     * @throws javax.persistence.EntityNotFoundException
     */
    void addReview(@NotNull String teacherID, @NotNull Review review);
}


SimpleTeacherService.java

package com.okta.developer.postgresql.service;

import com.okta.developer.postgresql.dao.TeacherDAO;
import com.okta.developer.postgresql.entities.Review;
import com.okta.developer.postgresql.entities.Teacher;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import javax.persistence.EntityNotFoundException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Objects;
import java.util.UUID;

@Service
public class SimpleTeacherService implements TeacherService {
    private final TeacherDAO teacherDAO;

    public SimpleTeacherService(TeacherDAO teacherDAO) {
        this.teacherDAO = teacherDAO;
    }

    @Override
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void addReview(String teacherID, Review review) {
        Objects.requireNonNull(teacherID);
        Objects.requireNonNull(review);
        Teacher teacher = teacherDAO
                .findById(UUID.fromString(teacherID))
                .orElseThrow(() -> new EntityNotFoundException(teacherID));
        review.setDate(LocalDate.now());
        if(teacher.getReviews() == null){
            teacher.setReviews(new ArrayList<>());
        }
        teacher.getReviews().add(review);
        teacherDAO.save(teacher);
    }
}


TeacherController.java

package com.okta.developer.postgresql.controllers;

import com.okta.developer.postgresql.entities.Review;
import com.okta.developer.postgresql.service.TeacherService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import javax.persistence.EntityNotFoundException;

@RestController
public class TeacherController {

    private final TeacherService teacherService;

    public TeacherController(TeacherService teacherService) {
        this.teacherService = teacherService;
    }

    @PostMapping("/teachers/{id}/review")
    public ResponseEntity addReview(@RequestBody Review review, @PathVariable("id") String teacherID){
        try {
            teacherService.addReview(teacherID, review);
            return ResponseEntity.ok().build();
        }
        catch (EntityNotFoundException e){
            return ResponseEntity.notFound().build();
        }
    }
}


In SimpleTeacherService, note that there is an annotation @Transactional(isolation = Isolation.SERIALIZABLE). This means every call to this method runs in the most secure transaction level. In other words, once the code reads the reviews JSON value, no other code can read or write on the reviews column (it is blocked) until the transaction finishes. Why’s that? Since you are manipulating JSON content if a concurrent transaction updates the column, one of them will have consistency problems.

You can now start your app:

./mvnw spring-boot:run


And test the review upload by running CURL:

curl -X POST \
  -H "Authorization: Bearer $TOKEN"\
  http://localhost:8080/teachers/531e4cdd-bb78-4769-a0c7-cb948a9f1238/review \
  -H 'Content-Type: application/json' \
  -d '{
    "author" : "Raphael",
    "review" : "Test"
  }'


Learn More about Spring Boot, PostgreSQL, Hibernate, JPA, and Spring Data REST

In this tutorial, you learned how to integrate Spring Boot with PostgreSQL and use some advanced technologies like Flyway and JSONB. You can find the source code for this blog post on GitHub.

Spring Master Class - Beginner to Expert

Spring & Hibernate for Beginners

The Java Spring Tutorial: Learn Java’s Popular Web Framework

Learn Spring Boot - Rapid Spring Application Development

Spring MVC For Beginners : Build Java Web App in 25 Steps

The Complete Python & PostgreSQL Developer Course

SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners

In this video, we will go over the basics of the PostgreSQL. We will cover topics ranging from installations, to writing basic queries and retrieving data from tables. We will also explore the logic of joining tables to retrieve data and much more.

The course also covers the basics of creating tables, storing data with data types, and working with expressions, operators, and strings.

Topics also includes:

Installing PostgreSQL

Loading sample database

Creating database and tables

Performing CRUD operations

Joining Tables

Using aggregate and analytic functions

Creating views and triggers

What you’ll learn

Install PostgreSQL Server

Load sample database

Create a database

Create a table

Insert data into tables

Update existing records inside a table

Delete Records in a table

Remove duplicate records

Query data from a table

Create a subquery

Get data from multiple tables

Create and manage roles

Create a view

Create tablespace

Backup and restore database

Filter and sort data

Use various operators

Use aggregate and analytic functions

Create triggers

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

An Introduction to Queries in PostgreSQL

Build a Basic App with Spring Boot and JPA using PostgreSQL

Why We Moved From NoSQL MongoDB to PostgreSQL?

Learn Database Management System - Database modeling with Golang & PostgreSQL

Learn how to do all Database Management System directly from Golang to PostgreSQL.

Golang OR Go Programming language is modern programming language specialized in building server side applications.

Postgresql is world's most popular open source database management system.

Both are one of the best technologies to build server side modern applications.

So, In this course i will try to give introduction how you guys can use these both technologies togather to build robust backend services.

Remark: This series contains just golang and postgresql integration and workings, it doesn't contain how to build REST apis.

What you'll learn

  • People will learn how to connect to postgresql using golang and how to do DBMS directly from golang.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Golang & PostgreSQL

Google’s Go Essentials For Node.js / JavaScript Developers

Go Programming Language Tutorial | Golang Tutorial For Beginners | Go / Golang Crash Course

Learn Go Programming - Golang Tutorial for Beginners

MySQL vs PostgreSQL - What to Choose the Right Database for Your Project

Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners

Learn PostgreSQL - Full Course for Beginners

Build a Basic App with Spring Boot and JPA using PostgreSQL

Build a Basic App with Spring Boot and JPA using PostgreSQL

Build a Basic App with Spring Boot and JPA using PostgreSQL. Install PostgreSQL for JPA Persistence. Create a PostgreSQL Database for Your JPA Entities. Build a Spring Boot Resource Server. Add a Domain Class with Spring Data and JPA. Implement a CRUD Repository with Spring Data JPA.

Build a Basic App with Spring Boot and JPA using PostgreSQL. Install PostgreSQL for JPA Persistence. Create a PostgreSQL Database for Your JPA Entities. Build a Spring Boot Resource Server. Add a Domain Class with Spring Data and JPA. Implement a CRUD Repository with Spring Data JPA.

Every non-trivial application needs a way to save and update data: a resource server that is accessible via HTTP. Generally, this data must be secured. Java is a great language with decades of history in professional, enterprise development, and is a great choice for any application’s server stack. Within the Java ecosystem, Spring makes building secure resource servers for your data simple. When coupled with Okta, you get professionally maintained OAuth and JWT technologies easily integrated into Spring Boot using Spring Security.

In this post, you’re going to build a resource server using Spring Boot and Spring Data JPA. On top of that, you’re going to implement a group-based authentication and authorization layer using OAuth 2.0. If this sounds complicated - don’t worry! It’s not.

Before we dig in, let’s cover some background:

A resource server is a programmatic access point for your server’s functions and data (basically the same as an API server and/or possibly REST server).

JPA is the Java Persistence API, a specification for managing relational databases using Java. It describes an abstraction layer between Java classes and a relational database.

Spring Data JPA is a wrapper around JPA providers such as Hibernate. As you’ll see, it makes persisting your Java classes as simple as adding some annotations and creating a simple repository interface. No need to actually write persistence or retrieval methods! Another great benefit is that you can change the underlying database implementation transparently without having to change any code. For example, in this tutorial, you’ll be using Postgres, but later if you decided you’d rather use MySQL, all you’d have to do is change out some dependencies.

Install PostgreSQL for JPA Persistence

You’ll need to have PostgreSQL installed for this tutorial. If you don’t already have it installed, go to their downloads page and install it.

The next thing you’ll need to do is create a Postgres user and database for the project. For this, you can use the Postgres CLI, psql.

You should be able to run the following command: psql -V and get a response like:

psql (PostgreSQL) 11.12


Create a PostgreSQL Database for Your JPA Entities

Before you can use your database, you need to do a few things. You need to:

  1. Create a user for the app
  2. Set a password for that user
  3. Create a database for the app
  4. Grant privileges on the database for the user

This tutorial uses jpatutorial for the username and springbootjpa for the database name. Feel free to change these if you like, but you’ll have to remember to use your custom values throughout the tutorial.

Type psql from the terminal to enter the Postgres shell. Then enter the following command.

Create a user

create user jpatutorial;


The shell should respond with: CREATE ROLE

Don’t forget the semicolon! I would never, ever do this. I am definitely not speaking from experience. But if you don’t type in the semicolon psql doesn’t process the command and you can lose 20-30 minutes in frustrated confusion wondering what is happening until you do enter a semicolon, at which point it tries to process all of your commands.

Give the user a password

alter user jpatutorial with encrypted password '<your really secure password>';


The shell should respond with: ALTER ROLE.

Create the database

create database springbootjpa;


The shell should respond with: CREATE DATABASE.

Grant privileges

grant all privileges on database springbootjpa to jpatutorial;


The shell should respond with GRANT.

Finally, type \q to quit the shell, if you want.

If you want to read more about psql you can take a look at Postgres’ docs.

Build a Spring Boot Resource Server

Clone the starter project from the GitHub repository and check out the start branch:

git clone -b start https://github.com/oktadeveloper/okta-spring-boot-jpa-example.git


The starter project is a clean slate Spring Boot project with just a little bit of Postgres-specific configuration already in place. If you look in the build.gradle file you’ll see a PostgreSQL JPA connector dependency. You’ll also notice the file src/main/resources/hibernate.properties whose sole purpose is to get rid of an annoying warning/error that doesn’t really matter to us. The src/main/resources/application.yml file also has some properties pre-filled for you.

Go ahead and open the application.yml file and fill in the password you created for your database user. You should also update the username, database name, and port (if they are different).

spring:  
  jpa:  
    hibernate:  
      ddl-auto: create  
    database-platform: org.hibernate.dialect.PostgreSQLDialect  
  datasource:  
    url: "jdbc:postgresql://localhost:5432/springbootjpa"  
    username: jpatutorial  
    password: < your password >


The ddl-auto property specifies hibernate’s behavior upon loading. The options are:

  • validate: validates the schema but makes no changes
  • update: updates the schema
  • create: creates the schema, destroying any previous data
  • create-drop: like create, but also drops the schema when the session closes (useful for testing)

You’re using create. Each time the program is run, a new database is created, starting with fresh tables and data.

database-platform is actually unnecessary. Spring Data/Hibernate can autodetect the platform. However, without this property, if you run the app without having started your Postgres server, what you’ll get is a rather unhelpful error about not having added this config property instead of being told to start your server. This happens because Hibernate cannot autodetect the database platform, so complains about that before complaining about there not actually being a running server.

Run the app with ./gradlew bootRun. You should see something like this:

2018-11-21 09:27:50.233  INFO 31888 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2018-11-21 09:27:50.302  INFO 31888 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2018-11-21 09:27:50.308  INFO 31888 --- [           main] c.o.s.SpringBootJpaApplication           : Started SpringBootJpaApplication in 21.361 seconds (JVM running for 21.848)
<=========----> 75% EXECUTING [4m 26s]
> :bootRun


It doesn’t do much just yet, though. There are no domain models, resource repositories, or controller classes.

Add a Domain Class with Spring Data and JPA

A domain or model is the programmatic representation of the data you will be storing. The magic of Spring Data and JPA is that Spring can take a Java class and turn it into a database table for you. It will even autogenerate the necessary load and save methods. The best part is that this is (more or less) database independent.

You’re using PostgreSQL in this tutorial, and you could pretty easily switch it to MySQL if you wanted, just by changing a dependency in the build.gradle file. And, of course, creating a MySQL database and updating the necessary properties in the application.yml file. This is super useful for testing, development, and long-term maintenance.

Keep reading to learn how to develop a simple server to store types of kayaks.

Create a Java file in the com.okta.springbootjpa package called Kayak.java. Your kayak model will have a name, an owner, a value, and a make/model.

package com.okta.springbootjpa;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
@Data // Lombok: adds getters and setters
public class Kayak {

    public Kayak(String name, String owner, Number value, String makeModel) {
        this.name = name;
        this.owner = owner;
        this.value = value;
        this.makeModel = makeModel;
    }

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

    private final String name;

    private String owner;

    private Number value;

    private String makeModel;
}


This project uses Lombok to avoid having to code a bunch of ceremony getters and setters and whatnots. You can check out their docs, or more specifically for the @Data annotation you’re using.

The @Entity annotation is what tells Spring that this class is a model class and should be transformed into a database table.

Most properties can be mapped automatically. The id property, however, is decorated with a couple annotations because we need to tell JPA that this is the ID field and that it should be auto-generated.

Implement a CRUD Repository with Spring Data JPA

With the domain class defined, Spring knows enough to build the database table, but it doesn’t have any controller methods defined. There’s no output or input for the data. Spring makes adding a resource server trivial. In fact, it’s so trivial, you probably won’t believe it.

In the package com.okta.springbootjpa, create an interface called KayakRepository.java.

package com.okta.springbootjpa;

import org.springframework.data.repository.CrudRepository;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

@RepositoryRestResource
public interface KayakRepository extends CrudRepository<Kayak, Integer> {
}


That’s it!

You can now create, read, update, and delete kayaks from the resource server. In just a sec you’re going to do exactly that, but before you do, make one more change.

Add the following init() method to the SpringBootJpaApplication class:

package com.okta.springbootjpa;

import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import java.text.NumberFormat;
import java.text.ParseException;
import java.util.stream.Stream;

@SpringBootApplication
public class SpringBootJpaApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringBootJpaApplication.class, args);
    }

  @Bean
  ApplicationRunner init(KayakRepository repository) {

    String[][] data = {
        {"sea", "Andrew", "300.12", "NDK"},
        {"creek", "Andrew", "100.75", "Piranha"},
        {"loaner", "Andrew", "75", "Necky"}
    };

    return args -> {
      Stream.of(data).forEach(array -> {
        try {
          Kayak kayak = new Kayak(
              array[0],
              array[1],
                  NumberFormat.getInstance().parse(array[2]),
              array[3]
          );
          repository.save(kayak);
        }
        catch (ParseException e) {
          e.printStackTrace();
        }
      });
      repository.findAll().forEach(System.out::println);
    };
  }

}


This method will be run when the application starts. It loads some sample data into the resource server just to give you something to look at in the next section.

Test Your Spring Boot Resource Server

HTTPie is a great command line utility that makes it easy to run requests against the resource server. If you don’t have HTTPie installed, install it using brew install httpie. Or head over to their website and make it happen. Or just follow along.

Make sure your Spring Boot app is running. If it isn’t, start it using ./gradlew bootRun.

Run a GET request against your resource server: http :8080/kayaks, which is shorthand for http GET http://localhost:8080/kayaks.

You’ll see this:

HTTP/1.1 200
Content-Type: application/hal+json;charset=UTF-8
Date: Wed, 21 Nov 2018 20:39:11 GMT
Transfer-Encoding: chunked

{
    "_embedded": {
        "kayaks": [
            {
                "_links": {
                    "kayak": {
                        "href": "http://localhost:8080/kayaks/1"
                    },
                    "self": {
                        "href": "http://localhost:8080/kayaks/1"
                    }
                },
                "makeModel": "NDK",
                "name": "sea",
                "owner": "Andrew",
                "value": 300.12
            },
            {
                "_links": {
                    "kayak": {
                        "href": "http://localhost:8080/kayaks/2"
                    },
                    "self": {
                        "href": "http://localhost:8080/kayaks/2"
                    }
                },
                "makeModel": "Piranha",
                "name": "creek",
                "owner": "Andrew",
                "value": 100.75
            },
            {
                "_links": {
                    "kayak": {
                        "href": "http://localhost:8080/kayaks/3"
                    },
                    "self": {
                        "href": "http://localhost:8080/kayaks/3"
                    }
                },
                "makeModel": "Necky",
                "name": "loaner",
                "owner": "Andrew",
                "value": 75
            }
        ]
    },
    "_links": {
        "profile": {
            "href": "http://localhost:8080/profile/kayaks"
        },
        "self": {
            "href": "http://localhost:8080/kayaks"
        }
    }
}


This output gives you a pretty solid idea of the format of data that the Spring Boot resource returns. You can also add a new kayak using a POST.

Command:

http POST :8080/kayaks name="sea2" owner="Andrew" value="500" makeModel="P&H"


Reply:

HTTP/1.1 201
Content-Type: application/json;charset=UTF-8
Date: Wed, 21 Nov 2018 20:42:14 GMT
Location: http://localhost:8080/kayaks/4
Transfer-Encoding: chunked

{
    "_links": {
        "kayak": {
            "href": "http://localhost:8080/kayaks/4"
        },
        "self": {
            "href": "http://localhost:8080/kayaks/4"
        }
    },
    "makeModel": "P&H",
    "name": "sea2",
    "owner": "Andrew",
    "value": 500
}


If you list the kayaks again (http :8080/kayaks) you’ll see the new kayak among the listed items.

HTTP/1.1 200
Content-Type: application/hal+json;charset=UTF-8
Date: Wed, 21 Nov 2018 20:44:22 GMT
Transfer-Encoding: chunked

{
    "_embedded": {
        "kayaks": [
            ...
            {
                "_links": {
                    "kayak": {
                        "href": "http://localhost:8080/kayaks/4"
                    },
                    "self": {
                        "href": "http://localhost:8080/kayaks/4"
                    }
                },
                "makeModel": "P&H",
                "name": "sea2",
                "owner": "Andrew",
                "value": 500
            }
        ]
    },
    ...
}


You can also delete the kayak. Run this command: http DELETE :8080/kayaks/4 This deletes the kayak with ID = 4, or the kayak we just created. GET the list of kayaks a third time and you’ll see that it’s gone.

With very minimal code, using Spring Boot you can create a fully functioning resource server. This data is being persisted to your Postgres database.

You can verify this by using the Postgres command shell. At the terminal, type psql to enter the shell, then type the following commands.

Connect to the database:

\connect springbootjpa

psql (9.6.2, server 9.6.6)
You are now connected to database "springbootjpa" as user "cantgetnosleep".


Show the table contents:

SELECT * FROM kayak;

 id | make_model |  name  | owner  |                                                                                   value
----+------------+--------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | NDK        | sea    | Andrew | \xaced0005737200106a6176612e6c616e67...8704072c1eb851eb852
  2 | Piranha    | creek  | Andrew | \xaced0005737200106a6176612e6c616e672e...078704059300000000000
  3 | Necky      | loaner | Andrew | \xaced00057372000e6a6176612e6c616e67...7870000000000000004b
  5 | P&H        | sea2   | Andrew | \xaced0005737200116a6176612e6...08b0200007870000001f4
(4 rows)


A couple things to note. First, notice that value is being stored as a binary object because it was defined as a Number type instead of a primitive (double, float, or int). Second, remember that this data is being erased and the entire table is being recreated on every boot of the app because of the ddl-auto: create line in the application.yml file.

Set Up Authentication

Okta is a software-as-service identity, authentication, and authorization provider. While I have definitely worked on projects where outsourcing everything to SaaS providers created more problems than it promised to solve, authentication and authorization is a place where this model makes total sense. Online security is hard. Vulnerabilities are found and servers must be updated quickly. Standards change and code needs modification. All of these changes have the potential to create new vulnerabilities. Letting Okta handle security means that you can worry about the things that make your application unique.

To show you how easy it is to set up, you’re going integrate Okta OAuth and add token-based authentication to the resource server. If you haven’t already, head over to developer.okta.com and sign up for a free account. Once you have an account, open the developer dashboard and create an OpenID Connect (OIDC) application by clicking on the Application top-menu item, and then on the Add Application button.

Select Single-Page App.

The default application settings are great, except that you need to add a Login Redirect URI: a. You’re going to use this in a moment to retrieve a test token.

Also, note your Client ID, as you’ll need that in a moment.

Configure Your Spring Boot Resource Server for Token Authentication

Okta has made adding token authentication to Spring Boot super easy. They have a project called Okta Spring Boot Starter (check out the GitHub project) that simplifies the whole process to a few simple steps.

Add a couple dependencies to your build.gradle file.

compile('org.springframework.security.oauth.boot:spring-security-oauth2-autoconfigure:2.1.0.RELEASE')  
compile('com.okta.spring:okta-spring-boot-starter:0.6.1')


Add the following to the bottom of the build.gradle file (this resolves a logback logging dependency conflict).

configurations.all {  
  exclude group: 'org.springframework.boot', module: 'spring-boot-starter-logging'  
  exclude group: 'org.springframework.boot', module: 'logback-classic'  
}


Next, you need to add some configuration to your application.yml file, replacing {yourClientId} with the Client ID from your Okta OIDC application and {yourOktaDomain} with your Okta URL. Something like https://dev-123456.oktapreview.com.

okta:  
  oauth2:  
    issuer: https://{yourOktaDomain}/oauth2/default  
    client-id: {yourClientId}  
    scopes: openid profile email


Finally, you need to add the @EnableResourceServer annotation to your SpringBootVueApplication class.

import org.springframework.security.oauth2.config.annotation.web.configuration.EnableResourceServer;

@EnableResourceServer  // <- add me
@SpringBootApplication  
public class SpringBootJpaApplication {  

    public static void main(String[] args) {  
        SpringApplication.run(SpringBootJpaApplication.class, args);  
    }
    ...
}


Test the Protected Spring Boot Server

Stop your Spring Boot server and restart it using: ./gradlew bootRun

From the command line, run a simple GET request.

http :8080/kayaks


You’ll get a 401/unauthorized.

HTTP/1.1 401
Cache-Control: no-store
Content-Type: application/json;charset=UTF-8

{
    "error": "unauthorized",
    "error_description": "Full authentication is required to access this resource"
}


Generate an Access Token

To access the server now, you need a valid access token. You can use OpenID Connect Debugger to help you do this. In another window, open oidcdebugger.com.

Authorize URI: https://{yourOktaUrl}/oauth2/default/v1/authorize, with {yourOktaUrl} replaced with your actual Okta preview URL.

Redirect URI: do not change. This is the value you added to your OIDC application above.

Client ID: from the OIDC application you just created.

Scope: openid profile email.

State: any value you want to pass through the OAuth redirect process. I set it to {}.

Nonce: can be left alone. Nonce means “number used once” and is a simple security measure used to prevent the same request being used multiple times.

Response Type: token.

Response mode: form_post.

Click Send Request. If you are not logged into developer.okta.com, then you’ll be required to log in. If you are (as is likely) already logged in, then the token will be generated for your signed-in identity.

Use the Access Token

You use the token by including in an Authorization request header of type Bearer.

Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJXS3QzSVl1MlJZc3VJSzBBYUl3NkU4SDJfNVJr...


To make the request with HTTPie:

http :8080/kayaks 'Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJXS3QzSVl1...'


Add Group-based Authorization

Up until now, the authorization scheme has been pretty binary. Does the request carry a valid token or not. Now you’re going to add Group-based authentication. Note that despite being used interchangeably sometimes on websites of ill repute, roles and groups are not the same thing, and are different ways of implementing authorization.

A role is a collection of collection of permissions that a user can inherit. A group is a collection of users to which a set of standard permissions are assigned. However, in the scope of tokens and how you’re using Spring Security with JPA, the implementation is exactly the same; they’re both passed from the OAuth OIDC application as a string “authority” to Spring, so for the moment they’re essentially interchangeable. The difference would be in what is protected and how they are defined.

To use group-based authorization with Okta, you need to add a “groups” claim to your access token. Create an Admin group (Users > Groups > Add Group) and add your user to it. You can use the account you signed up with, or create a new user (Users > Add Person). Navigate to API > Authorization Servers, click the Authorization Servers tab and edit the default one. Click the Claims tab and Add Claim. Name it “groups”, and include it in the access token. Set the value type to “Groups” and set the filter to be a Regex of .*.

Create a new access token using OIDC Debugger. Take a look at your decoded token by going to jsonwebtoken.io and entering in your generated access token.

The payload will look a bit like this:

{
 "ver": 1,
 "jti": "AT.Hk8lHezJNw4wxey1czypDiNXJUxIlKmdT16MrnLGp9E",
 "iss": "https://dev-533919.oktapreview.com/oauth2/default",
 "aud": "api://default",
 "iat": 1542862245,
 "exp": 1542866683,
 "cid": "0oahpnkb44pcaOIBG0h7",
 "uid": "00ue9mlzk7eW24e8Y0h7",
 "scp": [
  "email",
  "profile",
  "openid"
 ],
 "sub": "[email protected]",
 "groups": [
  "Everyone",
  "Admin"
 ]
}


The groups claim carries the groups to which the user is assigned. The user you’re using to sign into the developer.okta.com website will also be a member of both the “Everyone” group and the “Admin” group.

To get Spring Boot and the resource server to play nicely with group-based authorization, you need to make a few changes to the code.

First, add a new Java class in the com.okta.springbootjpa package called SecurityConfiguration.

package com.okta.springbootjpa;

import org.springframework.security.config.annotation.method.configuration.EnableGlobalMethodSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;

@EnableWebSecurity
@EnableGlobalMethodSecurity(prePostEnabled = true)
public class SecurityConfiguration extends WebSecurityConfigurerAdapter {
}


This configuration class is required to enable the @PreAuthorize annotation that you’re going to use to protect the resource server based on group membership.

Next, add the @PreAuthorize annotation to the KayakRepository, like so:

...
import org.springframework.security.access.prepost.PreAuthorize;
...

@RepositoryRestResource  
@PreAuthorize("hasAuthority('Admin')")  
public interface KayakRepository extends CrudRepository<Kayak, Long> {  
}


Finally, in the SpringBootJpaApplication, delete the ApplicationRunner init(KayakRepository repository) method (or just comment out the @Bean annotation). If you skip this step, the build will fail with the following error:

 AuthenticationCredentialsNotFoundException: An Authentication object was not found in the SecurityContext


The @PreAuthorize annotation actually blocks the init() method from creating the bootstrapped data programmatically because no user is logged in. Thus with the method runs, it throws an error.

Notice that you’re using hasAuthority() in the @PreAuthorize annotation and not hasRole(). The difference is that hasRole() expects groups or roles to be in ALL CAPS and to have a ROLE_ prefix. This can be configured, of course, but hasAuthority() comes without this baggage and simply checks whatever claim you’ve defined as the okta.oauth2.roles-claim in your application.yml.

Test the Admin User in Your Spring Boot App

Restart your Spring Boot app (start with ./gradlew bootRun).

Try an unauthenticated GET request: http :8080/kayaks.

HTTP/1.1 401
Cache-Control: no-store
Content-Type: application/json;charset=UTF-8

{
    "error": "unauthorized",
    "error_description": "Full authentication is required to access this resource"
}


Try it using your token.

Command:

http :8080/kayaks 'Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJXS3QzSVl1MlJZc3VJSzBBYUl3NkU4SDJf...'


Reply:

HTTP/1.1 200
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Content-Type: application/hal+json;charset=UTF-8

{
    "_embedded": {
        "kayaks": []
    },
    "_links": {
        "profile": {
            "href": "http://localhost:8080/profile/kayaks"
        },
        "self": {
            "href": "http://localhost:8080/kayaks"
        }
    }
}


It worked! We don’t have any kayaks because we had to remove the init() method above, so the _embedded.kayaks array is empty.

TIP: going forward, if you don’t want to copy and paste the whole enormous token string, you can store it to a shell variable and reuse it like so:

TOKEN=eyJraWQiOiJldjFpay1DS3UzYjJXS3QzSVl1MlJZc3VJSzBBYUl3NkU4SDJf...
http :8080/kayaks 'Authorization: Bearer $TOKEN'


Create a Non-Admin User

To demonstrate group-based authorization, you need to create a new user on Okta that isn’t an admin. Go to the developer.okta.com dashboard.

From the top-menu, select Users and People.

Click the Add Person button.

Give the user a First Name, Last Name, and Username (which will also be the Primary Email). The values do not matter, and you won’t need to be able to check the email. You simply need to know the email address/username and password so you can log in to Okta in a minute.

Password: change the drop down to Set by admin.

Assign the user a password.

Click Save.

You’ve just created a user that is NOT a member of the Admin group but is a member of the default group Everyone.

Test Group-based Authorization in Your Spring Boot App

Log out of your Okta developer dashboard.

Return to the OIDC Debugger and generate a new token.

This time, log in as your new non-admin user. You’ll be asked to choose a security question, after which you’ll be redirected to the https://oidcdebugger.com/debug page where your token can be copied.

If you like, you can go to jsonwebtoken.io and decode your new token. In the payload, the sub claim will show the email/username of the user, and the groups claim will show only the Everyone group.

{
 ...
 "sub": "[email protected]",
 "groups": [
  "Everyone"
 ]
}


If you use the new token to make a request on the /kayaks endpoint, you’ll get a 403/Access Denied.

http :8080/kayaks 'Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJX...'

HTTP/1.1 403
...

{
    "error": "access_denied",
    "error_description": "Access is denied"
}


To demonstrate the real power of the @PreAuthorize annotation, create a method-level security constraint. Change the KayakRepository class to the following:

@RepositoryRestResource  
public interface KayakRepository extends CrudRepository<Kayak, Long> {  

    @PreAuthorize("hasAuthority('Admin')")  
    <S extends Kayak> S save(S entity);  

}


This restricts only the save() method to members of the Admin group. The rest of the repository will be restricted simply requiring authentication, but no specific group membership.

Restart your Spring Boot server. Run the same request again.

http :8080/kayaks 'Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJX...'

HTTP/1.1 200
...

{
    "_embedded": {
        "kayaks": []
    },
    "_links": {
        "profile": {
            "href": "http://localhost:8080/profile/kayaks"
        },
        "self": {
            "href": "http://localhost:8080/kayaks"
        }
    }
}


The kayaks repository is empty, so _.embedded.kayaks is an empty array.

Try and create a new kayak.

http POST :8080/kayaks name="sea2" owner="Andrew" value="500" makeModel="P&H" "Authorization: Bearer eyJraWQiOiJldjFpay1DS3UzYjJX..."


You’ll get another 403. “Saving” is going to equal an HTML POST here.

However, if you use a token generated from your original, admin account, it’ll work.

NOTE: It’s possible your token will be expired and you’ll have to log out of developer.okta.com again and re-generate the token on the OIDC Debugger.

POST a new kayak with the token generated from your admin account.

This time you’ll get a 201.

HTTP/1.1 201
Cache-Control: no-cache, no-store, max-age=0, must-revalidate
Content-Type: application/json;charset=UTF-8
...

{
    "_links": {
        "kayak": {
            "href": "http://localhost:8080/kayaks/1"
        },
        "self": {
            "href": "http://localhost:8080/kayaks/1"
        }
    },
    "makeModel": "P&H",
    "name": "sea2",
    "owner": "Andrew",
    "value": 500
}


Success!

Take a look at Spring Data’s CrudRepository interface to get an idea of the methods that can be overridden and assigned method-level security. The @PreAuthorize annotation can be used with a lot more than just groups, as well. The entire power of Spring’s expression language (SpEL) can be leveraged.

public interface CrudRepository<T, ID> extends Repository<T, ID> {
  <S extends T> S save(S entity);
  <S extends T> Iterable<S> saveAll(Iterable<S> entities);
  Optional<T> findById(ID id);
  boolean existsById(ID id);
  Iterable<T> findAll();
  Iterable<T> findAllById(Iterable<ID> ids);
  long count();
  void deleteById(ID id);
  void delete(T entity);
  void deleteAll(Iterable<? extends T> entities);
  void deleteAll();
}


And that’s it! Pretty cool right? In this tutorial, you set up a PostgreSQL database, created a Spring Boot resource server that used Spring Data and JPA to persist a data model, and then turned this data model into a REST API with shockingly little code. Further, you used Okta to add OIDC authentication and OAuth 2.0 authorization to your server application. And finally, you implemented a simple group-based authorization scheme.

If you’d like to check out this complete project, you can find the repo on GitHub at @oktadeveloper/okta-spring-boot-jpa-example.

Watch out for our next post in this series that will cover using a NoSQL database (MongoDB) with Spring WebFlux.

Learn More

Complete Java Masterclass

Complete Step By Step Java For Testers

Java Web Service Complete Guide - SOAP + REST + Buide App

Selenium WebDriver with Java - Basics to Advanced& Interview

Java Persistence: Hibernate and JPA Fundamentals

Java Swing (GUI) Programming: From Beginner to Expert

Java Basics: Learn to Code the Right Way

The Complete Python & PostgreSQL Developer Course

SQL & Database Design A-Z™: Learn MS SQL Server + PostgreSQL

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

SQL for Newbs: Data Analysis for Beginners