In this Article, I will show How to implement paging in Cassandra.
This is common requirement in many of projects.
Tools Uses :
1) Apache-cassandra-2.1.6
2) eclipse version Luna 4.4.1.
3) Maven 4.0.0
4) JDK 1.7
If you are completely new to Cassandra, Pl. refer Cassandra Quick Start.
Once your Cassandra Database server is up and CQL window is open.
Follow given simple steps to setup test data,
1) Create a KEYSPACE (Similar to NAMESPACE in relational database), If not exist.
cqlsh> CREATE KEYSPACE devJavaSource
WITH REPLICATION = { ‘class’ : ‘SimpleStrategy’, ‘replication_factor’ : 1 };
2) Use created key space(devJavaSource).
cqlsh> USE devJavaSource;
3) Create table name (USERS) in key space devJavaSource, with columns ID, NAME, ADDRESS.
cqlsh> CREATE TABLE USERS ( ID int PRIMARY KEY, NAME text, ADDRESS text );
4) Create data into USER table.
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11101, ‘john’, ‘Oakland’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11102, ‘smith’, ‘California’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11103, ‘Joe’, ‘Nederland’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11104, ‘Benny David’, ‘UK’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11105, ‘Scattergood John’, ‘USA’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11106, ‘Bob Ronstan’, ‘Canada’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11107, ‘Stuart’, ‘UK’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11108, ‘Adamson’, ‘USA’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11109, ‘Mike’, ‘Canada’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11110, ‘Lussi’, ‘UK’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11111, ‘Meena’, ‘USA’);
INSERT INTO USERS (ID, NAME, ADDRESS) VALUES (11112, ‘Sam’, ‘Canada’);
5) Retrieve the data from USERS table.
cqlsh> SELECT * FROM USERS;
id | address | name -------+------------+------------------ 11106 | Canada | Bob Ronstan 11107 | UK | Stuart 11103 | Nederland | Joe 11105 | USA | Scattergood John 11102 | California | smith 11112 | Canada | Sam 11101 | Oakland | john 11110 | UK | Lussi 11108 | USA | Adamson 11109 | Canada | Mike 11104 | UK | Benny David 11111 | USA | Meena (12 rows)
Write a simple program to implement paging :
1) Create a maven project.
2) Add required dependencies.
3) Create a simple java program to retrieve data.
4) Start cassandra server.
5) Run and verify the out put.
Add required dependencies :
Open pom.xml file and add given dependency.
<dependency> <groupId>com.datastax.cassandra</groupId> <artifactId>cassandra-driver-core</artifactId> <version>2.1.6</version> </dependency>
Create a simple java program to implement paging :
PagingState is a cassandra class that holds paging state.
We can get PagingState from ResultSet object.
ResultSet result = null;
String savingPageState = null;
savingPageState = result.getExecutionInfo()
.getPagingState().toString();
We can set fetch size to statement object,
Statement statement = null; statement.setFetchSize(size);
Complete CassandraPaging class source code is Here,
CassandraPaging.java
package com.devjavasource.cassandra.PagingExample;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.datastax.driver.core.PagingState;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Row;
import com.datastax.driver.core.Session;
import com.datastax.driver.core.Statement;
/**
*
* The solution of skipping rows is that use page state rather than iterator
* rows one by one.
*
*/
public class CassandraPaging {
private Session session;
public CassandraPaging(Session session) {
this.session = session;
}
/**
* Retrieve rows for the specified page offset.
*
* @param statement
* @param start
* starting row (>1), inclusive
* @param size
* the maximum rows need to retrieve.
* @return List<Row>
*/
public List<Row> fetchRowsWithPage(Statement statement, int start, int size) {
ResultSet result = skipRows(statement, start, size);
return getRows(result, start, size);
}
private ResultSet skipRows(Statement statement, int start, int size) {
ResultSet result = null;
int skippingPages = getPageNumber(start, size);
String savingPageState = null;
statement.setFetchSize(size);
boolean isEnd = false;
for (int i = 0; i < skippingPages; i++) {
if (null != savingPageState) {
statement = statement.setPagingState(PagingState
.fromString(savingPageState));
}
result = session.execute(statement);
PagingState pagingState = result.getExecutionInfo()
.getPagingState();
if (null != pagingState) {
savingPageState = result.getExecutionInfo().getPagingState()
.toString();
}
if (result.isFullyFetched() && null == pagingState) {
// if hit the end more than once, then nothing to return,
// otherwise, mark the isEnd to 'true'
if (true == isEnd) {
return null;
} else {
isEnd = true;
}
}
}
return result;
}
private int getPageNumber(int start, int size) {
if (start < 1) {
throw new IllegalArgumentException(
"Starting row need to be larger than 1");
}
int page = 1;
if (start > size) {
page = (start - 1) / size + 1;
}
return page;
}
private List<Row> getRows(ResultSet result, int start, int size) {
List<Row> rows = new ArrayList<>(size);
if (null == result) {
return rows;
}
int skippingRows = (start - 1) % size;
int index = 0;
for (Iterator<Row> iter = result.iterator(); iter.hasNext()
&& rows.size() < size;) {
Row row = iter.next();
if (index >= skippingRows) {
rows.add(row);
}
index++;
}
return rows;
}
}
Create a Simple class App.java to test paging,
Create a Cluster object and session objects,
Cluster cluster = null;
Session session = null;
final Cluster.Builder clusterBuilder =
Cluster.builder().addContactPoint("127.0.0.1").withPort(9042)
.withCredentials("devjavasource", "devjavasource");
cluster = clusterBuilder.build();
session = cluster.connect("devjavasource");
Create a Statement with help of QueryBuilder class,
Statement select = QueryBuilder.select().all().from("devjavasource", "users");
Complete source code is Here,
App.java
package com.devjavasource.cassandra.PagingExample;
import java.util.List;
import com.datastax.driver.core.Cluster;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Row;
import com.datastax.driver.core.Session;
import com.datastax.driver.core.Statement;
import com.datastax.driver.core.querybuilder.QueryBuilder;
/**
* Hello world!
*
*/
public class App {
public static void main(String[] args) {
Cluster cluster = null;
Session session = null;
try{
// Connect to the cluster and keyspace "devjavasource"
final Cluster.Builder clusterBuilder = Cluster.builder()
.addContactPoint("127.0.0.1").withPort(9042)
.withCredentials("devjavasource", "devjavasource");
cluster = clusterBuilder.build();
session = cluster.connect("devjavasource");
Statement select = QueryBuilder.select().all().from("devjavasource", "users");
CassandraPaging cassandraPaging = new CassandraPaging(session);
System.out.println("*************First Page1 **************");
List<Row> firstPageRows = cassandraPaging.fetchRowsWithPage(select, 1, 3);
printUser(firstPageRows);
System.out.println("*************Second Page2 **************");
List<Row> secondPageRows = cassandraPaging.fetchRowsWithPage(select, 4, 3);
printUser(secondPageRows);
System.out.println("*************Third Page3 **************");
List<Row> thirdPageRows = cassandraPaging.fetchRowsWithPage(select, 4, 3);
printUser(thirdPageRows);
System.out.println("*************Fourth Page4 **************");
List<Row> fourthPageRows = cassandraPaging.fetchRowsWithPage(select, 4, 3);
printUser(fourthPageRows);
cluster.close();
session.close();
}catch(Exception exp){
exp.printStackTrace();
}finally{
cluster.close();
session.close();
}
}
private static void printUser(final List<Row> inRows){
for (Row row : inRows) {
System.out.println("Id is:" + row.getInt("id"));
System.out.println("Name is:" + row.getString("name"));
System.out.println("Address is:" + row.getString("address"));
}
}
}
4) Start the Cassandra server :
Cassandra server should be up and running.
If the server is not running, run the server using following command.
Command to start Casandra server is,
C:\apache-cassandra-2.1.6\bin>cassandra.bat -f
5) Run Maven project :
Select and Run As -> Java Application.
Out Put :
*************First Page1 ************** Id is:11106 Name is:Bob Ronstan Address is:Canada Id is:11107 Name is:Stuart Address is:UK Id is:11103 Name is:Joe Address is:Nederland *************Second Page2 ************** Id is:11105 Name is:Scattergood John Address is:USA Id is:11102 Name is:smith Address is:California Id is:11112 Name is:Sam Address is:Canada *************Third Page3 ************** Id is:11101 Name is:john Address is:Oakland Id is:11110 Name is:Lussi Address is:UK Id is:11108 Name is:Adamson Address is:USA *************Fourth Page4 ************** Id is:11109 Name is:Mike Address is:Canada Id is:11104 Name is:Benny David Address is:UK Id is:11111 Name is:Meena Address is:USA
Select the user details from cassandra database,
You can download complete project, Here
*** Venkat – Happy leaning ****