Cassandra JDBC to perform CRUD operations in Cassandra NoSql DB

Hope everybody is doing good. I was trying and using different NOSQL databases. Last week I used Cassandra. It was a good experience , as a developer I developed and started using Cassandra with JAVA.
There are many Java client available for Cassandra. Hector, Kundera, Easy Cassandra are few of them.  As  I'm just migrating from MySql to Cassandra ,I preferred   Cassandra JDBC Driver because it is quite simple and easy to use. Its just like using a jdbc driver and the queries are quite simple too.

Cassandra JDBC is a  JDBC-compliant driver for Cassandra using CQL.
For somebody new to Cassandra download Cassandra from  link http://cassandra.apache.org/download/.I used Cassandra 1.1.6 latest stable release available.You can download the latest stable build of Cassandra JDBC from the link http://code.google.com/a/apache-extras.org/p/cassandra-jdbc/downloads/list .

If you are new to cassandra and want to know how to install and use you can view link http://wiki.apache.org/cassandra/GettingStarted. This is one of the best link  if you want to start and explore Cassandra.

 Start the Cassandra  server and open the Cassandra-cli a command line interface to create and view keyspaces. We can perform other task as well like creating column family but we'll be using JAVA and cassandra  JDBC to perform CRUD operation in cassandra.


Create Keyspace keyspace1 .
Syntax is  CREATE KEYSPACE keyspace1.


Include the Cassandra JDBC driver in your project and import the files :

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;

Load the driver and make JDBC connection :

Class.forName("org.apache.cassandra.cql.jdbc.CassandraDriver");

java.sql.Connection con=null;

con=DriverManager.getConnection("jdbc:cassandra://localhost:9160/Keyspace1");

To create a column family created as 

String query="CREATE columnfamily news"+

               "(key int primary key, category text , linkcounts int ,url text)";

Statement st = con.createStatement();

same way we can insert values into column family newLinkCount as :
String data=
         "BEGIN BATCH \n"+   
         "insert into news (key, category, linkcounts,url) values ('user5','class',71,'news.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user6','education',15,'tech.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user7','technology',415,'ba.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user8','travelling',45,'google.com/teravel') \n"+
         "APPLY BATCH;"
Statement st = con.createStatement();
st.executeUpdate(data);

To drop a column family :
       String data="drop columnfamily "+ name +";";
      Statement st = con.createStatement();
       st.execute(data);

 The original code look likes below :

package com.classteacher.common.db;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class CassandraCql {
    private static java.sql.Connection con = null;
   
    public static void main(String[] a){
        try {
            Class.forName("org.apache.cassandra.cql.jdbc.CassandraDriver");
            con=DriverManager.getConnection("jdbc:cassandra://localhost:9160/niranjan");
            CassandraCql sample = new CassandraCql();
            String Columnname="subject";
          
            /* -- Functions to perform on Keyspace --*/
            createColumnFamily();
            pouplateData();
            deleteData();
            updateData();
            listData();
            dropColumnFamily("news");
          
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
   
   
     public static void createColumnFamily() throws SQLException {
         String data="CREATE columnfamily news (key int primary key, category text , linkcounts int ,url text)";
         Statement st = con.createStatement();
         st.execute(data);
         }

         public static void dropColumnFamily(String name) throws SQLException {
         String data="drop columnfamily "+ name +";";
         Statement st = con.createStatement();
         st.execute(data);
         }
        
         public static void pouplateData() throws SQLException {
         String data=
         "BEGIN BATCH \n"+  
         "insert into news (key, category, linkcounts,url) values ('user5','class',71,'news.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user6','education',15,'tech.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user7','technology',415,'ba.com') \n"+
         "insert into news (key, category, linkcounts,url) values ('user8','travelling',45,'google.com/teravel') \n"+
         "APPLY BATCH;";  
         Statement st = con.createStatement();
        st.executeUpdate(data);
         }
         public static void deleteData() throws SQLException {
         String data=
         "BEGIN BATCH \n"+
         "delete from  news where key='user5' \n"+
         "delete  category from  news where key='user2' \n"+
         "APPLY BATCH;";  
         Statement st = con.createStatement();
         st.executeUpdate(data);
         }
         public static void updateData() throws SQLException {
         String t = "update news set category='sports', linkcounts=1 where key='user5'";         
         Statement st = con.createStatement();
         st.executeUpdate(t);
         }
         public static void listData() throws SQLException {
         String t = "SELECT * FROM news";         
         Statement st = con.createStatement();
         ResultSet rs = st.executeQuery(t);
         while(rs.next())
         {
         System.out.println(rs.getString("KEY"));
         for(int j=1;j<rs.getMetaData().getColumnCount()+1;j++)
         {
         System.out.println(rs.getMetaData().getColumnName(j) +" : "+rs.getString(rs.getMetaData().getColumnName(j)));
         }
         }
         }
   
}
 
 That's it of now. Please share your feedback. Will share more codes and changes with thrift and other Cassandra clients soon.