The following steps demonstrate how to create the metadata
for a Java™ Database Connectivity (JDBC) data mediator
service (DMS), as well as how to instantiate the DMS dataGraph.
- Create the metadata factory. This can be used
for creating metadata, tables, columns, filters, filter arguments,
database constraints, keys, order-by objects, and relationships.
MetadataFactory factory = MetadataFactory.eINSTANCE;
Metadata metadata = factory.createMetadata();
- Create the table for the metadata. You can
do this two ways. Either the metadata factory can create the table
and then the table can add itself to the already created metadata,
or the metadata can add a new table in which case a new table is created.
Because it involves fewer steps, this example uses the second option
to create a table called CUSTOMER.
Table custTable = metadata.addTable("CUSTOMER");
- Set the root table for the metadata. Again,
you can do this in two ways. Either the table can declare itself
to be the root or the metadata can set its own root table. For the
first option, code:
custTable.beRoot();
If you want to use
the second option, you code: metadata.setRootTable(custTable)
- Set up the columns in the table. The example
table is called CUSTOMER. Each column is created using its type. The
column types in the metadata can only be the types supported by the
JDBC driver being used. If you have questions on which types the
JDBC driver being used supports, consult the JDBC driver documentation.
Column custID = custTable.addIntegerColumn("CUSTID");
custID.setNullable(false);
This example creates a column object
for this column, but does not for the remainder. The reason is because
this column is the primary key, and is used to set the table's
primary key after the rest of the columns are added. A primary key
cannot be null; therefore custID.setNullable(false) prohibits this
from happening. Adding the rest of the columns:custTable.addStringColumn("CUSTFIRSTNAME");
custTable.addStringColumn("CUSTLASTNAME");
custTable.addStringColumn("CUSTSTREETADDRESS");
custTable.addStringColumn("CUSTCITY");
custTable.addStringColumn("CUSTSTATE");
custTable.addStringColumn("CUSTZIPCODE");
custTable.addIntegerColumn("CUSTAREACODE");
custTable.addStringColumn("CUSTPHONENUMBER");
custTable.setPrimaryKey(custID);
- Create other tables as needed. For this example,
create the Orders table. Each order is made by one Customer.
Table orderTable = metadata.addTable("ORDER");
Column orderNumber = orderTable.addIntegerColumn("ORDERNUMBER");
orderNumber.setNullable(false);
orderTable.addDateColumn("ORDERDATE");
orderTable.addDateColumn("SHIPDATE");
Column custFKColumn = orderTable.addIntegerColumn("CUSTOMERID");
orderTable.setPrimaryKey(orderNumber);
- Create foreign keys for the tables that need relationships.
In this example, orders have a foreign key that points to the
customer who made the order. In order to create a relationship between
the two tables, you must first make a foreign key for the Orders table.
Key custFK = factory.createKey();
custFK.getColumns().add(custFKColumn);
orderTable.getForeignKeys().add(custFK);
The relationship takes
two keys, the parent key and the child key. Because no specific
name is given, the default concatenation of CUSTOMER_ORDER is the
name used for this relationship.metadata.addRelationship(custTable.getPrimaryKey(), custFK);
The
default relationship includes all customers who have orders. To
get all customers, even if they do not have orders, you need this
line as well: metadata.getRelationship("CUSTOMER_ORDER")
.setExclusive(false);
Now that
the two tables are related to one another you can add a filter to
the Customer table to find customers with specific characteristics.
- Specify any filters needed. In this example,
set filters to the Customer table to find all the customers in a particular
state, with a certain last name, who have made orders.
Filter filter = factory.createFilter();
filter.setPredicate("CUSTOMER.CUSTSTATE = ? AND CUSTOMER.CUSTLASTNAME = ?");
FilterArgument arg1 = factory.createFilterArgument();
arg1.setName("CUSTSTATE");
arg1.setType(Column.STRING);
filter.getFilterArguments().add(arg1);
FilterArgument arg2 = factory.createFilterArgument();
arg2.setName("CUSTLASTNAME");
arg2.setType(Column.STRING);
filter.getFilterArguments().add(arg2);
custTable.setFilter(filter);
- Add any order by objects needed. In this example,
set the order by object to sort by the customer's first name.
Column firstName = ((TableImpl)custTable).getColumn("CUSTFIRSTNAME");
OrderBy orderBy = factory.createOrderBy();
orderBy.setColumn(firstName);
orderBy.setAscending(true);
metadata.getOrderBys().add(orderBy);
This completes the creation
of the metadata for this JDBC DMS.
- Create a connection to the database. This example
does not show the creation of the connection to the database; it assumes
that the SDO client calls the method connect() that does that.
See the topic,
- Instantiate and initialize the JDBC DMS object (dataGraph).
The SDO client performs these actions. For this example:
ConnectionWrapperFactory factory = ConnectionWrapperFactory.soleInstance;
connectionWrapper = factory.createConnectionWrapper(connect());
JDBCMediatorFactory mFactory = JDBCMediatorFactory.soleInstance;
JDBCMediator mediator = mFactory.createMediator(metadata, connectionWrapper);
DataObject parameters = mediator.getParameterDataObject();
parameters.setString("CUSTSTATE", "NY");
parameters.setString('CUSTLASTNAME', 'Smith');
DataObject graph = mediator.getGraph(parameters);
Now that you
have the dataGraph, you can manipulate the information as you wish.
Some simple examples are contained in the topic, Example: Manipulating
data in a DataGraph object.
- Submit the changed information to the DMS for updating
the database.
Example
Manipulating data in a DataGraph object.This example
shows how to do basic manipulation of data in a DataGraph object that
you have created.
Using the simple DataGraph that was created
during the task Using the Java Database Connectivity data mediator
service for data access, some typical data manipulation follows.
First
get
the list of customers, then for each customer
get every order,
then
print out the customer's first name and order date. For
this example, assume that you already know the last name is Smith.
List customersList = graph.getList("CUSTOMER");
Iterator i = customersList.iterator();
while (i.hasNext())
{
DataObject customer = (DataObject)i.next();
List ordersList = customer.getList("CUSTOMER_ORDER");
Iterator j = ordersList.iterator();
while (j.hasNext())
{
DataObject order = (DataObject)j.next();
System.out.print( customer.get("CUSTFIRSTNAME") + " ");
System.out.println( order.get("ORDERDATE"));
}
}
Now
change every customer with the name Will
to be Matt.
i = customersList.iterator();
while (i.hasNext())
{
DataObject customer = (DataObject)i.next();
if (customer.get("CUSTFIRSTNAME").equals("Will"))
{
customer.set("CUSTFIRSTNAME", "Matt");
}
}
Delete the first Customer entry.
((DataObject) customersList.get(0)).delete();
Add a
new DataObject to the graph
DataObject newCust = graph.createDataObject("CUSTOMER");
newCust.setInt("CUSTID", 12345);
newCust.set("CUSTFIRSTNAME", "Will");
newCust.set("CUSTLASTNAME", "Smith");
newCust.set("CUSTSTREETADDRESS", "123 Main St.");
newCust.set("CUSTCITY", "New York");
newCust.set("CUSTSTATE", "NY");
newCust.set("CUSTZIPCODE", "12345");
newCust.setInt("CUSTAREACODE", 555);
newCust.set("CUSTPHONENUMBER", "555-5555");
graph.getList("CUSTOMER").add(newCust);
Submit the
changes.
mediator.applyChanges(graph);