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:
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:
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 BasicSampleThe 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:
- Download
TxnSample.java, or create the file yourself and copy the code below. Note the use ofSQLException.getSQLState()instead ofgetErrorCode(). 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 TxnSampleThe output should be:
account 1: 900 account 2: 350
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:
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 ofSQLException.getSQLState()instead ofgetErrorCode(). 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
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
Was this helpful?
