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...