In this blog, We will be exploring CrockroachDB along with Java. We will also take up a few more things like
- How to install CrockroachDB
- How to start a Local cluster
- How to connect CrockroachDB with Java
Alright, Before we jump into installation of CrockroachDB, Let’s find out What is CrockraochDB?
CrockroachDB – 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?
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?
CockroachDB is not a good choice when very low latency reads and writes are critical; use an in-memory database instead.
Also, CrockroachDB is not yet suitable for
- Complex SQL Joins
- Heavy Analytics/OLAP
How to install CrockraochDB?
Installing CorckroachDB is very easy. Let’s see How to install CrockroachDB.
Follow the below link to install CrockroachDB
https://www.cockroachlabs.com/docs/install-cockroachdb.html
How to start a Local cluster?
Once the CrockroachDB is installed. We will be starting up an insecure multi-node cluster locally . To start the Local cluster follow the below steps.
Steps to start Local cluster(in Insecure Mode)
1.Start the first node by executing the following command in terminal
cockroach start --insecure \
--host=localhostThis will start up the first node and You also view the admin UI at http://localhost:8080
By default node1 would start on port 26257.
2. Add 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:26257This will basically add the second node to the cluster on port 26258. Here we are joining the second node with the first node.
3. Add 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:26257This 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 setup a three node cluster locally. Now you can access the admin UI at http://localhost:8080
How to connect CrockroachDB with Java?
Before you begin, make sure you have installed CrockroachDB.
Follow the following steps to build a Java application using jdbc driver.
Step1. Install the Java JDBC driver.
Step2. Start a cluster
Step3. 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
Step4. 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 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 CrockroachDB with Java using JDBC?
Step1. First we will be creating connection to the CrockroachDB 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;
}
}
Step2. Now we will create a class which will have 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");
}
}
Step3. Now that everything is setup, We will be writing a class which will have a main method accessing StudentCRUD class’s method. Write a class with 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 CrockroachDB is complete. To run your application and see the results, you can execute the SampleApp.java class from 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 challenge building the app , you can access the full code at this link on github. You can simply clone the repo and execute the SampleApp.java class.
If you find any challenge, Do let me know in the comments.If you enjoyed this post, I’d be very grateful if you’d help it spread.Keep smiling, Keep coding! Cheers!
