Use the Java Database Connectivity data mediator service for data access

The following steps demonstrate how to create the metadata for a JDBC data mediator service (DMS), as well as how to instantiate the DMS dataGraph.

1. Create the metadata factory. 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();

2. Create the table for the metadata. We 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");

3. Set the root table for the metadata. Again, we 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();

   To use the second option, you code:

   metadata.setRootTable(custTable)

4. 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 we 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);

5. 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);

6. 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. To create a relationship between the two tables, 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 we can add a filter to the Customer table to find customers with specific characteristics.

7. 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);

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

9. 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,

10. 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 we have the dataGraph, we can manipulate the information. The following example contains basic manipulation of data in a DataGraph object.

    Example: Manipulating data in a DataGraph object

    Use the simple DataGraph that was created during the task Using the Java Database Connectivity data meditaor 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);

11. Submit the changed information to the DMS for updating the database.

Related tasks

Configure a JDBC provider and data source

Dynamic and static object types for the JDBC DMS

JDBC mediator generated query

JDBC mediator supplied query