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

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:

  1. $ cockroach sql --certs-dir=certs

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

  1. > CREATE USER IF NOT EXISTS maxroach;
  1. > CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

  1. > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

  1. > \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.

New in v19.1: You can pass the —also-generate-pkcs8-key flag to generate 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.

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

Step 4. Run the Java code

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

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. $ javac -classpath .:/path/to/postgresql.jar BasicSample.java
  1. $ java -classpath .:/path/to/postgresql.jar BasicSample

The output should be:

  1. Initial balances:
  2.     account 1: 1000
  3.     account 2: 250

The contents of BasicSample.java:

  1. import java.sql.*;
  2. import java.util.Properties;
  4. /*
  5.   Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.
  7.   Then, compile and run this example like so:
  9.   $ export CLASSPATH=.:/path/to/postgresql.jar
  10.   $ javac BasicSample.java && java BasicSample
  11. */
  13. public class BasicSample {
  14.     public static void main(String[] args)
  15.         throws ClassNotFoundException, SQLException {
  17.         // Load the Postgres JDBC driver.
  18.         Class.forName("org.postgresql.Driver");
  20.         // Connect to the "bank" database.
  21.         Properties props = new Properties();
  22.         props.setProperty("user", "maxroach");
  23.         props.setProperty("sslmode", "require");
  24.         props.setProperty("sslrootcert", "certs/ca.crt");
  25.         props.setProperty("sslkey", "certs/client.maxroach.key.pk8");
  26.         props.setProperty("sslcert", "certs/client.maxroach.crt");
  27.         props.setProperty("ApplicationName", "roachtest");
  29.         Connection db = DriverManager
  30.             .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);
  32.         try {
  33.             // Create the "accounts" table.
  34.             db.createStatement()
  35.                 .execute("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");
  37.             // Insert two rows into the "accounts" table.
  38.             db.createStatement()
  39.                 .execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");
  41.             // Print out the balances.
  42.             System.out.println("Initial balances:");
  43.             ResultSet res = db.createStatement()
  44.                 .executeQuery("SELECT id, balance FROM accounts");
  45.             while (res.next()) {
  46.                 System.out.printf("\taccount %s: %s\n",
  47.                                   res.getInt("id"),
  48.                                   res.getInt("balance"));
  49.             }
  50.         } finally {
  51.             // Close the database connection.
  52.             db.close();
  53.         }
  54.     }
  55. }

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):
  1. $ javac -classpath .:/path/to/postgresql.jar TxnSample.java
  1. $ java -classpath .:/path/to/postgresql.jar TxnSample

The output should be:

  1. account 1: 900
  2. 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.

  1. import java.sql.*;
  2. import java.util.Properties;
  4. /*
  5.   Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.
  7.   Then, compile and run this example like so:
  9.   $ export CLASSPATH=.:/path/to/postgresql.jar
  10.   $ javac TxnSample.java && java TxnSample
  11. */
  13. // Ambiguous whether the transaction committed or not.
  14. class AmbiguousCommitException extends SQLException{
  15.     public AmbiguousCommitException(Throwable cause) {
  16.         super(cause);
  17.     }
  18. }
  20. class InsufficientBalanceException extends Exception {}
  22. class AccountNotFoundException extends Exception {
  23.     public int account;
  24.     public AccountNotFoundException(int account) {
  25.         this.account = account;
  26.     }
  27. }
  29. // A simple interface that provides a retryable lambda expression.
  30. interface RetryableTransaction {
  31.     public void run(Connection conn)
  32.         throws SQLException, InsufficientBalanceException,
  33.                AccountNotFoundException, AmbiguousCommitException;
  34. }
  36. public class TxnSample {
  37.     public static RetryableTransaction transferFunds(int from, int to, int amount) {
  38.         return new RetryableTransaction() {
  39.             public void run(Connection conn)
  40.                 throws SQLException, InsufficientBalanceException,
  41.                        AccountNotFoundException, AmbiguousCommitException {
  43.                 // Check the current balance.
  44.                 ResultSet res = conn.createStatement()
  45.                     .executeQuery("SELECT balance FROM accounts WHERE id = "
  46.                                   + from);
  47.                 if(!res.next()) {
  48.                     throw new AccountNotFoundException(from);
  49.                 }
  51.                 int balance = res.getInt("balance");
  52.                 if(balance < from) {
  53.                     throw new InsufficientBalanceException();
  54.                 }
  56.                 // Perform the transfer.
  57.                 conn.createStatement()
  58.                     .executeUpdate("UPDATE accounts SET balance = balance - "
  59.                                    + amount + " where id = " + from);
  60.                 conn.createStatement()
  61.                     .executeUpdate("UPDATE accounts SET balance = balance + "
  62.                                    + amount + " where id = " + to);
  63.             }
  64.         };
  65.     }
  67.     public static void retryTransaction(Connection conn, RetryableTransaction tx)
  68.         throws SQLException, InsufficientBalanceException,
  69.                AccountNotFoundException, AmbiguousCommitException {
  71.         Savepoint sp = conn.setSavepoint("cockroach_restart");
  72.         while(true) {
  73.             boolean releaseAttempted = false;
  74.             try {
  75.                 tx.run(conn);
  76.                 releaseAttempted = true;
  77.                 conn.releaseSavepoint(sp);
  78.                 break;
  79.             }
  80.             catch(SQLException e) {
  81.                 String sqlState = e.getSQLState();
  83.                 // Check if the error code indicates a SERIALIZATION_FAILURE.
  84.                 if(sqlState.equals("40001")) {
  85.                     // Signal the database that we will attempt a retry.
  86.                     conn.rollback(sp);
  87.                 } else if(releaseAttempted) {
  88.                     throw new AmbiguousCommitException(e);
  89.                 } else {
  90.                     throw e;
  91.                 }
  92.             }
  93.         }
  94.         conn.commit();
  95.     }
  97.     public static void main(String[] args)
  98.         throws ClassNotFoundException, SQLException {
  100.         // Load the Postgres JDBC driver.
  101.         Class.forName("org.postgresql.Driver");
  103.         // Connect to the 'bank' database.
  104.         Properties props = new Properties();
  105.         props.setProperty("user", "maxroach");
  106.         props.setProperty("sslmode", "require");
  107.         props.setProperty("sslrootcert", "certs/ca.crt");
  108.         props.setProperty("sslkey", "certs/client.maxroach.key.pk8");
  109.         props.setProperty("sslcert", "certs/client.maxroach.crt");
  110.         props.setProperty("ApplicationName", "roachtest");
  112.         Connection db = DriverManager
  113.             .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);
  116.         try {
  117.                 // We need to turn off autocommit mode to allow for
  118.                 // multi-statement transactions.
  119.                 db.setAutoCommit(false);
  121.                 // Perform the transfer. This assumes the 'accounts'
  122.                 // table has already been created in the database.
  123.                 RetryableTransaction transfer = transferFunds(1, 2, 100);
  124.                 retryTransaction(db, transfer);
  126.                 // Check balances after transfer.
  127.                 db.setAutoCommit(true);
  128.                 ResultSet res = db.createStatement()
  129.                     .executeQuery("SELECT id, balance FROM accounts");
  130.                 while (res.next()) {
  131.                     System.out.printf("\taccount %s: %s\n", res.getInt("id"),
  132.                                       res.getInt("balance"));
  133.                 }
  135.             } catch(InsufficientBalanceException e) {
  136.                 System.out.println("Insufficient balance");
  137.             } catch(AccountNotFoundException e) {
  138.                 System.out.println("No users in the table with id " + e.account);
  139.             } catch(AmbiguousCommitException e) {
  140.                 System.out.println("Ambiguous result encountered: " + e);
  141.             } catch(SQLException e) {
  142.                 System.out.println("SQLException encountered:" + e);
  143.             } finally {
  144.                 // Close the database connection.
  145.                 db.close();
  146.             }
  147.     }
  148. }

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

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

To check the account balances, issue the following statement:

  1. > SELECT id, balance FROM accounts;
  1. +----+---------+
  2. | id | balance |
  3. +----+---------+
  4. |  1 |     900 |
  5. |  2 |     350 |
  6. +----+---------+
  7. (2 rows)

Step 2. Create the maxroach user and bank database

Start the built-in SQL client:

  1. $ cockroach sql --insecure

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

  1. > CREATE USER IF NOT EXISTS maxroach;
  1. > CREATE DATABASE bank;

Give the maxroach user the necessary permissions:

  1. > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

  1. > \q

Step 3. Run the Java code

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

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. $ javac -classpath .:/path/to/postgresql.jar BasicSample.java
  1. $ java -classpath .:/path/to/postgresql.jar BasicSample

The contents of BasicSample.java:

  1. import java.sql.*;
  2. import java.util.Properties;
  4. /*
  5.   Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.
  7.   Then, compile and run this example like so:
  9.   $ export CLASSPATH=.:/path/to/postgresql.jar
  10.   $ javac BasicSample.java && java BasicSample
  11. */
  13. public class BasicSample {
  14.     public static void main(String[] args)
  15.         throws ClassNotFoundException, SQLException {
  17.         // Load the Postgres JDBC driver.
  18.         Class.forName("org.postgresql.Driver");
  20.         // Connect to the "bank" database.
  21.         Properties props = new Properties();
  22.         props.setProperty("user", "maxroach");
  23.         props.setProperty("sslmode", "disable");
  25.         Connection db = DriverManager
  26.             .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);
  28.         try {
  29.             // Create the "accounts" table.
  30.             db.createStatement()
  31.                 .execute("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");
  33.             // Insert two rows into the "accounts" table.
  34.             db.createStatement()
  35.                 .execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");
  37.             // Print out the balances.
  38.             System.out.println("Initial balances:");
  39.             ResultSet res = db.createStatement()
  40.                 .executeQuery("SELECT id, balance FROM accounts");
  41.             while (res.next()) {
  42.                 System.out.printf("\taccount %s: %s\n",
  43.                                   res.getInt("id"),
  44.                                   res.getInt("balance"));
  45.             }
  46.         } finally {
  47.             // Close the database connection.
  48.             db.close();
  49.         }
  50.     }
  51. }

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):
  1. $ javac -classpath .:/path/to/postgresql.jar TxnSample.java
  1. $ 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.

  1. import java.sql.*;
  2. import java.util.Properties;
  4. /*
  5.   Download the Postgres JDBC driver jar from https://jdbc.postgresql.org.
  7.   Then, compile and run this example like so:
  9.   $ export CLASSPATH=.:/path/to/postgresql.jar
  10.   $ javac TxnSample.java && java TxnSample
  11. */
  13. // Ambiguous whether the transaction committed or not.
  14. class AmbiguousCommitException extends SQLException{
  15.     public AmbiguousCommitException(Throwable cause) {
  16.         super(cause);
  17.     }
  18. }
  20. class InsufficientBalanceException extends Exception {}
  22. class AccountNotFoundException extends Exception {
  23.     public int account;
  24.     public AccountNotFoundException(int account) {
  25.         this.account = account;
  26.     }
  27. }
  29. // A simple interface that provides a retryable lambda expression.
  30. interface RetryableTransaction {
  31.     public void run(Connection conn)
  32.         throws SQLException, InsufficientBalanceException,
  33.                AccountNotFoundException, AmbiguousCommitException;
  34. }
  36. public class TxnSample {
  37.     public static RetryableTransaction transferFunds(int from, int to, int amount) {
  38.         return new RetryableTransaction() {
  39.             public void run(Connection conn)
  40.                 throws SQLException, InsufficientBalanceException,
  41.                        AccountNotFoundException, AmbiguousCommitException {
  43.                 // Check the current balance.
  44.                 ResultSet res = conn.createStatement()
  45.                     .executeQuery("SELECT balance FROM accounts WHERE id = "
  46.                                   + from);
  47.                 if(!res.next()) {
  48.                     throw new AccountNotFoundException(from);
  49.                 }
  51.                 int balance = res.getInt("balance");
  52.                 if(balance < from) {
  53.                     throw new InsufficientBalanceException();
  54.                 }
  56.                 // Perform the transfer.
  57.                 conn.createStatement()
  58.                     .executeUpdate("UPDATE accounts SET balance = balance - "
  59.                                    + amount + " where id = " + from);
  60.                 conn.createStatement()
  61.                     .executeUpdate("UPDATE accounts SET balance = balance + "
  62.                                    + amount + " where id = " + to);
  63.             }
  64.         };
  65.     }
  67.     public static void retryTransaction(Connection conn, RetryableTransaction tx)
  68.         throws SQLException, InsufficientBalanceException,
  69.                AccountNotFoundException, AmbiguousCommitException {
  71.         Savepoint sp = conn.setSavepoint("cockroach_restart");
  72.         while(true) {
  73.             boolean releaseAttempted = false;
  74.             try {
  75.                 tx.run(conn);
  76.                 releaseAttempted = true;
  77.                 conn.releaseSavepoint(sp);
  78.             }
  79.             catch(SQLException e) {
  80.                 String sqlState = e.getSQLState();
  82.                 // Check if the error code indicates a SERIALIZATION_FAILURE.
  83.                 if(sqlState.equals("40001")) {
  84.                     // Signal the database that we will attempt a retry.
  85.                     conn.rollback(sp);
  86.                     continue;
  87.                 } else if(releaseAttempted) {
  88.                     throw new AmbiguousCommitException(e);
  89.                 } else {
  90.                     throw e;
  91.                 }
  92.             }
  93.             break;
  94.         }
  95.         conn.commit();
  96.     }
  98.     public static void main(String[] args)
  99.         throws ClassNotFoundException, SQLException {
  101.         // Load the Postgres JDBC driver.
  102.         Class.forName("org.postgresql.Driver");
  104.         // Connect to the 'bank' database.
  105.         Properties props = new Properties();
  106.         props.setProperty("user", "maxroach");
  107.         props.setProperty("sslmode", "disable");
  109.         Connection db = DriverManager
  110.             .getConnection("jdbc:postgresql://127.0.0.1:26257/bank", props);
  113.         try {
  114.                 // We need to turn off autocommit mode to allow for
  115.                 // multi-statement transactions.
  116.                 db.setAutoCommit(false);
  118.                 // Perform the transfer. This assumes the 'accounts'
  119.                 // table has already been created in the database.
  120.                 RetryableTransaction transfer = transferFunds(1, 2, 100);
  121.                 retryTransaction(db, transfer);
  123.                 // Check balances after transfer.
  124.                 db.setAutoCommit(true);
  125.                 ResultSet res = db.createStatement()
  126.                     .executeQuery("SELECT id, balance FROM accounts");
  127.                 while (res.next()) {
  128.                     System.out.printf("\taccount %s: %s\n", res.getInt("id"),
  129.                                       res.getInt("balance"));
  130.                 }
  132.             } catch(InsufficientBalanceException e) {
  133.                 System.out.println("Insufficient balance");
  134.             } catch(AccountNotFoundException e) {
  135.                 System.out.println("No users in the table with id " + e.account);
  136.             } catch(AmbiguousCommitException e) {
  137.                 System.out.println("Ambiguous result encountered: " + e);
  138.             } catch(SQLException e) {
  139.                 System.out.println("SQLException encountered:" + e);
  140.             } finally {
  141.                 // Close the database connection.
  142.                 db.close();
  143.             }
  144.     }
  145. }

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

  1. $ cockroach sql --insecure --database=bank

To check the account balances, issue the following statement:

  1. > SELECT id, balance FROM accounts;
  1. +----+---------+
  2. | id | balance |
  3. +----+---------+
  4. |  1 |     900 |
  5. |  2 |     350 |
  6. +----+---------+
  7. (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 page helpful?
YesNo