Database Table Mapping code example

The JDBCTable service works with an existing database table. Therefore, it does not manage the table's primary keys when new records are added to the database. An application using this service must set the primary key column for any record to be added, since records with duplicate primary keys are not allowed.

The following is an example application working with the JDBC-specific implementation of a Database Table Mapping service and the XML files needed to run it. As shown in the example, the reuse of the application code with a different implementation of a table service depends on reference to the TableService interface instead of explicitly use using the JDBCTable object implementing this interface. This example is implemented for a local environment, but you can easily adapt it to a client/server environment by implementing a toolkit client operation. The client operation should start the server operation that calls for the table services. In this example, the application requests the connection to be automatically set by the service, and also lets the service commit and roll back the database changes automatically. It is assumed that the appropriate JDBC driver is loaded.

dsedata.xml

<kColl id="myTableOperationData"/>
    <field id="ACCOUNT_NUMBER"/>
    <field id="AMOUNT"/>
    <field id="THE_DATE"/> 
    <field id="DESCRIPTION"/>
    <field id="RECID'/>
</kColl>

dsectxt.xml

<context id="branch" type="branch" parent="nil">
</context>
<context id="myTableOperationContext" type="op" parent="branch">
    <refKColl refId="myTableOperationData">
    </refKColl>
    <refService refId="myTableName" type="table"/> 
</context>

dsesrvce.xml

Note that the following definition does not include any column tags. This means that the data field names match the column names in the database table.

<JDBCServicesConnectionManager
    id="JDBCServicesConnectionManager"
    poolManager="WebSphere5.0" orphanTimeout="18000"
    reapTime="10000" />

<JDBCTable id="myTableName" autoCommit="true" 
    autoConnect="true"
    table="DatabaseTable"
    connManager="JDBCServicesConnectionManager"
</JDBCTable> 

dsefrmts.xml

<fmtDef id="tableFormatName">
    <hashtable>
        <fObject dataName="ACCOUNT_NUMBER"/>
        <fObject dataName="AMOUNT"/>
        <fObject dataName="THE_DATE"/> 
        <fObject dataName="DESCRIPTION"/>
        <fObject dataName="RECID"/> 
    </hashtable> 
</fmtDef>

ServiceRequesterIDs.properties

Note that the key is the ID of the service requester and the value is the name of the corresponding resource file.

myTableService=JDBCTableServiceRequester

JDBCTableServiceRequester.properties

This is the definition file for the Database Table Mapping service. Note that the ServiceType value must match the ID of the JDBCTable definition in the dsesrvce.xml file and the ServiceInvocation value must match the ID in the ServiceRequesterIDs.properties file .

ServiceRequester=com.ibm.btt.services.jdbctableservice.JDBCTableService

ServiceType=myTableName
CachingEnabled=true 
ServiceInvocation=RemoteEJB

Application flow

The code below is a runnable class that will work with a table named DatabaseTable with the following column definition

RECID INTEGER
ACCOUNT_NUMBER CHAR(14)
AMOUNT INTEGER
DESCRIPTION VARCHAR(50)
THE_DATE DATE

with RECID being the primary key.

public void accessTableService() throws java.io.IOException, 
     DSEObjectNotFoundException {
  Context tableContext;
  com.ibm.btt.formatter.client.FormatElement tableFormat= null;
  // Initializing toolkit for Java
  Settings.reset("c:\\dse\\dse.ini");
  Settings.initializeExternalizers(Settings.MEMORY); 
  // Working with the table service 
  JDBCTableService table = null;
  try { 
    TableContext=Context("myTableOperationContext",false);
    System.out.println(">>> Creating a Table Service Instance...");
    table = (JDBCTableService)BTTServiceRequesterFactory.getServiceRequester("myTableService"); 
    // Because of the service definition, the connection to the database
    // is not explicitly done 
    int nbrOfRecords = 3;
    Trace.trace(Trace.Information, ">>> Filling the Table with " + nbrOfRecords 
        + " records...");
    for (int i = 1; i <= nbrOfRecords ; i++) { 
      tableContext.setValueAt("ACCOUNT_NUMBER", "0007000" + i);
      tableContext.setValueAt("AMOUNT", new Integer(200000 + i));
      tableContext.setValueAt("THE_DATE", new java.sql.Date(98,4,16)); 
      tableContext.setValueAt("DESCRIPTION", "Adding record " + i + " in table...");
      tableContext.setValueAt("RECID", new Integer(i)); 
      tableFormat=new com.ibm.btt.formatter.client.FormatElement();
      tableFormat.setName("tableFormatName");
      // Call the format method with argument the operation context
      Hashtable dataTable1 = new Hashtable();
      dataTable1 = (Hashtable) tableFormat.formatHashTable(tableContext); 
      Trace.trace(Trace.Information, ">>> Adding a record to the database table...");
      table.addRecord(dataTable1); 
    } // End for
    // Display Table content
    Vector aDataVector = null;
    Hashtable aDataHashtable = new Hashtable();
    aDataVector= table.retrieveRecordsMatching("THE_DATE=DATE('1998-05-16')");
    Enumeration aDataVectorEnum = aDataVector.elements();
    while (aDataVectorEnum.hasMoreElements()) { 
      tableFormat.unformatHashTable((Hashtable)aDataVectorEnum.nextElement(), tableContext);
      System.out.println("Record retrieved with identification:" + 
	  tableContext.getValueAt("RECID")); 
    } // End while 
    // Delete record number 1
    table.deleteRecordsMatching("RECID=1"); 
    // Retrieving data of second record
    aDataVector = table.retrieveRecordsMatching("RECID=2");
    if (!aDataVector.isEmpty()) {
      aDataVectorEnum = aDataVector.elements();
      TableFormat.unformatHashTable((Hashtable)aDataVectorEnum.nextElement(), tableContext);
      System.out.println("Record retrieved with identification:" + 
	  tableContext.getValueAt("RECID"));
    } 
    else {
      System.out.println("No records found for this search criteria");
    } 
    tableContext.setValueAt("DESCRIPTION", "Updated description");
    // Updating last Record in the database 
    table.updateRecordsMatching("RECID=(SELECT MAX(RECID) 
	FROM"+table.getTableName()+")",tableContext,"tableFormatName");
  } // End try
  catch (Exception e ) {
    System.out.println(e.getMessage());
    try {
      table.disconnect(); }
    catch (Exception ex){
      System.out.println(ex.getMessage());} 
    return; 
  }
}