Wednesday, April 13, 2011

Getting started with Cluster/J - index search and table scans

This post follows the previous post, so set up the environment as described there.
In this example we will perform a full table scan (select * from my_data2) and an index scan (select * from my_data2 where userid=1).

The complete code is here from Severalnines.

Performing a full table scan (no search criteria)
Make sure you have records in the table my_data2.
To perform reads by primary key, we can just use Session::find(..), but here we will do a full table scan.
QueryBuilder qb = s.getQueryBuilder();

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/

List resultList = query.getResultList();
int count=0;
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}
That is it, if you run the example code, you will see the output.
Done reading X records in Y mssd

Performing a Index scan (search on part of the Primary Key)

The following snippet shows how to setup a EQUAL-filter:
/*
* We want to compare userid=1 (select * from my_data2 where userid=2)
*/
QueryBuilder qb = s.getQueryBuilder();
QueryDomainType dobj = qb.createQueryDefinition(MyData2.class);
/*
* Create a filter, we call this filter 'user_id_filter'
*/

PredicateOperand param = dobj.param("user_id_filter");
/*
* Set the column database column you want to filter on.
* Note: The setting dobj.get("userId") is really strange.
* In the table the column in the database is called userid, and in MyData2.
* The documentation of Cluster/J is very bad here, but
* the getter/setter are getUserId/setUserId, and you have to
* take the word after the get/set (in this case UserId) and change the first
* letter to lower case :(
*/
PredicateOperand column = dobj.get("userId");

/**
* Perform an equal compare
* we want to compare userid=1
*/

Predicate compare=column.equal(param);
dobj.where(compare);

Query query = s.createQuery(dobj);

/*
* Bind the search value to the filter
*/
query.setParameter("user_id_filter", new Long(1));

/*
* Run the query:
*/
List resultList = query.getResultList();
int count=0;

/*
* Loop over the results
*/
for (MyData2 result: resultList) {
System.out.println(result.getFriendId());
count++;
}
That is it for this time. I hope the clusterj documention will become better.

1 comment:

Anonymous said...

Is there any efficient way to count total rows in a table?