UpdateJSON.updateTableUsingSQLQuery()

The UpdateJSON.updateTableUsingSQLQuery() method uses a Oracle NoSQL Database SQL query to retrieve the required table rows.

This third and final query method is the easiest to implement, and it has the advantage of not requiring an index. If an appropriate index is available, it will be used — with all the advantages that an index offers — but the index is not required as it was for the previous method.

    // Update the zip code found on all Boston home addresses
    // to "02102-1000"
    //
    // This query works with or without an index. If an index is 
    // available, it is automatically used. For larger datasets, 
    // the read operation will be faster with an index because only 
    // the rows where person.address.home.city=Boston are returned 
    // for the read.
    private void updateTableUsingSQLQuery(KVStore kvstore) {
        TableAPI tableH = kvstore.getTableAPI();
        Table myTable = tableH.getTable("personContacts");

        String query = "select * from personContacts p ";
        query += "where p.person.address.home.city=\"Boston\"";

        StatementResult result = kvstore.executeSync(query);

        for (RecordValue rv : result) {
            Row row = myTable.createRowFromJson(rv.toString(), 
                      false);
            updateZipCode(tableH, row, "home", "02102-1000");
        }
        System.out.println("Updated a table using a SQL Query to " +
        "read.");
    }