JDBC Mediator for the J2EE Platform

Overview

The JDBC Mediator for the J2EE Platform is an EJB component which mediates between an EJB component, it clients and DBMS. For each EJB 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 analysis. The mediator sends the SQL input query to the (input) DBMS and then applies the method from the EJB 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: This diagram illustrates how JDBC Mediator intermediates the communication between the client, the Application Server 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. You will be given the opportunity to use JDBC Mediator with Application Server DataSources and Connection Pools or directly specifying these properties and passing them to the create methods.

• 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 the most well known database systems. Please feel to jump to the section which refers to the particular DBMS you plan to use with our 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-106.ibm.com/developerworks/db2/zones/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.

JDBC Components

Most methods implemented by our enterprise Beans accept numeric parameters and return numbers or arrays of numbers. By using these JDBC methods directly inside a database server without any change in functionality will definitely prove necessary. In this case the database server becomes the DataSource for the input and/or output parameters for every one of these methods.

Remark: What we are going to discuss applies only to EJBs which either implement JDBC or use a particular JDBC implementation.

For every bean implementing number-based methods (methods with numeric parameters and numeric return values) there is a bean in a sub-package called `jdbc'' bearing a similar name. For example, if there's a bean called `FinancialBean'' within a package called `com.webcab.ejb.finance', the corresponding JDBC-implementing component would be `com.webcab.ejb.finance.jdbc.FinancialBeanJDBC'.

The JDBC components are designed to easily transfer to an Application Server JDBC specific tasks that make use of the capabilities of every bean within our enterprise application. The enterprise database performs demanding transactions which take place on the same machine that hosts your EJB components. This allows the client to take advantage of the processing power of your Application Server and minimize bandwidth transfer.

The JDBC beans are created by specifying all necessary JDBC connection parameters, such as drivers, url, username and password plus the enterprise Bean's creation parameters. Every JDBC component contains several methods that invoke methods from their corresponding enterprise Bean and apply them directly to your database tables and/or write the answer back into the same or another DBMS. The JDBC methods accept the name of the enterprise 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 exemplifies how to use an input/output SQL query JDBC method for a generic bean called `com.webcab.ejb.finance.FinancialBean'' and a generic method `double calculateAmount (double, double)'.

  import com.webcab.ejb.finance.FinancialBean;
  import com.webcab.ejb.finance.jdbc.FinancialBeanJDBC;
  ...
   try {
    ...
    */
    * The first creation parameter (riskFreeInterestRate) is FinancialBean
    * specific. The next creation parameters define the input and the output
    * DBMS connections by driver, url, username, password and additional
    * properties.
    */
   FinancialBeanJDBC jdbcBean = home.create (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);

   jdbcBean.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 FinancialBean 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.

As an alternative you may wish to define two DataSources that point to the input and output connections described above. You may then map the corresponding resource references defined inside the ejb-jar.xml deployment descriptor to the JNDI names corresponding to these DataSources. In this case scenario, the previous code would look like this:

  import com.webcab.ejb.finance.FinancialBean;
  import com.webcab.ejb.finance.jdbc.FinancialBeanJDBC;
  ...
   try {
    ...
    /*
     * The only creation parameter is riskFreeInterestRate. Ignore the
     * JDBC specific parameters. The bean will be using the
     * resource references defined inside the {\tt ejb-jar.xml} XML
     * to locate the Input and Output JDBC connections.
     */
    FinancialBeanJDBC jdbcBean = home.create (riskFreeInterestRate);

    jdbcBean.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 ();
    }

Note: The primary key reference can be made across two different database servers a very useful feature provided by this type of JDBC methods.