Mongodb JAVA Driver: QueryBuilder class

I was recently assigned a task where I had to convert a SQL like where clause query into Mongo query on the fly.  There are some good drivers available to do the same but they are not free.  The best example is UnityJDBC but it was a bit expensive for our use case.  The query needed to be super simple and we made the following assumptions:

  1. We need to support simple SQL statements that don’t have brackets.  Hence, type = exam and score >= 60 is acceptable but type = exam and (score >=60 or score < 30) is not acceptable
  2. The query will only work against double for >, <, >=, <= and will support String and Double datatype when querying for = and != operators.

We decided to come up with something in-house that would be sufficient enough to satisfy our needs and we can build on top of the framework over time like adding support for brackets, Logical operators like XOR, etc.  Let’s use the following sample data to proceed with the example:

{ "_id" : { "$oid" : "50906d7fa3c412bb040eb577" }, "student_id" : 0, "type" : "exam", "score" : 54.6535436362647 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb578" }, "student_id" : 0, "type" : "quiz", "score" : 31.95004496742112 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb579" }, "student_id" : 0, "type" : "homework", "score" : 14.8504576811645 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57a" }, "student_id" : 0, "type" : "homework", "score" : 63.98402553675503 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57b" }, "student_id" : 1, "type" : "exam", "score" : 74.20010837299897 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57c" }, "student_id" : 1, "type" : "quiz", "score" : 96.76851542258362 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57d" }, "student_id" : 1, "type" : "homework", "score" : 21.33260810416115 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57e" }, "student_id" : 1, "type" : "homework", "score" : 44.31667452616328 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb57f" }, "student_id" : 2, "type" : "exam", "score" : 19.88180838833524 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb580" }, "student_id" : 2, "type" : "quiz", "score" : 1.528220212203968 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb581" }, "student_id" : 2, "type" : "homework", "score" : 60.9750047106029 }
{ "_id" : { "$oid" : "50906d7fa3c412bb040eb582" }, "student_id" : 2, "type" : "homework", "score" : 97.75889721343528 }

Let me start by showing a simple program that we can use to get all records for student with student_id of 2.  There are two classes available to allow us to do this.

1. Using BasicDBObject class:


import java.net.UnknownHostException;
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.Mongo;

public class BasicDBObjExample
{

public static void main(String[] args) throws UnknownHostException
{
// Connect to the Mongo database
Mongo mongoConn = new Mongo("localhost", 27017);
DB mongoDb = mongoConn.getDB("DP_TEST");
DBCollection collection = mongoDb.getCollection("DATA");

// Building the query parameters
BasicDBObject studentFinder = new BasicDBObject();
studentFinder.put("student_id", 2);

// Fetch the records for the query.  collection.find() will return Cursor with records that match the query DBObject
DBCursor dbCursor = collection.find(studentFinder);

while(dbCursor.hasNext())
System.out.println(dbCursor.next());
}

}

2. Using QueryBuilder class:


import java.net.UnknownHostException;
import com.mongodb.QueryBuilder;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.Mongo;

public class QueryBuilderExample
{

public static void main(String[] args) throws UnknownHostException
{
// Connect to the Mongo database
Mongo mongoConn = new Mongo("localhost", 27017);
DB mongoDb = mongoConn.getDB("DP_TEST");
DBCollection collection = mongoDb.getCollection("DATA");

// Building the query parameters
QueryBuilder studentFinder = new QueryBuilder();
studentFinder.put("student_id").is(2);

// Fetching the records for the query. get() method will convert QueryBuilder -&gt; DBObject class of query parameters
DBCursor dbCursor = collection.find(studentFinder.get());

while(dbCursor.hasNext())
System.out.println(dbCursor.next());
}

}

Notice that both the examples return the same set of records.  The only difference is in the way we construct the query to be run against the Mongo database.  The QueryBuilder class comes handy when you have A < 10 and A > 20 kind of queries.  For example, let us find records where student_id > 0 but < 2.  The query construct will be as follows for BasicDBObject :

BasicDBObject studentFinder = new BasicDBObject();
studentFinder.put("student_id", new BasicDBObject("$gt", 0).append("$lt", 2));

The syntax is simpler in case of QueryBuilder as follows :

QueryBuilder studentFinder = new QueryBuilder();
studentFinder.put("student_id").greaterThan(0).and("student_id").lessThan(2);

Finally, based on the above example, we can create queries at run time.  We are using groovy language and Java 1.7 for our framework and so the sample method to convert filter string at run time will be as follows:

	// Process queries of type A > 10 and B < 5 and B > 1
	private static QueryBuilder getQueryBuilderByString(String filter) {
        if(filter == null || filter=="") return null;
        String[] filters = filter.split(" ");
        QueryBuilder q2 = new QueryBuilder();
        for (int i = 0; i < filters.length; i++) {
            if (i == 0) {
                q2.put(filters[0]);
                continue;
            }
            switch (filters[i]) {
                case ">":
                    if (i + 1 <= filters.length)
                        q2.greaterThan(filters[i + 1].toDouble());
                    else
                        return null;
                    i += 1;
                    break;
                case ">=":
                    if (i + 1 <= filters.length)
                        q2.greaterThanEquals(filters[i + 1].toDouble());
                    else
                        return null;
                    i += 1;
                    break;
                case "<":
                    if (i + 1 <= filters.length)
                        q2.lessThan(filters[i + 1].toDouble());
                    else
                        return null;
                    i += 1;
                    break;
                case "<=":
                    if (i + 1 <= filters.length)
                        q2.lessThanEquals(filters[i + 1].toDouble());
                    else
                        return null;
                    i += 1;
                    break;
                case "=":
                case "==":
                    if (i + 1 <= filters.length)
                        if (filters[i + 1].isDouble())
                            q2.is(filters[i + 1].toDouble());
                        else
                            q2.is(filters[i + 1].toString());
                    else
                        return null;
                    i += 1;
                    break;
                case "<>":
                case "!=":
                    if (i + 1 <= filters.length)
                        if (filters[i + 1].isDouble())
                            q2.notEquals(filters[i + 1].toDouble());
                        else
                            q2.notEquals(filters[i + 1].toString());
                    else
                        return null;
                    i += 1;
                    break;
                case "and":
                case "AND":
                    if (i + 1 <= filters.length)
                        q2.and(filters[i + 1]);
                    else
                        return null;
                    i += 1;
                    break;
                default:
                    break;
            }
        }
        return q2;
    }

Refer the QueryBuilder API documentation for more info.

Advertisements

One comment on “Mongodb JAVA Driver: QueryBuilder class

Got something to say?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s