Données de requête SQL dans le cache
NCache vous offre la possibilité d'interroger les données de cache indexées via son propre mécanisme d'interrogation de type SQL. Il vous permet de rechercher des clés répondant aux critères donnés, puis renvoie les clés du jeu de résultats. Vous pouvez utiliser ExecuteReader()
or ExecuteScalar()
pour récupérer ces données.
Notes
Quelles que soient les projections que vous utilisez dans votre requête, le jeu de résultats ne renverra les clés que lorsque getdata
est défini sur false.
Utilisation des conditions préalables aux requêtes SQL
Notes
NCache prend également en charge les séquences d'échappement, notamment \n
,\b
, \t
, et des jokers *
ainsi que ?
comme personnages pour tous NCache-Opérateurs SQL pris en charge. Pour en savoir plus sur ces séquences d'échappement, reportez-vous au NCache De type SQL guide de syntaxe.
Utilisation d'ExecuteReader pour interroger des données dans le cache
ExecuteReader
effectue des requêtes sur le cache en fonction des critères spécifiés par l'utilisateur. Il renvoie une liste de paires clé-valeur dans un lecteur de données qui remplit les critères de requête. Cette paire clé-valeur possède une clé de cache et ses valeurs respectives.
Requête SQL pour les clés
Voici un exemple de requête qui renvoie les clés remplissant les critères basés sur ID de produit.
Important
Assurez-vous que l'instance de ICacheReader
est toujours fermé après exécution, car il est nécessaire de nettoyer les ressources.
// 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")
Notes
Pour garantir la sécurité de l'opération, il est recommandé de gérer toutes les exceptions potentielles au sein de votre application, comme expliqué dans Gestion des échecs.
Utilisation de l'opérateur SQL IN
Pour interroger le cache en utilisant ce format de requête de type SQL avec le IN , vous pouvez spécifier plusieurs valeurs d'attribut après l'opérateur IN mot-clé. Voici un exemple qui récupère ProductName ainsi que Unités en stock du cache ayant 10, 20 ou 30 Unités en stock en utilisant 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")
Utilisation de l'opérateur SQL LIKE
NCache vous permet de rechercher un modèle spécifique dans une colonne via un format de requête de type SQL à l'aide de la COMME opérateur. Les deux caractères génériques utilisés avec le COMME l'opérateur sont :
*
: utilisé en remplacement de zéro ou plusieurs caractères dans la chaîne.
?
: utilisé en remplacement d'un seul caractère dans la chaîne.
Voici un exemple qui recherche dans le cache et récupère ProductName ainsi que Prix unitaire en utilisant 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")
Utilisation de l'opérateur SQL GROUP BY
NCache vous offre la possibilité de regrouper des données selon un critère donné via ce format de requête de type SQL en utilisant le PAR GROUPE clause.
Important
Notez que le PAR GROUPE La clause ne peut pas être utilisée sans une fonction Aggregate.
L'exemple suivant récupère tous les produits en fonction de leur Prix unitaire et les regroupe selon leur Catégories ainsi que que vous avez.
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")
Utilisation de l'opérateur SQL ORDER BY
NCache vous offre la possibilité de trier les données par ordre croissant ou décroissant, selon les critères donnés, via un format de requête de type SQL à l'aide de la COMMANDÉ PAR clause. L'exemple suivant récupère ProductName ainsi que Prix unitaire, Où ProductName est supérieur à 100 et les trie par ordre décroissant en utilisant 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")
Utilisation de l'opérateur SQL DateTime
NCache vous offre une facilité de recherche dans le cache en fournissant des paramètres dans différents formats. Vous pouvez utiliser de nombreuses fonctions dans une seule requête pour accélérer la recherche dans le cache. DateTime
peut être utilisé dans votre requête pour rechercher dans le cache une date ou une heure particulière. Pour plus de détails sur les formats de date, veuillez vous référer au Docs de structure Microsoft DateTime.
Notes
Assurez-vous de spécifier la date dans le format correct.
L'exemple suivant exécute une requête qui recherche toutes les commandes dans le cache dont Date de commande est tel que spécifié en utilisant 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")
Utilisation de l'opérateur logique SQL
NCache fournit à l'utilisateur le ET, ORet une ne pas opérateurs logiques pour spécifier plus d'une condition après l'opérateur dans ces requêtes via ce format de requête de type SQL. Voici un exemple montrant comment utiliser la combinaison de ET ainsi que OR opérateurs dans une seule requête dans la clause WHERE en utilisant 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")
Utilisation d'ExecuteScalar pour rechercher des données dans le cache
ExecuteScalar
exécute les requêtes utilisateur et renvoie la première colonne de la première ligne du jeu de résultats, toutes les colonnes ou lignes supplémentaires sont ignorées.
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.");
}
Ressources additionnelles
NCache fournit un exemple d'application pour les requêtes SQL sur GitHub.
Voir aussi
.RAPPORTER: Alachisoft.NCache.Services clients espace de noms.
Java: com.alachisoft.ncache.runtime.caching espace de noms.
Node.js : Cache classe.
python: ncache.services clients classe.