Build a Java App with CockroachDB

On this page

Warning:

As of October 4, 2019, CockroachDB v2.0 is no longer supported. For more details, refer to the Release Support Policy.

This tutorial shows you how build a simple Java application with CockroachDB using a PostgreSQL-compatible driver or ORM.

We have tested the Java JDBC driver and the Hibernate ORM enough to claim beta-level support, so those are featured here. If you encounter problems, please open an issue with details to help us make progress toward full support.

Before you begin

Install CockroachDB.
Start up a secure or insecure local cluster.
Choose the instructions that correspond to whether your cluster is secure or insecure:

Warning:

The examples on this page assume you are using a Java version <= 9. They do not work with Java 10.

Step 1. Install the Java JDBC driver

Download and set up the Java JDBC driver as described in the official documentation.

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

Start the built-in SQL client:

$ cockroach sql --certs-dir=certs

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

> CREATE USER IF NOT EXISTS maxroach;

> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

> \q

Step 3. 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.

$ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

Step 4. Convert the key file for use with Java

The private key generated for user maxroach by CockroachDB is PEM encoded. To read the key in a Java application, you will need to convert it into PKCS#8 format, which is the standard key encoding format in Java.

To convert the key to PKCS#8 format, run the following OpenSSL command on the maxroach user's key file in the directory where you stored your certificates:

$ openssl pkcs8 -topk8 -inform PEM -outform DER -in client.maxroach.key -out client.maxroach.pk8 -nocrypt

Step 5. Run the Java code

Now that you have created a database and set up encryption keys, in this section you will:

Create a table and insert some rows
Execute a batch of statements as a transaction

Basic example

First, use the following code to connect as the maxroach user and execute some basic SQL statements: create a table, insert rows, and read and print the rows.

To run it:

Download BasicSample.java, or create the file yourself and copy the code below.
Download the PostgreSQL JDBC driver.
Compile and run the code (adding the PostgreSQL JDBC driver to your classpath):
```
$ javac -classpath .:/path/to/postgresql.jar BasicSample.java
```
```
$ java -classpath .:/path/to/postgresql.jar BasicSample
```
The output should be:
```
Initial balances:
    account 1: 1000
    account 2: 250
```

The contents of BasicSample.java:

import java.sql.*;
import java.util.Properties;

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac BasicSample.java && java BasicSample
*/

public class BasicSample {
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the "bank" database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        props.setProperty("sslmode", "require");
        props.setProperty("sslrootcert", "certs/ca.crt");
        props.setProperty("sslkey", "certs/client.maxroach.pk8");
        props.setProperty("sslcert", "certs/client.maxroach.crt");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);

        try {
            // Create the "accounts" table.
            db.createStatement()
                .execute("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");

            // Insert two rows into the "accounts" table.
            db.createStatement()
                .execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

            // Print out the balances.
            System.out.println("Initial balances:");
            ResultSet res = db.createStatement()
                .executeQuery("SELECT id, balance FROM accounts");
            while (res.next()) {
                System.out.printf("\taccount %s: %s\n",
                                  res.getInt("id"),
                                  res.getInt("balance"));
            }
        } finally {
            // Close the database connection.
            db.close();
        }
    }
}

Transaction example (with retry logic)

Next, use the following code to execute a batch of statements as a transaction to transfer funds from one account to another.

To run it:

Download TxnSample.java, or create the file yourself and copy the code below. Note the use of SQLException.getSQLState() instead of getErrorCode().
Compile and run the code (again adding the PostgreSQL JDBC driver to your classpath):
```
$ javac -classpath .:/path/to/postgresql.jar TxnSample.java
```
```
$ java -classpath .:/path/to/postgresql.jar TxnSample
```
The output should be:
```
account 1: 900
account 2: 350
```

Note:

With the default SERIALIZABLE isolation level, CockroachDB may require the client to retry a transaction in case of read/write contention. CockroachDB provides a generic retry function that runs inside a transaction and retries it as needed. The code sample below shows how it is used.

import java.sql.*;
import java.util.Properties;

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac TxnSample.java && java TxnSample
*/

// Ambiguous whether the transaction committed or not.
class AmbiguousCommitException extends SQLException{
    public AmbiguousCommitException(Throwable cause) {
        super(cause);
    }
}

class InsufficientBalanceException extends Exception {}

class AccountNotFoundException extends Exception {
    public int account;
    public AccountNotFoundException(int account) {
        this.account = account;
    }
}

// A simple interface that provides a retryable lambda expression.
interface RetryableTransaction {
    public void run(Connection conn)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException;
}

public class TxnSample {
    public static RetryableTransaction transferFunds(int from, int to, int amount) {
        return new RetryableTransaction() {
            public void run(Connection conn)
                throws SQLException, InsufficientBalanceException,
                       AccountNotFoundException, AmbiguousCommitException {

                // Check the current balance.
                ResultSet res = conn.createStatement()
                    .executeQuery("SELECT balance FROM accounts WHERE id = "
                                  + from);
                if(!res.next()) {
                    throw new AccountNotFoundException(from);
                }

                int balance = res.getInt("balance");
                if(balance < from) {
                    throw new InsufficientBalanceException();
                }

                // Perform the transfer.
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance - "
                                   + amount + " where id = " + from);
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance + "
                                   + amount + " where id = " + to);
            }
        };
    }

    public static void retryTransaction(Connection conn, RetryableTransaction tx)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException {

        Savepoint sp = conn.setSavepoint("cockroach_restart");
        while(true) {
            boolean releaseAttempted = false;
            try {
                tx.run(conn);
                releaseAttempted = true;
                conn.releaseSavepoint(sp);
                break;
            }
            catch(SQLException e) {
                String sqlState = e.getSQLState();

                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if(sqlState.equals("40001")) {
                    // Signal the database that we will attempt a retry.
                    conn.rollback(sp);
                } else if(releaseAttempted) {
                    throw new AmbiguousCommitException(e);
                } else {
                    throw e;
                }
            }
        }
        conn.commit();
    }

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the 'bank' database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        props.setProperty("sslmode", "require");
        props.setProperty("sslrootcert", "certs/ca.crt");
        props.setProperty("sslkey", "certs/client.maxroach.pk8");
        props.setProperty("sslcert", "certs/client.maxroach.crt");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);


        try {
                // We need to turn off autocommit mode to allow for
                // multi-statement transactions.
                db.setAutoCommit(false);

                // Perform the transfer. This assumes the 'accounts'
                // table has already been created in the database.
                RetryableTransaction transfer = transferFunds(1, 2, 100);
                retryTransaction(db, transfer);

                // Check balances after transfer.
                db.setAutoCommit(true);
                ResultSet res = db.createStatement()
                    .executeQuery("SELECT id, balance FROM accounts");
                while (res.next()) {
                    System.out.printf("\taccount %s: %s\n", res.getInt("id"),
                                      res.getInt("balance"));
                }

            } catch(InsufficientBalanceException e) {
                System.out.println("Insufficient balance");
            } catch(AccountNotFoundException e) {
                System.out.println("No users in the table with id " + e.account);
            } catch(AmbiguousCommitException e) {
                System.out.println("Ambiguous result encountered: " + e);
            } catch(SQLException e) {
                System.out.println("SQLException encountered:" + e);
            } finally {
                // Close the database connection.
                db.close();
            }
    }
}

To verify that funds were transferred from one account to another, start the built-in SQL client:

$ cockroach sql --certs-dir=certs --database=bank

To check the account balances, issue the following statement:

> SELECT id, balance FROM accounts;

+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

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

Start the built-in SQL client:

$ cockroach sql --insecure

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

> CREATE USER IF NOT EXISTS maxroach;

> CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

> GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

> \q

Step 3. Run the Java code

Now that you have created a database, in this section you will:

Create a table and insert some rows
Execute a batch of statements as a transaction

Basic example

First, use the following code to connect as the maxroach user and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows.

To run it:

Download BasicSample.java, or create the file yourself and copy the code below.
Download the PostgreSQL JDBC driver.
Compile and run the code (adding the PostgreSQL JDBC driver to your classpath):
```
$ javac -classpath .:/path/to/postgresql.jar BasicSample.java
```
```
$ java -classpath .:/path/to/postgresql.jar BasicSample
```

The contents of BasicSample.java:

import java.sql.*;
import java.util.Properties;

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac BasicSample.java && java BasicSample
*/

public class BasicSample {
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the "bank" database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        props.setProperty("sslmode", "disable");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);

        try {
            // Create the "accounts" table.
            db.createStatement()
                .execute("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");

            // Insert two rows into the "accounts" table.
            db.createStatement()
                .execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

            // Print out the balances.
            System.out.println("Initial balances:");
            ResultSet res = db.createStatement()
                .executeQuery("SELECT id, balance FROM accounts");
            while (res.next()) {
                System.out.printf("\taccount %s: %s\n",
                                  res.getInt("id"),
                                  res.getInt("balance"));
            }
        } finally {
            // Close the database connection.
            db.close();
        }
    }
}

Transaction example (with retry logic)

Next, use the following code to execute a batch of statements as a transaction to transfer funds from one account to another.

To run it:

Download TxnSample.java, or create the file yourself and copy the code below. Note the use of SQLException.getSQLState() instead of getErrorCode().
Compile and run the code (again adding the PostgreSQL JDBC driver to your classpath):
```
$ javac -classpath .:/path/to/postgresql.jar TxnSample.java
```
```
$ java -classpath .:/path/to/postgresql.jar TxnSample
```

Note:

import java.sql.*;
import java.util.Properties;

/*
  Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.

  Then, compile and run this example like so:

  $ export CLASSPATH=.:/path/to/postgresql.jar
  $ javac TxnSample.java && java TxnSample
*/

// Ambiguous whether the transaction committed or not.
class AmbiguousCommitException extends SQLException{
    public AmbiguousCommitException(Throwable cause) {
        super(cause);
    }
}

class InsufficientBalanceException extends Exception {}

class AccountNotFoundException extends Exception {
    public int account;
    public AccountNotFoundException(int account) {
        this.account = account;
    }
}

// A simple interface that provides a retryable lambda expression.
interface RetryableTransaction {
    public void run(Connection conn)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException;
}

public class TxnSample {
    public static RetryableTransaction transferFunds(int from, int to, int amount) {
        return new RetryableTransaction() {
            public void run(Connection conn)
                throws SQLException, InsufficientBalanceException,
                       AccountNotFoundException, AmbiguousCommitException {

                // Check the current balance.
                ResultSet res = conn.createStatement()
                    .executeQuery("SELECT balance FROM accounts WHERE id = "
                                  + from);
                if(!res.next()) {
                    throw new AccountNotFoundException(from);
                }

                int balance = res.getInt("balance");
                if(balance < from) {
                    throw new InsufficientBalanceException();
                }

                // Perform the transfer.
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance - "
                                   + amount + " where id = " + from);
                conn.createStatement()
                    .executeUpdate("UPDATE accounts SET balance = balance + "
                                   + amount + " where id = " + to);
            }
        };
    }

    public static void retryTransaction(Connection conn, RetryableTransaction tx)
        throws SQLException, InsufficientBalanceException,
               AccountNotFoundException, AmbiguousCommitException {

        Savepoint sp = conn.setSavepoint("cockroach_restart");
        while(true) {
            boolean releaseAttempted = false;
            try {
                tx.run(conn);
                releaseAttempted = true;
                conn.releaseSavepoint(sp);
            }
            catch(SQLException e) {
                String sqlState = e.getSQLState();

                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if(sqlState.equals("40001")) {
                    // Signal the database that we will attempt a retry.
                    conn.rollback(sp);
                    continue;
                } else if(releaseAttempted) {
                    throw new AmbiguousCommitException(e);
                } else {
                    throw e;
                }
            }
            break;
        }
        conn.commit();
    }

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException {

        // Load the Postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the 'bank' database.
        Properties props = new Properties();
        props.setProperty("user", "maxroach");
        props.setProperty("sslmode", "disable");

        Connection db = DriverManager
            .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);


        try {
                // We need to turn off autocommit mode to allow for
                // multi-statement transactions.
                db.setAutoCommit(false);

                // Perform the transfer. This assumes the 'accounts'
                // table has already been created in the database.
                RetryableTransaction transfer = transferFunds(1, 2, 100);
                retryTransaction(db, transfer);

                // Check balances after transfer.
                db.setAutoCommit(true);
                ResultSet res = db.createStatement()
                    .executeQuery("SELECT id, balance FROM accounts");
                while (res.next()) {
                    System.out.printf("\taccount %s: %s\n", res.getInt("id"),
                                      res.getInt("balance"));
                }

            } catch(InsufficientBalanceException e) {
                System.out.println("Insufficient balance");
            } catch(AccountNotFoundException e) {
                System.out.println("No users in the table with id " + e.account);
            } catch(AmbiguousCommitException e) {
                System.out.println("Ambiguous result encountered: " + e);
            } catch(SQLException e) {
                System.out.println("SQLException encountered:" + e);
            } finally {
                // Close the database connection.
                db.close();
            }
    }
}

To verify that funds were transferred from one account to another, start the built-in SQL client:

$ cockroach sql --insecure --database=bank

To check the account balances, issue the following statement:

> SELECT id, balance FROM accounts;

+----+---------+
| id | balance |
+----+---------+
|  1 |     900 |
|  2 |     350 |
+----+---------+
(2 rows)

What's next?

Read more about using the Java JDBC driver.

You might also be interested in using a local cluster to explore the following CockroachDB benefits:

Was this helpful?

Cockroach
University

Docs Hub

Build a Java App with CockroachDB

Before you begin

Step 1. Install the Java JDBC driver

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

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

Step 4. Convert the key file for use with Java

Step 5. Run the Java code

Basic example

Transaction example (with retry logic)

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

Step 3. Run the Java code

Basic example

Transaction example (with retry logic)

What's next?

Cockroach University

Docs Hub

Cockroach University

Docs Hub

Build a Java App with CockroachDB

Before you begin

Step 1. Install the Java JDBC driver

Step 2. Create the maxroach user and bank database

Step 3. Generate a certificate for the maxroach user

Step 4. Convert the key file for use with Java

Step 5. Run the Java code

Basic example

Transaction example (with retry logic)

Step 2. Create the maxroach user and bank database

Step 3. Run the Java code

Basic example

Transaction example (with retry logic)

What's next?

Cockroach
University

Cockroach
University

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

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

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