Inserting with a Selector
To populate a database table with data selected from other database tables, we use a selector to provide data to an inserter. In this example, we want to ensure that we have 20 copies of each video in our inventory, so we generate purchase orders to replace copies that are sold. For simplicity, we assume there are no more than 20 copies of a particular video on hand.
RWDBTable sales = myDbase.table("sales");
RWDBTable videos = myDbase.table("videos");
RWDBTable suppliers = myDbase.table("supplier");
RWDBSelector select = myDbase.selector(); //1
select << sales["videoID"] << suppliers["ID"]
<< nextOrderNumber() << videos["price"]
<< 20 - videos["OnHand"] << rwdbSystemDateTime();
select.where(sales["ID"] == videos["ID"] &&
suppliers["ID"] == videos["supplierID"]);
RWDBTable purchases = myDbase.table("purchase"); //2
RWDBInserter insert = purchases.inserter(select); //3
insert.execute(myConnection); //4
The first few lines of the example instantiate three tables. Starting on
//1, an
RWDBSelector is constructed in the familiar way. The selector extracts the desired rows from a join of the
sales,
videos, and
supplier tables. On
//2, a reference to the
purchase table is instantiated, and on
//3 an inserter is obtained and provided with data from the selector. Until now, there is no interaction with the database. Finally, rows are inserted into the
purchase table by invoking the inserter's
execute() method on
//4. As recommended, we specify a connection for the database interaction.
Notice that no data flows from the database into the application. The selection and insertion are all done within the database server.
