SQL-Abfragedaten im Cache
NCache bietet Ihnen die Möglichkeit, indizierte Cache-Daten über einen eigenen SQL-ähnlichen Abfragemechanismus abzufragen. Es lässt Sie nach Schlüsseln suchen, die die angegebenen Kriterien erfüllen, und gibt dann die Schlüssel der Ergebnismenge zurück. Sie können verwenden ExecuteReader()
or ExecuteScalar()
um diese Daten abzurufen.
Note
Unabhängig davon, welche Projektionen Sie in Ihrer Abfrage verwenden, die Ergebnismenge gibt nur dann Schlüssel zurück, wenn getdata
ist auf false gesetzt.
Verwenden der SQL-Abfragevoraussetzungen
Note
NCache Unterstützt auch Escape-Sequenzen, einschließlich \n
,\b
, \t
, und Platzhalter *
und ?
als Charaktere für alle NCache-unterstützte SQL-Operatoren. Weitere Informationen zu diesen Escape-Sequenzen finden Sie im NCache SQL-ähnlich Syntax-Anleitung.
Verwenden von ExecuteReader zum Abfragen von Daten im Cache
ExecuteReader
Führt Abfragen im Cache basierend auf den vom Benutzer angegebenen Kriterien durch. Es gibt eine Liste von Schlüssel-Wert-Paaren in einem Datenleser zurück, die die Abfragekriterien erfüllen. Dieses Schlüssel-Wert-Paar verfügt über einen Cache-Schlüssel und seine jeweiligen Werte.
SQL-Abfrage für Schlüssel
Hier ist ein Abfragebeispiel, das die Schlüssel zurückgibt, die die Kriterien basierend auf erfüllen Produkt ID.
Wichtig
Stellen Sie sicher, dass die Instanz von ICacheReader
wird nach der Ausführung immer geschlossen, da eine Bereinigung der Ressourcen erforderlich ist.
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query which will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
string query = "SELECT * FROM FQN.Product WHERE ProductID > ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Providing parameters for query
queryCommand.Parameters.Add("ProductID",50000);
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand, false, 0);
// Check if the result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
string result = reader.GetValue<string>(0);
// Perform operations using the retrieved keys
}
}
else
{
// Null query result set retrieved
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT * FROM FQN.Product WHERE ProductID > ?";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Providing parameters for query
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("ProductID", 50000);
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Check if the result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
cacheReader.getString(0);
// Perform operations using the retrieved keys
}
}
// This is an async method
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query which will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
var query = "SELECT * FROM FQN.Product WHERE ProductID > ?";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Providing parameters for query
let parameter = new Map();
parameter.set("ProductID", 50000);
queryCommand.setParameters(parameter);
//Executing query command through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand, false, 0);
//Check if result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
var result = reader.getValue(0, Number());
// Perform operations using the retrieved keys
}
}
else
{
// Null query results retrieved
}
# Pre-condition: Cache is already connected
# Items are already present in the cache
# Create a query which will be executed on the dataset
query = "SELECT * FROM FQN.Product WHERE product_id > ?"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Providing parameters for query
parameter = {"product_id": 50000}
query_command.set_parameters(parameter)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
result = reader.get_value(str, 1)
# Perform operations using the retrieved keys
print(result)
else:
# None query results retrieved
print("Query result is None")
Note
Um sicherzustellen, dass der Vorgang ausfallsicher ist, wird empfohlen, alle potenziellen Ausnahmen in Ihrer Anwendung zu behandeln, wie in erläutert Umgang mit Fehlern.
Verwenden des SQL IN-Operators
Um den Cache mithilfe dieses SQL-ähnlichen Abfrageformats mit dem abzufragen IN Operator können Sie nach dem mehrere Attributwerte angeben IN Stichwort. Hier ist ein Beispiel, das abgerufen wird Produktname und Einheiten auf Lager aus dem Cache mit entweder 10, 20 oder 30 Einheiten auf Lager Verwendung von ExecuteReader
.
string query = "SELECT ProductName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?, ?, ?)";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
ArrayList unitsArray = new ArrayList();
unitsArray.Add(10);
unitsArray.Add(20);
unitsArray.Add(30);
queryCommand.Parameters.Add("UnitsInStock", unitsArray);
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Read results if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
string productName = reader.GetValue<string>("ProductName");
int unitsInStock = reader.GetValue<int>("UnitsInStock");
Console.WriteLine($"Product '{productName}' has {unitsInStock} Units In Stock.");
}
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT ProductName, UnitsInStock FROM FQN.Product WHERE UnitsInStock IN (?, ?, ?)";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Create a list for UnitsInStock values
ArrayList<Integer> unitsArray = new ArrayList<>();
unitsArray.add(10);
unitsArray.add(20);
unitsArray.add(30);
queryCommand.getParameters().put("UnitsInStock", unitsArray);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Read results if result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
// Get the product name
String productName = null;
productName = cacheReader.getValue("ProductName", String.class);
// Get the units in stock
Integer unitsInStock = null;
unitsInStock = cacheReader.getValue("UnitsInStock", Integer.class);
System.out.println("Product '" + productName + "' has " + unitsInStock + " Units In Stock.");
}
}
var query = "SELECT productName, UnitsInStock From FQN.Product WHERE UnitsInStock IN (?,?,?)";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Adding Parameters
let units = [10, 15, 20, 25];
let parameter = new Map();
parameter.set("UnitsInStock", units);
queryCommand.setParameters(parameter);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
// Get the value of the result set
var ProductName = reader.getValue(1, Number());
var UnitsAvailable = reader.getValue(2, Number());
}
}
else
{
// Null query result set retrieved
}
query = "SELECT product_name, units_in_stock From FQN.Product WHERE units_in_stock IN (10,20,30)"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
product_name = reader.get_value(str, 1)
units_available = reader.get_value(int, 2)
# Perform operations
print("Name: " + str(product_name))
print("Units: " + str(units_available))
else:
# None query results retrieved
print("Query result is None")
Verwenden des SQL LIKE-Operators
NCache ermöglicht Ihnen die Abfrage eines bestimmten Musters in einer Spalte über ein SQL-ähnliches Abfrageformat mit dem LIKE Operator. Die beiden Platzhalter, die mit verwendet werden LIKE Betreiber sind:
*
: Wird als Ersatz für null oder mehr Zeichen in der Zeichenfolge verwendet.
?
: Wird als Ersatz für ein einzelnes Zeichen in der Zeichenfolge verwendet.
Hier ist ein Beispiel, das den Cache durchsucht und abruft Produktname und Stückpreis Verwendung von ExecuteReader
.
string query = "SELECT ProductName, UnitPrice From FQN.Product WHERE ProductName LIKE ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("ProductName", "choco*");
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Read results if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
string productName = reader.GetValue<string>("ProductName");
double unitPrice = reader.GetValue<double>("UnitPrice");
Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
}
}
else
{
Console.WriteLine($"No product found having a name that starts with 'choco'.");
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT ProductName, UnitPrice FROM FQN.Product WHERE ProductName LIKE ?";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Setting the parameter for the LIKE operator
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("ProductName", "choco*");
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Read results if result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
String productName = cacheReader.getValue("ProductName", String.class);
Double unitPrice = cacheReader.getValue("UnitPrice", Double.class);
System.out.println("Product: " + productName + ", Unit Price: " + unitPrice);
}
} else {
System.out.println("No product found having a name that starts with 'choco'.");
}
var query = "SELECT * FROM FQN.Product WHERE ProductName LIKE ? AND Category LIKE ?";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
// Providing parameters for query
let parameter1 = new Map();
parameter1.set("ProductName", "P*");
queryCommand.setParameters(parameter1);
let parameter2 = new Map();
parameter2.set("Category", " Beverage?");
queryCommand.setParameters(parameter2);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
var result = reader.getValue(1, Number());
// Perform operations
}
}
else
{
// Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE product_name LIKE ? AND category LIKE ?"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Providing parameters for query
parameter1 = {"product_name": "Shamp*"}
parameter2 = {"category": "Househo*"}
query_command.set_parameters(parameter1)
query_command.set_parameters(parameter2)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
result = reader.get_value(str, columnname="product_name")
# Perform operations
print(result)
else:
# None query results retrieved
print("Query result is None")
Verwenden des SQL GROUP BY-Operators
NCache bietet Ihnen die Möglichkeit, Daten mithilfe dieses SQL-ähnlichen Abfrageformats nach einem bestimmten Kriterium zu gruppieren GRUPPIERE NACH Klausel.
Wichtig
Beachten Sie, dass die GRUPPIERE NACH -Klausel kann nicht ohne eine Aggregate-Funktion verwendet werden.
Das folgende Beispiel ruft alle Produkte basierend auf ihrer ab Stückpreis und gruppiert sie nach ihren Kategorie und Zu Zählen.
string query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice < ? Group By Category";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("UnitPrice", 100.0);
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Check if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
// Get the value of the result set
string category = reader.GetValue<string>("Category");
int count = reader.GetValue<int>("COUNT()");
Console.WriteLine($"Category '{category}' has '{count}' affordable products.");
}
}
else
{
Console.WriteLine($"No category contains affordable products.");
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice < ? GROUP BY Category";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Setting the parameter
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("UnitPrice", 100.0);
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Read results if result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
String productName = cacheReader.getValue("Category", String.class);
Double unitPrice = cacheReader.getValue("COUNT(*)", Double.class);
System.out.println("Category '" + category + "' has '" + count + "' affordable products.");
}
} else {
System.out.println("No category contains affordable products.");
}
var query = "SELECT Category, COUNT(*) FROM FQN.Product WHERE UnitPrice > ? Group By Category";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Adding Parameters
let parameter = new Map();
parameter.set("UnitPrice", 10);
queryCommand.setParameters(parameter);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
// Get the value of the result set
var result = reader.getValue(1, ncache.JsonDataType.Object);
var category = reader.getValue(0, Number());
}
}
else
{
// Null query result set retrieved
}
query = "SELECT category, COUNT(*) FROM FQN.Product WHERE unit_price > 5 Group By category"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
result = reader.get_value(int, 1)
category = reader.get_value(str, 0)
# Perform operations
print("Count: " + str(result))
print("Category: " + str(category))
else:
# None query results retrieved
print("Query result is None")
Verwenden des SQL ORDER BY-Operators
NCache bietet Ihnen die Möglichkeit, die Daten in aufsteigender oder absteigender Reihenfolge nach den angegebenen Kriterien über ein SQL-ähnliches Abfrageformat mit dem zu sortieren SORTIEREN NACH Klausel. Das folgende Beispiel ruft ab Produktname und Stückpreis, Wobei Produktname ist größer als 100 und sortiert sie in absteigender Reihenfolge nach ExecuteReader
.
string query = "SELECT ProductName,UnitPrice FROM FQN.Product WHERE UnitPrice < ? ORDER BY UnitPrice DESC";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
//changes
queryCommand.Parameters.Add("UnitPrice", 100.0);
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Check if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
// Get the value of the result set
string productName = reader.GetValue<string>("ProductName");
string unitPrice = reader.GetValue<string>("UnitPrice");
Console.WriteLine($"Product: {productName}, Unit Price: {unitPrice}");
}
}
else
{
Console.WriteLine($"No products found which are cheaper than 100.");
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT ProductName, UnitPrice FROM FQN.Product WHERE UnitPrice < ? ORDER BY UnitPrice DESC";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Setting the parameter
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("UnitPrice", 100.0);
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Read results if result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
String productName = cacheReader.getValue("ProductName", String.class);
Double unitPrice = cacheReader.getValue("UnitPrice", Double.class);
System.out.println("Product: " + productName + ", Unit Price: " + unitPrice);
}
} else {
System.out.println("No products found which are cheaper than 100.");
}
var query = "SELECT * FROM FQN.Product WHERE UnitPrice > ? ORDER BY UnitsInStock DESC";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Providing parameters for query
let parameter = new Map();
parameter.set("UnitPrice", 100);
queryCommand.setParameters(parameter);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
// Get the value of the result set
var result = reader.getValue(1, Number());
}
}
else
{
// Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE unit_price > 100 ORDER BY units_in_stock DESC"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
result = reader.get_value(int, columnname="units_in_stock")
# Perform operations
print("Units: " + str(result))
else:
# None query results retrieved
print("Query result is None")
Verwenden des SQL DateTime-Operators
NCache erleichtert Ihnen die Suche im Cache, indem Parameter in verschiedenen Formaten bereitgestellt werden. Sie können viele Funktionen in einer einzigen Abfrage verwenden, um die Cache-Suche zu beschleunigen. DateTime
kann in Ihrer Abfrage verwendet werden, um den Cache nach einem bestimmten Datum oder einer bestimmten Uhrzeit zu durchsuchen. Weitere Einzelheiten zu Datumsformaten finden Sie im Microsoft DateTime-Strukturdokumente.
Note
Stellen Sie sicher, dass Sie das Datum im richtigen Format angeben.
Im folgenden Beispiel wird eine Abfrage ausgeführt, die nach allen Bestellungen im Cache sucht, deren Auftragsdatum ist wie angegeben mit ExecuteReader
.
string query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
// Define order date
DateTime orderDate = new DateTime(2022, 01, 01);
// Providing parameters to query
queryCommand.Parameters.Add("OrderDate", orderDate);
// Executing QueryCommand through ICacheReader
ICacheReader reader = cache.SearchService.ExecuteReader(queryCommand);
// Read results if result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
// Get the value of the result set
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.");
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT OrderID FROM FQN.Order WHERE OrderDate = ?";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Define order date in Java
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date orderDate;
orderDate = sdf.parse("2022-01-01");
// Providing parameters to query
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("OrderDate", orderDate);
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Read results if result set is not empty
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.");
}
// Use the Fully Qualified Name (FQN) of your own custom class
var query = "SELECT * FROM FQN.Order WHERE OrderDate < ?";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
// Providing parameters to query
var date = new Date("2021-02-10");
let map = new Map();
var parameters = map.set("orderDate", date);
queryCommand.setParameters(parameters);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if the result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
// Get the value of the result set
var result = reader.getValue(1, Number());
// Perform operations
}
}
else
{
// Null query result set retrieved
}
query = "SELECT * FROM FQN.Order WHERE order_date < ?"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Providing parameters for query
parameter = {"order_date": datetime.strptime('Jan 1 2022 1:33PM', '%b %d %Y %I:%M%p')}
query_command.set_parameters(parameter)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
order_id = reader.get_value(int, 1)
# Perform operations
print("Order ID: " + str(order_id))
else:
# None query results retrieved
print("Query result is None")
Verwenden des logischen SQL-Operators
NCache stellt dem Benutzer die zur Verfügung UND, OR und NICHT Mithilfe logischer Operatoren können Sie in diesen Abfragen über dieses SQL-ähnliche Abfrageformat mehr als eine Bedingung nach dem Operator angeben. Hier ist ein Beispiel, das zeigt, wie die Kombination von verwendet wird UND und OR Operatoren in einer einzigen Abfrage innerhalb der WHERE-Klausel mit ExecuteReader
.
string query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
//Adding Parameters
ArrayList categoryList = new ArrayList();
categoryList.Add("Beverages");
categoryList.Add("Produce");
queryCommand.Parameters.Add("Category", categoryList);
queryCommand.Parameters.Add("UnitsInStock", 100);
queryCommand.Parameters.Add("UnitPrice", 100);
// Executing QueryCommand through ICacheReader
ICacheReader reader = _cache.SearchService.ExecuteReader(queryCommand);
// Check if the result set is not empty
if (reader.FieldCount > 0)
{
while (reader.Read())
{
// Get the value of the result set
string result = reader.GetValue<string>("ProductID");
}
}
else
{
// Null query result set retrieved
}
catch (OperationFailedException ex)
if (ex.ErrorCode == NCacheErrorCodes.INCORRECT_FORMAT)
{
// Make sure that the query format is correct
}
else
{
// Exception can occur due to:
// Connection Failures
// Operation Timeout
// Operation performed during state transfer
}
// Pre-condition: Cache is already connected
// Items are already present in the cache
// Create a query that will be executed on the dataset
// Use the Fully Qualified Name (FQN) of your own custom class
String query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
// Adding Parameters
ArrayList<String> categoryList = new ArrayList<>();
categoryList.add("Beverages");
categoryList.add("Produce");
HashMap<String, Object> parameters = new HashMap<>();
parameters.put("Category1", categoryList.get(0));
parameters.put("Category2", categoryList.get(1));
parameters.put("UnitsInStock", 100);
parameters.put("UnitPrice", 100.0);
queryCommand.getParameters().putAll(parameters);
// Executing QueryCommand through CacheReader
CacheReader cacheReader = cache.getSearchService().executeReader(queryCommand);
// Check if the result set is not empty
if (cacheReader.getFieldCount() > 0) {
while (cacheReader.read()) {
// Get the value of the result set
String result = cacheReader.getString(cacheReader.getOrdinal("ProductID"));
}
} else {
// Null query result set retrieved
}
var query = "SELECT * FROM FQN.Product WHERE (Category = ? AND UnitsInStock > ?) OR (Category = ? AND UnitPrice > ?)";
// Use QueryCommand for query execution
var queryCommand = new ncache.QueryCommand(query);
//Add parameters
let categoryList = Array();
categoryList.push("Produce");
categoryList.push("Beverages");
let parameter = new Map();
parameter.set("Category", categories);
parameter.set("UnitsInStock", 100);
parameter.set("UnitPrice", 100);
queryCommand.setParameters(parameter);
// Executing QueryCommand through CacheReader
var searchService = await this.cache.getSearchService();
var reader = await searchService.executeReader(queryCommand);
// Check if the result set is not empty
if (reader.getFieldCount() > 0)
{
while (reader.read())
{
// Get the value of the result set
var result = reader.getValue(1, Number());
}
}
else
{
// Null query result set retrieved
}
query = "SELECT * FROM FQN.Product WHERE (category = 'Beverages' AND units_in_stock > 0) OR (category = 'Produce' AND unit_price > 100)"
# Use QueryCommand for query execution
query_command = ncache.QueryCommand(query)
# Executing query command through CacheReader
search_service = cache.get_search_service()
reader = search_service.execute_reader(query_command)
# Check if result set is not empty
if reader.get_field_count() > 0:
while reader.read():
product_id = reader.get_value(str, 1)
# Perform operations
print("Product ID: " + str(product_id))
else:
# None query results retrieved
print("Query result is None")
Verwenden von ExecuteScalar zum Suchen von Daten im Cache
ExecuteScalar
führt Benutzerabfragen aus und gibt die erste Spalte der ersten Zeile im Ergebnissatz zurück. Alle zusätzlichen Spalten oder Zeilen werden ignoriert.
string query = "SELECT AVG(UnitPrice) FROM FQN.Product WHERE Category = ?";
// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);
queryCommand.Parameters.Add("Category", "Beverages");
// Executing QueryCommand through ICacheReader
decimal average = (decimal)cache.SearchService.ExecuteScalar(queryCommand);
Console.WriteLine($"Average price of Beverages is {average}.");
// Create a query that will be executed on the dataset
String query = "SELECT AVG(UnitPrice) FROM FQN.Product WHERE Category = ?";
// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);
queryCommand.getParameters().put("Category", "Beverages");
// Executing QueryCommand through ExecuteScalar
Double average = cache.getSearchService().executeScalar(queryCommand, Double.class);
if (average != null) {
System.out.println("Average price of Beverages is " + average + ".");
} else {
System.out.println("No data found for Beverages.");
}
Weitere Informationen
NCache stellt Beispielanwendungen für SQL-Abfragen bereit GitHub.
Siehe auch
.NETZ: Alachisoft.NCache.Kundenservice Namespace.
Java: com.alachisoft.ncache.runtime.caching Namespace.
Node.js: Cache-Speicher Klasse.
Python: ncache.Kundenservice Klasse.