Using Queries

Learn about some aspects of using queries to your application in Oracle NoSQL Database.

Oracle NoSQL Database provides a rich query language to read and update data. See Developers Guide for a full description of the query language.

To execute your query, you use the NoSQLHandle.query() API.

To execute a SELECT query to read data from your table:

/* QUERY a table named "users", using the primary key field "name". 
 * The table name is inferred from the query statement.
 */
QueryRequest queryRequest = new QueryRequest().
setStatement("SELECT * FROM users WHERE name = \"Taylor\"");

/* Queries can return partial results. It is necessary to loop,
 * reissuing the request until it is "done"
 */

do {
  QueryResult queryResult = handle.query(queryRequest);

  /* process current set of results */
  List<MapValue> results = queryResult.getResults();
  for (MapValue qval : results) {
    //handle result
  }
} while (!queryRequest.isDone());

When using queries, be aware of the following considerations:

You can use prepared queries when you want to run the same query multiple times. When you use prepared queries, the execution is more efficient than starting with a query string every time. The query language and API support query variables to assist with the reuse.

For example, to execute a SELECT query to read data from your table using a prepared statement:

/* Perform the same query using a prepared statement. This is more
 * efficient if the query is executed repeatedly and required if
 * the query contains any bind variables.
 */
String query = "DECLARE $name STRING; " +
               "SELECT * from users WHERE name = $name";

PrepareRequest prepReq = new PrepareRequest().setStatement(query);
/* prepare the statement */
PrepareResult prepRes = handle.prepare(prepReq);
/* set the bind variable and set the statement in the QueryRequest */
prepRes.getPreparedStatement()
       .setVariable("$name", new StringValue("Taylor"));
QueryRequest queryRequest = new QueryRequest().setPreparedStatement(prepRes);

/* perform the query in a loop until done */
do {
  QueryResult queryResult = handle.query(queryRequest);
  /* handle result */
} while (!queryRequest.isDone());

To execute a query use the borneo.NoSQLHandle.query() method. For example, to execute a SELECT query to read data from your table, a borneo.QueryResult contains a list of results. And if the borneo.QueryRequest.is_done() returns False, there may be more results, so queries should generally be run in a loop. It is possible for single request to return no results but the query still not done, indicating that the query loop should continue. For example:

from borneo import QueryRequest
# Query at table named 'users" using the field 'name' where name may match 
# 0 or more rows in the table. The table name is inferred from the query 
statement = 'select * from users where name = "Jane"'
request = QueryRequest().set_statement(statement) 
# loop until request is done, handling results as they arrive
while True: result = handle.query(request)
# handle results
handle_results(result)
# do something with results
if request.is_done(): break

When using queries it is important to be aware of the following considerations:

Oracle NoSQL Database provides the ability to prepare queries for execution and reuse. It is recommended that you use prepared queries when you run the same query for multiple times. When you use prepared queries, the execution is much more efficient than starting with a query string every time. The query language and API support query variables to assist with query reuse.
The borneo.QueryRequest allows you to set the read consistency for a query as well as modifying the maximum amount of resource (read and write) to be used by a single request. This can be important to prevent a query from getting throttled because it uses too much resource too quickly.

Here is an example of using a prepared query with a single variable:

from borneo import PrepareRequest, QueryRequest
# Use a similar query to above but make the name a variable
statement = 'declare $name string
select * from users where name = $name'
prequest = PrepareRequest().set_statement(statement)
presult = handle.prepare(prequest)
# use the prepared statement, set the variable 
pstatement = presult.get_prepared_statement()
pstatement.set_variable('$name', 'Jane')
qrequest = QueryRequest().set_prepared_statement(pstatement)
# loop until qrequest is done, handling results as they arrive
while True:
# use the prepared query in the query 
request qresult = handle.query(qrequest)
# handle results
handle_results(qresult)
# do something with results
if qrequest.is_done(): break
# use a different variable value with the same prepared query
pstatement.set_variable('$name', 'another_name') 
qrequest = QueryRequest().set_prepared_statement(pstatement)
# loop until qrequest is done, handling results as they arrive
while True:
# use the prepared query in the query 
request qresult = handle.query(qrequest)
# handle results
handle_results(qresult)
# do something with results
if qrequest.is_done(): break

To execute a query use the Client.Query function. For example, to execute a SELECT query to read data from your table:

prepReq := &nosqldb.PrepareRequest{
    Statement: "select * from users",
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
    fmt.Printf("Prepare failed: %v\n", err)
    return
}
queryReq := &nosqldb.QueryRequest{
    PreparedStatement: &prepRes.PreparedStatement,
}
var results []*types.MapValue
for {
    queryRes, err := client.Query(queryReq)
    if err != nil {
        fmt.Printf("Query failed: %v\n", err)
        return
    }
    res, err := queryRes.GetResults()
    if err != nil {
        fmt.Printf("GetResults() failed: %v\n", err)
        return
    }
    results = append(results, res...)
    if queryReq.IsDone() {
        break
    }
}

Queries should generally be run in a loop and check QueryRequest.IsDone() to determine if the query completes. It is possible for a single request to return no results but still have QueryRequest.IsDone() evaluated to false, indicating that the query loop should continue.

When using queries it is important to be aware of the following considerations:

Oracle NoSQL Database provides the ability to prepare queries for execution and reuse. It is recommended that you use prepared queries when you run the same query multiple times. When you use prepared queries, the execution is much more efficient than starting with a query string every time. The query language and API support query variables to assist with query reuse.
The nosqldb.QueryRequest allows you to set the read consistency for a query (via the QueryRequest.Consistency field), as well as modifying the maximum amount of resources (read and write, via the QueryRequest.MaxReadKB and QueryRequest.MaxWriteKB fields) to be used by a single request. This can be important to prevent a query from getting throttled because it uses too many resources too quickly.

To execute a query use query method. This method returns returns Promise of QueryResult which is plain JavaScript object that contains an Array of resulting rows as well as continuation key. The amount of data returned by the query is limited by the system default and could be further limited by setting maxReadKB property in the opt argument of query, which means that one invocation of query method may not return all available results. This situation is dealt with by using continuationKey property. Not-null continuation key means that more query results may be available. This means that queries should generally run in a loop, looping until continuation key becomes null. Note that it is possible for rows to be empty yet have not-null continuationKey, which means the query loop should continue. In order to receive all the results, call query in a loop. At each iteration, if non-null continuation key is received in QueryResult, set continuationKey property in the opt argument for the next iteration:

const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
.....
const client = new NoSQLClient('config.json');
async function queryUsersTable() {
    const opt = {};
    try {
        do {
            const result = await client.query('SELECT * FROM users', opt);
            for(let row of result.rows) {
                console.log(row);
            }
            opt.continuationKey = result.continuationKey;
        } while(opt.continuationKey);
    } catch(error) {
        //handle errors
    }
}

When using queries it is important to be aware of the following considerations:

The Oracle NoSQL Database provides the ability to prepare queries for execution and reuse. It is recommended that you use prepared queries when you run the same query for multiple times. When you use prepared queries, the execution is much more efficient than starting with a query string every time. The query language and API support query variables to assist with query reuse.
Using opt argument of query allows you to set the read consistency for query as well as modifying the maximum amount of data it reads in a single call. This can be important to prevent a query from getting throttled.

Use prepare method to prepare the query. This method returns Promise of PreparedStatement object. Use set method to bind query variables. To run prepared query, pass PreparedStatement to the query or queryIterable instead of the statement string.

const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
.....
const client = new NoSQLClient('config.json');

async function queryUsersTable() {
    const statement = 'DECLARE $name STRING; SELECT * FROM users WHERE ' +
        'name = $name';
    try {
        let prepStatement = await client.prepare(statement);
        const opt = {};
        // Set value for $name variable
        prepStatement.set('$name', 'Taylor');
        do {
            let result = await client.query(prepStatement);
            for(let row of result.rows) {
                console.log(row);
            }
           opt.continuationKey = result.continuationKey;
        } while (opt.continuationKey);
        // Set different value for $name and re-execute the query
        prepStatement.set('$name', 'Jane');
        do {
            let result = await client.query(prepStatement);
            for(let row of result.rows) {
                console.log(row);
            }
           opt.continuationKey = result.continuationKey;
        } while (opt.continuationKey);
    } catch(error) {
        //handle errors
    }
}

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable. You may pass options to each of these methods as QueryOptions. QueryAsync method return Task<QueryResult<RecordValue>>. QueryResult contains query results as a list of RecordValue instances, as well as other information. When your query specifies a complete primary key (or you are executing an INSERT statement), it is sufficient to call QueryAsync once.

var client = new NoSQLClient("config.json");
try {
    var result = await client.QueryAsync(
    "SELECT * FROM users WHERE id = 1");
    // Because we select by primary key, there can be at most one record.
    if (result.Rows.Count>0) {
       Console.WriteLine("Got record: {0}.", result.Rows[0]);
    }
    else {
       Console.WriteLine("Got no records.");
    }
}
catch(Exception ex) {
  // handle exceptions
}

The amount of data returned by the query is limited by the system. It could also be further limited by setting MaxReadKB property of QueryOptions. This means that one invocation of QueryAsync may not return all available results. This situation is dealt with by using continuation key. Non-null ContinuationKey in QueryResult means that more more query results may be available. This means that queries should run in a loop, looping until the continuation key becomes null.

Note that it is possible for query to return now rows (QueryResult.Rows is empty) yet have not-null continuation key, which means that the query should continue looping. To continue the query, set ContinuationKey in the QueryOptions for the next call to QueryAsync and loop until the continuation key becomes null. The following example executes the query and prints query results:

var client = new NoSQLClient("config.json");
var options = new QueryOptions();
try {
    do {
        var result = await client.QueryAsync(
            "SELECT id, name FROM users ORDER BY name",
            options);
        foreach(var row of result.Rows) {
            Console.WriteLine(row);
        }
        options.ContinuationKey = result.ContinuationKey;
    }
    while(options.ContinuationKey != null);
}
catch(Exception ex){
    // handle exceptions
}

Another way to execute the query in a loop is to use GetQueryAsyncEnumerable. It returns an instance of AsyncEnumerable<QueryResult> that can be iterated over. Each iteration step returns a portion of the query results as QueryResult.

var client = new NoSQLClient("config.json");
try {
    await foreach(var result in client.GetQueryAsyncEnumerable(
        "SELECT id, name FROM users ORDER BY name"))
    {
        foreach(var row of result.Rows) {
            Console.WriteLine(row);
        }
    }
}
catch(Exception ex){
    // handle exceptions
}

Oracle NoSQL Database provides the ability to prepare queries for execution and reuse. It is recommended that you use prepared queries when you run the same query for multiple times. When you use prepared queries, the execution is much more efficient than starting with a SQL statement every time. The query language and API support query variables to assist with query reuse.

Use PrepareAsync to prepare the query. This method returns Task<PreparedStatement>. PreparedStatement allows you to set query variables. The query methods QueryAsync and GetQueryAsyncEnumerable have overloads that execute prepared queries by taking PreparedStatement as a parameter instead of the SQL statement. For example:

var client = new NoSQLClient("config.json");
try {
    var sql = "DECLARE $name STRING; SELECT * FROM users WHERE " +
        "name = $name";
    var preparedStatement = await client.PrepareAsync(sql);
    // Set value for $name variable and execute the query
    preparedStatement.Variables["$name"] = "Taylor";
    await foreach(var result in client.GetQueryAsyncEnumerable(
        preparedStatement)) {
        foreach(var row of result.Rows) {
            Console.WriteLine(row);
        }
    }
    // Set different value for $name and re-execute the query.
    preparedStatement.Variables["$name"] = "Jane";
    await foreach(var result in client.GetQueryAsyncEnumerable(
        preparedStatement)) {
        foreach(var row of result.Rows) {
            Console.WriteLine(row);
        }
    }
}
catch(Exception ex){
    // handle exceptions
}

Use one of these methods to run your query - The NosqlRepository derived queries, native queries, or using NosqlTemplate runQuery(), runQueryJavaParams(), runQueryNosqlParams(). For details, see SDK for Spring Data API Reference.

Note:

First, create the AppConfig class that extends AbstractNosqlConfiguration class to provide the connection details of the Oracle NoSQL Database. For more details, see Obtaining a NoSQL connection.

In this section, you use the derived queries. For more details on the derived queries, see Derived Queries.

Create the UsersRepository interface. This interface extends the NosqlRepository interface and provides the entity class and the data type of the primary key in that class as parameterized types to the NosqlRepository interface. The NosqlRepository interface provides methods that are used to retrieve data from the database.

import com.oracle.nosql.spring.data.repository.NosqlRepository;
 
/* The Users is the entity class and Long is the data type of the primary key in the Users class.
   This interface provides methods that return iterable instances of the Users class. */
 
public interface UsersRepository extends NosqlRepository<Users, Long> {
    /* Search the Users table by the last name and return an iterable instance of the Users class.*/
    Iterable<Users> findByLastName(String lastname);
}

In the application, you select the row from the Users table with the last name as required and print the values to the output from the object.

@Autowired
private UsersRepository repo;
 
System.out.println("\nfindBylastName: Willard");
 
/* Use queries to find by the last Name. Search the Users table by the last name and return an iterable instance of the Users class.*/
allusers = repo.findByLastName("Willard");
 
for (Users s: allusers) {
    System.out.println(" User: " + s);
}

Run the program to display the output.

findBylastName: Willard

User: Users{id=2, firstName=Angela, lastName=Willard}