JDBC Mediator for the J2SE Platform

Overview

The JDBC Mediator for the J2SE Platform is a J2SE Component which mediates between a J2SE Component, its clients, and DBMS. For each J2SE Component there exists a corresponding mediator with its own exception class. The mediator receives an input and an output SQL query for the DBMS and a method name to analyze. The mediator sends the SQL input query to the (input) DBMS and then applies the method from the J2SE Component to each row of the input query results. Each result row is written into the (output) DBMS according to the output SQL query.

Fig: A diagram that illustrates how JDBC Mediator intermediates the communication between the J2SE client, the J2SE Component, and the Database Servers.

Connecting to your Database Server

If you wish to connect by JDBC to your Database server in order to run a select query an update statement or a stored procedure you will need to describe the connection with certain properties.

• Driver

  The driver is a Java class provided by your Database vendor that implements the java.sql.Driver interface.

• URL

  This property defines the address of your DBMS, the port number and additional information such as service and database name.

• Username and Password

  For authentication reasons, most connections will not work without providing a user name and a password.

• Additional Properties

  This field allows you to customize even more your JDBC connection as described in the JavaDocs.

We describe these properties individually for best known database systems. Please feel to jump to the section which refers to the particular DBMS you plan to use with our J2SE JDBC Components.

Oracle 9i

We recommend the JDBC thin driver oracle.jdbc.driver.OracleDriver. You may download this driver from http://otn.oracle.com/software/tech/java/sqlj_jdbc/content.html. The Oracle URL has the following format:

       jdbc:oracle:thin:@servername:port:service

where servername is the Internet name of your Oracle DBMS, port is usually 1521 and service is the name of the service you are going to connect to.

IBM DB2

The name of the DB2 driver is COM.ibm.db2.jdbc.net.DB2Driver and can be downloaded off the IBM site at http://www-3.ibm.com/software/data/db2/java. The format of the DB2 URL is:

       jdbc:db2:[servername]:[port][database]

where servername is the Internet name of your DB2 DBMS, port may be ignored and database is the name of the database you plan to connect to.

Microsoft SQL Server

The name of the SQL Server driver is com.microsoft.jdbc.sqlserver.SQLServerDriver and can be downloaded off the Microsoft site at http://msdn.microsoft.com/.... The format of the Microsoft SQL Server URL is:

       jdbc:microsoft:sqlserver:servername:1433

where servername is the Internet name of your SQL Server.

Sybase

The name of the Sybase driver is com.sybase.jdbc.SybDriver and can be downloaded off the Sybase site at http://www.sybase.com/home. The format of the Sybase JDBC URL is:

       jdbc:sybase:Tds:servername:port

where servername is the Internet name of your Sybase DBMS and port is the port number where the Sybase server is running.

J2SE JDBC Components

Most methods implemented by our components accept numeric parameters and return numbers or arrays of numbers. The JDBC Mediator enables these methods to take their numeric parameters from a Database server and return the numbers by writing them back into the same or another Database server.

For every class implementing number-based methods (methods with numeric parameters and numeric return values) there's a class in a sub package called `jdbc'' bearing a similar name. For example, if there's a class called `Volatility'' within a package called `webcab.lib.finance.options'', the corresponding J2SE JDBC class would be named `webcab.lib.finance.options.jdbc.VolatilityJDBC'.

A J2SE JDBC class may be instantiated by specifying all necessary JDBC connection parameters, such as drivers, url, username and password plus the constructing parameters of the underlying J2SE class. Every J2SE JDBC class contains several methods that invoke methods from its underlying J2SE class and apply them directly to your Database tables and/or write the answer back into the same or another Database. These J2SE JDBC methods accept the name of the method as the first parameter and an SQL Query or an array of Java objects for the method's input and output.

The following source code listing illustrates how to use an input/output SQL query JDBC method for a generic J2SE class `webcab.lib.finance.FinancialClass' and a generic method `double calculateAmount(double, double)'.

import webcab.lib.finance.*;
import webcab.lib.finance.jdbc.*;
  ...
    try {
       ...
         /*
          * The first construction parameter (riskFreeInterestRate) is FinancialClass
          * specific. The next parameters define the input and the output
          * DBMS connections by driver, url, username, password and additional
          * properties.
          */
         FinancialClassJDBC jdbcMediator = new FinancialClassJDBC (riskFreeInterestRate,
            "oracle.jdbc.driver.OracleDriver", // Input Connection
            "jdbc:oracle:thin:@inputserver.com:1521:ORACLE",
            "SCOTT", "tiger", null,
            "oracle.jdbc.driver.OracleDriver", // Output Connection
            "jdbc:oracle:thin:@updateserver.com:1521:ORACLE",
            "MIKE", "wolf", null);
            
         jdbcMediator.call ("calculateAmount",
            "SELECT C_ID, SHARES, VALUE FROM TRADES",
            "UPDATE CUSTOMER SET MONEY=? WHERE C_ID=?",
            new int[][] {{2, 1}});
         catch (Exception e) {
          e.printStackTrace ();
        }

The first parameter of the call method represents the FinancialClass method name. The second parameter is the input query, a select query which returns three columns C_ID, SHARES and VALUE. The third parameter is a prepared statement that is meant to write the value returned by calculateAmount in the MONEY field and the C_ID primary key in the where clause. This assignment is described by the last parameter, an array of integer pairs that specifies the index of the IN parameter in the output query and the column number of the input query. Both indices start counting from 1. In our case, the second IN parameter is the second question mark in the update statement and the first input column is C_ID.

Every output update is made according to a primary key C_ID. Even though this is returned by the select statement it is not used for computation by the two-parameter `calculateAmount'' method.