In this post, we take a look at basic CRUD (Create Read Update Delete) operations on Cassandra database using Java JDBC.
We only use Eclipse IDE. This example was tested in Windows 7 Operating System.
To find out more about NoSQL databases, look here.
Cassandra is an open source distributed database management system developed by Apache Software Foundation. It was designed to handle very large amounts of data spread out across many commodity servers while providing a highly available service with no single point of failure.
Popular user of Cassandra is Facebook
To find out more on Cassandra, refer its home site here.
- Download and Install Cassandra from here.
- Extract the files.
- Setup the following Environment Variables.
- JAVA_HOME (Give the link to the jre folder where java is installed)
- CASSANDRA_HOME (Give the link to the cassandra folder where the files are extracted)
- Go to the location where you extracted and then into the "!/apache-cassandra-0.6.6/bin" folder and open 'cassandra-cli.bat' file using any text editor
- Replace Line 21 "if NOT DEFINED CASSANDRA_HOME set CASSANDRA_HOME=%CD%" with "if NOT DEFINED CASSANDRA_HOME set CASSANDRA_HOME=%~dp0.."
Now save the file after editing the line. - Now open the command prompt and goto "~/apache-cassandra-0.6.6/bin>" folder and type the following command '.\cassandra.bat'. This starts the server.
- Now open another command prompt and goto "~/apache-cassandra-0.6.6/bin>" folder and run '.\cassandra-cli.bat'. This is the UI for the database
- Connect to the server by typing the command 'connect localhost/9160'. Here, type "Create Keyspace AuthDB ;" This creates a cluster (i.e., database)
- Here, type "Create Column Family AuthCollection ;" This creates a column family (i.e., table)
- There are certain jar files needed which are available in the Project Dump which is available for download below. It is present in the folder titled 'JarFiles' in the Project Directory.
- Be sure to add these jar files to the class path.
The code which performs all the CRUD operations is as follows.
package com.examples.cassandra;
import me.prettyprint.cassandra.serializers.StringSerializer;
import me.prettyprint.hector.api.Cluster;
import me.prettyprint.hector.api.Keyspace;
import me.prettyprint.hector.api.beans.ColumnSlice;
import me.prettyprint.hector.api.beans.Rows;
import me.prettyprint.hector.api.factory.HFactory;
import me.prettyprint.hector.api.mutation.Mutator;
import me.prettyprint.hector.api.query.MultigetSliceQuery;
import me.prettyprint.hector.api.query.QueryResult;
import me.prettyprint.hector.api.query.SliceQuery;
public class CassandraExample {
//The string serializer translates the byte[] to and from String using utf-8 encoding
private static StringSerializer stringSerializer = StringSerializer.get();
public static void insertData() {
try {
//Create a cluster object from your existing Cassandra cluster
Cluster cluster = HFactory.getOrCreateCluster("Test Cluster", "localhost:9160");
//Create a keyspace object from the existing keyspace we created using CLI
Keyspace keyspace = HFactory.createKeyspace("AuthDB", cluster);
//Create a mutator object for this keyspace using utf-8 encoding
Mutator<string> mutator = HFactory.createMutator(keyspace, stringSerializer);
//Use the mutator object to insert a column and value pair to an existing key
mutator.insert("sample", "authCollection", HFactory.createStringColumn("username", "admin"));
mutator.insert("sample", "authCollection", HFactory.createStringColumn("password", "admin"));
System.out.println("Data Inserted");
System.out.println();
} catch (Exception ex) {
System.out.println("Error encountered while inserting data!!");
ex.printStackTrace() ;
}
}
public static void retrieveData() {
try {
//Create a cluster object from your existing Cassandra cluster
Cluster cluster = HFactory.getOrCreateCluster("Test Cluster", "localhost:9160");
//Create a keyspace object from the existing keyspace we created using CLI
Keyspace keyspace = HFactory.createKeyspace("AuthDB", cluster);
SliceQuery<String, String, String> sliceQuery = HFactory.createSliceQuery(keyspace, stringSerializer, stringSerializer, stringSerializer);
sliceQuery.setColumnFamily("authCollection").setKey("sample");
sliceQuery.setRange("", "", false, 4);
QueryResult<ColumnSlice<String, String>> result = sliceQuery.execute();
System.out.println("\nInserted data is as follows:\n" + result.get());
System.out.println();
} catch (Exception ex) {
System.out.println("Error encountered while retrieving data!!");
ex.printStackTrace() ;
}
}
public static void updateData() {
try {
//Create a cluster object from your existing Cassandra cluster
Cluster cluster = HFactory.getOrCreateCluster("Test Sample", "localhost:9160");
//Create a keyspace object from the existing keyspace we created using CLI
Keyspace keyspace = HFactory.createKeyspace("AuthDB", cluster);
//Create a mutator object for this keyspace using utf-8 encoding
Mutator<string> mutator = HFactory.createMutator(keyspace, stringSerializer);
//Use the mutator object to update a column and value pair to an existing key
mutator.insert("sample", "authCollection", HFactory.createStringColumn("username", "administrator"));
//Check if data is updated
MultigetSliceQuery<String, String, String> multigetSliceQuery = HFactory.createMultigetSliceQuery(keyspace, stringSerializer, stringSerializer, stringSerializer);
multigetSliceQuery.setColumnFamily("authCollection");
multigetSliceQuery.setKeys("sample");
//The 3rd parameter returns the columns in reverse order if true
//The 4th parameter in setRange determines the maximum number of columns returned per key
multigetSliceQuery.setRange("username", "", false, 1);
QueryResult<Rows<String, String, String>> result = multigetSliceQuery.execute();
System.out.println("Updated data..." +result.get());
} catch (Exception ex) {
System.out.println("Error encountered while updating data!!");
ex.printStackTrace() ;
}
}
public static void deleteData() {
try {
//Create a cluster object from your existing Cassandra cluster
Cluster cluster = HFactory.getOrCreateCluster("Test Cluster", "localhost:9160");
//Create a keyspace object from the existing keyspace we created using CLI
Keyspace keyspace = HFactory.createKeyspace("AuthDB", cluster);
//Create a mutator object for this keyspace using utf-8 encoding
Mutator<string> mutator = HFactory.createMutator(keyspace, stringSerializer);
//Use the mutator object to delete row
mutator.delete("sample", "authCollection",null, stringSerializer);
System.out.println("Data Deleted!!");
//try to retrieve data after deleting
SliceQuery<String, String, String> sliceQuery = HFactory.createSliceQuery(keyspace, stringSerializer, stringSerializer, stringSerializer);
sliceQuery.setColumnFamily("authCollection").setKey("sample");
sliceQuery.setRange("", "", false, 4);
QueryResult<ColumnSlice<String, String>> result = sliceQuery.execute();
System.out.println("\nTrying to Retrieve data after deleting the key 'sample':\n" + result.get());
//close connection
cluster.getConnectionManager().shutdown();
} catch (Exception ex) {
System.out.println("Error encountered while deleting data!!");
ex.printStackTrace() ;
}
}
public static void main(String[] args) {
insertData() ;
retrieveData() ;
updateData() ;
deleteData() ;
}
}
</pre>
The screen shot of the output is as follows:
This code is available for download along with the entire project directory here.
