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