Build a Spring App with CockroachDB and JPA

On this page

This tutorial shows you how to build a Spring Boot web application with CockroachDB, using the Spring Data JPA module for data access. The code for the example application is available for download from GitHub, along with identical examples that use JDBC, jOOQ, and MyBatis for data access.

Step 1. Start CockroachDB

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

If you haven't already, download the CockroachDB binary.
Start a local, secure cluster.

Create a free cluster

Note:

Organizations without billing information on file can only create one CockroachDB Serverless cluster.

If you haven't already, sign up for a CockroachDB Cloud account.
Log in to your CockroachDB Cloud account.
On the Clusters page, click Create Cluster.
On the Select a plan page, select Serverless.
On the Cloud & Regions page, select a cloud provider (GCP or AWS) in the Cloud provider section.
In the Regions section, select a region for the cluster. Refer to CockroachDB Cloud Regions for the regions where CockroachDB Serverless clusters can be deployed. To create a multi-region cluster, click Add region and select additional regions. A cluster can have at most six regions.
Click Next: Capacity.
On the Capacity page, select Start for free. Click Next: Finalize.
On the Finalize page, 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:

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:
```
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.
```
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.
```
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.
Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

Warning:

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.
```
cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt'
```
```
cockroach sql --url "postgresql://<username>:<password>@<serverless-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.
- <serverless-host> is the hostname of the CockroachDB Serverless 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

Open a SQL shell to your local cluster using the cockroach sql command:
```
$ cockroach sql --certs-dir={certs-dir} --host=localhost:{port}
```
Where {certs_dir} is the full path to the certs directory that you created when setting up the cluster, and {port} is the port at which the cluster is listening for incoming connections.
In the SQL shell, create the roach_data database that your application will use:
```
> CREATE DATABASE roach_data;
```
Create a SQL user for your app:
```
> CREATE USER {username} WITH PASSWORD {password};
```
Take note of the username and password. You will use it to connect to the database later.
Give the user the necessary permissions:
```
> GRANT ALL ON DATABASE roach_data TO {username};
```
Exit the shell, and generate a certificate and key for your user by running the following command:

$ cockroach cert create-client {user} --certs-dir=certs --ca-key={certs-dir}/ca.key --also-generate-pkcs8-key

The --also-generate-pkcs8-key flag generates a key in PKCS#8 format, which is the standard key encoding format in Java. In this case, the generated PKCS8 key will be named client.{user}.key.pk8.

If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:
```
$ cockroach sql \
--url='postgres://{username}:{password}@{global host}:26257/{cluster_name}.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 {certs_dir} placeholder with the path to the certs directory that you created earlier.

Note:

CockroachDB Cloud does not yet support certificate-based user authentication.
In the SQL shell, create the roach_data database that your application will use:
```
> 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.

Step 4. Install Maven

This example application uses Maven to manage all application dependencies. Spring supports Maven versions 3.2 and later.

To install Maven on macOS, run the following command:

$ brew install maven

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

$ apt-get install maven

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

$ dnf install maven

For other ways to install Maven, see its official documentation.

Step 5. Get the application code

To get the application code, download or clone the roach-data repository. 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 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 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:

<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 atomic database transactions on the running CockroachDB cluster. For details about the application code, see 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:

...
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:

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

...
datasource:
  url: jdbc:postgresql://{globalhost}:{port}/{cluster_name}.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:

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

Note:

If you are using the connection string that you copied from the Connection info modal, your username, password, hostname, and cluster name will be pre-populated.

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

$ cd <path>/roach-data/roach-data-jpa

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

$ mvn clean install

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

$ java -jar target/roach-data-jpa.jar

The output should look like the following:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: 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, and then runs some test operations as requests to the application's REST API.

For more details about the application code, see 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.

$ curl -X GET http://localhost:9090/account | json_pp

{
   "_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:

$ curl -X GET http://localhost:9090/account/1 | json_pp

{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/1"
      }
   },
   "balance" : 500,
   "name" : "Alice",
   "type" : "asset"
}

$ curl -X GET http://localhost:9090/account/2 | json_pp

{
   "_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., INSERTs and UPDATEs) 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).

$ 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:

$ curl -X GET http://localhost:9090/account/1 | json_pp

{
   "_links" : {
      "self" : {
         "href" : "http://localhost:9090/account/1"
      }
   },
   "balance" : 650,
   "name" : "Alice",
   "type" : "asset"
}

$ curl -X GET http://localhost:9090/account/2 | json_pp

{
   "_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:

package io.roach.data.jpa;

import java.math.BigDecimal;
import java.util.ArrayDeque;
import java.util.Deque;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ScheduledExecutorService;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.WebApplicationType;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.builder.SpringApplicationBuilder;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.hateoas.Link;
import org.springframework.hateoas.config.EnableHypermediaSupport;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.client.HttpStatusCodeException;
import org.springframework.web.client.RestTemplate;

@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 enables hypermedia support for resource representation in the application. Currently, the only hypermedia format supported by Spring is HAL, and so the type = EnableHypermediaSupport.HypermediaType.HAL. For details, see Hypermedia representation.
@EnableJpaRepositories enables the creation of Spring repositories for data access using Spring Data JPA. For details, see Spring repositories.
@EnableAspectJAutoProxy enables the use of @AspectJ annotations for declaring aspects. For details, see Transaction management.
@EnableTransactionManagement enables declarative transaction management in the application. For details, see 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.
@SpringBootApplication is a standard configuration annotation used by Spring Boot applications. For details, see Using the @SpringBootApplication on the Spring Boot documentation site.

Schema management

To create and initialize the database schema, the application uses Liquibase.

Liquibase uses files called changelogs to manage the changes to the database. Changelog files include a list of instructions, known as changesets, that are executed against the database in a specified order.

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

<?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, 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:

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 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 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 built-in SQL shell, and query the databasechangelog table:

$ cockroach sql --certs-dir=certs

> SELECT * FROM roach_data.databasechangelog;

  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 file. In this application, application.yml, the properties file that sets the Spring properties, also includes some properties that enable and configure Liquibase:

...
 liquibase:
   change-log: classpath:db/changelog-master.xml
   default-schema:
   drop-first: false
   contexts: crdb
   enabled: true
...

Domain entities

Account.java defines the entity 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:

package io.roach.data.jpa;

import java.math.BigDecimal;

import javax.persistence.*;

@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 primary key column of the table.
@Enumerated specifies the type of data that the column holds.

Hypermedia representation

To represent database objects as HAL+JSON for the REST API, the application extends the Spring HATEOAS module's RepresentationModel class with AccountModel. Like the Account class, its attributes represent a row of data in the accounts table.

The contents of AccountModel.java:

package io.roach.data.jpa;

import java.math.BigDecimal;

import org.springframework.hateoas.RepresentationModel;
import org.springframework.hateoas.server.core.Relation;

@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.

Spring repositories

To abstract the database layer, Spring applications use the Repository interface, 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 SELECT or an INSERT statement against a table.

AccountRepository.java defines the main repository for the accounts table:

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 annotation strategy to define queries manually, as strings.

Note that, in addition to having the @Repository annotation, the AccountRepository interface has a @Transactional annotation. When transaction management is enabled in an application (i.e., with @EnableTransactionManagement), Spring automatically wraps all objects with the @Transactional annotation in a proxy 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. As such, the REST service boundaries should determine where a transaction 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 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. For more general information about Spring transaction management, see Understanding the Spring Framework’s Declarative Transaction Implementation 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:

package io.roach.data.jpa;

import java.math.BigDecimal;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.web.PageableDefault;
import org.springframework.data.web.PagedResourcesAssembler;
import org.springframework.hateoas.IanaLinkRelations;
import org.springframework.hateoas.PagedModel;
import org.springframework.hateoas.RepresentationModel;
import org.springframework.hateoas.server.RepresentationModelAssembler;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import static org.springframework.hateoas.server.mvc.WebMvcLinkBuilder.linkTo;
import static org.springframework.hateoas.server.mvc.WebMvcLinkBuilder.methodOn;
import static org.springframework.transaction.annotation.Propagation.REQUIRES_NEW;

@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, AccountController defines the primary web controller 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, called accountRepository, that establishes an interface to the accounts table through the data access layer.

As mentioned in the Spring repositories section, the application's transaction boundaries follow the entity-control-boundary (ECB) pattern, meaning that the web service boundaries of the application determine where a transaction 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

When transaction management is enabled in an application, Spring automatically wraps all objects annotated with @Transactional in a proxy 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, this sample application extends the default transaction proxy behavior to handle transaction retries with another explicitly-defined aspect: RetryableTransactionAspect. Methods of this aspect are declared as advice 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 (in this case, around @Transactional method calls), this application explicitly declares an order of precedence for calling advice.

The @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 on the Spring documentation site.

Transaction retries

Transactions may require retries if they experience deadlock or transaction contention that cannot be resolved without allowing serialization anomalies. To handle transactions that are aborted due to transient serialization errors, we highly recommend writing client-side transaction retry logic 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:

package io.roach.data.jpa;

import java.lang.reflect.UndeclaredThrowableException;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.NestedExceptionUtils;
import org.springframework.core.Ordered;
import org.springframework.core.annotation.Order;
import org.springframework.dao.ConcurrencyFailureException;
import org.springframework.dao.TransientDataAccessException;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionSystemException;
import org.springframework.transaction.annotation.Transactional;

@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. In Spring, pointcut declarations must include an expression to determine where join points occur in the application control flow. To help define these expressions, Spring supports a set of 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. The @Around annotation 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) 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.

Cockroach
University

Docs Hub

Build a Spring App with CockroachDB and JPA

Step 1. Start CockroachDB

Create a free cluster

Set up your cluster connection

Step 2. Create a database and a user

Step 3. Install JDK

Step 4. Install Maven

Step 5. Get the application code

Step 6. Run the application

Query the database

Reads

Writes

Implementation details

Main application process

Schema management

Domain entities

Hypermedia representation

Spring repositories

REST controller

Transaction management

Ordering advice

Transaction retries

See also

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Build a Spring App with CockroachDB and JPA

Step 1. Start CockroachDB

Create a free cluster

Set up your cluster connection

Step 2. Create a database and a user

Step 3. Install JDK

Step 4. Install Maven

Step 5. Get the application code

Step 6. Run the application

Query the database

Reads

Writes

Implementation details

Main application process

Schema management

Domain entities

Hypermedia representation

Spring repositories

REST controller

Transaction management

Ordering advice

Transaction retries

See also

Cockroach
University

Cockroach
University