# Build a Java App with CockroachDB

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

1. Install CockroachDB.
2. Start up a secure or insecure local cluster.
3. 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:

1. Download `BasicSample.java`, or create the file yourself and copy the code below.
2. Download the PostgreSQL JDBC driver.

3. 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");
            props.setProperty("ApplicationName", "roachtest");

            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:

1. Download `TxnSample.java`, or create the file yourself and copy the code below. Note the use of `SQLException.getSQLState()` instead of `getErrorCode()`.

2. 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");
            props.setProperty("ApplicationName", "roachtest");

            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:

1. Download `BasicSample.java`, or create the file yourself and copy the code below.
2. Download the PostgreSQL JDBC driver.

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

1. Download `TxnSample.java`, or create the file yourself and copy the code below. Note the use of `SQLException.getSQLState()` instead of `getErrorCode()`.

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

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);
                }
                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:

- Client Connection Parameters
- Data Replication
- Fault Tolerance & Recovery
- Automatic Rebalancing
- Cross-Cloud Migration
- Follow-the-Workload
- Automated Operations