Introduction to Roach Data

Using different Java data access frameworks with CockroachDB

·

4 min read

Roach Data is a collection of small demos using different Java data access frameworks and ORMs with CockroachDB.

The demo projects include:

  • JDBC - using Spring Data JDBC which is just a simpler wrapper around JDBC

  • JDBC (plain) - using plain JDBC without Spring Data JDBC

  • JPA - using Spring Data JPA with Hibernate as ORM provider

  • JPA Orders - using Spring Data JPA to model a very simple purchase order system

  • jOOQ - using Spring Boot with jOOQ (which is not officially supported by spring-data)

  • MyBatis - using Spring Data MyBatis/JDBC

  • JSON - using Spring Data JPA and JSONB types with inverted indexes

  • Reactive - using Spring Data r2dbc with the reactive PSQL driver

The demos are independent but use a similar schema and test workload, except for the Orders and JSON demos.

The demos cover the following concepts/features:

  • Liquibase Schema versioning

  • Connection Pooling via HikariCP

  • Executable jar with embedded Jetty container

  • Pagination via Spring Data JPA

  • Transaction retries with exponential backoffs

Source Code

The source code for the project can be found on GitHub.

Project Setup

The project is packaged as a single executable JAR file and runs on any platform for which there is a Java 8+ runtime.

Prerequisites

Setup CockroachDB

Create a local cluster of at least three nodes:

cockroach start --port=26257 --http-port=8080 --advertise-addr=localhost:26257 --join=localhost:26257 --insecure --store=datafiles/n1 --background

cockroach start --port=26258 --http-port=8081 --advertise-addr=localhost:26258 --join=localhost:26257 --insecure --store=datafiles/n2 --background

cockroach start --port=26259 --http-port=8082 --advertise-addr=localhost:26259 --join=localhost:26257 --insecure --store=datafiles/n3 --background

cockroach init --insecure --host=localhost:26257

Next, set up a database called roach_data:

cockroach sql --insecure --host=localhost:26257 -e "CREATE database roach_data"

Setup the Demos

Install the JDK

Install the JDK (Ubuntu example):

sudo apt-get install openjdk-8-jdk

Confirm the installation by running:

java -version

Clone the project

git clone git@github.com:cockroachlabs/roach-data.git
cd roach-data

Build the executable jars

chmod +x mvnw
./mvnw clean install

Running

Most demos will do the same thing which is to run through a series of concurrent account transfer requests. The requests are intentionally submitted in a way that will cause contention (by overlapping reads and writes on the same keys) in the database and trigger transaction aborts and retries.

By default, the contention level is zero (effectively serial execution) so you won't see any transient errors. To observe serialization conflict errors, pass a number (>1) to the command line representing the thread count. Then you should start seeing transaction conflicts and retries until the demo settles with an end message:

"All client workers finished but the server keeps running. Have a nice day!"

The service remains running after the test is complete and can be accessed via: http://localhost:9090.

JDBC demo

The JDBC demo uses Spring Data JDBC.

In this example, we are passing a custom JDBC URL to connect to a specific IP (default is localhost).

java -jar roach-data-jdbc/target/roach-data-jdbc.jar --spring.datasource.url=jdbc:postgresql://192.168.1.66:26257/roach_data?sslmode=disable

To run with higher contention/retries:

java -jar roach-data-jdbc/target/roach-data-jdbc.jar --concurrency=8

JPA demo

The JPA demo uses Spring Data JPA with Hibernate. It's run in the same way as the previous JDBC demo.

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

JPA Orders Demo

The JPA Orders demo also uses Spring Data JPA with Hibernate but deviates a bit and models purchase orders.

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

jOOQ demo

The jOOQ demo does not use Spring Data (unsupported) but the Spring Boot jOOQ starter.

java -jar roach-data-jooq/target/roach-data-jooq.jar

MyBatis demo

The MyBatis demo use the MyBatis data access strategy on top of Spring Data JDBC.

java -jar roach-data-mybatis/target/roach-data-mybatis.jar

Reactive demo

The reactive demo uses the Spring Boot r2dbc starter.

java -jar roach-data-reactive/target/roach-data-reactive.jar

Future Work

The next addition to the framework collection will likely be JDBI, yet another JDBC wrapper as an alternative to more advanced ORMs.

These demos are based on Spring 2.7 and Java 8. Spring Boot / Data 3 is gaining momentum so it may be time to migrate to the latest Spring 3 version and Java 17 LTS.

Conclusion

Roach Data is a collection of small Spring Boot demos that demonstrate how CockroachDB can be used with a mainstream Java framework stack. The demos run through a series of concurrent account transfer requests.