Taste of SQL for In-Memory Cache Queries

I plan to blog in more detail about support of native SQL in GridGain data grid in a few days, but here is just a taste on how complex of an SQL statement you can execute against GridGain distributed in-memory cache. Note that you are not querying a database - you are querying objects cached in memory.

The query below utilizes H2 database SQL over JDBC syntax to query a collection of QueueItems at specified positions of a dynamic priority-based queue stored in distributed GridGain cache. As we know, querying an element at a certain position of a result set is not trivial in any SQL, and nested 'select' statements with 'rownum' field usually need to be used. On top of that we need to pass a collection of item positions to a query, which is also a fairly advanced feature.

String sql = 
    "select * from (" +
        "select *, rownum as r from (" +
            "select * from QueueItem where qid=? " + 
                "order by priority desc, seq asc" +
        ")" +
    ") where r in (select * from table(x int=?))"

GridCacheQuery itemsQry = cache.createQuery(SQL, QueueItem.class, sql);

// Specify positions of queue items we want to get.
Collection<Integer> positions = Collections.asList(10, 12, 14);

// Query items at specified positions from queue with ID 123.
GridCacheQueryFuture fut = itemsQry.queryArguments(123, positions).execute();

System.out.println("Queried queue items at specified positions: " + fut.get());

Pretty powerful in my view especially when you also get smart routing, pagination, custom transformers, reducers, visit-only support, text queries, and a lot more...

More information on GridGain queries can be found here or here.

From http://gridgain.blogspot.com/2011/01/taste-of-sql-for-in-memory-cache.html