> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Build a Spring App with CockroachDB and JPA

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This tutorial shows you how to build a [Spring Boot](https://spring.io/projects/spring-boot) web application with CockroachDB, using the [Spring Data JPA](https://spring.io/projects/spring-data-jpa) module for data access. The code for the example application is available for download from [GitHub](https://github.com/cockroachlabs/roach-data/tree/master), along with identical examples that use [JDBC](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jdbc), [jOOQ](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jooq), and [MyBatis](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-mybatis) for data access.

## Step 1. Start CockroachDB

Choose whether to run a local cluster or a free CockroachDB Standard cluster.

### Create a free trial cluster

1. <InternalLink version="cockroachcloud" path="create-an-account">Create a CockroachDB Cloud account</InternalLink>. If this is your first CockroachDB Cloud organization, it will be credited with \$400 in <InternalLink version="cockroachcloud" path="free-trial">free trial credits</InternalLink> to get you started.
2. On the **Get Started** page, click **Create cluster**.
3. On the **Select a plan** page, select **Standard**.
4. On the **Cloud & Regions** page, select a cloud provider (GCP or AWS).
5. In the **Regions** section, select a region for the cluster. Refer to <InternalLink version="cockroachcloud" path="regions">CockroachDB Cloud Regions</InternalLink> for the regions where CockroachDB Standard clusters can be deployed. To create a multi-region cluster, click **Add region** and select additional regions.
6. Click **Next: Capacity**.
7. On the **Capacity** page, keep the <InternalLink version="cockroachcloud" path="plan-your-cluster">**Provisioned capacity**</InternalLink> at the default value of 2 vCPUs.

   Click **Next: Finalize**.
8. On the **Finalize** page, name your cluster. If an active free trial is listed in the right pane, you will not need to add a payment method, though you will need to do this by the <InternalLink version="cockroachcloud" path="free-trial#add-payment-methods">end of the trial</InternalLink> to maintain your organization's clusters.

   Click **Create cluster**.

   Your cluster will be created in a few seconds and the **Create SQL user** dialog will display.

### Set up your cluster connection

Once your cluster is created, the **Connect to cluster-name** dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

1. In your terminal, run the second command from the dialog to create a new `certs` directory on your local machine and download the CA certificate to that directory:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/{cluster-id}/cert
   ```

   Your `cert` file will be downloaded to `~/.postgresql/root.crt`.

2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

   This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the **SQL Users** page for the cluster, found at `https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users`.

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --url 'postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
   ```

   Where:

   * `<username` is the SQL user. By default, this is your CockroachDB Cloud account username.
   * `<password` is the password for the SQL user. The password will be shown only once in the **Connection info** dialog after creating the cluster.
   * `<cluster-hostname` is the hostname of your CockroachDB Cloud cluster.
   * `<cluster-id` is a unique string used to identify your cluster when downloading the CA certificate. For example, `12a3bcde-4fa5-6789-1234-56bc7890d123`.

     You can find these settings in the **Connection parameters** tab of the **Connection info** dialog.

3. In your terminal, run the second command from the dialog to create a new `certs` directory on your local machine and download the CA certificate to that directory:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/{cluster-id}/cert
   ```

   Your `cert` file will be downloaded to `~/.postgresql/root.crt`.

4. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

   This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the **SQL Users** page for the cluster, found at `https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users`.

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --url 'postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
   ```

   Where:

   * `<username` is the SQL user. By default, this is your CockroachDB Cloud account username.
   * `<password` is the password for the SQL user. The password will be shown only once in the **Connection info** dialog after creating the cluster.
   * `<cluster-hostname` is the hostname of your CockroachDB Cloud cluster.
   * `<cluster-id` is a unique string used to identify your cluster when downloading the CA certificate. For example, `12a3bcde-4fa5-6789-1234-56bc7890d123`.

     You can find these settings in the **Connection parameters** tab of the **Connection info** dialog.

5. In your terminal, run the second command from the dialog to create a new `certs` directory on your local machine and download the CA certificate to that directory:

   ```powershell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/{cluster-id}/cert -OutFile $env:appdata\.postgresql\root.crt
   ```

   Your `cert` file will be downloaded to `%APPDATA%/.postgresql/root.crt`.

6. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

   This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the **SQL Users** page for the cluster, found at `https://cockroachlabs.cloud/cluster/<CLUSTER ID>/users`.

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   cockroach sql --url "postgresql://<username:<password@<cluster-host:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt"
   ```

   Where:

   * `<username` is the SQL user. By default, this is your CockroachDB Cloud account username.
   * `<password` is the password for the SQL user. The password will be shown only once in the **Connection info** dialog after creating the cluster.
   * `<cluster-hostname` is the hostname of your CockroachDB Cloud cluster.
   * `<cluster-id` is a unique string used to identify your cluster when downloading the CA certificate. For example, `12a3bcde-4fa5-6789-1234-56bc7890d123`.

     You can find these settings in the **Connection parameters** tab of the **Connection info** dialog.

## Step 2. Create a database and a user

1. If you haven't already, <InternalLink path="install-cockroachdb">download the CockroachDB SQL Shell binary</InternalLink>.
2. Start the <InternalLink path="cockroach-sql">built-in SQL shell</InternalLink> using the connection string you got from the CockroachDB Cloud Console [earlier](#set-up-your-cluster-connection):

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ cockroach sql \
   --url='postgres://{username}:{password}@{global host}:26257/{cluster_host}.defaultdb?sslmode=verify-full&sslrootcert={certs_dir}/cc-ca.crt'
   ```

   In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <code>{'{certs_dir}'}</code> placeholder with the path to the `certs` directory that you created [earlier](#set-up-your-cluster-connection).

<Note>
  CockroachDB Cloud does not yet support certificate-based user authentication.
</Note>

3. In the SQL shell, create the `roach_data` database that your application will use:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > CREATE DATABASE roach_data;
   ```

## Step 3. Install JDK

Download and install a Java Development Kit on your machine. Spring Boot supports Java versions 8, 11, and 14. In this tutorial, we use [JDK 8 from OpenJDK](https://openjdk.java.net/install).

## Step 4. Install Maven

This example application uses [Maven](http://maven.apache.org/) to manage all application dependencies. Spring supports Maven versions 3.2 and later.

To install Maven on macOS, run the following command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ brew install maven
```

To install Maven on a Debian-based Linux distribution like Ubuntu:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ apt-get install maven
```

To install Maven on a Red Hat-based Linux distribution like Fedora:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ dnf install maven
```

For other ways to install Maven, see [its official documentation](http://maven.apache.org/install).

## Step 5. Get the application code

To get the application code, download or clone the [`roach-data` repository](https://github.com/cockroachlabs/roach-data). The code for the example JPA application is located under the `roach-data-jpa` directory.

(*Optional*) To recreate the application project structure with the same dependencies as those used by this sample application, you can use [Spring initializr](https://start.spring.io/) with the following settings:

**Project**

* Maven Project

**Language**

* Java

**Spring Boot**

* 2.2.6

**Project Metadata**

* Group: io.roach
* Artifact: data
* Name: data
* Package name: io.roach.data
* Packaging: Jar
* Java: 8

**Dependencies**

* Spring Web
* Spring Data JPA
* Spring HATEOS
* Liquibase Migration
* PostgreSQL Driver

The [Hibernate CockroachDB dialect](https://in.relation.to/2020/07/27/hibernate-orm-5419-final-release) is supported in Hibernate v5.4.19+. At the time of writing this tutorial, Spring Data JPA used Hibernate v5.4.15 as its default JPA provider. To specify a different version of Hibernate than the default, add an additional entry to your application's `pom.xml` file, as shown in [the `roach-data` GitHub repo](https://github.com/cockroachlabs/roach-data/blob/master/roach-data-jpa/pom.xml):

```xml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
<dependency>
    <groupId>org.hibernate</groupId
    <artifactId>hibernate-core</artifactId
    <version>5.4.19.Final</version
</dependency
```

## Step 6. Run the application

Compiling and running the application code will start a web application, initialize the `accounts` table in the `roach_data` database, and submit some requests to the app's REST API that result in <InternalLink path="transactions">atomic database transactions</InternalLink> on the running CockroachDB cluster. For details about the application code, see [Implementation details](#implementation-details).

Open the `roach-data/roach-data-jpa/src/main/resources/application.yml` file and edit the `datasource` settings to connect to your running database cluster:

```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
...
datasource:
  url: jdbc:postgresql://{globalhost}:{port}/{cluster_host}.roach_data?sslmode=verify-full&sslrootcert={path to the CA certificate}/cc-ca.crt
  username: {username}
  password: {password}
  driver-class-name: org.postgresql.Driver
...
```

Where:

* <code>{'{username}'}</code> and <code>{'{password}'}</code> specify the SQL username and password that you created earlier.
* <code>{'{globalhost}'}</code> is the name of the CockroachDB Cloud free tier host (e.g., `free-tier.gcp-us-central1.cockroachlabs.cloud` ).
* <code>{'{path to the CA certificate}'}</code> is the path to the `cc-ca.crt` file that you downloaded from the CockroachDB Cloud Console.
* <code>{'{cluster_name}'}</code> is the name of your cluster.

<Note>
  If you are using the connection string that you [copied from the **Connection info** modal](#set-up-your-cluster-connection), your username, password, hostname, and cluster name will be pre-populated.
</Note>

1. If you haven't already, <InternalLink path="install-cockroachdb">download the CockroachDB binary</InternalLink>.
2. <InternalLink path="secure-a-cluster">Start a local, secure cluster</InternalLink>.

## Step 2. Create a database and a user

1. Open a SQL shell to your local cluster using the <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink> command:

   ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   $ cockroach sql --certs-dir={certs-dir} --host=localhost:{port}
   ```

   Where <code>{'{certs_dir}'}</code> is the full path to the `certs` directory that you created when setting up the cluster, and <code>{'{port}'}</code> is the port at which the cluster is listening for incoming connections.
2. In the SQL shell, create the `roach_data` database that your application will use:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > CREATE DATABASE roach_data;
   ```
3. Create a SQL user for your app:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > CREATE USER {username} WITH PASSWORD {password};
   ```

   Take note of the username and password. You will use it to connect to the database later.
4. Give the user the necessary permissions:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > GRANT ALL ON DATABASE roach_data TO {username};
   ```
5. Exit the shell, and generate a certificate and key for your user by running the following command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach cert create-client {user} --certs-dir=certs --ca-key={certs-dir}/ca.key --also-generate-pkcs8-key
```

The <InternalLink path="cockroach-cert#flag-pkcs8">`--also-generate-pkcs8-key` flag</InternalLink> generates a key in [PKCS#8 format](https://tools.ietf.org/html/rfc5208), which is the standard key encoding format in Java. In this case, the generated PKCS8 key will be named `client.{user}.key.pk8`.

## Step 3. Install JDK

Download and install a Java Development Kit on your machine. Spring Boot supports Java versions 8, 11, and 14. In this tutorial, we use [JDK 8 from OpenJDK](https://openjdk.java.net/install).

## Step 4. Install Maven

This example application uses [Maven](http://maven.apache.org/) to manage all application dependencies. Spring supports Maven versions 3.2 and later.

To install Maven on macOS, run the following command:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ brew install maven
```

To install Maven on a Debian-based Linux distribution like Ubuntu:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ apt-get install maven
```

To install Maven on a Red Hat-based Linux distribution like Fedora:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ dnf install maven
```

For other ways to install Maven, see [its official documentation](http://maven.apache.org/install).

## Step 5. Get the application code

To get the application code, download or clone the [`roach-data` repository](https://github.com/cockroachlabs/roach-data). The code for the example JPA application is located under the `roach-data-jpa` directory.

(*Optional*) To recreate the application project structure with the same dependencies as those used by this sample application, you can use [Spring initializr](https://start.spring.io/) with the following settings:

**Project**

* Maven Project

**Language**

* Java

**Spring Boot**

* 2.2.6

**Project Metadata**

* Group: io.roach
* Artifact: data
* Name: data
* Package name: io.roach.data
* Packaging: Jar
* Java: 8

**Dependencies**

* Spring Web
* Spring Data JPA
* Spring HATEOS
* Liquibase Migration
* PostgreSQL Driver

The [Hibernate CockroachDB dialect](https://in.relation.to/2020/07/27/hibernate-orm-5419-final-release) is supported in Hibernate v5.4.19+. At the time of writing this tutorial, Spring Data JPA used Hibernate v5.4.15 as its default JPA provider. To specify a different version of Hibernate than the default, add an additional entry to your application's `pom.xml` file, as shown in [the `roach-data` GitHub repo](https://github.com/cockroachlabs/roach-data/blob/master/roach-data-jpa/pom.xml):

```xml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
<dependency>
    <groupId>org.hibernate</groupId
    <artifactId>hibernate-core</artifactId
    <version>5.4.19.Final</version
</dependency
```

## Step 6. Run the application

Compiling and running the application code will start a web application, initialize the `accounts` table in the `roach_data` database, and submit some requests to the app's REST API that result in <InternalLink path="transactions">atomic database transactions</InternalLink> on the running CockroachDB cluster. For details about the application code, see [Implementation details](#implementation-details).

Open the `roach-data/roach-data-jpa/src/main/resources/application.yml` file and edit the `datasource` settings to connect to your running database cluster:

```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
...
datasource:
  url: jdbc:postgresql://localhost:{port}/roach_data?ssl=true&sslmode=require&sslrootcert={certs-dir}/ca.crt&sslkey={certs-dir}/client.{username}.key.pk8&sslcert={certs-dir}/client.{username}.crt
  username: {username}
  password: {password}
  driver-class-name: org.postgresql.Driver
...
```

Where:

* <code>{'{port}'}</code> is the port number.
* <code>{'{certs-dir}'}</code> is the full path to the certificates directory containing the authentication certificates that you created earlier.
* <code>{'{username}'}</code> and <code>{'{password}'}</code> specify the SQL username and password that you created earlier.

Open a terminal, and navigate to the `roach-data-jpa` project subfolder:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cd <path>/roach-data/roach-data-jpa
```

Use Maven to download the application dependencies and compile the code:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ mvn clean install
```

From the `roach-data-jpa` directory, run the application JAR file:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ java -jar target/roach-data-jpa.jar
```

The output should look like the following:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.7.RELEASE)

2020-06-22 11:54:46.243  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Starting JpaApplication v1.0.0.BUILD-SNAPSHOT on MyComputer.local with PID 81343 (path/code/roach-data/roach-data-jpa/target/roach-data-jpa.jar started by user in path/code/roach-data/roach-data-jpa)
2020-06-22 11:54:46.246  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : No active profile set, falling back to default profiles: default
2020-06-22 11:54:46.929  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!
2020-06-22 11:54:46.930  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2020-06-22 11:54:47.023  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 80ms. Found 1 JPA repository interfaces.
2020-06-22 11:54:47.211  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Multiple Spring Data modules found, entering strict repository configuration mode!
2020-06-22 11:54:47.211  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JDBC repositories in DEFAULT mode.
2020-06-22 11:54:47.224  INFO 81343 --- [           main] .RepositoryConfigurationExtensionSupport : Spring Data JDBC - Could not safely identify store assignment for repository candidate interface io.roach.data.jpa.AccountRepository. If you want this repository to be a JDBC repository, consider annotating your entities with one of these annotations: org.springframework.data.relational.core.mapping.Table.
2020-06-22 11:54:47.224  INFO 81343 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 12ms. Found 0 JDBC repository interfaces.
2020-06-22 11:54:47.913  INFO 81343 --- [           main] org.eclipse.jetty.util.log               : Logging initialized @2990ms to org.eclipse.jetty.util.log.Slf4jLog
2020-06-22 11:54:47.982  INFO 81343 --- [           main] o.s.b.w.e.j.JettyServletWebServerFactory : Server initialized with port: 9090
2020-06-22 11:54:47.985  INFO 81343 --- [           main] org.eclipse.jetty.server.Server          : jetty-9.4.28.v20200408; built: 2020-04-08T17:49:39.557Z; git: ab228fde9e55e9164c738d7fa121f8ac5acd51c9; jvm 11.0.7+10
2020-06-22 11:54:48.008  INFO 81343 --- [           main] o.e.j.s.h.ContextHandler.application     : Initializing Spring embedded WebApplicationContext
2020-06-22 11:54:48.008  INFO 81343 --- [           main] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 1671 ms
2020-06-22 11:54:48.123  INFO 81343 --- [           main] org.eclipse.jetty.server.session         : DefaultSessionIdManager workerName=node0
2020-06-22 11:54:48.123  INFO 81343 --- [           main] org.eclipse.jetty.server.session         : No SessionScavenger set, using defaults
2020-06-22 11:54:48.124  INFO 81343 --- [           main] org.eclipse.jetty.server.session         : node0 Scavenging every 660000ms
2020-06-22 11:54:48.130  INFO 81343 --- [           main] o.e.jetty.server.handler.ContextHandler  : Started o.s.b.w.e.j.JettyEmbeddedWebAppContext@41394595{application,/,[file:///private/var/folders/pg/r58v54857gq_1nqm_2tr6lg40000gn/T/jetty-docbase.7785392427958606416.8080/],AVAILABLE}
2020-06-22 11:54:48.131  INFO 81343 --- [           main] org.eclipse.jetty.server.Server          : Started @3207ms
2020-06-22 11:54:48.201  INFO 81343 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-06-22 11:54:48.483  INFO 81343 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-06-22 11:54:49.507  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM public.databasechangeloglock
2020-06-22 11:54:49.522  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE public.databasechangeloglock (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP WITHOUT TIME ZONE, LOCKEDBY VARCHAR(255), CONSTRAINT DATABASECHANGELOGLOCK_PKEY PRIMARY KEY (ID))
2020-06-22 11:54:49.535  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM public.databasechangeloglock
2020-06-22 11:54:49.554  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : DELETE FROM public.databasechangeloglock
2020-06-22 11:54:49.555  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.databasechangeloglock (ID, LOCKED) VALUES (1, FALSE)
2020-06-22 11:54:49.562  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
2020-06-22 11:54:49.570  INFO 81343 --- [           main] l.lockservice.StandardLockService        : Successfully acquired change log lock
2020-06-22 11:54:50.519  INFO 81343 --- [           main] l.c.StandardChangeLogHistoryService      : Creating database history table with name: public.databasechangelog
2020-06-22 11:54:50.520  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : CREATE TABLE public.databasechangelog (ID VARCHAR(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))
2020-06-22 11:54:50.534  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM public.databasechangelog
2020-06-22 11:54:50.547  INFO 81343 --- [           main] l.c.StandardChangeLogHistoryService      : Reading from public.databasechangelog
2020-06-22 11:54:50.548  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT * FROM public.databasechangelog ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
2020-06-22 11:54:50.550  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT COUNT(*) FROM public.databasechangeloglock
2020-06-22 11:54:50.566  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : create table account
(
    id      int            not null primary key default unique_rowid(),
    balance numeric(19, 2) not null,
    name    varchar(128)   not null,
    type    varchar(25)    not null
)
2020-06-22 11:54:50.575  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : SQL in file db/create.sql executed
2020-06-22 11:54:50.581  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelog-master.xml::1::root ran successfully in 16ms
2020-06-22 11:54:50.585  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : SELECT MAX(ORDEREXECUTED) FROM public.databasechangelog
2020-06-22 11:54:50.589  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1', 'root', 'classpath:db/changelog-master.xml', NOW(), 1, '8:567321cdb0100cbe76731a7ed414674b', 'sqlFile', '', 'EXECUTED', 'crdb', NULL, '3.8.9', '2852090551')
2020-06-22 11:54:50.593  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.account (id, name, balance, type) VALUES ('1', 'Alice', 500.00, 'asset')
2020-06-22 11:54:50.601  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into account
2020-06-22 11:54:50.602  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.account (id, name, balance, type) VALUES ('2', 'Bob', 500.00, 'expense')
2020-06-22 11:54:50.603  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into account
2020-06-22 11:54:50.604  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.account (id, name, balance, type) VALUES ('3', 'Bobby Tables', 500.00, 'asset')
2020-06-22 11:54:50.605  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into account
2020-06-22 11:54:50.605  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.account (id, name, balance, type) VALUES ('4', 'Doris', 500.00, 'expense')
2020-06-22 11:54:50.606  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : New row inserted into account
2020-06-22 11:54:50.608  INFO 81343 --- [           main] liquibase.changelog.ChangeSet            : ChangeSet classpath:db/changelog-master.xml::2::root ran successfully in 16ms
2020-06-22 11:54:50.609  INFO 81343 --- [           main] liquibase.executor.jvm.JdbcExecutor      : INSERT INTO public.databasechangelog (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('2', 'root', 'classpath:db/changelog-master.xml', NOW(), 2, '8:c2945f2a445cf60b4b203e1a91d14a89', 'insert tableName=account; insert tableName=account; insert tableName=account; insert tableName=account', '', 'EXECUTED', 'crdb', NULL, '3.8.9', '2852090551')
2020-06-22 11:54:50.615  INFO 81343 --- [           main] l.lockservice.StandardLockService        : Successfully released change log lock
2020-06-22 11:54:50.727  INFO 81343 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2020-06-22 11:54:50.817  INFO 81343 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 5.4.19.Final
2020-06-22 11:54:50.993  INFO 81343 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
2020-06-22 11:54:51.154  INFO 81343 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.CockroachDB201Dialect
2020-06-22 11:54:51.875  INFO 81343 --- [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
2020-06-22 11:54:51.886  INFO 81343 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2020-06-22 11:54:52.700  INFO 81343 --- [           main] o.s.s.concurrent.ThreadPoolTaskExecutor  : Initializing ExecutorService 'applicationTaskExecutor'
2020-06-22 11:54:52.958  INFO 81343 --- [           main] o.e.j.s.h.ContextHandler.application     : Initializing Spring DispatcherServlet 'dispatcherServlet'
2020-06-22 11:54:52.958  INFO 81343 --- [           main] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2020-06-22 11:54:52.966  INFO 81343 --- [           main] o.s.web.servlet.DispatcherServlet        : Completed initialization in 8 ms
2020-06-22 11:54:52.997  INFO 81343 --- [           main] o.e.jetty.server.AbstractConnector       : Started ServerConnector@1568159{HTTP/1.1, (http/1.1)}{0.0.0.0:9090}
2020-06-22 11:54:52.999  INFO 81343 --- [           main] o.s.b.web.embedded.jetty.JettyWebServer  : Jetty started on port(s) 9090 (http/1.1) with context path '/'
2020-06-22 11:54:53.001  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Started JpaApplication in 7.518 seconds (JVM running for 8.077)
2020-06-22 11:54:53.002  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Lets move some $$ around!
2020-06-22 11:54:54.399  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 7 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 6 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 5 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 4 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 3 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 2 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 1 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : Worker finished - 0 remaining
2020-06-22 11:54:54.447  INFO 81343 --- [           main] io.roach.data.jpa.JpaApplication         : All client workers finished but server keeps running. Have a nice day!
```

As the output states, the application configures a database connection, starts a web servlet listening on the address `http://localhost:9090/`, initializes the `account` table and changelog tables with [Liquibase](https://www.liquibase.org/), and then runs some test operations as requests to the application's REST API.

For more details about the application code, see [Implementation details](#implementation-details).

### Query the database

#### Reads

The `http://localhost:9090/account` endpoint returns information about all accounts in the database. `GET` requests to these endpoints are executed on the database as `SELECT` statements.

The following `curl` command sends a `GET` request to the endpoint. The `json_pp` command formats the JSON response.

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X GET http://localhost:9090/account | json_pp
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
   "_embedded" : {
      "accounts" : [
         {
            "_links" : {
               "self" : {
                  "href" : "http://localhost:9090/account/1"
               }
            },
            "balance" : 500,
            "name" : "Alice",
            "type" : "asset"
         },
         {
            "_links" : {
               "self" : {
                  "href" : "http://localhost:9090/account/2"
               }
            },
            "balance" : 500,
            "name" : "Bob",
            "type" : "expense"
         },
         {
            "_links" : {
               "self" : {
                  "href" : "http://localhost:9090/account/3"
               }
            },
            "balance" : 500,
            "name" : "Bobby Tables",
            "type" : "asset"
         },
         {
            "_links" : {
               "self" : {
                  "href" : "http://localhost:9090/account/4"
               }
            },
            "balance" : 500,
            "name" : "Doris",
            "type" : "expense"
         }
      ]
   },
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account?page=0&size=5"
      }
   },
   "page" : {
      "number" : 0,
      "size" : 5,
      "totalElements" : 4,
      "totalPages" : 1
   }
}
```

For a single account, specify the account number in the endpoint. For example, to see information about the accounts `1` and `2`:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X GET http://localhost:9090/account/1 | json_pp
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/1"
      }
   },
   "balance" : 500,
   "name" : "Alice",
   "type" : "asset"
}
```

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X GET http://localhost:9090/account/2 | json_pp
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/2"
      }
   },
   "balance" : 500,
   "name" : "Bob",
   "type" : "expense"
}
```

The `http://localhost:9090/transfer` endpoint performs transfers between accounts. `POST` requests to this endpoint are executed as writes (i.e., <InternalLink path="insert">`INSERT`s</InternalLink> and <InternalLink path="update">`UPDATE`s</InternalLink>) to the database.

#### Writes

To make a transfer, send a `POST` request to the `transfer` endpoint, using the arguments specified in the `"href`" URL (i.e., `http://localhost:9090/transfer%7B?fromId,toId,amount`).

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X POST -d fromId=2 -d toId=1 -d amount=150 http://localhost:9090/transfer
```

You can use the `accounts` endpoint to verify that the transfer was successfully completed:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X GET http://localhost:9090/account/1 | json_pp
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/1"
      }
   },
   "balance" : 650,
   "name" : "Alice",
   "type" : "asset"
}
```

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ curl -X GET http://localhost:9090/account/2 | json_pp
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/2"
      }
   },
   "balance" : 350,
   "name" : "Bob",
   "type" : "expense"
}
```

## Implementation details

This section guides you through the different components of the application project in detail.

### Main application process

`JpaApplication.java` defines the application's main process. It starts a Spring Boot web application, and then submits requests to the app's REST API that result in database transactions on the CockroachDB cluster.

Here are the contents of [`JpaApplication.java`](https://github.com/cockroachlabs/roach-data/blob/master/roach-data-jpa/src/main/java/io/roach/data/jpa/JpaApplication.java):

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

@EnableHypermediaSupport(type = EnableHypermediaSupport.HypermediaType.HAL)
@EnableJpaRepositories
@EnableAspectJAutoProxy(proxyTargetClass = true)
@EnableTransactionManagement
@SpringBootApplication
public class JpaApplication implements CommandLineRunner {
    protected static final Logger logger = LoggerFactory.getLogger(JpaApplication.class);

    public static void main(String[] args) {
        new SpringApplicationBuilder(JpaApplication.class)
                .web(WebApplicationType.SERVLET)
                .run(args);
    }

    @Override
    public void run(String... args) throws Exception {
        logger.info("Lets move some $$ around!");

        final Link transferLink = Link.of("http://localhost:9090/transfer{?fromId,toId,amount}");

        final int concurrency = args.length > 0 ? Integer.parseInt(args[0]) : 1;

        final ScheduledExecutorService executorService = Executors.newScheduledThreadPool(concurrency);

        Deque<Future<Integer>> futures = new ArrayDeque<>();

        for (int i = 0; i < concurrency; i++) {
            Future<Integer> future = executorService.submit(() -> {
                RestTemplate template = new RestTemplate();
                int errors = 0;
                for (int j = 0; j < 100; j++) {
                    int fromId = j % 4 + 1;
                    int toId = fromId % 4 + 1;

                    BigDecimal amount = new BigDecimal("10.00");

                    Map<String, Object> form = new HashMap<>();
                    form.put("fromId", fromId);
                    form.put("toId", toId);
                    form.put("amount", amount);

                    String uri = transferLink.expand(form).getHref();

                    logger.debug("({}) Transfer {} from {} to {}", uri, amount, fromId, toId);

                    try {
                        template.postForEntity(uri, null, String.class);
                    } catch (HttpStatusCodeException e) {
                        logger.warn(e.getResponseBodyAsString());
                        errors++;
                    }
                }
                return errors;
            });
            futures.add(future);
        }

        int totalErrors = 0;

        while (!futures.isEmpty()) {
            try {
                int errors = futures.pop().get();
                totalErrors += errors;
                logger.info("Worker finished with {} errors - {} remaining", errors, futures.size());
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
            } catch (ExecutionException e) {
                logger.warn("Worker failed", e.getCause());
            }
        }

        logger.info("All client workers finished with {} errors and server keeps running. Have a nice day!",
                totalErrors);

        executorService.shutdownNow();
    }
}
```

The annotations listed at the top of the `JpaApplication` class definition declare some important configuration properties for the entire application:

* [`@EnableHypermediaSupport`](https://docs.spring.io/spring-hateoas/docs/current/api/org/springframework/hateoas/config/EnableHypermediaSupport) enables [hypermedia support for resource representation](https://wikipedia.org/wiki/HATEOAS) in the application. Currently, the only hypermedia format supported by Spring is [HAL](https://wikipedia.org/wiki/Hypertext_Application_Language), and so the `type = EnableHypermediaSupport.HypermediaType.HAL`. For details, see [Hypermedia representation](#hypermedia-representation).
* [`@EnableJpaRepositories`](https://docs.spring.io/spring-data/data-jpa/docs/current/api/org/springframework/data/jpa/repository/config/EnableJpaRepositories) enables the creation of [Spring repositories](https://docs.spring.io/spring-data/jpa/docs/current/reference/html#repositories) for data access using [Spring Data JPA](https://spring.io/projects/spring-data-jpa). For details, see [Spring repositories](#spring-repositories).
* [`@EnableAspectJAutoProxy`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/context/annotation/EnableAspectJAutoProxy) enables the use of [`@AspectJ` annotations for declaring aspects](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-ataspectj). For details, see [Transaction management](#transaction-management).
* [`@EnableTransactionManagement`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/EnableTransactionManagement) enables [declarative transaction management](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#transaction-declarative) in the application. For details, see [Transaction management](#transaction-management).

  Note that the `@EnableTransactionManagement` annotation is passed an `order` parameter, which indicates the ordering of advice evaluation when a common join point is reached. For details, see [Ordering advice](#ordering-advice).
* [`@SpringBootApplication`](https://docs.spring.io/spring-boot/docs/current/api/org/springframework/boot/autoconfigure/SpringBootApplication) is a standard configuration annotation used by Spring Boot applications. For details, see [Using the @SpringBootApplication](https://docs.spring.io/spring-boot/docs/current/reference/html/using-spring-boot#using-boot-using-springbootapplication-annotation) on the Spring Boot documentation site.

### Schema management

To create and initialize the database schema, the application uses [Liquibase](https://www.liquibase.org/).

Liquibase uses files called [changelogs](https://docs.liquibase.com/concepts/basic/changelog) to manage the changes to the database. Changelog files include a list of instructions, known as [changesets](https://docs.liquibase.com/concepts/basic/changeset), that are executed against the database in a specified order.

`resources/db/changelog-master.xml` defines the changelog for this application:

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <!-- Note: Context names match Spring profile names -->

    <changeSet id="1" author="root" context="crdb">
        <validCheckSum>ANY</validCheckSum>
        <sqlFile path="db/create.sql"/>
    </changeSet>

    <changeSet id="2" author="root" context="crdb">
        <insert tableName="account">
            <column name="id">1</column>
            <column name="name">Alice</column>
            <column name="balance" valueNumeric="500.00"/>
            <column name="type">asset</column>
        </insert>
        <insert tableName="account">
            <column name="id">2</column>
            <column name="name">Bob</column>
            <column name="balance" valueNumeric="500.00"/>
            <column name="type">expense</column>
        </insert>
        <insert tableName="account">
            <column name="id">3</column>
            <column name="name">Bobby Tables</column>
            <column name="balance" valueNumeric="500.00"/>
            <column name="type">asset</column>
        </insert>
        <insert tableName="account">
            <column name="id">4</column>
            <column name="name">Doris</column>
            <column name="balance" valueNumeric="500.00"/>
            <column name="type">expense</column>
        </insert>
    </changeSet>
</databaseChangeLog>
```

The first changeset uses [the `sqlFile` tag](https://docs.liquibase.com/change-types/community/sql-file), which tells Liquibase that an external `.sql` file contains some SQL statements to execute. The file specified by the changeset, `resources/db/create.sql`, creates the `account` table:

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
create table account
(
    id      int            not null primary key default unique_rowid(),
    balance numeric(19, 2) not null,
    name    varchar(128)   not null,
    type    varchar(25)    not null
);
```

The second changeset in the changelog uses the [Liquibase XML syntax](https://docs.liquibase.com/concepts/basic/xml-format) to specify a series of sequential `INSERT` statements that initialize the `account` table with some values.

When the application is started, all of the queries specified by the changesets are executed in the order specified by their `changeset` tag's `id` value. At application startup, Liquibase also creates a table called [`databasechangelog`](https://docs.liquibase.com/concepts/databasechangelog-table) in the database where it performs changes. This table's rows log all completed changesets.

To see the completed changesets, open a new terminal, start the <InternalLink path="cockroach-sql">built-in SQL shell</InternalLink>, and query the `databasechangelog` table:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach sql --certs-dir=certs
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM roach_data.databasechangelog;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  id | author |             filename              |           dateexecuted           | orderexecuted | exectype |               md5sum               |                                              description                                               | comments | tag  | liquibase | contexts | labels | deployment_id
-----+--------+-----------------------------------+----------------------------------+---------------+----------+------------------------------------+--------------------------------------------------------------------------------------------------------+----------+------+-----------+----------+--------+----------------
  1  | root   | classpath:db/changelog-master.xml | 2020-06-17 14:57:01.431506+00:00 |             1 | EXECUTED | 8:939a1a8c47676119a94d0173802d207e | sqlFile                                                                                                |          | NULL | 3.8.9     | crdb     | NULL   | 2420221402
  2  | root   | classpath:db/changelog-master.xml | 2020-06-17 14:57:01.470847+00:00 |             2 | EXECUTED | 8:c2945f2a445cf60b4b203e1a91d14a89 | insert tableName=account; insert tableName=account; insert tableName=account; insert tableName=account |          | NULL | 3.8.9     | crdb     | NULL   | 2420221402
(2 rows)
```

Typically, Liquibase properties are defined in a separate [`liquibase.properties`](https://docs.liquibase.com/workflows/liquibase-community/creating-config-properties) file. In this application, `application.yml`, the properties file that sets the [Spring properties](https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties), also includes some properties that enable and configure Liquibase:

```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
...
 liquibase:
   change-log: classpath:db/changelog-master.xml
   default-schema:
   drop-first: false
   contexts: crdb
   enabled: true
...
```

### Domain entities

`Account.java` defines the [entity](https://wikipedia.org/wiki/Domain-driven_design#Building_blocks) for the `accounts` table. This class is used throughout the application to represent a row of data in the `accounts` table.

Here are the contents of [`Account.java`](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jpa/src/main/java/io/roach/data/jpa/Account.java):

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

@Entity
@Table(name = "account")
public class Account {
    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 128, nullable = false, unique = true)
    private String name;

    @Column(length = 25, nullable = false)
    @Enumerated(EnumType.STRING)
    private AccountType type;

    @Column(length = 25, nullable = false)
    private BigDecimal balance;

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public AccountType getType() {
        return type;
    }

    public BigDecimal getBalance() {
        return balance;
    }
}
```

Spring Data JPA supports standard Java Persistence API (JPA) annotations for domain entity class definitions. The `Account` class definition uses these annotations to create the `accounts` table entity:

* `@Entity` declares the `Account` an entity class.
* `@Table` associates the entity with the persisted `account` table.
* `@Column` declare each private attribute a column of the `account` table.
* `@GeneratedValue` indicates that the value for the column should be automatically generated.
* `@Id` declares the <InternalLink path="primary-key">primary key column</InternalLink> of the table.
* `@Enumerated` specifies the type of data that the column holds.

### Hypermedia representation

To represent database objects as [HAL+JSON](https://wikipedia.org/wiki/Hypertext_Application_Language) for the REST API, the application extends the Spring HATEOAS module's [RepresentationModel](https://docs.spring.io/spring-hateoas/docs/current/reference/html#fundamentals.representation-models) class with `AccountModel`. Like the `Account` class, its attributes represent a row of data in the `accounts` table.

The contents of [`AccountModel.java`](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jpa/src/main/java/io/roach/data/jpa/AccountModel.java):

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

@Relation(value = "account", collectionRelation = "accounts")
public class AccountModel extends RepresentationModel<AccountModel> {
    private String name;

    private AccountType type;

    private BigDecimal balance;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public AccountType getType() {
        return type;
    }

    public void setType(AccountType type) {
        this.type = type;
    }

    public BigDecimal getBalance() {
        return balance;
    }

    public void setBalance(BigDecimal balance) {
        this.balance = balance;
    }
}
```

We do not go into much detail about hypermedia representation in this tutorial. For more information, see the [Spring HATEOAS Reference Documentation](https://docs.spring.io/spring-hateoas/docs/current/reference/html).

### Spring repositories

To abstract the database layer, Spring applications use the [`Repository` interface](https://docs.spring.io/spring-data/jpa/docs/current/reference/html#repositories), or some subinterface of `Repository`. This interface maps to a database object, like a table, and its methods map to queries against that object, like a <InternalLink path="selection-queries">`SELECT`</InternalLink> or an <InternalLink path="insert">`INSERT`</InternalLink> statement against a table.

[`AccountRepository.java`](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jpa/src/main/java/io/roach/data/jpa/AccountRepository.java) defines the main repository for the `accounts` table:

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

import java.math.BigDecimal;

import javax.persistence.LockModeType;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import static org.springframework.transaction.annotation.Propagation.MANDATORY;

@Repository
@Transactional(propagation = MANDATORY)
public interface AccountRepository extends JpaRepository<Account, Long>, JpaSpecificationExecutor<Account> {
    @Query(value = "select balance from Account where id=?1")
    @Lock(LockModeType.PESSIMISTIC_READ)
    BigDecimal getBalance(Long id);

    @Modifying
    @Query("update Account set balance = balance + ?2 where id=?1")
    void updateBalance(Long id, BigDecimal balance);
}
```

`AccountRepository` extends a subinterface of `Repository` that is provided by Spring for JPA data access called `JpaRepository`. The `AccountRepository` methods use the [`@Query`](https://docs.spring.io/spring-data/jpa/docs/current/reference/html#jpa.query-methods.at-query) annotation strategy to define queries manually, as strings.

Note that, in addition to having the `@Repository` annotation, the `AccountRepository` interface has a [`@Transactional` annotation](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#transaction-declarative-annotations). When [transaction management](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#transaction-declarative) is enabled in an application (i.e., with [`@EnableTransactionManagement`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/transaction/annotation/EnableTransactionManagement)), Spring automatically wraps all objects with the `@Transactional` annotation in [a proxy](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-understanding-aop-proxies) that handles calls to the object.

`@Transactional` takes a number of parameters, including a `propagation` parameter that determines the transaction propagation behavior around an object (i.e., at what point in the stack a transaction starts and ends). This sample application follows the [entity-control-boundary (ECB) pattern](https://wikipedia.org/wiki/Entity-control-boundary). As such, the [REST service boundaries](#rest-controller) should determine where a <InternalLink path="transactions">transaction</InternalLink> starts and ends rather than the query methods defined in the data access layer. To follow the ECB design pattern, `propagation=MANDATORY` for `AccountRepository`, which means that a transaction must already exist in order to call the `AccountRepository` query methods. In contrast, the `@Transactional` annotations on the [Rest controller entities](#rest-controller) in the web layer have `propagation=REQUIRES_NEW`, meaning that a new transaction must be created for each REST request.

For details about control flow and transaction management in this application, see [Transaction management](#transaction-management). For more general information about Spring transaction management, see [Understanding the Spring Framework’s Declarative Transaction Implementation](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#tx-decl-explained) on Spring's documentation website.

### REST controller

There are several endpoints exposed by the application's web layer, some of which monitor the health of the application, and some that map to queries executed against the connected database. All of the endpoints served by the application are handled by the `AccountController` class, which is defined in [`AccountController.java`](https://github.com/cockroachlabs/roach-data/tree/master/roach-data-jpa/src/main/java/io/roach/data/jpa/AccountController.java):

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

@RestController
public class AccountController {
    @Autowired
    private AccountRepository accountRepository;

    @Autowired
    private PagedResourcesAssembler<Account> pagedResourcesAssembler;

    @GetMapping
    public ResponseEntity<RepresentationModel> index() {
        RepresentationModel index = new RepresentationModel();

        index.add(linkTo(methodOn(AccountController.class)
                .listAccounts(PageRequest.of(0, 5)))
                .withRel("accounts"));

        index.add(linkTo(AccountController.class)
                .slash("transfer{?fromId,toId,amount}")
                .withRel("transfer"));

        return new ResponseEntity<>(index, HttpStatus.OK);
    }

    @GetMapping("/account")
    @Transactional(propagation = REQUIRES_NEW)
    public HttpEntity<PagedModel<AccountModel>> listAccounts(
            @PageableDefault(size = 5, direction = Sort.Direction.ASC) Pageable page) {
        return ResponseEntity
                .ok(pagedResourcesAssembler.toModel(accountRepository.findAll(page), accountModelAssembler()));
    }

    @GetMapping(value = "/account/{id}")
    @Transactional(propagation = REQUIRES_NEW)
    public HttpEntity<AccountModel> getAccount(@PathVariable("id") Long accountId) {
        return new ResponseEntity<>(accountModelAssembler().toModel(accountRepository.getOne(accountId)),
                HttpStatus.OK);
    }

    @PostMapping(value = "/transfer")
    @Transactional(propagation = REQUIRES_NEW)
    public HttpEntity<BigDecimal> transfer(
            @RequestParam("fromId") Long fromId,
            @RequestParam("toId") Long toId,
            @RequestParam("amount") BigDecimal amount
    ) {
        if (amount.compareTo(BigDecimal.ZERO) < 0) {
            throw new IllegalArgumentException("Negative amount");
        }
        if (fromId.equals(toId)) {
            throw new IllegalArgumentException("From and to accounts must be different");
        }

        BigDecimal fromBalance = accountRepository.getBalance(fromId).add(amount.negate());

//        if (fromBalance.compareTo(BigDecimal.ZERO) < 0) {
//            throw new NegativeBalanceException("Insufficient funds " + amount + " for account " + fromId);
//        }

        accountRepository.updateBalance(fromId, amount.negate());
        accountRepository.updateBalance(toId, amount);

        return ResponseEntity.ok().build();
    }

    private RepresentationModelAssembler<Account, AccountModel> accountModelAssembler() {
        return (entity) -> {
            AccountModel model = new AccountModel();
            model.setName(entity.getName());
            model.setType(entity.getType());
            model.setBalance(entity.getBalance());
            model.add(linkTo(methodOn(AccountController.class)
                    .getAccount(entity.getId())
            ).withRel(IanaLinkRelations.SELF));
            return model;
        };
    }
}
```

Annotated with [`@RestController`](https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/web/bind/annotation/RestController), `AccountController` defines the primary [web controller](https://wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller) component of the application. The `AccountController` methods define the endpoints, routes, and business logic of REST services for account querying and money transferring. Its attributes include an instantiation of [`AccountRepository`](#spring-repositories), called `accountRepository`, that establishes an interface to the `accounts` table through the data access layer.

As mentioned in the [Spring repositories](#spring-repositories) section, the application's transaction boundaries follow the [entity-control-boundary (ECB) pattern](https://wikipedia.org/wiki/Entity-control-boundary), meaning that the web service boundaries of the application determine where a <InternalLink path="transactions">transaction</InternalLink> starts and ends. To follow the ECB pattern, the `@Transactional` annotation on each of the HTTP entities (`listAccounts()`, `getAccount()`, and `transfer()`) has `propagation=REQUIRES_NEW`. This ensures that each time a REST request is made to an endpoint, a new transaction context is created.

For details on how aspects handle control flow and transaction management in the application, see [Transaction management](#transaction-management).

### Transaction management

When [transaction management](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#transaction-declarative) is enabled in an application, Spring automatically wraps all objects annotated with `@Transactional` in [a proxy](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-understanding-aop-proxies) that handles calls to the object. By default, this proxy starts and closes transactions according to the configured transaction management behavior (e.g., the `propagation` level). The proxy methods that handle transactions make up the *primary transaction advisor*.

Using [@AspectJ annotations](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#transaction-declarative-aspectj), this sample application extends the default transaction proxy behavior to handle [transaction retries](#transaction-retries) with another explicitly-defined [aspect](https://wikipedia.org/wiki/Aspect_\(computer_programming\)): `RetryableTransactionAspect`. Methods of this aspect are declared as [advice](https://wikipedia.org/wiki/Advice_\(programming\)) to be executed around method calls annotated with `@Transactional`.

#### Ordering advice

To determine the order of evaluation when multiple transaction advisors match the same [pointcut](https://wikipedia.org/wiki/Pointcut) (in this case, around `@Transactional` method calls), this application explicitly declares an order of precedence for calling advice.

The [`@Order`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/core/annotation/Order) annotation takes a value that indicates the precedence of its advice. In the case of `RetryableTransactionAspect`, the annotation is passed `Ordered.LOWEST_PRECEDENCE-1`, which places the retry advisor one level of precedence above the lowest level. By default, the primary transaction advisor has the lowest level of precedence (`Ordered.LOWEST_PRECEDENCE`). This means that the retry logic will be evaluated before a transaction is opened.

For more details about advice ordering, see [Advice Ordering](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-ataspectj-advice-ordering) on the Spring documentation site.

#### Transaction retries

Transactions may require retries if they experience deadlock or <InternalLink path="performance-best-practices-overview#transaction-contention">transaction contention</InternalLink> that cannot be resolved without allowing <InternalLink path="demo-serializable">serialization</InternalLink> anomalies. To handle transactions that are aborted due to transient serialization errors, we highly recommend writing <InternalLink path="transaction-retry-error-reference#client-side-retry-handling">client-side transaction retry logic</InternalLink> into applications written on CockroachDB.

In this application, transaction retry logic is written into the methods of the `RetryableTransactionAspect` class, declared an aspect with the `@Aspect` annotation. Here are the contents of [`RetryableTransactionAspect.java`](https://github.com/cockroachlabs/roach-data/blob/master/roach-data-jpa/src/main/java/io/roach/data/jpa/RetryableTransactionAspect.java):

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
package io.roach.data.jpa;

@Component
@Aspect
@Order(Ordered.LOWEST_PRECEDENCE - 1)
public class RetryableTransactionAspect {
    protected final Logger logger = LoggerFactory.getLogger(getClass());

    private final int retryAttempts = 30;

    private final int maxBackoff = 15000;

    @Pointcut("execution(* io.roach..*(..)) && @annotation(transactional)")
    public void anyTransactionBoundaryOperation(Transactional transactional) {
    }

    @Around(value = "anyTransactionBoundaryOperation(transactional)",
            argNames = "pjp,transactional")
    public Object doInTransaction(ProceedingJoinPoint pjp, Transactional transactional)
            throws Throwable {
        int numCalls = 0;

        final Instant callTime = Instant.now();

        do {
            try {
                numCalls++;
                Object rv = pjp.proceed();
                if (numCalls > 1) {
                    logger.debug(
                            "Transient error recovered after " + numCalls + " of " + retryAttempts + " retries ("
                                    + Duration.between(callTime, Instant.now()).toString() + ")");
                }
                return rv;
            } catch (TransientDataAccessException | TransactionSystemException ex) { // TX abort on commit's
                Throwable cause = NestedExceptionUtils.getMostSpecificCause(ex);
                if (cause instanceof SQLException) {
                    SQLException sqlException = (SQLException) cause;
                    if ("40001".equals(sqlException.getSQLState())) { // Transient error code
                        handleTransientException(sqlException, numCalls, pjp.getSignature().toShortString());
                        continue;
                    }
                }

                throw ex;
            } catch (UndeclaredThrowableException ex) {
                Throwable t = ex.getUndeclaredThrowable();
                while (t instanceof UndeclaredThrowableException) {
                    t = ((UndeclaredThrowableException) t).getUndeclaredThrowable();
                }

                Throwable cause = NestedExceptionUtils.getMostSpecificCause(ex);
                if (cause instanceof SQLException) {
                    SQLException sqlException = (SQLException) cause;
                    if ("40001".equals(sqlException.getSQLState())) { // Transient error code
                        handleTransientException(sqlException, numCalls, pjp.getSignature().toShortString());
                        continue;
                    }
                }

                throw ex;
            }
        } while (numCalls < retryAttempts);

        throw new ConcurrencyFailureException("Too many transient errors (" + numCalls + ") for method ["
                + pjp.getSignature().toShortString() + "]. Giving up!");
    }

    private void handleTransientException(SQLException ex, int numCalls, String method) {
        try {
            long backoffMillis = Math.min((long) (Math.pow(2, numCalls) + Math.random() * 1000), maxBackoff);
            if (numCalls <= 1 && logger.isWarnEnabled()) {
                logger.warn("Transient error detected (backoff {}ms) in call {} to '{}': {}",
                        backoffMillis, numCalls, method, ex.getMessage());
            }
            Thread.sleep(backoffMillis);
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}
```

The `anyTransactionBoundaryOperation` method is declared as a pointcut with the [`@Pointcut` annotation](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-pointcuts). In Spring, pointcut declarations must include an expression to determine where [join points](https://wikipedia.org/wiki/Join_point) occur in the application control flow. To help define these expressions, Spring supports a set of [designators](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-pointcuts-designators). The application uses two of them here: `execution`, which matches method execution joint points (i.e., defines a joint point when a specific method is executed, in this case, *any* method in the `io.roach.` namespace), and `@annotation`, which limits the matches to methods with a specific annotation, in this case `@Transactional`.

`retryableOperation` handles the application retry logic in the form of advice. Spring supports [several different annotations to declare advice](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-advice). The [`@Around` annotation](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#aop-ataspectj-around-advice) allows an advice method to work before and after the `anyTransactionBoundaryOperation(transactional)` join point. It also allows the advice method to call the next matching advisor with the `ProceedingJoinPoint.proceed();` method.

`retryableOperation` first verifies that there is no active transaction. It then increments the retry count and attempts to proceed to the next advice method with the `ProceedingJoinPoint.proceed()` method. If the underlying methods (i.e., the primary transaction advisor's methods and the [annotated query methods](#spring-repositories)) succeed, the transaction has been successfully committed to the database. The results are then returned and the application flow continues. If a failure in the underlying layers occurs due to a transient error, then the transaction is retried. The time between each retry grows with each retry until the maximum number of retries is reached.

## See also

Spring documentation:

* [Spring Boot website](https://spring.io/projects/spring-boot)
* [Spring Framework Overview](https://docs.spring.io/spring/docs/current/spring-framework-reference/overview#overview)
* [Spring Core documentation](https://docs.spring.io/spring/docs/current/spring-framework-reference/core#spring-core)
* [Accessing Data with JPA](https://spring.io/guides/gs/accessing-data-jpa)
* [Data Access with JDBC](https://docs.spring.io/spring/docs/current/spring-framework-reference/data-access#jpa)
* [Spring Web MVC](https://docs.spring.io/spring/docs/current/spring-framework-reference/web#mvc)

CockroachDB documentation:

* <InternalLink path="learn-cockroachdb-sql">Learn CockroachDB SQL</InternalLink>
* <InternalLink path="connection-parameters">Client Connection Parameters</InternalLink>
* <InternalLink path="example-apps">Example Apps</InternalLink>
* <InternalLink path="transactions">Transactions</InternalLink>