SQL Support in NCache

In real-life complex scenarios, your application often needs to fetch data based on particular attributes rather than searching through the primary key. Querying your data using SQL makes it easier for you to retrieve data from your database based on your custom logic at runtime.

NCache supports an SQL querying mechanism to let you search your cache according to a given criterion and return the required result set. NCache uses a query language that is very close to the native SQL structured language, which makes it easy for you to query your indexed cached data.

Query methods

NCache uses query interface similar to the ADO.NET interface to support various query methods to search for or delete data from the cache. Here are some of the most important query methods supported in NCache.

• ExecuteReader is used to search data and return key-value pairs. While using the ExecuteReader method, you can specify whether you only want the keys returned or both the keys and the associated data (either as a whole or in chunks).
• .NET
• Java

string query = "SELECT * FROM FQN.Product WHERE ProductID > ?";
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("ProductID",50000);
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand, false, 0);
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        string result = reader.GetValue<string>(0);
    }
}

String query = "SELECT * FROM FQN.Product WHERE ProductID > ?";
QueryCommand queryCommand = new QueryCommand(query);
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("ProductID", 50000);
queryCommand.getParameters().putAll(parameters);
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
if (cacheReader.getFieldCount() > 0) {
    while (cacheReader.read()) {
        cacheReader.getString(0);
}

• ExecuteNonQuery is employed when deleting entries using SQL statements. It returns the number of deleted rows as a result.
• ExecuteScalar is normally used for count and aggregate operations where the query needs to return a single value.

SQL Projections

NCache allows you to specify columns to project against your SQL query. Mentioned below are the basic, most commonly used projections supported in NCache.

• * : when used in a query, it returns the values of all indexed attributes of the specified object defined at the time of configuration.
• $VALUE$ : when used in a query, it returns you the key with the entire object that contains indexed as well as non-indexed attributes.
• Aggregate functions: these functions include functions like SUM(), COUNT(), MIN/MAX(), etc. You can use aggregate functions with any combination of other provided SQL projections.

SELECT COUNT(*) FROM FQN.Product WHERE UnitsInStock > ?

Refer to SQL Search for Object for more information.

SQL Miscellaneous Operators

These operators are used and re-used throughout the SQL database for processing and manipulating data. NCache supports various SQL operators that allow you to get the desired result efficiently. These functions are:

• GROUPBY: returns the value by grouping all keys that fall under the given criterion.
• ORDERBY: returns the value in a sorted form.
• DateTime: searches the cache concerning a particular date or time.
• .NET
• Java

string query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";
var queryCommand = new QueryCommand(query);
DateTime orderDate = new DateTime(2022, 01, 01);
queryCommand.Parameters.Add("OrderDate", orderDate);
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
if (reader.FieldCount > 0)
{
    while (reader.Read())
    {
        int orderID = reader.GetValue<int>("OrderID");
        Console.WriteLine($"Order: {orderID} was placed on 1st January, 2022.");
    }
}
else
{
    Console.WriteLine($"No orders were placed on 1st January 2022.");
}

String query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";
QueryCommand queryCommand = new QueryCommand(query);

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date orderDate;
orderDate = sdf.parse("2022-01-01");
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("OrderDate", orderDate);
queryCommand.getParameters().putAll(parameters);

CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
if (cacheReader.getFieldCount() > 0) {
    while (cacheReader.read()) {

        int orderID = cacheReader.getInt(cacheReader.getOrdinal("OrderID"));
        System.out.println("Order: " + orderID + " was placed on 1st January, 2022.");
    }
} else {
    System.out.println("No orders were placed on 1st January 2022.");
}

SQL Operators in Query

SQL operators are reserved keywords in the WHERE clause to perform logical and comparison operations. NCache provides you with the support of the following SQL operators to query your data according to custom conditions.

• IN: This operation helps search the cache for all provided attributes. Similarly, you have the NOT IN operator that searches for attributes other than the ones provided in the clause.

SELECT ProductName, UnitsAvailable From FQN.Product WHERE Category IN ('Beverages', 'Confections')

• LIKE: This operation searches for specific patterns in a column. LIKE operator supports two wildcards:
  • * : a substitute for zero or more characters in the string.
  • ? : a substitute for a single character in a string.
  • Similarly, you have the NOT LIKE operator.

What to Do Next?

Review NCache Features
Download NCache
Request a Personalized LIVE Demo