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

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. In 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 as you wish. Some simple examples are contained in the topic, Example: Manipulating data in a DataGraph object.

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

Example: Manipulating data in a DataGraph object

 

Related tasks

Set a JDBC provider and data source

 

Related

Dynamic and static object types for the JDBC DMS
JDBC mediator generated query
JDBC mediator supplied query