Apr 24, 2018

Apache Cassandra CRUD operation Using Java client : Step by step guide to setup Java client for CRUD operation with Cassandra

In previous post we used CQLSH Client for performing CRUD operation and created Keyspace, Table and inserted records in it. In this post we will use Java client to achieve the same.
In order to follow below post Apache Cassandra should be up and running. Here I have used a single node cluster with default settings of host and port to connect Cassandra database.
To simulate CRUD operation with Cassandra using Java client, we will create a Java project and under that create various classes to perform CRUD operations in Cassandra.

1. Create Java Project and convert into maven project or Create a Maven project
2. Add dependency for Cassandra in pom.xml
<dependencies>
    <dependency>
        <groupId>com.datastax.cassandra</groupId>
        <artifactId>cassandra-driver-core</artifactId>
        <version>3.1.0</version>
    </dependency>
</dependencies>

3. Connection(Cluster and Session) class: Create a class "CASConnection" for storing connection objects (Cluster and Session). In Cassandra, session object is created with cluster object.

import com.datastax.driver.core.Cluster;
import com.datastax.driver.core.Host;
import com.datastax.driver.core.Metadata;
import com.datastax.driver.core.Session;

/**
 * 
 * @author nikhil(www.devinline.com)
 * 
 */
public class CASConnection {
 /* Cassandra Cluster. */
 private Cluster cluster;
 /* Cassandra Session. */
 private Session session;

 public CASConnection() {

 }

 public CASConnection(String node, Integer port) {
  connectionUtils(node, port);
 }

 public Cluster getCluster() {
  return cluster;
 }

 public void setCluster(Cluster cluster) {
  this.cluster = cluster;
 }

 public Session getSession() {
  return session;
 }

 public void setSession(Session session) {
  this.session = session;
 }

 public void connectionUtils(final String node, final int port) {
  this.cluster = Cluster.builder().addContactPoint(node).withPort(port).build();
  Metadata metadata = cluster.getMetadata();
  System.out.printf("Connected to cluster: %s\n", metadata.getClusterName());
  for (Host host : metadata.getAllHosts()) {
   System.out.printf("Datacenter: %s; Host: %s; Rack: %s\n", host.getDatacenter(), host.getAddress(),
     host.getRack());
  }
  session = cluster.connect();
 }

 public Session getSession(String keySpace) {
  return cluster.connect(keySpace);
 }

 public void close() {
  session.close();
  cluster.close();
 }
}
4. Create a connectionFactory class "CASConnectionFactory" which gives connection object (Cluster and Session) for CRUD operation.

/**
 * 
 * @author nikhil(www.devinline.com)
 * 
 */
public class CASConnectionFactory {
 private static CASConnection INSTANCE;
 private final static String node = "localhost";
 private final static Integer port = 9042;

 /**
  * Get instance of GetBalanceCashOutUtil
  * 
  * @return
  */
 public static CASConnection getInstance() {
  if (INSTANCE == null) {
   synchronized (CASConnectionFactory.class) {
    if (INSTANCE == null) {
     INSTANCE = new CASConnection(node,port);
    }
   }
  }
  return INSTANCE;
 }
}
5. Create Database operation class: Here we write various CRUD operation methods.
import com.datastax.driver.core.BoundStatement;
import com.datastax.driver.core.PreparedStatement;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Session;

/**
 * 
 * @author nikhil(www.devinline.com)
 * 
 */
public class CassandraDBOperation {
 public Boolean createKeyspace(String keyspaceName, String replicationStrategy, int replicationFactor) {
  StringBuilder sb = new StringBuilder("CREATE KEYSPACE IF NOT EXISTS ").append(keyspaceName)
    .append(" WITH replication = {").append("'class':'").append(replicationStrategy)
    .append("','replication_factor':").append(replicationFactor).append("};");
  String query = sb.toString();
  CASConnectionFactory.getInstance().getSession().execute(query);
  CASConnectionFactory.getInstance().getSession().close();
  return Boolean.TRUE;
 }

 public Boolean createTable(String query, String keyspace) {
  Session session = CASConnectionFactory.getInstance().getSession(keyspace);
  session.execute(query);
  session.close();
  return Boolean.TRUE;
 }

 public Boolean createNewOrder(String keyspace, String orderNumber, String order_date, String item_id,
   Double item_price, String delivery_date) {
  Session session = CASConnectionFactory.getInstance().getSession(keyspace);
  PreparedStatement prepared = session.prepare(CQLQueryConstants.CREATE_NEW_ORDER);
  BoundStatement boundStmt = prepared.bind(orderNumber, order_date, item_id, item_price, delivery_date);
  session.execute(boundStmt);
  session.close();
  return Boolean.TRUE;
 }

 public Boolean deleteOrder(String keyspace, String orderNumber) {
  Session session = CASConnectionFactory.getInstance().getSession(keyspace);
  PreparedStatement prepared = session.prepare(CQLQueryConstants.DELETE_ORDER);
  BoundStatement boundStmt = prepared.bind(orderNumber);
  session.execute(boundStmt);
  CASConnectionFactory.getInstance().close();
  return Boolean.TRUE;
 }

 public ResultSet getOrderDetailByOrdrNumber(String keyspace, String orderNumber) {
  Session session = CASConnectionFactory.getInstance().getSession(keyspace);
  PreparedStatement prepared = session.prepare(CQLQueryConstants.SELECT_ORDER);
  BoundStatement boundStmt = prepared.bind(orderNumber);
  ResultSet result = session.execute(boundStmt);
  CASConnectionFactory.getInstance().close();
  return result;
 }

 public ResultSet getAllOrders(String keyspace) {
  Session session = CASConnectionFactory.getInstance().getSession(keyspace);
  ResultSet resultSet = session.execute(CQLQueryConstants.SELECT_ORDER_ALL);
  CASConnectionFactory.getInstance().close();
  return resultSet;
 }

}
6. SQL Query and parametrised query constant: Create a class with following constants which are used in above class file.
/**
 * 
 * @author nikhil(www.devinline.com)
 * 
 */
public class CQLQueryConstants {
 public static final String CREATE_ORDER_TABLE = "CREATE TABLE \"ORDERS\" \n" + 
   "(\n" + 
   "   order_number varchar,\n" + 
   "   order_date varchar,\n" + 
   "   item_id varchar,\n" + 
   "   item_price double,\n" + 
   "   delivery_date varchar,\n" + 
   "   PRIMARY KEY (order_number)\n" + 
   ");";
 
 public static final String SCHEDULE_DELIVERY_TABLE = "CREATE TABLE ORDER_DELIVERY \n" + 
   "(\n" + 
   "   shipment_number varchar,\n" +
   "   order_number varchar,\n" + 
   "   expected_delivery_date int,\n" + 
   "   customer_id varchar,\n" + 
   "   delivery_mode varchar,\n" + 
   "   PRIMARY KEY (shipment_number,order_number)\n" + 
   ");";
 
 public static final String CREATE_NEW_ORDER = "INSERT into \"ORDERS\" ("
   + "order_number, "
   + "order_date,"
   + "item_id,"
   + "item_price,"
   + "delivery_date) "
   + "VALUES (?,?,?,?,?)";
 
 public static final String UPDATE_ORDER_DELIVERY_DATE = "UPDATE \"ORDERS\"  SET "
   + "delivery_date = ?"
   + "WHERE order_number = ?";
 
 public static final String DELETE_ORDER = "DELETE FROM \"ORDERS\" WHERE order_number=? IF EXISTS;";
 
 public static final String SELECT_ORDER = "SELECT * FROM \"ORDERS\" WHERE order_number= ?;";
 
 public static final String SELECT_ORDER_ALL = "SELECT * FROM \"ORDERS\";";
}

7. Client/Driver Program unit : Finally we create Java client program which create Keyspace, Create table, Insert rows, etc.
import java.net.UnknownHostException;
import java.util.Random;
import java.util.Scanner;

import com.datastax.driver.core.ResultSet;
/**
 * 
 * @author nikhil(www.devinline.com)
 * 
 */
public class CASClient {
 private static String KEY_SPACE_NAME = "\"SPOrders\"";
 private static String REPLICAION_STRATEGY = "SimpleStrategy";
 private static Integer REPLICATION_FACTOR = 1;

 public static void main(String[] args) throws UnknownHostException {
  System.out.print("******Enter Operation**********");
  System.out.print("\n 1. CREATE_KEYSPACE, " + "\n 2. CREATE_ORDER_TABLE, " + "\n 3. CREATE_NEW_ORDER, "
    + "\n 4. DELETE_ORDER, " + "\n 5. SELECT_ORDER_BY_ORDER_NUMBER, " + "\n 6. SELECT_ALL_ORDER");
  System.out.print("\n*******************************");
  Scanner sc = new Scanner(System.in);
  System.out.println("\nEnter your choice: ");
  int choice = sc.nextInt();
  switch (choice) {
  case 1:
   doDBOperation("CREATE_KEYSPACE");
   break;
  case 2:
   doDBOperation("CREATE_ORDER_TABLE");
   break;
  case 3:
   doDBOperation("CREATE_NEW_ORDER");
   break;
  case 4:
   doDBOperation("DELETE_ORDER");
   break;

  case 5:
   doDBOperation("SELECT_ORDER_BY_ORDER_NUMBER");
   break;

  case 6:
   doDBOperation("SELECT_ALL_ORDER");
   break;

  default:
   break;
  }
  sc.close();

 }

 public static void doDBOperation(String value) {
  CassandraDBOperation dbOperation = new CassandraDBOperation();
  DBOperationsEnum operation = DBOperationsEnum.valueOf(value);
  switch (operation) {
  case CREATE_KEYSPACE:
   System.out.println("CREATE_KEYSPACE");
   /* Create Keyspace */
   if (dbOperation.createKeyspace(KEY_SPACE_NAME, REPLICAION_STRATEGY, REPLICATION_FACTOR)) {
    System.out.println("Keyspace " + KEY_SPACE_NAME + " created successfully !");
   }
   break;
  case CREATE_ORDER_TABLE:
   System.out.println("CREATE_ORDER_TABLE");
   /*
    * Create ORDERS table : KEY_SPACE_NAME is passed in input
    */
   if (dbOperation.createTable(CQLQueryConstants.CREATE_ORDER_TABLE, KEY_SPACE_NAME)) {
    System.out.println("TABLE CREATE_ORDER created successfully !");
   }
   break;
  case CREATE_NEW_ORDER:
   System.out.println("CREATE_NEW_ORDER");
   String orderNumber = getOrderNum();
   if (dbOperation.createNewOrder(KEY_SPACE_NAME, getOrderNum(), "2018-03-18", getItemNum(), getPrice(),
     "2018-03-23")) {
    System.out.println("New ORDER created successfully with order number " + orderNumber);
   }
   break;

  case DELETE_ORDER:
   Scanner sc = new Scanner(System.in);
   System.out.println("\nEnter Order Number: ");
   String orderNumberDel = sc.next();
   if (dbOperation.deleteOrder(KEY_SPACE_NAME, orderNumberDel)) {
    System.out.println("ORDER " + orderNumberDel + "deleted successfully ");
   }
   sc.close();
   break;

  case SELECT_ORDER_BY_ORDER_NUMBER:
   System.out.println("\nEnter Order Number: ");
   Scanner sc1 = new Scanner(System.in);
   String orderNumSel = sc1.next();
   ResultSet resultSet = dbOperation.getOrderDetailByOrdrNumber(KEY_SPACE_NAME, orderNumSel);
   if (null != resultSet) {
    System.out.println(resultSet.all());
   }
   sc1.close();
   break;

  case SELECT_ALL_ORDER:
   ResultSet resultSet1 = dbOperation.getAllOrders(KEY_SPACE_NAME);
   if (null != resultSet1) {
    System.out.println(resultSet1.all());
   }
   break;

  default:
   System.out.println("No operations selectd !!");
   break;
  }
 }

 private static String getOrderNum() {
  Random rand = new Random();
  int value = rand.nextInt(50);
  return "ORDER" + value;
 }

 private static Double getPrice() {
  Random rand = new Random();
  int value = rand.nextInt(50);
  return value * 0.9;
 }

 private static String getItemNum() {
  Random rand = new Random();
  int value = rand.nextInt(50);
  return "ITEM" + value;
 }
}

enum DBOperationsEnum {
 CREATE_KEYSPACE, CREATE_ORDER_TABLE, CREATE_NEW_ORDER, DELETE_ORDER, SELECT_ORDER_BY_ORDER_NUMBER, SELECT_ALL_ORDER

}

Sample Output
:
Hide Output
******Enter Operation**********
 1. CREATE_KEYSPACE, 
 2. CREATE_ORDER_TABLE, 
 3. CREATE_NEW_ORDER, 
 4. DELETE_ORDER, 
 5. SELECT_ORDER_BY_ORDER_NUMBER, 
 6. SELECT_ALL_ORDER
*******************************
Enter your choice: 
1
CREATE_KEYSPACE
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Connected to cluster: Test Cluster
Datacenter: datacenter1; Host: localhost/127.0.0.1; Rack: rack1
Keyspace "SPOrders" created successfully !


******Enter Operation**********
 1. CREATE_KEYSPACE, 
 2. CREATE_ORDER_TABLE, 
 3. CREATE_NEW_ORDER, 
 4. DELETE_ORDER, 
 5. SELECT_ORDER_BY_ORDER_NUMBER, 
 6. SELECT_ALL_ORDER
*******************************
Enter your choice: 
3
CREATE_NEW_ORDER
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Connected to cluster: Test Cluster
Datacenter: datacenter1; Host: localhost/127.0.0.1; Rack: rack1

New ORDER created successfully with order number ORDER44



cqlsh:SPOrders> select * from "ORDERS";
 order_number | delivery_date | item_id | item_price | order_date
--------------+---------------+---------+------------+------------
      ORDER44 |    2018-03-23 |  ITEM34 |       12.6 | 2018-03-18
      ORDER30 |    2018-03-23 |  ITEM20 |       14.4 | 2018-03-18

******Enter Operation**********
 1. CREATE_KEYSPACE, 
 2. CREATE_ORDER_TABLE, 
 3. CREATE_NEW_ORDER, 
 4. DELETE_ORDER, 
 5. SELECT_ORDER_BY_ORDER_NUMBER, 
 6. SELECT_ALL_ORDER
*******************************
Enter your choice: 
5

Enter Order Number: 
ORDER30
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Connected to cluster: Test Cluster
Datacenter: datacenter1; Host: localhost/127.0.0.1; Rack: rack1
[Row[ORDER30, 2018-03-23, ITEM20, 14.4, 2018-03-18]]

******Enter Operation**********
 1. CREATE_KEYSPACE, 
 2. CREATE_ORDER_TABLE, 
 3. CREATE_NEW_ORDER, 
 4. DELETE_ORDER, 
 5. SELECT_ORDER_BY_ORDER_NUMBER, 
 6. SELECT_ALL_ORDER
*******************************
Enter your choice: 
4

Enter Order Number: 
ORDER30
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
Connected to cluster: Test Cluster
Datacenter: datacenter1; Host: localhost/127.0.0.1; Rack: rack1
ORDER ORDER30deleted successfully 


cqlsh:SPOrders> select * from "ORDERS";
 order_number | delivery_date | item_id | item_price | order_date
--------------+---------------+---------+------------+------------
      ORDER44 |    2018-03-23 |  ITEM34 |       12.6 | 2018-03-18



Location: Bengaluru, Karnataka, India