spring-authorization-server/docs/modules/ROOT/pages/guides/how-to-jpa.adoc

[[how-to-jpa]]
= How-to: Implement core services with JPA
:index-link: ../how-to.html
:docs-dir: ..

This guide shows how to implement the xref:core-model-components.adoc[core services] of xref:index.adoc[Spring Authorization Server] with JPA.
The purpose of this guide is to provide a starting point for implementing these services yourself, with the intention that you can make modifications to suit your needs.

* xref:guides/how-to-jpa.adoc#define-data-model[Define the data model]
* xref:guides/how-to-jpa.adoc#create-jpa-entities[Create JPA entities]
* xref:guides/how-to-jpa.adoc#create-spring-data-repositories[Create Spring Data repositories]
* xref:guides/how-to-jpa.adoc#implement-core-services[Implement core services]

[[define-data-model]]
== Define the data model

This guide provides a starting point for the data model and uses the simplest possible structure and data types.
To come up with the initial schema, we begin by reviewing the xref:core-model-components.adoc[domain objects] used by the core services.

[NOTE]
Except for token, state, metadata, settings, and claims values, we use the JPA default column length of 255 for all columns.
In reality, the length and even type of columns you use may need to be customized.
You are encouraged to experiment and test before deploying to production.

* xref:guides/how-to-jpa.adoc#client-schema[Client Schema]
* xref:guides/how-to-jpa.adoc#authorization-schema[Authorization Schema]
* xref:guides/how-to-jpa.adoc#authorization-consent-schema[Authorization Consent Schema]

[[client-schema]]
=== Client Schema

The xref:core-model-components.adoc#registered-client[`RegisteredClient`] domain object contains a few multi-valued fields and some settings fields that require storing arbitrary key/value data.
The following listing shows the `client` schema.

.Client Schema
[source,sql]
----
CREATE TABLE client (
    id varchar(255) NOT NULL,
    clientId varchar(255) NOT NULL,
    clientIdIssuedAt timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    clientSecret varchar(255) DEFAULT NULL,
    clientSecretExpiresAt timestamp DEFAULT NULL,
    clientName varchar(255) NOT NULL,
    clientAuthenticationMethods varchar(1000) NOT NULL,
    authorizationGrantTypes varchar(1000) NOT NULL,
    redirectUris varchar(1000) DEFAULT NULL,
    postLogoutRedirectUris varchar(1000) DEFAULT NULL,
    scopes varchar(1000) NOT NULL,
    clientSettings varchar(2000) NOT NULL,
    tokenSettings varchar(2000) NOT NULL,
    PRIMARY KEY (id)
);
----

[[authorization-schema]]
=== Authorization Schema

The xref:core-model-components.adoc#oauth2-authorization[`OAuth2Authorization`] domain object is more complex and contains several multi-valued fields as well as numerous arbitrarily long token values, metadata, settings and claims values.
The built-in JDBC implementation utilizes a flattened structure that prefers performance over normalization, which we adopt here as well.

[CAUTION]
It has been difficult to find a flattened database schema that works well in all cases and with all database vendors.
You may need to normalize or heavily alter the following schema for your needs.

The following listing shows the `authorization` schema.

.Authorization Schema
[source,sql]
----
CREATE TABLE authorization (
    id varchar(255) NOT NULL,
    registeredClientId varchar(255) NOT NULL,
    principalName varchar(255) NOT NULL,
    authorizationGrantType varchar(255) NOT NULL,
    authorizedScopes varchar(1000) DEFAULT NULL,
    attributes varchar(4000) DEFAULT NULL,
    state varchar(500) DEFAULT NULL,
    authorizationCodeValue varchar(4000) DEFAULT NULL,
    authorizationCodeIssuedAt timestamp DEFAULT NULL,
    authorizationCodeExpiresAt timestamp DEFAULT NULL,
    authorizationCodeMetadata varchar(2000) DEFAULT NULL,
    accessTokenValue varchar(4000) DEFAULT NULL,
    accessTokenIssuedAt timestamp DEFAULT NULL,
    accessTokenExpiresAt timestamp DEFAULT NULL,
    accessTokenMetadata varchar(2000) DEFAULT NULL,
    accessTokenType varchar(255) DEFAULT NULL,
    accessTokenScopes varchar(1000) DEFAULT NULL,
    refreshTokenValue varchar(4000) DEFAULT NULL,
    refreshTokenIssuedAt timestamp DEFAULT NULL,
    refreshTokenExpiresAt timestamp DEFAULT NULL,
    refreshTokenMetadata varchar(2000) DEFAULT NULL,
    oidcIdTokenValue varchar(4000) DEFAULT NULL,
    oidcIdTokenIssuedAt timestamp DEFAULT NULL,
    oidcIdTokenExpiresAt timestamp DEFAULT NULL,
    oidcIdTokenMetadata varchar(2000) DEFAULT NULL,
    oidcIdTokenClaims varchar(2000) DEFAULT NULL,
    userCodeValue varchar(4000) DEFAULT NULL,
    userCodeIssuedAt timestamp DEFAULT NULL,
    userCodeExpiresAt timestamp DEFAULT NULL,
    userCodeMetadata varchar(2000) DEFAULT NULL,
    deviceCodeValue varchar(4000) DEFAULT NULL,
    deviceCodeIssuedAt timestamp DEFAULT NULL,
    deviceCodeExpiresAt timestamp DEFAULT NULL,
    deviceCodeMetadata varchar(2000) DEFAULT NULL,
    PRIMARY KEY (id)
);
----

[[authorization-consent-schema]]
=== Authorization Consent Schema

The xref:core-model-components.adoc#oauth2-authorization-consent[`OAuth2AuthorizationConsent`] domain object is the simplest to model and contains only a single multi-valued field in addition to a composite key.
The following listing shows the `authorizationConsent` schema.

.Authorization Consent Schema
[source,sql]
----
CREATE TABLE authorizationConsent (
    registeredClientId varchar(255) NOT NULL,
    principalName varchar(255) NOT NULL,
    authorities varchar(1000) NOT NULL,
    PRIMARY KEY (registeredClientId, principalName)
);
----

[[create-jpa-entities]]
== Create JPA entities

The preceding schema examples provide a reference for the structure of the entities we need to create.

[NOTE]
The following entities are minimally annotated and are just examples.
They allow the schema to be created dynamically and therefore do not require the above sql scripts to be executed manually.

* xref:guides/how-to-jpa.adoc#client-entity[Client Entity]
* xref:guides/how-to-jpa.adoc#authorization-entity[Authorization Entity]
* xref:guides/how-to-jpa.adoc#authorization-consent-entity[Authorization Consent Entity]

[[client-entity]]
=== Client Entity

The following listing shows the `Client` entity, which is used to persist information mapped from the xref:core-model-components.adoc#registered-client[`RegisteredClient`] domain object.

[[sample.jpa.entity.client]]
.Client Entity
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/entity/client/Client.java[]
----

[[authorization-entity]]
=== Authorization Entity

The following listing shows the `Authorization` entity, which is used to persist information mapped from the xref:core-model-components.adoc#oauth2-authorization[`OAuth2Authorization`] domain object.

[[sample.jpa.entity.authorization]]
.Authorization Entity
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/entity/authorization/Authorization.java[]
----

[[authorization-consent-entity]]
=== Authorization Consent Entity

The following listing shows the `AuthorizationConsent` entity, which is used to persist information mapped from the xref:core-model-components.adoc#oauth2-authorization-consent[`OAuth2AuthorizationConsent`] domain object.

[[sample.jpa.entity.authorizationConsent]]
.Authorization Consent Entity
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/entity/authorizationConsent/AuthorizationConsent.java[]
----

[[create-spring-data-repositories]]
== Create Spring Data repositories

By closely examining the interfaces of each core service and reviewing the `Jdbc` implementations, we can derive a minimal set of queries needed for supporting a JPA version of each interface.

* xref:guides/how-to-jpa.adoc#client-repository[Client Repository]
* xref:guides/how-to-jpa.adoc#authorization-repository[Authorization Repository]
* xref:guides/how-to-jpa.adoc#authorization-consent-repository[Authorization Consent Repository]

[[client-repository]]
=== Client Repository

The following listing shows the `ClientRepository`, which is able to find a xref:guides/how-to-jpa.adoc#client-entity[`Client`] by the `id` and `clientId` fields.

[[sample.jpa.repository.client]]
.Client Repository
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/repository/client/ClientRepository.java[]
----

[[authorization-repository]]
=== Authorization Repository

The following listing shows the `AuthorizationRepository`, which is able to find an xref:guides/how-to-jpa.adoc#authorization-entity[`Authorization`] by the `id` field as well as the `state`, `authorizationCodeValue`, `accessTokenValue`, `refreshTokenValue`, `userCodeValue` and `deviceCodeValue` token fields.
It also allows querying a combination of token fields.

[[sample.jpa.repository.authorization]]
.Authorization Repository
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/repository/authorization/AuthorizationRepository.java[]
----

[[authorization-consent-repository]]
=== Authorization Consent Repository

The following listing shows the `AuthorizationConsentRepository`, which is able to find and delete an xref:guides/how-to-jpa.adoc#authorization-consent-entity[`AuthorizationConsent`] by the `registeredClientId` and `principalName` fields that form a composite primary key.

[[sample.jpa.repository.authorizationConsent]]
.Authorization Consent Repository
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/repository/authorizationConsent/AuthorizationConsentRepository.java[]
----

[[implement-core-services]]
== Implement core services

With the above xref:guides/how-to-jpa.adoc#create-jpa-entities[entities] and xref:guides/how-to-jpa.adoc#create-spring-data-repositories[repositories], we can begin implementing the core services.
By reviewing the `Jdbc` implementations, we can derive a minimal set of internal utilities for converting to and from string values for enumerations and reading and writing JSON data for attributes, settings, metadata and claims fields.

[CAUTION]
Keep in mind that writing JSON data to text columns with a fixed length has proven problematic with the `Jdbc` implementations.
While these examples continue to do so, you may need to split these fields out into a separate table or data store that supports arbitrarily long data values.

* <<registered-client-repository>>
* xref:guides/how-to-jpa.adoc#authorization-service[Authorization Service]
* xref:guides/how-to-jpa.adoc#authorization-consent-service[Authorization Consent Service]

[[registered-client-repository]]
=== Registered Client Repository

The following listing shows the `JpaRegisteredClientRepository`, which uses a xref:guides/how-to-jpa.adoc#client-repository[`ClientRepository`] for persisting a xref:guides/how-to-jpa.adoc#client-entity[`Client`] and maps to and from the xref:core-model-components.adoc#registered-client[`RegisteredClient`] domain object.

[[sample.jpa.service.client]]
.`RegisteredClientRepository` Implementation
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/service/client/JpaRegisteredClientRepository.java[]
----

[[authorization-service]]
=== Authorization Service

The following listing shows the `JpaOAuth2AuthorizationService`, which uses an xref:guides/how-to-jpa.adoc#authorization-repository[`AuthorizationRepository`] for persisting an xref:guides/how-to-jpa.adoc#authorization-entity[`Authorization`] and maps to and from the xref:core-model-components.adoc#oauth2-authorization[`OAuth2Authorization`] domain object.

[[sample.jpa.service.authorization]]
.`OAuth2AuthorizationService` Implementation
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/service/authorization/JpaOAuth2AuthorizationService.java[]
----

[[authorization-consent-service]]
=== Authorization Consent Service

The following listing shows the `JpaOAuth2AuthorizationConsentService`, which uses an xref:guides/how-to-jpa.adoc#authorization-consent-repository[`AuthorizationConsentRepository`] for persisting an xref:guides/how-to-jpa.adoc#authorization-consent-entity[`AuthorizationConsent`] and maps to and from the xref:core-model-components.adoc#oauth2-authorization-consent[`OAuth2AuthorizationConsent`] domain object.

[[sample.jpa.service.authorizationConsent]]
.`OAuth2AuthorizationConsentService` Implementation
[source,java]
----
include::{examples-dir}/main/java/sample/jpa/service/authorizationConsent/JpaOAuth2AuthorizationConsentService.java[]
----