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. Before requesting the database connection, the application must register and load the specific JDBC driver needed to work with the database.

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
<!-- Operation data definitions -->
<kColl id="myTableOperationData"/>
    <field id="ACCOUNT_NUMBER"/>
    <field id="AMOUNT"/>
    <field id="THE_DATE"/>
    <field id="DESCRIPTION"/>
    <field id="RECID"/>
</kColl>
DSECTXT.XML
<!-- Contexts definitions -->
<context id="myTableOperationContext" type="op" >
    <refKColl refId="myTableOperationData">
    </refKColl>
</context>
DSESRVCE.XML
<!-- Services definition sample file. Note that no column 
tag is used. This means that the data field names match 
the column names in the database table-->
<JDBCTable id="myTableName" autoCommit="true" 
        autoConnect="true"
        databaseURL="jdbc:db2://myhostname:8888/mydatabasename"
        user="myuser" password="mypassword" 
        table="DatabaseTable">
</JDBCTable>
DSEFMTS.XML
<!-- Formats definitions -->
<fmtDef id="tableFormatName">
    <hashtable>
        <fObject dataName="ACCOUNT_NUMBER"/>
        <fObject dataName="AMOUNT"/>
        <fObject dataName="THE_DATE"/>
        <fObject dataName="DESCRIPTION"/>
        <fObject dataName="RECID"/>
    </hashtable>
</fmtDef>

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 static void main(String args[]) throws java.io.IOException, 
     DSEObjectNotFoundException {
  Context tableContext;
  HashtableFormat tableFormat= null;
  // Initializing toolkit for Java
	if(!InitManager.isInitialized()){
				InitManager.reset("file:///c:\\btt\\btt.xml");
			}
  // Working with the table service 
  TableService table = null;
  try { 
    TableContext=ContextFactory.createContext("myTableOperationContext");
    System.out.println(">>> Creating a Table Service Instance...");
    table = (TableService)Service.readObject("myTableName"); 
    // Because of the service definition, no connection to the database
    // is 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= (HashtableFormat)FormatElement.readObject("tableFormatName");
      // Call the format method with argument the operation context
      Hashtable dataTable1 = new Hashtable();
      dataTable1 = (Hashtable) tableFormat.format(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.unformat((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 2st record;
    aDataVector = table.retrieveRecordsMatching("RECID=2");
    if ( !aDataVector.isEmpty()) {
      aDataVectorEnum = aDataVector.elements();
      TableFormat.unformat((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; 
  }
}