3 Using SODA for Java
How to access SODA for Java is described, as well as how to use it to perform create, read (retrieve), update, and delete (CRUD) operations on collections.
(CRUD operations are also called “read and write operations” in this document.)
Getting Started with SODA for Java
How to access SODA for Java is described, as well as how to use it to create a database collection, insert a document into a collection, and retrieve a document from a collection.
Note:
Don’t worry if not everything in this topic is clear to you on first reading. The necessary concepts are developed in detail in other topics. This topic should give you an idea of what is involved overall in using SODA.
Follow these steps to get started with SODA for Java:
-
Ensure that all of the prerequisites have been met for using SODA for Java. See SODA for Java Prerequisites.
-
Identify the database schema (user account) used to store collections, and grant database role
SODA_APP
to that schema. (Replace placeholderuser
here by a real account name.)GRANT SODA_APP TO user;
-
Place all required jar files and file
testSoda.java
(which contains the text in Example 3-1) into a directory. -
In
testSoda.java
:-
Use a host name, port number, and service name that are appropriate for your Oracle Database instance. (Replace placeholders
host_name
,port_number
, andservice_name
, respectively.) -
Use the name and password for the database schema (user account) identified in step 2. (Replace placeholders
user
andpassword
.)
-
-
Use the
cd
command to go to the directory that contains the jar files and filetestSoda.java
. -
Execute these commands:
javac -classpath "*" testSoda.java java -classpath "*:." testSoda
Instead of the second of these commands, you can optionally use the following command. It has the additional effect of dropping the collection, cleaning up the database table that is used to store the collection and its metadata.
java -classpath "*:." testSoda drop
Using argument
drop
here has the effect of invoking methoddrop()
, which is the proper way to drop a collection.Caution:
Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.
To work with SODA for Java you must first open a JDBC connection. This is illustrated in Example 3-1. For details of how to open a JDBC connection, see Oracle Database JDBC Developer’s Guide.
Example 3-1 testSoda.java
In this example, replace placeholders host_name
, port_number
, service_name
, user
, and password
by a host name, port number, service name, database schema (user) name, and password appropriate for your database instance.
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.soda.rdbms.OracleRDBMSClient;
import oracle.soda.OracleDatabase;
import oracle.soda.OracleCursor;
import oracle.soda.OracleCollection;
import oracle.soda.OracleDocument;
import oracle.soda.OracleException;
import java.util.Properties;
public class testSoda
{
public static void main(String[] arg)
{
// Set the JDBC connection string, using information appropriate for your Oracle Database instance.
// (Be sure to replace placeholders host_name, port_number, and service_name in the string.)
String url = "jdbc:oracle:thin:@//host_name:port_number/service_name";
// Set properties user and password.
// (Be sure to replace placeholders user and password with appropriate string values.)
Properties props = new Properties();
props.setProperty("user", user);
props.setProperty("password", password);
Connection conn = null;
try
{
// Get a JDBC connection to an Oracle instance.
conn = DriverManager.getConnection(url, props);
// Enable JDBC implicit statement caching
conn.setImplicitCachingEnabled(true);
conn.setStatementCacheSize(50);
// Get an OracleRDBMSClient - starting point of SODA for Java application.
OracleRDBMSClient cl = new OracleRDBMSClient();
// Get a database.
OracleDatabase db = cl.getDatabase(conn);
// Create a collection with the name "MyJSONCollection".
// This creates a database table, also named "MyJSONCollection", to store the collection.
OracleCollection col = db.admin().createCollection("MyJSONCollection");
// Create a JSON document.
OracleDocument doc =
db.createDocumentFromString("{ \"name\" : \"Alexander\" }");
// Insert the document into a collection.
col.insert(doc);
// Find all documents in the collection.
OracleCursor c = null;
try
{
c = col.find().getCursor();
OracleDocument resultDoc;
while (c.hasNext())
{
// Get the next document.
resultDoc = c.next();
// Print document components
System.out.println ("Key: " + resultDoc.getKey());
System.out.println ("Content: " + resultDoc.getContentAsString());
System.out.println ("Version: " + resultDoc.getVersion());
System.out.println ("Last modified: " + resultDoc.getLastModified());
System.out.println ("Created on: " + resultDoc.getCreatedOn());
System.out.println ("Media: " + resultDoc.getMediaType());
System.out.println ("\n");
}
}
finally
{
// IMPORTANT: YOU MUST CLOSE THE CURSOR TO RELEASE RESOURCES.
if (c != null) c.close();
}
// Drop the collection, deleting the table underlying it and the collection metadata.
if (arg.length > 0 && arg[0].equals("drop")) {
col.admin().drop();
System.out.println ("\nCollection dropped");
}
}
// SODA for Java throws a checked OracleException
catch (OracleException e) { e.printStackTrace(); }
catch (Exception e) { e.printStackTrace(); }
finally
{
try { if (conn != null) conn.close(); }
catch (Exception e) { }
}
}
}
Creating a Document Collection with SODA for Java
How to use SODA for Java to create a new document collection is explained.
In your Java application, first create an OracleRDBMSClient
object, which is the starting point for any Java application working with SODA for Java:
OracleRDBMSClient myClient = new OracleRDBMSClient();
Caution:
An OracleRDBMSClient
object is thread-safe. Other SODA for Java interfaces are not thread-safe, however — do not share them among multiple threads.
Next, pass the JDBC connection (jdbcConnection
, here) to method OracleClient.getDatabase()
, to obtain an OracleDatabase
object (db
, here):
OracleDatabase db = myClient.getDatabase(jdbcConnection);
Note:
Oracle recommends that you enable implicit statement caching for the JDBC connection that you pass to SODA. This can improve the performance of read and write operations. The underlying implementation of read and write operations generates JDBC prepared statements.
If you do not enable implicit caching then each time a read or write operation is created a new JDBC prepared statement is constructed. With implicit caching enabled, a new JDBC prepared statement is created only if it is not already in the cache.
See also: Oracle Database JDBC Developer’s Guide and Oracle Universal Connection Pool Developer’s Guide
Collection creation methods are available on interface OracleDatabaseAdmin
. To access this interface, invoke method admin()
on an OracleDatabase
object (db
, here):
OracleDatabaseAdmin dbAdmin = db.admin();
You can then create a collection — an OracleCollection
object (col
, here), by invoking method createCollection()
on the object returned from admin()
, passing it the collection name (myCollection
, here) as a string:
OracleCollection col = dbAdmin.createCollection("myCollection");
Method createCollection()
without a metadata argument creates the following in Oracle Database:
-
Persistent default collection metadata.
-
A table for storing the collection, in the schema with which the input JDBC connection is configured.
Note:
If the table name used by method
createCollection()
names an existing table in the schema with which the JDBC connection is configured, then the method tries to map that table to the collection. This behavior includes the default case, where the table name is derived from the collection name.
The default collection metadata has the following characteristics.
-
Each document in the collection has these document components:
-
Key
-
Content
-
Creation timestamp
-
Last-modified timestamp
-
Version
-
-
The collection can store only JSON documents.
-
Document keys are automatically generated for documents that you add to the collection.
The default collection configuration is recommended in most cases, but collections are highly configurable. When you create a collection you can specify things such as the following:
-
Storage details, such as the name of the table that stores the collection and the names and data types of its columns.
-
The presence or absence of columns for creation timestamp, last-modified timestamp, and version.
-
Whether the collection can store only JSON documents.
-
Methods of document key generation, and whether document keys are client-assigned or generated automatically.
-
Methods of version generation.
This configurability also lets you map a new collection to an existing table.
To configure a collection in a nondefault way, create a JSON OracleDocument
instance of custom collection metadata (collectionMetadata
, here) and pass it, along with the collection-name string (myCollection
, here) to method createCollection()
:
OracleCollection col2 = dbAdmin.createCollection("myCollection", collectionMetadata);
To build and generate this OracleDocument
instance easily, you can use OracleRDBMSMetadataBuilder
.
If you do not care about the details of collection storage and configuration, then use method createCollection(myCollection)
, as in Example 3-2.
You can search or change a collection only if it is open. A newly created collection is open for the life of your session.
When invoking a createCollection()
method, if a collection with the same name already exists then it is simply opened and its object is returned. If custom metadata is passed to the method and it does not match that of the existing collection then the collection is not opened and an error is raised. (To match, all metadata fields must have the same values.)
Note:
Unless otherwise stated, the remainder of this documentation assumes that a collection has the default configuration.
See Also:
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about the default naming of a collection table
Opening an Existing Document Collection with SODA for Java
You can use OracleDatabase
method openCollection()
to open an existing document collection or to test whether a given name names an existing collection.
Example 3-2 Opening an Existing Document Collection
This example opens the collection named myCollectionName
and returns the OracleCollection
object that represents this collection. If the value returned is null
then there is no existing collection named myCollectionName
.
OracleCollection col
= db.openCollection("myCollectionName");
Checking Whether a Given Collection Exists with SODA for Java
You can use OracleDatabase
method openCollection()
to check for the existence of a given collection. It returns null
if the collection argument does not name an existing collection; otherwise, it opens the collection having that name.
In Example 3-2, if myCollectionName
does not name an existing collection then ocol
is assigned the value null
.
Discovering Existing Collections with SODA for Java
You can use OracleDatabaseAdmin
method
getCollectionNames()
to discover existing collections.
Example 3-3 Printing the Names of All Existing Collections
This example prints the names of all existing collections. It uses method
getCollectionNames()
with the simplest signature, which accepts
no arguments.
List<String> names = db.admin().getCollectionNames();
for (String name : names)
System.out.println ("Collection name: " + name);
Dropping a Document Collection with SODA for Java
You use OracleCollectionAdmin
method
drop()
to drop a document collection.
Caution:
Do not use SQL to drop the database table that underlies a collection. Dropping a collection involves more than just dropping its database table. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the table underlying a collection does not also drop the collection metadata.
Note:
Day-to-day use of a typical application that makes use of SODA does not require that you drop and re-create collections. But if you need to do that for any reason then this guideline applies.
Do not drop a collection and then re-create it with different metadata if there is any application running that uses the collection in any way. Shut down any such applications before re-creating the collection, so that all live SODA objects are released.
There is no problem just dropping a collection. Any read or write operation on a dropped collection raises an error. And there is no problem dropping a collection and then re-creating it with the same metadata. But if you re-create a collection with different metadata, and if there are any live applications using SODA objects, then there is a risk that a stale collection is accessed, and no error is raised in this case.
In SODA implementations that allow collection metadata caching, such as SODA for Java, this risk is increased if such caching is enabled. In that case, a (shared or local) cache can return an entry for a stale collection object even if the collection has been dropped.
Note:
Commit all writes to
a collection before using method drop()
. For
drop()
to succeed, all uncommitted writes to the collection
must first be committed. Otherwise, an exception is raised.
Example 3-4 Dropping a Document Collection
This example drops collection
col
.
col.admin().drop();
Creating Documents with SODA for Java
Creation of documents by SODA for Java is described.
SODA for Java represents a document using Java interface OracleDocument
. This interface is designed primarily to represent JSON documents, but it also supports other content types. An OracleDocument
object is a carrier of document content and other document components, such as the document key.
To create JSON content for an OracleDocument
instance, you can use your favorite package — for example, JSR353, the Java API for JSON processing.
Here is an example of a simple JSON document:
{ "name" : "Alexander",
"address" : "1234 Main Street",
"city" : "Anytown",
"state" : "CA",
"zip" : "12345"
}
Note:
In SODA, JSON content must conform to RFC 4627. In addition, in SODA for Java, the encoding of JSON content must be either UTF-8 or UTF-16 (big endian (BE) or little endian (LE)). Although RFC 4627 also allows UTF-32 (BE and LE) encodings, SODA for Java does not support them.
To create an OracleDocument
instance from content that is represented as a byte array or a String
instance, use the following methods (which OracleDatabase
inherits from OracleDocumentFactory
), respectively:
-
createDocumentFromByteArray()
-
createDocumentFromString()
A document has these components:
-
Key
-
Content
-
Creation time stamp
-
Last-modified time stamp
-
Version
-
Media type (
"application/json"
for JSON documents)
When you create a document by invoking method
createDocumentFromString()
or
createDocumentFromByteArray()
:
-
You might need to provide the document key as a method argument.
In a collection, each document must have a key. You must provide the key when you create the document only if you expect to insert the document into a collection that does not automatically generate keys for inserted documents. By default, collections are configured to automatically generate document keys.
-
You can provide the document content as a method argument (the
content
parameter is required, but its value can benull
). -
The method sets the values of the creation time stamp, last-modified time stamp, and version to
null
.
Methods createDocumentFromString()
and
createDocumentFromByteArray()
each have multiple variants:
-
The simplest variant accepts only document content. The media type defaults to
"application/json"
, and the other components default tonull
. This variant is useful for creating documents for insertion into collections that automatically generate document keys. -
Another variant accepts both document key and document content. The media type defaults to
"application/json"
, and the other components default tonull
. This variant is useful for creating documents for insertion into collections that have client-assigned document keys. -
The most flexible (and most verbose) variant accepts key, content, and content type. Because it lets you specify content type, this variant is useful for creating non-JSON documents.
Example 3-5 creates an OracleDocument
instance with content only. The media type defaults to "application/json"
, and the other document components default to null
.
Example 3-6 creates an OracleDocument
instance with document key and content. The media type defaults to "application/json"
, and the other document components default to null
.
You write documents to collections using SODA for Java write operations, and you read documents from collections using SODA for Java read operations.
See Also:
-
OracleDocumentFactory
Javadoc for more information about methodscreateDocumentFromString()
andcreateDocumentFromByteArray()
-
OracleDocument
Javadoc for information about getter methods, which you use to access document components -
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an overview of SODA documents
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for restrictions that apply for SODA documents
Example 3-5 Creating a Document with JSON Content
OracleDocument doc =
odb.createDocumentFromString("{ \"name\" : \"Alexander\"}");
// Get the content
String content = doc.getContentAsString();
// Get the content type (it is "application/json")
String contentType = doc.getContentType();
Example 3-6 Creating a Document with Document Key and JSON Content
OracleDocument doc
= odb.createDocumentFromString("myKey", "{ \"name\" : \"Alexander\"}");
Inserting Documents into Collections with SODA for Java
To insert a document into a collection, you invoke OracleCollection
method insert(OracleDocument)
or insertAndGet(OracleDocument)
. These methods create document keys automatically, unless the collection is configured with client-assigned keys and the input document provides the key.
Method insert(OracleDocument)
only inserts the document into the collection. Method insertAndGet(OracleDocument)
also returns a result document, which contains the document key and any other generated document components (except the content).
Both methods automatically set the values of the creation time stamp, last-modified time stamp, and version (if the collection is configured to include these components and to generate the version automatically, as is the case by default).
Note:
If the collection is configured with client-assigned document keys (which is not the default case), and the input document provides a key that identifies an existing document in the collection, then these methods throw an exception. If you want the input document to replace the existing document instead of causing an exception, see Saving Documents into Collections with SODA for Java.
Example 3-7 creates a document and inserts it into a collection using method insert()
.
Example 3-8 creates a document, inserts it into a collection using method insertAndGet()
, and then gets each of the generated components from the result document (which contains them).
To efficiently insert a large number of documents into a collection, invoke OracleCollection
method insert(Iterator<OracleDocument>)
or insertAndGet(Iterator<OracleDocument>)
. These methods are analogous to insert(OracleDocument)
and insertAndGet(OracleDocument)
, but instead of handling a single document, they handle multiple documents. Parameter Iterator<oracleDocument>
is an iterator over multiple input documents.
Method insertAndGet(Iterator<OracleDocument>)
returns a list of result documents — one OracleDocument
instance for each input document. Each such result document contains the document key and any other generated document components (except the content). The order of the result documents corresponds to the order of input documents, allowing correlation of result and input documents.
There is a variant of method insertAndGet()
that accepts an optional
second argument, options
, whose value is a Java Map
.
You can use argument options
to provide a SQL hint,
to turn real-time SQL monitoring of queries on and off. Use method
put()
to add key "hint"
with value
"MONITOR"
to the map argument. The hint is passed down to
the SQL code that underlies SODA.
The string value for key "hint"
uses the SQL hint
syntax (that is, the hint text, without the enclosing SQL comment syntax
/*+
...*/
). Use only hint
MONITOR
(turn on monitoring) or NO_MONITOR
(turn off monitoring).
(You can use this to pass any SQL hints, but
MONITOR
and NO_MONITOR
are the useful ones
for SODA, and an inappropriate hint can cause the optimizer to produce a
suboptimal query plan.)
See Also:
-
OracleCollection
Javadoc for more information about the insertion methods:insert(OracleDocument)
insert(Iterator<OracleDocument>)
insertAndGet(OracleDocument)
insertAndGet(OracleDocument document, Map<String, ?> options)
insertAndGet(Iterator<OracleDocument>)
insertAndGet(Iterator<OracleDocument> documents, Map<String, ?> options)
-
Monitoring Database Operations in Oracle Database SQL Tuning Guide for complete information about monitoring database operations
-
MONITOR and NO_MONITOR Hints in Oracle Database SQL Tuning Guide for information about the syntax and behavior of SQL hints
MONITOR
andNO_MONITOR
Example 3-7 Inserting a Document into a Collection
OracleDocument doc =
db.createDocumentFromString("{ \"name\" : \"Alexander\"}");
col.insert(doc);
Example 3-8 Inserting a Document into a Collection and Getting the Result Document
OracleDocument doc =
db.createDocumentFromString("{ \"name\" : \"Alexander\"}");
OracleDocument insertedDoc = col.insertAndGet(doc);
// Get the generated document key
String key = insertedDoc.getKey();
// Get the generated creation timestamp
String createdOn = insertedDoc.getCreatedOn();
// Get the generated last-modified timestamp
String lastModified = insertedDoc.getLastModified();
// Get the generated version
String version = insertedDoc.getVersion();
Saving Documents into Collections with SODA for Java
You use OracleCollection
methods save(OracleDocument)
and saveAndGet(OracleDocument
) to save documents into collections.
These methods are similar to methods insert(OracleDocument)
and
insertAndGet(OracleDocument)
except that, if the collection is configured
with client-assigned document keys, and the input document provides a key that already
identifies a document in the collection, then the input document replaces the
existing document. (Methods insert(OracleDocument)
and
insertAndGet(OracleDocument)
throw an exception in that case.)
Note:
By default, collections are configured with automatically generated document keys. Therefore, for a default collection, methods save(OracleDocument)
and saveAndGet(OracleDocument
) are equivalent to methods insert(OracleDocument)
and insertAndGet(OracleDocument
), respectively.
There is a variant of method saveAndGet()
that accepts an optional second
argument, options
, whose value is a Java Map
.
You can use argument options
to provide a SQL hint,
to turn real-time SQL monitoring of queries on and off. Use method
put()
to add key "hint"
with value
"MONITOR"
to the map argument. The hint is passed down to
the SQL code that underlies SODA.
The string value for key "hint"
uses the SQL hint
syntax (that is, the hint text, without the enclosing SQL comment syntax
/*+
...*/
). Use only hint
MONITOR
(turn on monitoring) or NO_MONITOR
(turn off monitoring).
(You can use this to pass any SQL hints, but
MONITOR
and NO_MONITOR
are the useful ones
for SODA, and an inappropriate hint can cause the optimizer to produce a
suboptimal query plan.)
See Also:
-
OracleCollection
Javadoc for more information about methodssave(OracleDocument)
,saveAndGet(OracleDocument)
, andsaveAndGet(OracleDocument document, Map<String, ?> options)
-
Monitoring Database Operations in Oracle Database SQL Tuning Guide for complete information about monitoring database operations
-
MONITOR and NO_MONITOR Hints in Oracle Database SQL Tuning Guide for information about the syntax and behavior of SQL hints
MONITOR
andNO_MONITOR
Example 3-9 Saving a Document into a Collection
This example saves a document into a collection that is configured with client-assigned document keys, using method saveAndGet()
. It then gets the key and the generated document components (except the content) from the result document (which contains them).
OracleRDBMSClient cl = new OracleRDBMSClient();
OracleDatabase db = ...
// Configures the collection with client-assigned document keys
OracleDocument collMeta =
cl.createMetadataBuilder().keyColumnAssignmentMethod("client").build();
OracleCollection clientKeysColl = db.createCollection("collectionName",
collMeta);
// For a collection configured with client-assigned document keys,
// you must provide the key for the input document.
OracleDocument cKeyDoc =
db.createDocumentFromString("myKey", "{ \"name\" : \"Alexander\"}");
// If key "myKey" already identifies a document in the collection
// then cKeyDoc replaces the existing doc.
OracleDocument savedDoc = clientKeysColl.saveAndGet(cKeyDoc);
// Get document key ("myKey")
String key = savedDoc.getKey();
// Get the generated creation timestamp
String createdOn = savedDoc.getCreatedOn();
// Get the generated last-modified timestamp
String lastModified = savedDoc.getLastModified();
// Get the generated version
String version = savedDoc.getVersion();
SODA for Java Read and Write Operations
The primary way you specify read and write operations (other than insert and save) is to chain together OracleOperationBuilder
methods.
OracleOperationBuilder
provides the following nonterminal
methods, which you can chain together to specify a read or write operation:
key()
, keyLike()
, keys()
,
filter()
, version()
, skip()
,
limit()
, and headerOnly()
.
These are called nonterminal methods because they
return the same OracleOperationBuilder
object on which they are
invoked, which allows them to be chained together. Nonterminal methods let you specify
parts of an operation; they do not create or execute an operation.
OracleOperationBuilder
also provides terminal methods. A
terminal method always appears at the end of a method
chain, and it creates and executes the operation.
The terminal methods for read operations are
getCursor()
, getOne()
, and
count()
. The terminal methods for write operations are
replaceOne()
, replaceOneAndGet()
, and
remove()
.
Note:
If you use
OracleCursor
method next()
or
OracleOperationBuilder
method getOne()
, and if
the underlying document is larger than 2 gigabytes, then an exception is
thrown.
Unless the Javadoc documentation for a method states otherwise, you can chain
together any nonterminal methods, and you can end the chain with any terminal method.
However, not all combinations make sense. For example, it does not make sense to chain
method version()
together with a method that does not uniquely identify
the document, such as keys()
.
Table 3-1 briefly describes
OracleOperationBuilder
nonterminal methods for building operations
against a collection.
Table 3-1 OracleOperationBuilder Nonterminal Methods
Method | Description |
---|---|
|
Find a document that has the specified document key. |
|
(Supported only for collections with
client-assigned keys and a key column of data type
Find documents that have keys matching a given pattern. The
first parameter is the pattern, which can contain the wildcards
If the second
parameter is non-null then it is a character that, when it
immediately precedes either |
|
Find documents that have the specified document keys. The maximum number of keys passed as argument must not exceed 1000, or else a runtime error is raised. |
|
Find documents that match a filter specification (a query-by-example expressed in JSON). |
|
Find documents that have the
specified version. This is typically used with
|
|
Exclude document content from the result. |
|
Skip the specified number of documents in the result. |
|
Limit the number of documents in the result to the specified number. |
|
Provide a SQL tuning hint, to turn real-time SQL
monitoring of queries on and off. The argument is a string with the
SQL hint syntax. Use this only with argument
(You can use this to pass any SQL hints, but
See Also:
|
Table 3-2 briefly describes
OracleOperationBuilder
terminal methods for creating and executing
read operations against a collection.
Table 3-2 OracleOperationBuilder Terminal Methods for Read Operations
Method | Description |
---|---|
|
Create and execute an
operation that returns at most one document — for example, an
operation that includes an invocation of nonterminal method
|
|
Get a cursor over read operation results. |
|
Count the number of documents found by the operation. |
Table 3-3 briefly describes
OracleOperationBuilder
terminal methods for executing write
operations against a collection.
Table 3-3 OracleOperationBuilder Terminal Methods for Write Operations
Method | Description |
---|---|
|
Replace one document. |
|
Replace one document and return the result document. |
|
Remove documents from a collection. |
See Also:
-
The SODA for Java Javadoc for complete information about
OracleOperationBuilder
methods -
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about SODA restrictions
Finding Documents in Collections with SODA for Java
To find documents in a collection, you invoke
OracleCollection
method find()
, which returns an
OracleOperationBuilder
object that represents a query that finds all
documents in the collection.
To execute the query, obtain a cursor for its results by
invoking OracleOperationBuilder
method getCursor()
. Then
use the cursor to visit each document in the result list. To determine whether the result
list has a next document, and to obtain the next document, invoke
OracleCursor
methods hasNext()
and
next()
, respectively. This is illustrated by Example 3-10 and other examples here.
However, you
typically do not work directly with the OracleOperationBuilder
object.
Instead, you chain together some of its methods, to specify various find operations.
This is illustrated in the other examples here, which find documents by their keys or using
query-by-example (QBE) filter specifications.
Note:
Examples here that use method
getContentAsString()
assume that all documents in the collection are
JSON documents. If they are not, this method throws an exception.
Example 3-10 Finding All Documents in a Collection
This example first obtains a cursor for a
query result list that contains each document in a collection. It then uses the cursor in a
while
statement to get and print the content of each document in the
result list, as a string. Finally, it closes the cursor.
Note:
To avoid resource leaks, close any cursor that you no longer need.
OracleCursor c = col.find().getCursor();
while (c.hasNext()) {
OracleDocument resultDoc = c.next();
System.out.println("Document content: " +
resultDoc.getContentAsString());
}
// IMPORTANT: You must close the cursor to release resources!
c.close;
Example 3-11 Finding the Unique Document That Has a Given Document Key
This example chains together
OracleOperationBuilder
methods to specify an operation that finds the
unique document whose key is "key1"
. It uses nonterminal method
key()
to specify the document. It then uses terminal method
getOne()
to execute the read operation and return the document (or
null
if no such document is
found).
OracleDocument doc = col.find().key("key1").getOne();
Example 3-12 Finding Multiple Documents with Specified Document Keys
This example defines
HashSet
myKeys
, with (string) keys "key1"
,
"key2"
, and "key3"
. It then finds the documents that
have those keys, and it prints the key and content of each of those documents.
Nonterminal method keys()
specifies the documents with the
given keys. Terminal method getCursor()
executes the read operation and
returns a cursor over the result documents.
Note:
The maximum number of keys in
the set supplied to method keys()
must not exceed 1000, or else a runtime
error is
raised.
Set<String> myKeys = new HashSet<String>();
myKeys.put("key1");
myKeys.put("key2");
myKeys.put("key3");
OracleCursor c = col.find().keys(myKeys).getCursor();
while (c.hasNext()) {
OracleDocument resultDoc = c.next();
// Print the document key and document content
System.out.println ("Document key: " + resultDoc.getKey() + "\n" +
" document content: " + resultDoc.getContentAsString());
}
c.close();
Example 3-13 Finding Documents with a Filter Specification
Nonterminal method
filter()
provides a powerful way to filter JSON documents in a
collection. Its OracleDocument
parameter is a JSON query-by-example (QBE,
also called a filter specification).
The syntax of filter specifications is an expressive pattern-matching language for JSON documents. This example uses only a very simple QBE, just to indicate how you make use of one in SODA for Java.
This example does the following:
-
Creates a filter specification that looks for all JSON documents whose
name
field has value"Alexander"
. -
Uses the filter specification to find the matching documents.
-
Prints the key and content of each document.
// Create the filter specification
OracleDocument filterSpec =
db.createDocumentFromString("{ \"name\" : \"Alexander\"}");
OracleCursor c = col.find().filter(filterSpec).getCursor();
while (c.hasNext()) {
OracleDocument resultDoc = c.next();
// Print the document key and document content
System.out.println ("Document key: " + resultDoc.getKey() + "\n" +
" document content: " + resultDoc.getContent());
}
c.close();
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an introduction to SODA filter specifications
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for reference information about SODA filter specifications
Example 3-14 Specifying Pagination Queries with Methods skip() and limit()
This example uses
nonterminal methods skip()
and limit()
in a pagination
query. (Filter specification filterSpec
is from Example 3-13.)
// Find all documents matching the filterSpec, skip the first 1000,
// and limit the number of returned documents to 100.
OracleCursor c =
col.find().filter(filterSpec).skip(1000).limit(100).getCursor();
while (c.hasNext()) {
OracleDocument resultDoc = c.next();
// Print the document key and document content
System.out.println ("Document key: " + resultDoc.getKey() + "\n" +
" document content: " + resultDoc.getContent());
}
c.close();
Example 3-15 Specifying Document Version
This example uses nonterminal method
version()
to specify the document version. This is useful for
implementing optimistic locking, when used with the terminal methods for write operations.
You typically use version()
together with method
key()
, which specifies the document. You can also use
version()
with methods keyLike()
and
filter()
, provided they identify at most one
document.
// Find a document with key "key1" and version "version1".
OracleDocument doc = col.find().key("key1").version("version1").getOne();
Example 3-16 Finding Documents and Returning Only Their Headers
This
example finds all documents with the specified document keys and returns only their headers.
(The keys are those in HashSet
myKeys
, which is defined in Example 3-12.) Nonterminal method headerOnly()
specifies the return of document headers only. A document header has all the document
components except the
content.
// Find all documents matching the keys in HashSet myKeys.
// For each document, return all document components except the content.
OracleCursor c = col.find().keys(myKeys).headerOnly().getCursor();
Example 3-17 Counting the Number of Documents Found
This example uses terminal method count()
to
get a count of all of the documents in the collection. It then gets a count of all of the
documents that are returned by the filter specification filterSpec
from
Example 3-13.
// Get a count of all documents in the collection
int numDocs = col.find().count();
// Get a count of all documents in the collection that match a filter spec
numDocs = col.find().filter(filterSpec).count();
Replacing Documents in a Collection with SODA for Java
To replace the content of one document in a collection with the content of another, you chain together OracleOperationBuilder
method key(String)
with either method replaceOne(OracleDocument)
or method replaceOneAndGet(OracleDocument)
. Method replaceOne(OracleDocument)
only replaces the document. Method replaceOneAndGet(OracleDocument)
also returns a result document, which contains all document components except the content.
Both replaceOne(OracleDocument)
and replaceOneAndGet(OracleDocument)
update the values of the last-modified timestamp and the version. Replacement does not change the document key or the creation timestamp.
Note:
Some version-generation methods, including the default method, generate hash values of the document content. In such a case, if the document content does not change then neither does the version. For more information about version-generation methods, see SODA Collection Configuration Using Custom Metadata.
See Also:
OracleOperationBuilder
Javadoc for more information about replaceOne()
and replaceOneAndGet()
Example 3-18 Replacing a Document in a Collection and Getting the Result Document
This example replaces a document in a collection, gets the result document, and gets the generated components from the result document.
OracleDocument newDoc = ...
OracleDocument resultDoc = col.find().key("k1").replaceOneAndGet(newDoc);
if (resultDoc != null)
{
// Get the generated document key (unchanged by replacement operation)
String key = resultDoc.getKey();
// Get the generated version
String version = resultDoc.getVersion();
// Get the generated last-modified timestamp
String lastModified = resultDoc.getLastModified();
// Get the creation timestamp (unchanged by replacement operation)
String createdOn = resultDoc.getCreatedOn();
}
Example 3-19 Replacing a Particular Version of a Document
To implement optimistic locking when replacing a document, you can chain together methods key()
and version()
, as in this example.
OracleDocument resultDoc =
col.find().key("k1").version("v1").replaceOneAndGet(newDoc);
Removing Documents from a Collection with SODA for Java
To remove a document from a collection, you chain together (1) OracleCollection
method find()
with these OracleOperationBuilder
methods: (2) key()
, keyLike()
, keys()
, or filter()
; (3) version()
(optional); and (4) remove()
. Examples are provided.
See Also:
OracleOperationBuilder
Javadoc for more information about key()
, keys()
, filter()
, version()
, and remove()
Example 3-20 Removing a Document from a Collection Using a Document Key
This example removes the document whose document key is "k1"
. The number of documents removed is returned.
// Count is 1, if the document with key "k1" is found in the collection.
// Count is 0, otherwise.
int count = col.find().key("k1").remove();
Example 3-21 Removing a Particular Version of a Document
This example implements optimistic locking when removing a document, by specifying the version of the document, as well as its key.
col.find().key("k1").version("v1").remove();
Example 3-22 Removing Documents from a Collection Using Document Keys
This example removes the documents whose keys are "k1"
and "k2"
.
Set<String> myKeys = new HashSet<String>();
myKeys.add("k1");
myKeys.add("k2");
// Count is 2 if two documents with keys "k1" and "k2"
// were found in the collection.
int count = col.find().keys(myKeys).remove();
Example 3-23 Removing JSON Documents from a Collection Using a Filter
This example uses a filter to remove the JSON documents whose greeting
field has value "hello"
. It then prints the number of documents removed.
OracleDocument filterSpec =
db.createDocumentFromString("{ \"greeting\" : \"hello\" }");
int count = col.find().filter(filterSpec).remove();
// Print the number of documents removed
System.out.println ("Removed " + count + " documents"):
Indexing the Documents in a Collection with SODA for Java
You index the documents in a SODA collection with OracleCollectionAdmin
method createIndex()
. Its OracleDocument
parameter is a textual JSON index specification. This can specify B-tree, spatial, full-text, or ad hoc indexing, and it can specify support for a JSON data guide.
Note:
To create any kind of index using SODA you need Oracle Database Release 12c (12.2.0.1) or later. But to create a B-tree index for a DATE
or TIMESTAMP
value you need Oracle Database Release 18c (18.1) or later.
You drop an index on a SODA collection with method dropIndex()
.
A JSON search index is used for full-text and ad hoc structural queries, and for persistent recording and automatic updating of JSON data-guide information.
An Oracle Spatial and Graph index is used for GeoJSON (spatial) data. A JSON search index is used for full-text searching and for persisting data-guide information.
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an overview of using SODA indexing
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about SODA index specifications
-
Oracle Database JSON Developer’s Guide for information about JSON search indexes
-
Oracle Database JSON Developer’s Guide for information about persistent data-guide information as part of a JSON search index
-
Oracle Database JSON Developer’s Guide for information about spatial indexing of GeoJSON data.
Example 3-24 Creating a B-Tree Index for a JSON Field with SODA for Java
This example creates a B-tree non-unique index for numeric field address.zip
of the JSON documents in collection myCollection
.
OracleDocument indexSpec =
db.createDocumentFromString(
"{\"name\" : \"ZIPCODE_IDX\",
\"fields\" :
[{\"path\" : \"address.zip\",
\"datatype\" : \"number\",
\"order\" : \"asc\"}]}");
col.admin().createIndex(indexSpec);
This is the same index specification, pretty-printed for legibility:
{"name" : "ZIPCODE_IDX",
"fields" : [{"path" : "address.zip",
"datatype" : "number",
"order" : "asc"}]}
Example 3-25 JSON Search Indexing with SODA for Java
This example indexes the documents in collection myCollection
for ad hoc queries and full-text search (queries using QBE operator $contains
), and it automatically accumulates and updates data-guide information about your JSON documents (aggregate structural and type information). The index specification has only field name
(no field fields
).
OracleDocument indexSpec = db.createDocumentFromString(
"{\"name\" : \"SEARCH_AND_DATA_GUIDE_IDX\"}");
col.admin().createIndex(indexSpec);
The simple index specification it uses is equivalent to this one, which makes explicit the default values:
{"name" : "SEARCH_AND_DATA_GUIDE_IDX",
"dataguide" : "on",
"search_on" : "text_value"}
If you instead wanted only ad hoc (search) indexing then you would explicitly specify a value of "off"
for field dataguide
. If you instead wanted only data-guide support then you would explicitly specify a value of "none"
for field search_on
.
Note:
To create a data
guide-enabled JSON search index, or to data guide-enable an existing JSON search
index, you need database privilege CTXAPP
and Oracle Database
Release 12c (12.2.0.1) or later.
Example 3-26 Dropping an Index with SODA for Java
To drop an index on a SODA collection, just pass the index name to OracleCollectionAdmin
method dropIndex()
. This example drops index myIndex
.
col.admin().dropIndex("myIndex");
Getting a Data Guide for a Collection
You use OracleCollectionAdmin
method getDataGuide()
to get a data guide for a collection. A data guide is a JSON document that summarizes the structural and type information of the JSON documents in the collection. It records metadata about the fields used in those documents.
Before you can obtain a data guide for your collection you must create a data guide-enabled JSON search index on it. Example 3-25 shows how to do that.
Example 3-27 Getting a Data Guide with SODA for Java
This example gets a data guide using OracleCollectionAdmin
method getDataGuide()
.
OracleDocument dataGuide = col.admin().getDataGuide();
This returns a document whose content is a JSON data guide. To obtain this content as a string value, you can use OracleDocument
method getContentAsString()
.
System.out.println("Dataguide " + dataGuide.getContentAsString());
Handling Transactions with SODA for Java
You can cause SODA for Java to treat individual read and write operations, or groups of them, as a single transaction.
The JDBC connection that you pass to method OracleClient.getDatabase()
has auto-commit mode either on or off.
If auto-commit mode is on, then each SODA for Java read operation and write operation is treated as a single transaction. If the operation succeeds, then the transaction automatically commits. If the operation fails, then an OracleException
or RuntimeException
is thrown, and the transaction automatically rolls back. SODA for Java itself throws only checked exceptions (OracleException
and exceptions derived from OracleException
). However, SODA for Java is built upon JDBC, which can throw a RuntimeException
that SODA for Java passes through.
If auto-commit mode is off, then you can combine multiple SODA for Java read and write operations into one transaction. If the transaction succeeds, then your application must explicitly commit it, by calling method commit()
on the JDBC connection. If the transaction fails, then an OracleException
or RuntimeException
, is thrown. Your application must handle the exception and explicitly roll back the transaction, by invoking method rollback()
on the JDBC connection. (RuntimeException
can be thrown only by JDBC, as mentioned in the preceding paragraph.)
Caution:
If auto-commit mode is off, an uncommitted operation raises an error, and you do not explicitly roll back the transaction, the incomplete transaction might leave the relevant data in an inconsistent state (uncommitted, partial results).To facilitate transactional programming, SODA for Java supports optimistic locking: checking, before writing data back, that the data has not been modified (by another transaction) since it was read. With SODA for Java, you do this using replacement or removal by key and version, that is, checking the version value.Example 3-19 illustrates this for replacing a document, and Example 3-21 illustrates it for removing a document. Optimistic locking is especially useful in contexts where there is low data contention.