1. Java Direct Driver Developer's Guide
  2. Writing and Deleting Table Rows
  3. Writing Rows to a Table in the Store

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:

  1. Construct a handle for the table to which are writing data. You do this by retrieving a TableAPI interface instance using KVStore.getTableAPI(). Use that instance to retrieve a handle for the table using the TableAPI.getTable(), which then returns a Table 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.

  2. Use the Table.createRow() method to create a Row interface instance, using the Table instance you retrieved in the previous step.

  3. Using the Row.put() method, write to each field in the row.

    To write a NULL value, use Row.putNull(), rather than Row.put().

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

You create each IDENTITY column in a table with one of these choices:
  • GENERATED ALWAYS AS IDENTITY
  • GENERATED BY DEFAULT AS IDENTITY
  • GENERATED BY DEFAULT ON NULL AS IDENTITY
Additionally, an identity column can be a primary key, which prevents you from changing the IDENTITY value.
Each of the ways in which you create your identity column affects activities when you add rows using the 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)
This section describes the different effects of inserting and updating IDENTITY columns.
For example, create the following table with a column defined with GENERATED ALWAYS AS IDENTITY. The IDENTITY field is a primary key: 
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 the foo 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 a get() call to the IDENTITY column, as shown in L6 and L9.
Also, to return the 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();
Updating an IDENTITY Column

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
Trying to update with any of the put() methods causes the following errors when a column is defined as 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
To use UPDATE on a column defined as 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  
To use 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
To use 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 an IDENTITY Column

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.