Using Tables in Java

Learn how to create, update, and delete tables from your Java application.

Topics

• About the Oracle NoSQL Database Java SDK

• About Compartments

• Obtaining a NoSQL Handle

• Creating Tables and Indexes

• Adding Data

• Reading Data

• Using Queries

• Deleting Data

• Modifying Tables

• Dropping Tables and Indexes

• Handling Errors

About the Oracle NoSQL Database Java SDK

Learn about the Oracle NoSQL Database Java SDK.

The Oracle NoSQL Database Java Driver contains the jar files that enable an application to communicate with the on-premises Oracle NoSQL Database or the Oracle NoSQL Database Cloud Service or the Oracle NoSQL Database Cloud Simulator.

Download and unpack the Java SDK from Oracle Technology Network. Extracting the Oracle NoSQL Database Java SDK provides you with all the Java classes, methods, interfaces, examples, and documentation. See Java API Reference Guide.

About Compartments

Learn how to specify the compartment while creating and working with Oracle NoSQL Database Cloud Service tables using the Oracle NoSQL Database Java Driver.

Oracle NoSQL Database Cloud Service tables are created in a compartment and are scoped to that compartment. When authenticated as a specific user, your tables are managed in the root compartment of your tenancy unless otherwise specified. Organizing tables into different compartments will help with respect to organization and security.

If you have been authenticated using an instance principal (accessing the service from an OCI compute instance), you must specify a compartment using its id (OCID), as there is no default in this case. See Calling Service From an Instance in Oracle Cloud Infrastructure Documentation.

There are several ways to specify a compartment in your application code:

1. Use a default compartment in NoSQLHandleConfig so that it applies to all the operations using the handle. See Obtaining a NoSQL Handle for an example.
2. Use the compartment name or id (OCID) in each request in addition to the table name. This overrides any default compartment.
   For example:

   GetRequest getReq = new GetRequest().setTableName("mytable")
                                       .setCompartment("mycompartment");

3. Use the compartment name as a prefix on the table name. This overrides any default compartment as well as a compartment specified using API.
   For example:

   GetRequest getReq = new GetRequest().setTableName("mycompartment:mytable");

When using a named compartment, the name can be the simple name of a top-level compartment or a path to a nested compartment. In the latter case, the path is a "." (dot) separated path.

Note:

While specifying the path to a nested compartment, do not include the top-level compartment's name in the path as that is inferred from the tenancy.

Obtaining a NoSQL Handle

Learn how to access tables using the Oracle NoSQL Database Java Driver.

Start developing your application by creating a NoSQL Handle. Use the NoSQLHandle to access the tables and execute all operations. To create a connection represented by a NoSQLHandle, obtain a handle using the NoSQLHandleFactory.createNoSQLHandle method and the NoSQLHandleConfig class. The NoSQLHandleConfig class allows an application to specify the handle configuration. See the Java API Reference guide to learn more.

Obtain a NoSQL Handle

Use the following code to obtain a NoSQL handle:

/* Configure a handle for the desired Region and AuthorizationProvider.
 * By default this SignatureProvider constructor reads authorization
 * information from ~/.oci/config and uses the default user profile and
 * private key for request signing. Additional SignatureProvider
 * constructors are available if a config file is not available or
 * desirable. 
 */
AuthorizationProvider ap = new SignatureProvider();

/* Use the us-ashburn-1 region */
NoSQLHandleConfig config = new NoSQLHandleConfig(Region.US - ASHBURN - 1, ap);
config.setAuthorizationProvider(ap);

/* Sets a default compartment for all requests from this handle. This 
 * may be overridden in individual requests or by using a
 * compartment-name prefixed table name.
 */
config.setDefaultCompartment("mycompartment");

// Open the handle
NoSQLHandle handle = NoSQLHandleFactory.createNoSQLHandle(config);

// Use the handle to execute operations

A handle has memory and network resources associated with it. Use the NoSQLHandle.close method to free up the resources when your application is done using the handle.

To minimize network activity and resource allocation and deallocation overheads, it's best to avoid creating and closing handles repeatedly. For example, creating and closing a handle around each operation would result in poor application performance. A handle permits concurrent operations, so a single handle is sufficient to access tables in a multi-threaded application. The creation of multiple handles incurs additional resource overheads without providing any performance benefit.

Creating Tables and Indexes

Learn how to create tables and indexes.

Creating a table is the first step of developing your application. You use the TableRequest class and methods to execute all DDL statements, such as, creating, modifying, and dropping tables. You also set table limits using the TableRequest.setTableLimits method.

Before creating a table, see:

• Supported Data Types, and

• Oracle NoSQL Database Cloud Service Limits

The TableRequest class lets you pass a DDL statement to the TableRequest.setStatement method. Examples of DDL statements are:

/* Create a new table called users */
CREATE IF NOT EXISTS users(id INTEGER,
 name STRING,
 PRIMARY KEY(id));

/* Create a new table called users and set the TTL value to 4 days */
CREATE IF NOT EXISTS users(id INTEGER,
 name STRING,
 PRIMARY KEY(id))
USING TTL 4 days;


/* Create a new index called nameIdx on the name field in the users table */
CREATE INDEX IF NOT EXISTS nameIdx ON users(name);

Note:

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To create a table and index using the TableRequest and its methods:

/* Create a simple table with an integer key and a single json data
 * field  and set your desired table capacity.
 * Set the table TTL value to 3 days.
 */
String createTableDDL = "CREATE TABLE IF NOT EXISTS users " +
 "(id INTEGER, name STRING, " +
 "PRIMARY KEY(id)) USING TTL 3 days";

TableLimits limits = new TableLimits(200, 100, 5);
TableRequest treq = new TableRequest().setStatement(createTableDDL)
 .setTableLimits(limits);

// start the asynchronous operation
TableResult tres = handle.tableRequest(treq);

// wait for completion of the operation
tres.waitForCompletion(handle,
 60000, // wait for 60 sec
 1000); // delay in ms for poll
 

// Create an index called nameIdx on the name field in the users table.
treq = new TableRequest().setStatement("CREATE INDEX 
  IF NOT EXISTS nameIdx ON users(name)
  ");

// start the asynchronous operation
  handle.tableRequest(treq);

// wait for completion of the operation
  tres.waitForCompletion(handle,
   60000, // wait for 60 sec
   1000); // delay in ms for poll

Adding Data

Add rows to your table.

When you store data in table rows, your application can easily retrieve, add to, or delete information from a table.

The PutRequest class represents the input to a NoSQLHandle.put(oracle.nosql.driver.ops.PutRequest) operation. This request can be used to perform unconditional and conditional puts to:

• Overwrite any existing row. Overwrite is the default functionality.
• Succeed only if the row does not exist. Use the PutRequest.Option.IfAbsent method in this case.
• Succeed only if the row exists. Use the PutRequest.Option.IfPresent method in this case.
• Succeed only if the row exists and the version matches a specific version. Use the PutRequest.Option.IfVersion method for this case and the setMatchVersion(oracle.nosql.driver.Version) method to specify the version to match.

Note:

First, connect your client driver to Oracle NoSQL Database Cloud Service to get a handle and then complete other steps. This topic omits the steps for connecting your client driver and creating a table. If you do not yet have a table, see Creating Tables and Indexes.

The following example assumes that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To add rows to your table:

/* use the MapValue class and input the contents of a new row */
MapValue value = new MapValue().put("id", 1).put("name", "myname");

/* create the PutRequest, setting the required value and table name */
PutRequest putRequest = new PutRequest().setValue(value)
                                        .setTableName("users");

/* use the handle to execute the PUT request
 * on success, PutResult.getVersion() returns a non-null value
 */
PutResult putRes = handle.put(putRequest);
if (putRes.getVersion() != null) {
  // success
} else {
 // failure
}

You can perform a sequence of PutRequest operations on a table that share the shard key using the WriteMultipleRequest class. If the operation is successful, the WriteMultipleResult.getSuccess() method returns true.

See the Java API Reference Guide for more information about the APIs.

You can also add JSON data to your table. You can either convert JSON data into a record for a fixed schema table or you can insert JSON data into a column whose data type is of type JSON. See Adding JSON Data.

Adding JSON Data

Learn how to add JSON data to a fixed schema table.

Note:

First, connect your client driver to Oracle NoSQL Database Cloud Service to get a handle and then complete other steps. This topic omits the steps for connecting your client driver and creating a table. If you do not yet have a table, see Creating Tables and Indexes.

Table rows are added to the table by using APIs which let you individually specify each table field value. For example, you use the MapValue.put() method to fill in each field value for a row, before inserting the entire row into the table.

The PutRequest class also provides the setValueFromJson method which takes a JSON string and uses that to populate a row to insert into the table. The JSON string should specify field names that correspond to the table field names.

Note:

The following example assumes that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To add JSON data to your table:

/* Construct a simple row, specifying the values for each 
 * field. The value for the row is this: 
 * 
 * { 
 *   "cookie_id": 123, 
 *   "audience_data": { 
 *     "ipaddr": "10.0.00.xxx", 
 *     "audience_segment": { 
 *        "sports_lover": "2018-11-30", 
 *        "book_reader": "2018-12-01" 
 *      } 
 *   } 
 * } 
 */
MapValue segments = new MapValue()
    .put("sports_lover", new TimestampValue("2018-11-30"))
    .put("book_reader", new TimestampValue("2018-12-01"));
MapValue value = new MapValue()
    .put("cookie_id", 123) // fill in cookie_id field
    .put("ipaddr", "10.0.00.xxx")
    .put("audience_segment", segments); 
PutRequest putRequest = new PutRequest()
    .setValue(value)
    .setTableName(tableName);
PutResult putRes = handle.put(putRequest);

The same row can be inserted into the table as a JSON string:

/* Construct a simple row in JSON */  
String jsonString = "{\"cookie_id\":123,\"ipaddr\":\"10.0.00.xxx\",
                      \"audience_segment\":{\"sports_lover\":\"2018-11-30\",
                      \"book_reader\":\"2018-12-01\"}}";   
PutRequest putRequest = new PutRequest()
      .setValueFromJson(jsonString, null) // no options
      .setTableName(tableName);  
PutResult putRes = handle.put(putRequest);

Reading Data

Learn how to read data from your table.

You can read data from your application by using the NoSQLHandle.get() method. This method allows you to retrieve a record based on a single primary key value, or by using queries. The GetRequest class provides a simple and powerful way to read data, while queries can be used for more complex read requests.

Note:

First, connect your client driver to Oracle NoSQL Database Cloud Service to get a handle and then complete other steps. This topic omits the steps for connecting your client driver and creating a table. If you do not yet have a table, see Creating Tables and Indexes.

To read data from a table, specify the target table and target key using the GetRequest class and use NoSQLHandle.get() to execute your request. The result of the operation is available in GetResult.

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To read data from your table:

/* GET the row, first create the row key */
MapValue key = new MapValue().put("id", 1);
GetRequest getRequest = new GetRequest().setKey(key)
                                        .setTableName("users");
GetResult getRes = handle.get(getRequest);

/* on success, GetResult.getValue() returns a non-null value */
if (getRes.getValue() != null) {
  // success
} else {
  // failure
}

Note:

By default, all read operations are eventually consistent. You can change the default Consistency for a NoSQLHandle instance by using the NoSQLHandleConfig.setConsistency(oracle.nosql.driver.Consistency) and GetRequest.setConsistency() methods.

See the Java API Reference Guide for more information about the GET (read) APIs.

Using Queries

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

Oracle NoSQL Database Cloud Service provides a rich query language to read and update data. See SQL Reference for NoSQL Database for a full description of the query language.

To execute your query, you use the NoSQLHandle.query() API. See the Java API Reference Guide for more information about this API.

Note:

The following examples consider that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

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. See NoSQLHandle.prepare in the Java API Reference Guide for more information.

• You can set important query attributes, such as the usable amount of resources, or the read consistency used by the read operations, using the QueryRequest class. See QueryRequest in the Java API Reference Guide for more information.

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());

Deleting Data

Learn how to delete rows from your table.

After you insert or load data into a table, you can delete the table rows when they are no longer required.

Note:

First, connect your client driver to Oracle NoSQL Database Cloud Service to get a handle and then complete other steps. This topic omits the steps for connecting your client driver and creating a table. If you do not yet have a table, see Creating Tables and Indexes.

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To delete a row from a table:

/* identify the row to delete */
MapValue delKey = new MapValue().put("id", 2);

/* construct the DeleteRequest */
DeleteRequest delRequest = new DeleteRequest().setKey(delKey)
                                              .setTableName("users");

/* Use the NoSQL handle to execute the delete request */
DeleteResult del = handle.delete(delRequest);

/* on success DeleteResult.getSuccess() returns true */
if (del.getSuccess()) {
  // success, row was deleted
} else {
  // failure, row either did not exist or conditional delete failed
}

You can perform a sequence of DeleteRequest operations on a table using the MultiDeleteRequest class.

See the Java API Reference Guide for more information about the APIs.

Modifying Tables

Learn how to modify tables.

You modify a table to:

• Add new fields to an existing table

• Delete currently existing fields in a table

• To change the default TTL value

• Modify table limits

Note:

First, connect your client driver to Oracle NoSQL Database Cloud Service to get a handle and then complete other steps. This topic omits the steps for connecting your client driver and creating a table. If you do not yet have a table, see Creating Tables and Indexes.

Examples of DDL statements are:

/* Add a new field to the table */
ALTER TABLE users (ADD age INTEGER);

/* Drop an existing field from the table */
ALTER TABLE users (DROP age);

/* Modify the default TTL value*/
ALTER TABLE users USING TTL 4 days;

Note:

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

When altering a table, you may also use the TableRequests.setTableLimits method to modify table limits. For example:

/* Alter the users table to modify the TTL value to 4 days. 
 * When modifying the table schema or other table state you cannot also
 * modify the table limits. These must be independent operations.
 */
String alterTableDDL = "ALTER TABLE users " +
                       "USING TTL 4 days";
TableRequest treq = new TableRequest().setStatement(alterTableDDL);

/* start the operation, it is asynchronous */
TableResult tres = handle.tableRequest(treq);

/* wait for completion of the operation */
tres.waitForCompletion(handle,
                        60000,  /* wait for 60 sec */
                        1000);  /* delay in ms for poll */ 

Dropping Tables and Indexes

Learn how to delete a table or index that you have created in Oracle NoSQL Database Cloud Service.

To drop a table in Oracle NoSQL Database Cloud Service, you must have the NOSQL_TABLE_DROP permission. See Details for Verb + Resource-Type Combinations to learn about different permissions.

To drop a table or index, use the DROP TABLE or DROP INDEX DDL statements. For example:

/* Drop the table named users */
DROP TABLE users;

/* Drop the index called nameIndex on the table users */
DROP INDEX IF EXISTS nameIndex ON users;

Note:

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To drop a table using the TableRequests.setStatement method:

/* create the TableRequest to drop the users table */
TableRequest tableRequest = new TableRequest().setStatement("drop table users");

/* start the operation, it is asynchronous */
TableResult tres = handle.tableRequest(tableRequest);

/* wait for completion of the operation */
tres.waitForCompletion(handle,
                        60000,  /* wait for 60 sec */
                        1000);  /* delay in ms for poll */

Handling Errors

Learn how to handle errors and exceptions.

Java errors are thrown as exceptions when you build or run your application. The NoSQlException class is the base for most exceptions thrown by the driver. However, the driver throws exceptions directly for some classes, such as IllegalArgumentException and NullPointerException.

In general, NoSQL exception instances are split into two broad categories:

• Exceptions that may be retried with the expectation that they may succeed on retry.

  These exceptions are instances of the RetryableException class. These exceptions usually indicate resource consumption violations such as ThrottlingException.

• Exceptions that will fail even after retry.

  Examples of exceptions that should not be retried are IllegalArgumentException, TableNotFoundException, and any other exception indicating a syntactic or semantic error.

See the Java API Reference guide to learn more about these exceptions and how to handle them.