Using Tables in Java
Learn how to create, update, and delete tables from your Java application.
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 or the Oracle NoSQL Database Cloud Service or the Oracle NoSQL Database Cloud Simulator.
Download the SDK for Java from GitHub. The Oracle NoSQL SDK for Java provides you with all the Java classes, methods, interfaces and examples. Documentation is available as javadoc in GitHub or from 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.
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.
- 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. - 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");
- 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");
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.
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.
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 inNoSQLHandleConfig
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
Related Topics
Adding Data
Add rows to your 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 thesetMatchVersion(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.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
}
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 inNoSQLHandleConfig
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.
/* 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.
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
.
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 theNoSQLHandleConfig.setConsistency(oracle.nosql.driver.Consistency)
and GetRequest.setConsistency()
methods.
GET
(read) APIs.
Using Queries
Learn about some aspects of using queries to your application in Oracle NoSQL Database Cloud Service.
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 inNoSQLHandleConfig
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.
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. SeeQueryRequest
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.
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.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./* 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
}
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./* 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 inNoSQLHandleConfig
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.
NOSQL_TABLE_DROP
permission. See Details for Verb + Resource-Type Combinations to learn about different permissions.
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 inNoSQLHandleConfig
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.
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 asThrottlingException
. -
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.