Build a Hello World App with CockroachDB and JDBC

This tutorial shows you how build a simple Hello World Java application with CockroachDB and the JDBC driver.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB Serverless (beta). The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachDB Cloud account.
  2. Log in to your CockroachDB Cloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select CockroachDB Serverless.

    Note:

    This cluster will be free forever.

  5. Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:

    icon/buttons/copy

    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
    

    Your cert file will be downloaded to %APPDATA%/.postgres/root.crt.

  2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

    Warning:

    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

    icon/buttons/copy

    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<routing-id>'
    

    icon/buttons/copy
    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<routing-id>'
    
    icon/buttons/copy
    cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<routing-id>"
    

    Where:

    • <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
    • <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
    • <serverless-host> is the hostname of the CockroachDB Serverless (beta) cluster.
    • <routing-id> identifies your tenant cluster on a multi-tenant host. For example, funny-skunk-123.
    • <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

    You can find these settings in the Connection parameters tab of the Connection info dialog.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --no-example-database
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

    Note:

    If cockroach demo fails due to SSL authentication, make sure you have cleared any previously downloaded CA certificates from the directory ~/.postgresql.

  3. Take note of the (sql) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (webui)    http://127.0.0.1:8080/demologin?password=demo76950&username=demo
    #   (sql)      postgres://demo:demo76950@127.0.0.1:26257?sslmode=require
    #   (sql/unix) postgres://demo:demo76950@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo070856957&port=26257
    

Step 2. Get the code

Clone the code's GitHub repo:

icon/buttons/copy
$ git clone https://github.com/cockroachlabs/hello-world-java-jdbc/

Check out the cockroachcloud branch:

icon/buttons/copy

git checkout cockroachcloud

The app/src/main/java/example/app/App.java file contains all of the code for the sample Hello World app:

icon/buttons/copy
package example.app;

import org.postgresql.ds.PGSimpleDataSource;

public class App {

    public static void main(String[] args) {

        try {
            PGSimpleDataSource ds = new PGSimpleDataSource();
            ds.setServerNames(new String[]{"localhost"});
            ds.setPortNumbers(new int[]{26257});
            ds.setDatabaseName("defaultdb");
            ds.setUser("root");
            ds.setSsl(false);
            ds.setSslMode("disable");
            ds.setApplicationName("App");
            System.out.println("Hey! You connected to your CockroachDB cluster.");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

The main method of this program does the following:

  1. Attempts to connect to a running cluster, given some connection information.
  2. Prints a message to the terminal about the connection status.

Step 3. Run the code

Update the connection parameters

In a text editor modify app/src/main/java/example/app/App.java with the settings to connect to the cluster:

icon/buttons/copy
ds.setServerNames(new String[]{"{globalhost}"});
ds.setDatabaseName("{cluster_name}.defaultdb");
ds.setUser("{username}");
ds.setPassword("{password}");
ds.setSslRootCert(System.getenv("{path to the CA certificate}"));

Where:

  • {username} and {password} specify the SQL username and password that you created earlier.
  • {globalhost} is the name of the CockroachDB Serverless (beta) host (e.g., free-tier.gcp-us-central1.cockroachlabs.cloud).
  • {path to the CA certificate} is the path to the cc-ca.crt file that you downloaded from the CockroachDB Cloud Console.
  • {cluster_name} is the name of your cluster.
Note:

If you are using the connection string that you copied from the Connection info modal, your username, password, hostname, and cluster name will be pre-populated.

Tip:

For guidance on connection pooling, with an example using JDBC and HikariCP, see Connection Pooling.

Compile and run the code:

icon/buttons/copy
./gradlew run

The app will prompt you for the password to the demo cluster:

> Task :app:run
Enter the demo password:
<=========----> 75% EXECUTING [22s]

Enter the password.

The output should look like this:

Hey! You successfully connected to your CockroachDB cluster.

See also

You might also be interested in the following pages:

YesYes NoNo