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.
MetadataFactory factory = MetadataFactory.eINSTANCE; Metadata metadata = factory.createMetadata();
Table custTable = metadata.addTable("CUSTOMER");
custTable.beRoot();If you want to use the second option, you code:
metadata.setRootTable(custTable)
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);
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);
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.
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);
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.
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. The example below contains basic manipulation of data in a DataGraph object.
Using 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.
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")); } }
i = customersList.iterator(); while (i.hasNext()) { DataObject customer = (DataObject)i.next(); if (customer.get("CUSTFIRSTNAME").equals("Will")) { customer.set("CUSTFIRSTNAME", "Matt"); } }
((DataObject) customersList.get(0)).delete();
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);
mediator.applyChanges(graph);