Writing Rows to a Table in the Store
Writing a new row to a table in the data store, and updating an existing row are similar operations. Later in this section, we describe methods that work only if a row is being updated, or only if you are creating a row. You can write data to a table only after it has been added to the store. See Introducing Oracle NoSQL Database Tables and Indexes for details.
To write a row to a table in the store:
-
Construct a handle for the table to which are writing data. You do this by retrieving a
TableAPI
interface instance usingKVStore.getTableAPI()
. Use that instance to retrieve a handle for the table using theTableAPI.getTable()
, which then returns aTable
interface instance.Note:
The
TableAPI.getTable()
method is an expensive call requiring server side access. For best performance, do not call this method each time you need a table handle. If possible, call this method for all relevant tables in the set up section of your code. Then, reuse the handles throughout your application. -
Use the
Table.createRow()
method to create a Row interface instance, using theTable
instance you retrieved in the previous step. -
Using the
Row.put()
method, write to each field in the row.To write a
NULL
value, useRow.putNull()
, rather thanRow.put()
. -
Write the new row to the store using
TableAPI.put()
.Note:
If the table you are writing to contains an IDENTITY column, the generated value from the sequence generator will be available in the row.
You can also load rows into the store using special purpose streams. For more information, see Bulk Put Operations.
The following example shows how to write a row to the store, assuming that you have already created the KVStore
handle.
package kvstore.basicExample;
import oracle.kv.KVStore;
import oracle.kv.table.Row;
import oracle.kv.table.Table;
import oracle.kv.table.TableAPI;
...
// KVStore handle creation is omitted for brevity
...
TableAPI tableH = kvstore.getTableAPI();
// The name you give getTable() must be identical
// to the name of the table when you created it with
// the CREATE TABLE DDL statement (myTable in this example).
Table myTable = tableH.getTable("myTable");
// Get a Row instance
Row row = myTable.createRow();
// Use row.put to put all of the cells into the row.
// This does NOT actually write the data to the store.
row.put("item", "Bolts");
row.put("description", "Hex head, stainless");
row.put("count", 5);
row.put("percentage", 0.2173913);
// Now write the table to the store.
// "item" is the row's primary key. If we had not set that key and its value,
// this operation will result in an IllegalArgumentException.
tableH.put(row, null, null);
Writing Rows to a Child Table
To write to a child table, complete the tasks that you do for a parent table, except using the two-part table name, such as parent-table.child-table.
For example, in Defining Child Tables we showed how to create a child table. To write data to that table, do this:
package kvstore.basicExample;
import oracle.kv.KVStore;
import oracle.kv.table.Row;
import oracle.kv.table.Table;
import oracle.kv.table.TableAPI;
...
// KVStore handle creation is omitted for brevity
...
TableAPI tableH = kvstore.getTableAPI();
// Get the corresponding child table
Table myChildTable = tableH.getTable("myInventory.itemDetails");
// Get a row instance
Row childRow = myChildTable.createRow();
// Populate the rows. Because the parent table's "itemCategory"
// field is a primary key, this must be populated in addition
// to all of the child table's rows
childRow.put("itemCategory", "Bolts");
childRow.put("itemSKU", "1392610");
childRow.put("itemDescription", "1/4-20 x 1/2 Grade 8 Hex");
childRow.put("price", new Float(11.99));
childRow.put("inventoryCount", 1457);
Writing rows to an IDENTITY column
Special considerations arise when you are inserting values into an IDENTITY column programmatically. This section presents the issues that exist, and how to work around them using put()
and other methods.
- GENERATED ALWAYS AS IDENTITY
- GENERATED BY DEFAULT AS IDENTITY
- GENERATED BY DEFAULT ON NULL AS IDENTITY
put
function, with one of its variants:
put
(unconditional)put if absent
(only if the row does not have values)put if present
(only if the row has values)
CREATE Table foo(
idValue INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 MAXVALUE 2 NO CYCLE),
name STRING,
PRIMARY KEY(idValue));
Insert a row into the IDENTITY Column
To insert a row into thefoo
table, here's what to do in your application:L1: TableAPI api = store.getTableAPI(); // Gets the TableAPI for the store
L2: Table table = api.getTable("foo"); // Gets the Table foo instance
L3: Row row = table.createRow(); // constructs an empty Row row for Table foo.
L4: row.put("name", "joe"); // populates the values for the Row row
L5: api.put(row, null /* previous-row */, null /* write-options */);
// The client driver recognizes that the system must generate the id values and \
generates value 1 for the id field in the row before putting it in the DB.
L6: System.out.println("Value of idValue: " + row.get("idValue")); // 1
L7: row.put("name", "smith");
L8: api.put(row, null /* previous-row */, null /* write-options */);
// driver sets id field to 2
L9: System.out.println("Value of id: " + row.get("idValue")); // 2
Note:
To get the value of a generated IDENTITY column, use aget()
call to the IDENTITY column, as shown
in L6 and L9
.
idValue
use the
RETURNING idValue
clause, as follows:
StatementResult sr = store.executeSync("INSERT INTO foo " + "(name) VALUES ('foe')
RETURNING idValue");
int id = sr.iterator().next().get("idValue").asInteger().get();
When you define a column as GENERATED
ALWAYS AS IDENTITY
you cannot supply a value for the IDENTITY column,
because the Sequence Generator(SG) must always supply the next value.
The following example illustrates what happens when you try to specify
a value for the IDENTITY column. The first additions, joe
and
john
are fine, and the SG supplies an idValue
for both:
CREATE TABLE foo(
idValue INTEGER GENERATED ALWAYS AS IDENTITY,
name STRING, PRIMARY KEY (idValue))
api.put('joe')
api.put('john')
get(idValue, name) or
select * from foo;
1, joe
2, john
GENERATED ALWAYS AS
IDENTITY
:api.put(2,'dave') // error –- cannot specify a value for \
a column defined as GENERATED ALWAYS AS IDENTITY
api.putIfPresent (2, 'dave') -- The following error occurs first in the code path,
even though idValue = 2 is present
// error – user cannot specify a value for \
IDENTITY column defined as GENERATED ALWAYS
api.putIfPresent (3,'cezar') -- The following error occurs, first in the code path,
even though idValue = 3 is NOT present
// error - user cannot specify a value for \
IDENTITY column defined as GENERATED ALWAYS
api.putIfPresent ('hema')
//error – a primary key is not provided to look up the record.
putIfAbsent (10, 'joe') -– is an insert
// error - user cannot specify a value for \
IDENTITY column defined as GENERATED ALWAYS
GENERATED ALWAYS AS
IDENTITY
:Create table foo( idValue INTEGER GENERATED ALWAYS AS IDENTITY,
name STRING,
PRIMARY KEY (idValue))
UPDATE foo SET idValue = 10 WHERE name='joe'
// error - user cannot set a value for an IDENTITY column defined as GENERATED ALWAYS
UPDATE foo SET name=hema WHERE idValue=2
// Success! By using the Primary Key value (idValue=2)to locate its name record,
// you can update the value and hema replaces john
select * from foo
1, joe
2, hema
put
, putIfPresent
, and
putIfAbsent
on an IDENTITY column that is not a PRIMARY
KEY:Create table Foo(idValue INTEGER GENERATED ALWAYS AS IDENTITY,
acctNumber INTEGER,
name STRING,
PRIMARY KEY (acctNumber) )
//Put two acctNumber and name values.
api.put(100, 'joe')
api.put (200, 'john')
//SG increments the IDENTITY values, 1 and 2:
api.get(idValue, acctNumber, name)
1, 100, joe
2, 200, john
//Attempt to put an idValue
api.put (2, 200,'dave')
// error – Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS
api.putIfPresent(3, 200,'cezar')
//error – Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS
api.putIfPresent (400,'cezar') // not IDENTITY column value error
// error - Cannot specify a primary key (400) that is not present
api.putIFPresent (200,'cezar')
1, 100, joe
2, 200, cezar
// Success! The IDENTITY value is updated.
The system generates a value on putIfPresent as the API semantics are to update
the entire record, and not update fields within the record selectively.
api.putIfAbsent (300,'hema')
// Success! IDENTITY idValue was generated (3), and 300, hema were absent
get(idValue, acctNumber, name)
1, 100, joe
2, 200, cezar
3, 300, hema
api.putIfAbsent (20, 300,'hema')
// error – user cannot specify a value for IDENTITY column defined as GENERATED ALWAYS
api.putIfAbsent (300,'hema')
//error - no row with primary key = 300 is present
api.putIfAbsent (3,400,'hema')
// error – user cannot specify a value for IDENTITY column defined as GENERATED ALWAYS
UPDATE
on an IDENTITY column that is not a
PRIMARY KEY:
Create table Foo(idValue INTEGER GENERATED ALWAYS AS IDENTITY,
acctNumber INTEGER,
name STRING,
PRIMARY KEY (acctNumber))
select * from foo
1, 100, joe
2, 200, cezar
3, 300, hema
UPDATE foo set name= dave where PRIMARY KEY = 200
// replaces (2, 200, cezar) with (2, 200, dave)
select * from foo;
1, 100, joe
2, 200, dave
3, 300, hema
UPDATE foo set name=george where acctNumber=100
// acctNumber is the PRIMARY KEY
// replaces (1, 100, joe) with (1, 100, george)
select * from foo;
1, 100, george
2, 200, dave
3, 300, hema
UPDATE foo set idValue=10 where acctNumber=100
// acctNumber is the PRIMARY KEY
// error - Cannot specify a value for IDENTITY column defined as GENERATED ALWAYS
To use put()
on a column defined as GENERATED BY
DEFAULT AS IDENTITY, which is a PRIMARY KEY, review the following examples. In this
case, not specifying a value for the IDENTITY column causes the SG to generate a
value. Specifying a value, the system uses what you supply.
Create table foo( idValue INTEGER GENERATED BY DEFAULT AS IDENTITY,
name STRING,
PRIMARY KEY (idValue))
api.put('joe')
api.put('john')
//Since you supplied no idValue, SG supplies them:
get(idValue,name)
1, joe
2, john
//You supply 4 as the idValue, so system uses it
api.put (4,'george')
get(idValue, name)
1, joe
2, john
4, george
api.put (2,'sam') // replaces (2, john) with (2, sam)
get(idValue,name)
1, joe
2, sam
4, george
To use UPDATE()
on the column:
select * from foo;
1, joe
2, sam
4, george
UPDATE foo SET name='brian' where idValue=2
// Updates idValue 2 (2, sam) with 2, brian
select * from foo
1, joe
2, brian
4, george
You can update any column other than the primary key column using the UPDATE clause.
Deleting a row with an IDENTITY column follows the existing delete logic in the product. There is no change.
Other put Operations
Beyond the very simple usage of the TableAPI.put()
method illustrated above, there are three other put operations that you can use:
-
TableAPI.putIfAbsent()
This method will only put the row if the row's primary key value DOES NOT currently exist in the table. That is, this method is successful only if it results in a create operation.
-
TableAPI.putIfPresent()
This method will only put the row if the row's primary key value already exists in the table. That is, this method is only successful if it results in an update operation.
-
TableAPI.putIfVersion()
This method will put the row only if the value matches the supplied version information. For more information, see Using Row Versions.