Dati delle query SQL nella cache
NCache ti offre la possibilità di interrogare i dati della cache indicizzata attraverso il proprio meccanismo di interrogazione simile a SQL. Ti consente di cercare le chiavi che soddisfano i criteri specificati e quindi restituisce le chiavi del set di risultati. Puoi usare ExecuteReader()
or ExecuteScalar()
per recuperare questi dati.
Note:
Indipendentemente dalle proiezioni utilizzate nella query, il set di risultati restituirà le chiavi solo quando getdata
è impostato su false.
Utilizzo dei prerequisiti delle query SQL
Note:
NCache supporta anche sequenze di escape incluse \n
,\b
, \t
e caratteri jolly *
ed ?
come personaggi per tutti NCache-Operatori SQL supportati. Per saperne di più su queste sequenze di escape fare riferimento a NCache Simile a SQL guida alla sintassi.
Utilizzo di ExecuteReader per interrogare i dati nella cache
ExecuteReader
esegue query sulla cache in base ai criteri specificati dall'utente. Restituisce un elenco di coppie chiave-valore in un lettore di dati che soddisfa i criteri della query. Questa coppia chiave-valore ha una chiave di cache e i rispettivi valori.
Query SQL per le chiavi
Ecco un esempio di query che restituisce le chiavi che soddisfano i criteri basati su Codice prodotto.
Consigli
Assicurati che l'istanza di ICacheReader
viene sempre chiuso dopo l'esecuzione, in quanto è necessario pulire le risorse.
// 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:
Per garantire che l'operazione sia a prova di errore, si consiglia di gestire eventuali potenziali eccezioni all'interno dell'applicazione, come spiegato in Gestione dei guasti.
Utilizzo dell'operatore SQL IN
Per interrogare la cache utilizzando questo formato di query simile a SQL con il file IN operatore, è possibile specificare più valori di attributo dopo l' IN parola chiave. Ecco un esempio che recupera Nome del prodotto ed UnitàInStock dalla cache con 10, 20 o 30 UnitàInStock utilizzando 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")
Utilizzo dell'operatore SQL LIKE
NCache consente di eseguire query per uno schema specifico in una colonna tramite un formato di query simile a SQL utilizzando il file COME operatore. I due caratteri jolly utilizzati con il file COME operatore sono:
*
: utilizzato come sostituto di zero o più caratteri nella stringa.
?
: Usato come sostituto di un singolo carattere nella stringa.
Ecco un esempio che cerca nella cache e recupera Nome del prodotto ed Prezzo unitario utilizzando 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")
Utilizzo dell'operatore SQL GROUP BY
NCache offre la possibilità di raggruppare i dati in base a determinati criteri tramite questo formato di query simile a SQL utilizzando il file RAGGRUPPA PER clausola.
Consigli
Notare quello RAGGRUPPA PER La clausola non può essere utilizzata senza una funzione Aggregate.
L'esempio seguente recupera tutti i prodotti in base al loro Prezzo unitario e li raggruppa secondo il loro Categoria ed Contare.
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")
Utilizzo dell'operatore SQL ORDER BY
NCache offre la possibilità di ordinare i dati in ordine crescente o decrescente, in base ai criteri indicati, tramite un formato di query simile a SQL utilizzando il ORDINATO DA clausola. L'esempio seguente recupera Nome del prodotto ed Prezzo unitario, Dove Nome del prodotto è maggiore di 100 e li ordina in ordine decrescente utilizzando 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")
Utilizzo dell'operatore SQL DateTime
NCache fornisce facilità di ricerca nella cache fornendo parametri in vari formati. È possibile utilizzare molte funzioni in una singola query per una ricerca nella cache più rapida. DateTime
può essere utilizzato nella query per cercare nella cache relativa a una data o ora particolare. Per ulteriori dettagli sui formati delle date, fare riferimento a Documenti Microsoft DateTime Struct.
Note:
Assicurati di specificare la data nel formato corretto.
L'esempio seguente esegue una query che ricerca tutti gli ordini nella cache di cui Data dell'ordine è come specificato utilizzando 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")
Utilizzo dell'operatore logico SQL
NCache fornisce all'utente il E, ORe NON operatori logici per specificare più di una condizione dopo l'operatore in queste query tramite questo formato di query simile a SQL. Ecco un esempio che mostra come utilizzare la combinazione di E ed OR operatori in una singola query all'interno della clausola WHERE utilizzando 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")
Utilizzo di ExecuteScalar per cercare dati nella cache
ExecuteScalar
esegue le query dell'utente e restituisce la prima colonna della prima riga nel set di risultati, eventuali colonne o righe aggiuntive vengono ignorate.
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.");
}
Risorse addizionali
NCache fornisce un'applicazione di esempio per le query SQL su GitHub.
Vedere anche
.NETTO: Alachisoft.NCache.Servizi.clienti spazio dei nomi.
Giava: com.alachisoft.ncache.cache.di.runtime spazio dei nomi.
Node.js: Cache classe.
Pitone: ncache.servizi.clienti classe.