title	summary	toc	twitter	filter_category	filter_html	filter_sort	docs_area
Build a Spring App with CockroachDB and MyBatis	Learn how to use CockroachDB from a simple Spring application with MyBatis.	true	false	crud_java	Use <strong>MyBatis-Spring</strong>	4	get_started

{% include filter-tabs.md %}

This tutorial shows you how to build a simple Spring Boot application with CockroachDB, using the MyBatis-Spring-Boot-Starter module for data access.

Before you begin

{% include {{page.version.version}}/app/before-you-begin.md %}

Step 1. Install JDK

Download and install a Java Development Kit. MyBatis-Spring supports Java versions 8+. In this tutorial, we use JDK 11 from OpenJDK.

Step 2. Install Gradle

This example application uses Gradle to manage all application dependencies. Spring supports Gradle versions 6+.

To install Gradle on macOS, run the following command:

{% include_cached copy-clipboard.html %}

$ brew install gradle

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

{% include_cached copy-clipboard.html %}

$ apt-get install gradle

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

{% include_cached copy-clipboard.html %}

$ dnf install gradle

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

Step 3. Get the application code

To get the application code, download or clone the mybatis-cockroach-demo repository.

Step 4. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

{% include_cached copy-clipboard.html %}

$ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the maxroach user and bank database:

{% include_cached copy-clipboard.html %}

> CREATE USER IF NOT EXISTS maxroach;

{% include_cached copy-clipboard.html %}

> CREATE DATABASE bank;

Give the bank user the necessary permissions:

{% include_cached copy-clipboard.html %}

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

{% include_cached copy-clipboard.html %}

> \q

Step 5. Generate a certificate for the `maxroach` user

Create a certificate and key for the maxroach user by running the following command. The code samples will run as this user.

{% include_cached copy-clipboard.html %}

$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/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.maxroach.key.pk8.

Step 6. Run the application

To run the application:

Open and edit the src/main/resources/application.yml file so that the url field specifies the full connection string to the running CockroachDB cluster. To connect to a secure cluster, this connection string must set the sslmode connection parameter to require, and specify the full path to the client, node, and user certificates in the connection parameters. For example:
```
...
datasource:
  url: jdbc:postgresql://localhost:26257/bank?ssl=true&sslmode=require&sslrootcert=certs/ca.crt&sslkey=certs/client.maxroach.key.pk8&sslcert=certs/client.maxroach.crt
...
```
Open a terminal, and navigate to the mybatis-cockroach-demo project directory:

{% include_cached copy-clipboard.html %}
```
$ cd <path>/mybatis-cockroach-demo
```
Run the Gradle script to download the application dependencies, compile the code, and run the application:

{% include_cached copy-clipboard.html %}
```
$ ./gradlew bootRun
```

Start the built-in SQL shell:

{% include_cached copy-clipboard.html %}

$ cockroach sql --insecure

In the SQL shell, issue the following statements to create the maxroach user and bank database:

{% include_cached copy-clipboard.html %}

> CREATE USER IF NOT EXISTS maxroach;

{% include_cached copy-clipboard.html %}

> CREATE DATABASE bank;

Give the bank user the necessary permissions:

{% include_cached copy-clipboard.html %}

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

{% include_cached copy-clipboard.html %}

> \q

Step 6. Run the application

To run the application:

Open and edit the src/main/resources/application.yml file so that the url field specifies the full connection string to the running CockroachDB cluster. For example:
```
...
datasource:
  url: jdbc:postgresql://localhost:26257/bank?ssl=false
...
```
Open a terminal, and navigate to the mybatis-cockroach-demo project directory:

{% include_cached copy-clipboard.html %}
```
$ cd <path>/mybatis-cockroach-demo
```
Run the Gradle script to download the application dependencies, compile the code, and run the application:

{% include_cached copy-clipboard.html %}
```
$ ./gradlew bootRun
```

The output should look like the following:

> Task :bootRun

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.2.6.RELEASE)

2020-06-01 14:40:04.333  INFO 55970 --- [           main] c.e.c.CockroachDemoApplication           : Starting CockroachDemoApplication on MyComputer with PID 55970 (path/mybatis-cockroach-demo/build/classes/java/main started by user in path/mybatis-cockroach-demo)
2020-06-01 14:40:04.335  INFO 55970 --- [           main] c.e.c.CockroachDemoApplication           : No active profile set, falling back to default profiles: default
2020-06-01 14:40:05.195  INFO 55970 --- [           main] c.e.c.CockroachDemoApplication           : Started CockroachDemoApplication in 1.39 seconds (JVM running for 1.792)
2020-06-01 14:40:05.216  INFO 55970 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-06-01 14:40:05.611  INFO 55970 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
deleteAllAccounts:
    => 2 total deleted accounts
insertAccounts:
    => 2 total new accounts in 1 batches
printNumberOfAccounts:
    => Number of accounts at time '14:40:05.660226':
    => 2 total accounts
printBalances:
    => Account balances at time '14:40:05.678942':
    ID 1 => $1000
    ID 2 => $250
transferFunds:
    => $100 transferred between accounts 1 and 2, 2 rows updated
printBalances:
    => Account balances at time '14:40:05.688511':
    ID 1 => $900
    ID 2 => $350
bulkInsertRandomAccountData:
    => finished, 500 total rows inserted in 1 batches
printNumberOfAccounts:
    => Number of accounts at time '14:40:05.960214':
    => 502 total accounts
2020-06-01 14:40:05.968  INFO 55970 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2020-06-01 14:40:05.993  INFO 55970 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

BUILD SUCCESSFUL in 12s
3 actionable tasks: 3 executed

The application runs a number of test functions that result in reads and writes to the accounts table in the bank database.

For more details about the application code, see Application details.

Application details

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

Main process

The main process of the application is defined in src/main/java/com/example/cockroachdemo/CockroachDemoApplication.java:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/CockroachDemoApplication.java %}

The SpringApplication.run call in the main method bootstraps and launches a Spring application. The @SpringBootApplication annotation on the CockroachDemoApplication class triggers Spring's component scanning and auto-configuration features.

The BasicExample class, defined in src/main/java/com/example/cockroachdemo/BasicExample.java, is one of the components detected in the component scan:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/BasicExample.java %}

BasicExample implements the Spring CommandLineRunner interface. Implementations of this interface automatically run when detected in a Spring project directory. BasicExample runs a series of test methods that are eventually executed as SQL queries in the data access layer of the application.

Configuration

All MyBatis-Spring applications need a DataSource, a SqlSessionFactory, and at least one mapper interface. The MyBatis-Spring-Boot-Starter module, built on MyBatis and MyBatis-Spring, and used by this application, greatly simplifies how you configure each of these required elements.

Applications that use MyBatis-Spring-Boot-Starter typically need just an annotated mapper interface and an existing DataSource in the Spring environment. The module detects the DataSource, creates a SqlSessionFactory from the DataSource, creates a thread-safe SqlSessionTemplate with the SqlSessionFactory, and then auto-scans the mappers and links them to the SqlSessionTemplate for injection. The SqlSessionTemplate automatically commits, rolls back, and closes sessions, based on the application's Spring-based transaction configuration.

This sample application implements batch write operations, a CockroachDB best practice for executing multiple INSERT and UPSERT statements. MyBatis applications that support batch operations require some additional configuration work, even if the application uses MyBatis-Spring-Boot-Starter:

The application must define a specific mapper interface for batch query methods.
The application must define a SqlSessionTemplate constructor, specifically for batch operations, that uses the BATCH executor type.
The batch mapper must be explicitly registered with the batch-specific SqlSessionTemplate.

The class defined in src/main/java/com/example/cockroachdemo/MyBatisConfiguration.java configures the application to meet these requirements:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/MyBatisConfiguration.java %}

This class explicitly defines the batch SqlSessionTemplate (i.e., batchSqlSessionTemplate), and registers batchmapper, the batch mapper interface defined in src/main/java/com/example/cockroachdemo/batchmapper/BatchMapper.java with batchSqlSessionTemplate. To complete the MyBatis configuration, the class also declares a DataSource, and defines the remaining SqlSessionFactory and SqlSessionTemplate beans.

Note that a configuration class is not required for MyBatis-Spring-Boot-Starter applications that do not implement batch operations.

Data source

src/main/resources/application.yml contains the metadata used to create a connection to the CockroachDB cluster:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/resources/application.yml %}

Spring Boot uses the application's datasource property to auto-configure the database connection. This database connection configuration can be injected into the application's SqlSessionFactoryBean, as is explicitly done in the MyBatisConfiguration configuration class definition.

Mappers

All MyBatis applications require at least one mapper interface. These mappers take the place of manually-defined data access objects (DAOs). They provide other layers of the application an interface to the database.

MyBatis-Spring-Boot-Starter usually scans the project for interfaces annotated with @Mapper, links the interfaces to a SqlSessionTemplate, and registers them with Spring so they can be injected into the application's Spring beans. As mentioned in the Configuration section, because the application supports batch writes, the two mapper interfaces in the application are registered and linked manually in the MyBatisConfiguration configuration class definition.

Account mapper

src/main/java/com/example/cockroachdemo/mapper/AccountMapper.java defines the mapper interface to the accounts table using the MyBatis Java API:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/mapper/AccountMapper.java %}

The @Mapper annotation declares the interface a mapper for MyBatis to scan. The SQL statement annotations on each of the interface methods map them to SQL queries. For example, the first method, deleteAllAccounts() is marked as a DELETE statement with the @Delete annotation. This method executes the SQL statement specified in the string passed to the annotation, "delete from accounts", which deletes all rows in the accounts table.

Batch account mapper

src/main/java/com/example/cockroachdemo/batchmapper/BatchAccountMapper.java defines a mapper interface for batch writes:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/batchmapper/BatchAccountMapper.java %}

This interface has a single INSERT statement query method, along with a method for flushing (i.e., executing) a batch of statements.

Services

src/main/java/com/example/cockroachdemo/service/AccountService.java defines the service interface, with a number of methods for reading and writing to the database:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/service/AccountService.java %}

MyBatisAccountService.java implements the AccountService interface, using the mappers defined in AccountMapper.java and BatchAccountMapper.java, and the models defined in Account.java and BatchResults.java:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/service/MyBatisAccountService.java %}

Note that the public methods (i.e., the methods to be called by other classes in the project) are annotated as @Transactional methods. This ensures that all of the SQL statements executed in the data access layer are run within the context of a database transaction

@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). propagation=REQUIRES_NEW for the methods in the service layer, meaning that a new transaction must be created each time a request is made to the service layer. With this propagation behavior, the application follows the entity-control-boundary (ECB) pattern, as the service boundaries determine where a transaction starts and ends rather than the lower-level query methods of the mapper interfaces.

For more details on aspect-oriented transaction management in this application, see below.

Models

Instances of the Account class, defined in src/main/java/com/example/cockroachdemo/model/Account.java, represent rows in the accounts table:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/model/Account.java %}

Instances of the BatchResults class, defined in src/main/java/com/example/cockroachdemo/model/BatchResults.java, hold metadata about a batch write operation and its results:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/model/BatchResults.java %}

Transaction management

MyBatis-Spring supports Spring's declarative, aspect-oriented transaction management syntax, including the @Transactional annotation and AspectJ's AOP annotations.

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, defined in src/main/java/com/example/cockroachdemo/RetryableTransactionAspect.java:

{% include_cached copy-clipboard.html %}

{% remote_include https://raw.githubusercontent.com/jeffgbutler/mybatis-cockroach-demo/master/src/main/java/com/example/cockroachdemo/RetryableTransactionAspect.java %}

The @Aspect annotation declares RetryableTransactionAspect an aspect, with pointcut and advice methods.

Transactional pointcut

The @Pointcut annotation declares the anyTransactionBoundaryOperation method the pointcut for determining when to execute the aspect's advice. The @annotation designator passed to the @Pointcut annotation limits the matches (i.e., join points) to method calls with a specific annotation, in this case, @Transactional.

Transaction retry advice

retryableOperation handles the application retry logic, with exponential backoff, as the advice to execute at an anyTransactionBoundaryOperation(transactional) join point. Spring supports several different annotations to declare advice. The @Around annotation allows an advice method to work before and after the join point. It also gives the advice method control over executing any other matching advisors.

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 data access layer method (i.e., the mapper interface method annotated with @Transactional) succeeds, the results are returned and the application flow continues. If the method fails, then the transaction is retried. The time between each retry grows with each retry until the maximum number of retries is reached.

Advice ordering

Spring automatically adds transaction management advice to all methods annotated with @Transactional. Because the pointcut for RetryableTransactionAspect also matches methods annotated with @Transactional, there will always be two advisors that match the same pointcut. When multiple advisors match at the same pointcut, an @Order annotation on an advisor's aspect can specify the order in which the advice should be evaluated.

To control when and how often a transaction is retried, the transaction retry advice must be executed outside the context of a transaction (i.e., it must be evaluated before the primary transaction management advisor). By default, the primary transaction management advisor is given the lowest level of precedence. The @Order annotation on RetryableTransactionAspect is passed Ordered.LOWEST_PRECEDENCE-1, which places this aspect's advice at a level of precedence above the primary transaction advisor, which results in the retry logic being evaluated before the transaction management advisor.

For more details about advice ordering in Spring, see Advice Ordering on the Spring documentation site.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

build-a-spring-app-with-cockroachdb-mybatis.md

build-a-spring-app-with-cockroachdb-mybatis.md

Before you begin

Step 1. Install JDK

Step 2. Install Gradle

Step 3. Get the application code

Step 4. Create the `maxroach` user and `bank` database

Step 5. Generate a certificate for the `maxroach` user

Step 6. Run the application

Step 6. Run the application

Application details

Main process

Configuration

Data source

Mappers

Account mapper

Batch account mapper

Services

Models

Transaction management

Transactional pointcut

Transaction retry advice

Advice ordering

See also

Files

build-a-spring-app-with-cockroachdb-mybatis.md

Latest commit

History

build-a-spring-app-with-cockroachdb-mybatis.md

File metadata and controls

Before you begin

Step 1. Install JDK

Step 2. Install Gradle

Step 3. Get the application code

Step 4. Create the maxroach user and bank database

Step 5. Generate a certificate for the maxroach user

Step 6. Run the application

Step 6. Run the application

Application details

Main process

Configuration

Data source

Mappers

Account mapper

Batch account mapper

Services

Models

Transaction management

Transactional pointcut

Transaction retry advice

Advice ordering

See also

Step 4. Create the `maxroach` user and `bank` database

Step 5. Generate a certificate for the `maxroach` user