Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

CockroachDB With Java Using JDBC

DZone's Guide to

CockroachDB With Java Using JDBC

Cockroach is built to automatically replicate, rebalance, and recover. Learn how to install it, start a local cluster on it, and connect it with Java.

· Database Zone
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

In this blog, we will be exploring CockroachDB along with Java. We will also take up a few more things like:

  1. How to install CockroachDB.
  2. How to start a local cluster.
  3. How to connect CockroachDB with Java.

Alright, before we jump into the installation of CockroachDB, let’s find out what CockroachDB is.

What Is CockroachDB?

According to the GitHub page:

CockroachDB is a cloud-native SQL database for building global, scalable cloud services that survive disasters. CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. It scales horizontally; survives disk, machine, rack, and even datacenter failures with minimal latency disruption and no manual intervention; supports strongly-consistent ACID transactions; and provides a familiar SQL API for structuring, manipulating, and querying data.

When Is CockroachDB a Good Choice?

According to the Cockroach Labs FAQ:

CockroachDB is well-suited for applications that require reliable, available, and correct data regardless of scale. It is built to automatically replicate, rebalance, and recover with minimal configuration and operational overhead.

When Is CockroachDB Not a Good Choice?

Also according to the Cockroach Labs FAQ:

CockroachDB is not a good choice when very low latency reads and writes are critical; use an in-memory database instead. Also, CockroachDB is not yet suitable for:

1. Complex SQL Joins.
2. Heavy Analytics/OLAP.

How to Install CockroachDB

Installing CorckroachDB is very easy. Let’s see how to install CockroachDB. Follow this link to install CockroachDB.

How to Start a Local Cluster

Once CockroachDB is installed, we'll start up an insecure multi-node cluster locally. To start the local cluster, follow the below steps.

1. Starting the First Node

Start the first node by executing the following command in the terminal:

cockroach start --insecure \
--host=localhost

This will start up the first node. You can also view the admin UI at http://localhost:8080.

By default, node1 will start on port 26257.

2. Adding the Second Node

Add the second node to the cluster by executing the following command in a new terminal:

cockroach start --insecure \
--store=node2 \
--host=localhost \
--port=26258 \
--http-port=8081 \
--join=localhost:26257

This will basically add the second node to the cluster on port 26258. Here, we are joining the second node with the first node.

3. Adding the Third Node

Add the third node to the cluster by executing the following command in a new terminal:

cockroach start --insecure \
--store=node3 \
--host=localhost \
--port=26259 \
--http-port=8082 \
--join=localhost:26257

This will basically add the third node to the cluster on port 26259. Here, we are joining the third node with the first node.

So, we have successfully set up a three-node cluster locally. Now, you can access the admin UI at http://localhost:8080.

Cockroach Console

How to Connect CockroachDB With Java

Make sure you have installed CockroachDB. Then, go ahead and follow these steps to build a Java application using the JDBC driver.

  1. Install the Java JDBC driver.

  2. Start a cluster.

  3. Create a user.

In a new terminal, as the root user, use the Cockroach user command to create a new user: testuser.

cockroach user set testuser --insecure

Create a Database and Grant Privileges

As the root user, use the built-in SQL client to create a school database: cockroach sql --insecure -e 'CREATE DATABASE bank'.

Then grant privileges to the testuser user.

cockroach sql --insecure -e 'GRANT ALL ON DATABASE school TO testuser'

Now that everything is in place — we've started the cluster, created the database, and granted all the privileges — we will write the Java code.

Now that you have a database and a user, you’ll run code to create a table and insert some rows, and then you’ll run code to read and update values as an atomic transaction.

How to Connect CockroachDB With Java Using JDBC

Follow these three steps to connect CockroachDB with Java using JDBC.

1. Create a Connection Using a Singleton Class

First, we will be creating connection to the CockroachDB using a singleton class like below

public class DBConnection {


 private static DBConnection dbInstance;
 private static Connection con;


 private DBConnection() {
  // private constructor //
 }

 public static DBConnection getInstance() {
  if (dbInstance == null) {
   dbInstance = new DBConnection();
  }
  return dbInstance;
 }

 public Connection getConnection() {

  if (con == null) {
   String url = "jdbc:postgresql://127.0.0.1:26257/";
   String dbName = "school?sslmode=disable";
   String driver = "org.postgresql.Driver";
   String userName = "testuser";
   String password = "";
   try {
    Class.forName(driver).newInstance();
    this.con = DriverManager.getConnection(url + dbName, userName, password);
   } catch (Exception ex) {
    ex.printStackTrace();
   }
  }

  return con;
 }
}

2. Create a Class With Basic Student CRUD Methods

Now we will create a class with basic student CRUD methods like below.

public class StudentCRUD {

 static Connection con = DBConnection.getInstance().getConnection();
 public static void insertStudent() throws SQLException {
  con.createStatement().execute("INSERT INTO school.student (id, name) VALUES (11, 'Deepak'), (22, 'Abhishek')");
 }

 public static void selectStudent() throws SQLException {
  ResultSet res = con.createStatement().executeQuery("SELECT id, name FROM student");
  while (res.next()) {
   System.out.printf("\tStudent %s: %s\n", res.getInt("id"), res.getString("name"));
  }
 }

 public static void createTable() throws SQLException {
  // Create the "student" table.
  con.createStatement().execute("CREATE TABLE IF NOT EXISTS student (id INT PRIMARY KEY, name varchar(30))");

 }

 public static void deleteStudent() throws SQLException {
  // Create the "student" table.
  con.createStatement().execute("delete from student where id=22");

 }

 public static void updateStudent() throws SQLException {
  // Create the "student" table.
  con.createStatement().execute("update student set name='deepak mehra' where id=11");

 }
}

3. Write a Class With a Main Method

Now that everything is set up, we'll write a class with a main method accessing the StudentCRUD class’s method. Write a class with the main method like below.

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

 // Connect to the "school" database.
 try {
  // Create the "student" table.
  StudentCRUD.createTable();
  //Insert data into "student" table.
  StudentCRUD.insertStudent();
  //Select data from "student" table.
  StudentCRUD.selectStudent();
  //Delete data from "student" table.
  StudentCRUD.deleteStudent();
  System.out.println("\tPrinting student after deleting id :: 22");
  //Select data from "student" table.
  StudentCRUD.selectStudent();
  //Update data into "student" table.
  StudentCRUD.updateStudent();
  System.out.println("\tPrinting student after updating id :: 11");
  //Select data from "student" table.
  StudentCRUD.selectStudent();

 } catch (Exception ex) {
  ex.printStackTrace();
 }

}

}

Now, your Java application with CockroachDB is complete. To run your application and see the results, you can execute the SampleApp.java class from the console or directly from the IDE you are using.

Note: Before you execute the SampleApp.java class, make sure the cluster is up and running with the database name school and user testuser with all the privileges. If you have followed the blog from the beginning, you don’t have to do anything. Otherwise, you will have to create a user and grant privileges or you can simply go ahead with the default user, which is root.

If you are having any challenges building the app, you can access the full code at this page on GitHub. You can simply clone the repo and execute the SampleApp.java class.

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,tutorial ,cockroachdb ,jdbc

Published at DZone with permission of Deepak Mehra, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}