1. Administrator's Guide
  2. Configure
  3. Oracle NoSQL Database Drivers
  4. Adding Data

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.

Note:

If the table is a Global Active table, then adding data in one region also adds the data to all the regional table replicas.

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 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 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.

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.

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.

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" 
 *      } 
 *   } 
 * } 
 */
/* Supply the JSON fields for the nested JSON audience_segment field */   
MapValue segments = new MapValue()
    .put("sports_lover", new TimestampValue("2018-11-30"))
    .put("book_reader", new TimestampValue("2018-12-01"));

/* Supply the ipaddr JSON field and Mapvalue for audience_data JSON */
MapValue audience_data=new MapValue()
    .put("ipaddr","10.0.00.xxx")
    .put("audience_segment",segments);

/* Supply the top level JSON data - cookie_id JSON field and the Mapvalue for audience_data JSON */
MapValue value=new MapValue()
    .put("cookie_id",123)
    .put("audience_data",audience_data);
   
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);
The borneo.PutRequest class represents input to the borneo.NoSQLHandle.put() method used to insert single rows. This method can be used for unconditional and conditional puts to:
  • Overwrite any existing row. This is the default.
  • Succeed only if the row does not exist. Use borneo.PutOption.IF_ABSENT for this case.
  • Succeed only if the row exists. Use borneo.PutOption.IF_PRESENT for this case.
  • Succeed only if the row exists and its borneo.Version matches a specific borneo.Version. Use borneo.PutOption.IF_VERSION for this case and borneo.PutRequest.set_match_version() to specify the version to match. 
from borneo import PutRequest
# PutRequest requires a table name 
request = PutRequest().set_table_name('users')
# set the value 
request.set_value({'id': i, 'name': 'Jane'})
result = handle.put(request)
# a successful put returns a non-empty version
if result.get_version() is not None: 
# success

When adding data the values supplied must accurately correspond to the schema for the table. If they do not, IllegalArgumentException is raised. Columns with default or nullable values can be left out without error, but it is recommended that values be provided for all columns to avoid unexpected defaults. By default, unexpected columns are ignored silently, and the value is put using the expected columns.

If you have multiple rows that share the same shard key they can be put in a single request using borneo.WriteMultipleRequest which can be created using a number of PutRequest or DeleteRequest objects. You can also add JSON data to your table. In the case of a fixed-schema table the JSON is converted to the target schema. JSON data can be directly inserted into a column of type JSON. The use of the JSON data type allows you to create table data without a fixed schema, allowing more flexible use of the data.

The data value provided for a row or key is a Python dict. It can be supplied to the relevant requests (GetRequest, PutRequest, DeleteRequest) in multiple ways:
  • as a Python dict directly:
    request.set_value({'id': 1})
request.set_key({'id': 1 })
  • as a JSON string:
    request.set_value_from_json('{"id": 1, "name": "Jane"}')
request.set_key_from_json('{"id": 1}')

In both cases the keys and values provided must accurately correspond to the schema of the table. If not an borneo.IllegalArgumentException exception is raised. If the data is provided as JSON and the JSON cannot be parsed a ValueError is raised.

The nosqldb.PutRequest represents an input to the nosqldb.Put() function used to insert single rows. This function can be used for unconditional and conditional puts to:
  • Overwrite any existing row. This is the default.
  • Succeed only if the row does not exist. Specify types.PutIfAbsent for the PutRequest.PutOption field for this case.
  • Succeed only if the row exists. Specify types.PutIfPresent for the PutRequest.PutOption field for this case.
  • Succeed only if the row exists and its version matches a specific version. Specify types.PutIfVersion for the PutRequest.PutOption field and a desired version for the PutRequest.MatchVersion field for this case.
The data value provided for a row (in PutRequest) or key (in GetRequest and DeleteRequest ) is a *types.MapValue. The key portion of each entry in the MapValue must match the column name of target table, and the value portion must be a valid value for the column. There are several ways to create a MapValue for the row to put into a table:
  1. Create an empty MapValue and put values for each column.
    value:=&types.MapValue{}
value.Put("id", 1).Put("name", "Jack")
req:=&nosqldb.PutRequest{
    TableName: "users",
    Value: value,
}
res, err:=client.Put(req)
  2. Create a MapValue from a map[string]interface{}.
    m:=map[string]interface{}{
    "id": 1,
    "name": "Jack",
}
value:=types.NewMapValue(m)
req:=&nosqldb.PutRequest{
    TableName: "users",
    Value: value,
}
res, err:=client.Put(req)
  3. Create a MapValue from JSON. This is convenient for setting values for a row in the case of a fixed-schema table where the JSON is converted to the target schema. For example:
    value, err:=types.NewMapValueFromJSON(`{"id": 1, "name": "Jack"}`)
iferr!=nil {
    return
}
req:=&nosqldb.PutRequest{
    TableName: "users",
    Value: value,
}
res, err:=client.Put(req)

JSON data can also be directly inserted into a column of type JSON. The use of the JSON data type allows you to create table data without a fixed schema, allowing more flexible use of the data.

Method put is used to insert a single row into the table. It takes table name, row value as plain JavaScript object and opts as an optional 3rd argument. This method can be used for unconditional and conditional puts to:
  • Overwrite existing row with the same primary key if present. This is the default.
  • Succeed only if the row with the same primary key does not exist. Specify ifAbsent in the opt argument for this case: { ifAbsent: true }. Alternatively, you may use putIfAbsent method.
  • Succeed only if the row with the same primary key exists. Specify ifPresent in the opt argument for this case: { ifPresent: true }. Alternatively, you may use putIfPresent method.
  • Succeed only if the row with the same primary key exists and its Version matches a specific Version value. Set matchVersion in the opt argument for this case to the specific version: { matchVersion: my_version }. Alternatively, you may use putIfVersion method and specify the version value as the 3rd argument (after table name and row).

Each put method returns a Promise of PutResult which is a plain JavaScript object containing information such as success status and resulting row Version. Note that the property names in the provided row object should be the same as underlying table column names.

To add rows to your table:
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const client = new NoSQLClient('config.json');

async function putRowsIntoUsersTable() {
    const tableName = 'users';
    try {
        // Uncondintional put, should succeed
        let result = await client.put(tableName, { id: 1, name: 'John' });

        // Will fail since the row with the same primary key exists
        result = await client.putIfAbsent(tableName, { id: 1, name: 'Jane' });
        // Expected output: putIfAbsent failed
        console.log('putIfAbsent ' + result.success ? 'succeeded' : 'failed');

        // Will succeed because the row with the same primary key exists
        res = await client.putIfPresent(tableName, { id: 1 , name: 'Jane' });
        // Expected output: putIfAbsent succeeded
        console.log('putIfPresent ' + result.success ?
            'succeeded' : 'failed');

        let version = result.version;
        // Will succeed because the version matches existing row
        result = await client.putIfVersion(tableName, { id: 1, name: 'Kim' },
            version);
        // Expected output: putIfVersion succeeded
        console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');

        // Will fail because the previous put has changed the row version, so
        // the old version no longer matches.
        result = await client.putIfVersion(tableName, { id: 1, name: 'June' },
            version);
        // Expected output: putIfVersion failed
        console.log('putIfVersion ' + result.success ? 'succeeded' : 'failed');

    } catch(error) {
        //handle errors
    }
}

Note that success results in false value only if conditional put operation fails due to condition not being satisfied (e.g. row exists for putIfAbsent, row doesn't exist for putIfPresent or version doesn't match for putIfVersion). If put operation fails for any other reason, the resulting Promise will reject with an error (which you can catch in async function). For example, this may happen if a column value supplied is of a wrong type, in which case the put will result in NoSQLArgumentError.

You can perform a sequence of put operations on a table that share the same shard key using putMany method. This sequence will be executed within the scope of single transaction, thus making this operation atomic. The result of this operation is a Promise of WriteMultipleResult. You can also use writeMany if the sequence includes both puts and deletes.

Using columns of type JSON allows more flexibility in the use of data as the data in the JSON column does not have predefined schema. To put data into JSON column, provide either plain JavaScript object or a JSON string as the column value. Note that the data in plain JavaScript object must be of supported JSON types.

Method PutAsync and related methods PutIfAbsentAsync, PutIfPresentAsync and PutIfVersionAsync are used to insert a single row into the table or update a single row.

These methods can be used for unconditional and conditional puts:
  • Use PutAsync (without conditional options) to insert a new row or overwrite existing row with the same primary key if present. This is unconditional put.
  • Use PutIfAbsentAsync to insert a new row only if the row with the same primary key does not exist.
  • Use PutIfPresentAsync to overwrite existing row only if the row with the same primary key exists.
  • Use PutIfVersionAsync to overwrite existing row only if the row with the same primary key exists and its RowVersion matches a specific version.
Each of the Put methods above returns Task<PutResult<RecordValue>>. PutResult instance contains info about a completed Put operation, such as success status (conditional put operations may fail if the corresponding condition was not met) and the resulting RowVersion.
To add rows to your table:
var client = new NoSQLClient("config.json");
var tableName = "users";

try {
    // Uncondintional put, should succeed.
    var result = await client.PutAsync(tableName,
        new MapValue
        {
            ["id"] = 1,
            ["name"] = "John"
        });

    // This Put will fail because the row with the same primary
    // key already exists.
    result = await client.PutIfAbsentAsync(tableName,
        new MapValue
        {
            ["id"] = 1,
            ["name"] = "Jane"
        });
    
    // Expected output: PutIfAbsentAsync failed.
    Console.WriteLine("PutIfAbsentAsync {0}.",
        result.Success ? "succeeded" : "failed");

    // This Put will succeed because the row with the same primary
    // key exists.
    result = await client.PutIfPresentAsync(tableName,
        new MapValue
        {
            ["id"] = 1,
            ["name"] = "Jane"
        });

    // Expected output: PutIfPresentAsync succeeded.
    Console.WriteLine("PutIfPresentAsync {0}.",
        result.Success ? "succeeded" : "failed");
    var rowVersion = result.Version;

    // This Put will succeed because the version matches existing
    // row.
    result = await client.PutIfVersionAsync(
        tableName,
        new MapValue
        {
            ["id"] = 1,
            ["name"] = "Kim"
        }),
        rowVersion);

    // Expected output: PutIfVersionAsync succeeded.
    Console.WriteLine("PutIfVersionAsync {0}.",
        result.Success ? "succeeded" : "failed");

    // This Put will fail because the previous Put has changed
    // the row version, so the old version no longer matches.
    result = await client.PutIfVersionAsync(
        tableName,
        new MapValue
        {
            ["id"] = 1,
            ["name"] = "June"
        }),
        rowVersion);

    // Expected output: PutIfVersionAsync failed.
    Console.WriteLine("PutIfVersionAsync {0}.",
        result.Success ? "succeeded" : "failed");

    // Put a new row with TTL indicating expiration in 30 days.
    result = await client.PutAsync(tableName,
        new MapValue
        {
            ["id"] = 2,
            ["name"] = "Jack"
        }),
        new PutOptions
        {
            TTL = TimeToLive.OfDays(30)
        });
}
catch(Exception ex) {
    // handle exceptions
}

Note that Success property of the result only indicates successful completion as related to conditional Put operations and is always true for unconditional Puts. If the Put operation fails for any other reason, an exception will be thrown.

You can perform a sequence of put operations on a table that share the same shard key using PutManyAsync method. This sequence will be executed within the scope of single transaction, thus making this operation atomic. You can also call WriteManyAsync to perform a sequence that includes both Put and Delete operations.

Using fields of data type JSON allows more flexibility in the use of data as the data in JSON field does not have a predefined schema. To put value into a JSON field, supply a MapValue instance as its field value as part of the row value. You may also create its value from a JSON string via FieldValue.FromJsonString.

Use one of these methods to add rows to the table - NosqlRepository save(entity_object), saveAll(Iterable<T> iterable), or NosqlTemplate insert(entity). For details, see SDK for Spring Data API Reference.

In this section, you use the repository.save(entity_object) method to add the rows.

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.
To add rows to your table, you can include the following code in your application.
@Override
public void run(String...args) throws Exception {
 
    /* Create a new User instance and load values into it.*/
 
    Users u1 = new Users();
    u1.firstName = "John";
    u1.lastName = "Doe";
 
    /* Save the User instance.*/
    repo.save(u1);
 
    /* Create a second User instance and load values into it. Save the instance.*/
    Users u2 = new Users();
    u2.firstName = "Angela";
    u2.lastName = "Willard";
 
    repo.save(u2);
}
This creates and saves two user entities. For each entity, the Spring Data SDK creates two columns:
  1. Primary key column
  2. JSON data type column

Here, the primary key is auto-generated. The @NosqlId annotation in the Users class specifies that the id field will act as the ID and be the primary key of the underlying storage table.

The generated=true attribute specifies that this ID will be auto-generated by a sequence. The rest of the entity fields, that is, the firstName and lastName fields are stored in the JSON column.