Dados de consulta SQL no cache
NCache fornece a capacidade de consultar dados de cache indexados por meio de seu próprio mecanismo de consulta semelhante a SQL. Ele permite pesquisar chaves que atendem aos critérios fornecidos e, em seguida, retorna as chaves do conjunto de resultados. Você pode usar ExecuteReader()
ou `Exe
Note
Não importa quais projeções você use em sua consulta, o conjunto de resultados só retornará chaves quando getdata
é definido como falso.
Usando pré-requisitos de consulta SQL
Note
NCache também suporta sequências de escape, incluindo \n
,\b
, \t
, e curingas *
e ?
como personagens para todos NCache-operadores SQL suportados. Para saber mais sobre essas sequências de escape, consulte o NCache Semelhante a SQL guia de sintaxe.
Usando o ExecuteReader para consultar dados no cache
ExecuteReader
executa consultas no cache com base nos critérios especificados pelo usuário. Ele retorna uma lista de pares de valores-chave em um leitor de dados que atende aos critérios de consulta. Este par chave-valor possui uma chave de cache e seus respectivos valores.
Consulta SQL para chaves
Aqui está um exemplo de consulta que retorna as chaves que atendem aos critérios com base em ID do produto.
importante
Certifique-se de que a instância de ICacheReader
é sempre fechado após a execução, pois é necessário limpar os recursos.
// 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
Para garantir que a operação seja à prova de falhas, é recomendável lidar com possíveis exceções em seu aplicativo, conforme explicado em Como lidar com falhas.
Usando o operador SQL IN
Para consultar o cache usando este formato de consulta semelhante ao SQL com o IN operador, você pode especificar vários valores de atributo após o IN palavra-chave. Aqui está um exemplo que recupera Nome do Produto e Unidades em estoque do cache com 10, 20 ou 30 Unidades em estoque utilização 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")
Usando o Operador SQL LIKE
NCache permite consultar um padrão específico em uma coluna por meio do formato de consulta semelhante ao SQL usando o COMO operador. Os dois curingas usados com o COMO operador são:
*
: Usado como substituto para zero ou mais caracteres na string.
?
: Usado como substituto de um único caractere na string.
Aqui está um exemplo que pesquisa o cache e recupera Nome do Produto e Preço unitário utilização 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")
Usando o operador SQL GROUP BY
NCache fornece a capacidade de agrupar dados de acordo com um determinado critério por meio deste formato de consulta semelhante ao SQL usando o GROUP BY cláusula.
importante
Observe que o GROUP BY cláusula não pode ser usada sem uma função Agregada.
O exemplo a seguir recupera todos os produtos com base em seus Preço unitário e os agrupa de acordo com suas Categoria e Contar.
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")
Usando o operador SQL ORDER BY
NCache fornece a capacidade de classificar os dados em ordem crescente ou decrescente, de acordo com os critérios fornecidos, por meio de um formato de consulta semelhante ao SQL usando o ORDENAR POR cláusula. O exemplo a seguir recupera Nome do Produto e Preço unitário, Onde Nome do Produto é maior que 100 e os classifica em ordem decrescente usando 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")
Usando o operador SQL DateTime
NCache oferece facilidade de pesquisa no cache, fornecendo parâmetros em vários formatos. Você pode usar muitas funções em uma única consulta para uma pesquisa de cache mais rápida. DateTime
pode ser usado em sua consulta para pesquisar o cache referente a uma data ou hora específica. Para obter mais detalhes sobre formatos de data, consulte o Documentos de estrutura de data e hora da Microsoft.
Note
Certifique-se de especificar a data no formato correto.
O exemplo a seguir executa uma consulta que procura todos os pedidos no cache cujos Data do pedido é como especificado usando 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")
Usando o Operador Lógico SQL
NCache fornece ao usuário o E, OR e NÃO operadores lógicos para especificar mais de uma condição após o operador nessas consultas por meio desse formato de consulta semelhante ao SQL. Aqui está um exemplo que mostra como usar a combinação de E e OR operadores em uma única consulta dentro da cláusula WHERE usando 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")
Usando ExecuteScalar para pesquisar dados no cache
ExecuteScalar
executa consultas do usuário e retorna a primeira coluna da primeira linha no conjunto de resultados, quaisquer colunas ou linhas adicionais serão ignoradas.
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.");
}
Recursos adicionais
NCache fornece aplicativo de amostra para consultas SQL em GitHub.
Veja também
.INTERNET: Alachisoft.NCache.Serviço para clientes espaço para nome.
Java: com.alachisoft.ncache.runtime.caching espaço para nome.
Node.js: Esconderijo classe.
Pitão: ncache.Serviço para clientes classe.