Use the Java Database Connectivity data mediator service for data access

 

Overview

The following steps use code samples to describe a simple instance of how to create the Java Database Connectivity (JDBC) data mediator service (DMS) metadata.

 

Procedure

  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, one 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)
    

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

  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 one 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 there is a method called connect() that does that.

  10. Create the JDBC DMS object (DataGraph) using this metadata. 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, one can manipulate the information as you wish. Some simple examples are contained in Example: manipulating data in a DataGraph.

  • Submit the changed information to the database.

     

    See also


    Example: manipulating data in a DataGraph

     

    See Also


    SDO data object types
    JDBC mediator generated query
    JDBC mediator supplied query