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 thesetMatchVersion(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.
/* 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);
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 andborneo.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.
- 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.
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 thePutRequest.PutOption
field for this case. - Succeed only if the row exists. Specify
types.PutIfPresent
for thePutRequest.PutOption
field for this case. - Succeed only if the row exists and its version matches a specific
version. Specify
types.PutIfVersion
for thePutRequest.PutOption
field and a desired version for thePutRequest.MatchVersion
field for this case.
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:
- 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)
- Create a
MapValue
from amap[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)
- 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.
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 useputIfAbsent
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 useputIfPresent
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 useputIfVersion
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.
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.
- 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.
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.
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.
repository.save(entity_object)
method
to add the rows.
Note:
First, create theAppConfig
class that extends
AbstractNosqlConfiguration
class to provide the connection details of
the Oracle NoSQL Database. For more details, see Obtaining a NoSQL connection.
@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);
}
- Primary key column
- 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.