Using JDBC Connection Pool in XI Message Mapping

[Former Member]

Using JDBC Connection Pool in XI Message Mapping

In XI Message Mapping, many time we have needs to lookup values from SQL tables. There are 3 primary ways to execute SQL statements from Message Mapping:

1. Develop a user-function to connect to a datasource and execute the SQL statement.
2. Use a Receiver JDBC Communication Channel to connect to a datasource and execute the SQL statement.
3. Use a J2EE JDBC connection pool to connect to a datasource and execute the SQL statement.

Method 1 is the most inefficient, and the least desirable. For each lookup, a connection to the database will have be made. The resources required to make a database connection maybe greater than executing the SQL statement itself. In addition, userid/password to access the datasource will have to be included in the java source code.

Method 2 provides the most flexibility to the developer, since the developer can create and configure the communication channel in the Integration Directory. However, the number of threads for the JDBC receiver communication channel is limited and may create a performance bottleneck if many scenarios are also using the same communication channel.

Method 3 is the most efficient way and does not require any interface design in the Integration Repository. The connections in the pool are shared by everyone, even non-XI applications. There is no overhead of repeated connections to the datasource. The maximum number of concurrent connections can be dynamically changed. Virtually any SQL statement can be executed. However, some preparation and setup are required.

In this blog, we will go through the procedure is setting up a Message Mapping using method 3. Please note, access to the J2EE Visual Administrator will be required.

This blog will also give 3 datasource configuration examples. One example using SQLServer, and two examples using Oracle.

There are 3 steps involved:

1. Create/configure the JDBC datasource in the J2EE Visual Administrator.
2. Develop a generic java class to access any datasource and execute any SQL statement.
3. Develop a simple user-function in Message Mapping to use the java class from the previous step.

Here are the steps:

1. Create/configure the JDBC datasource in the Visual Administrator.
   1. Login to the J2EE Visual Administrator and go to: Server 0 -> Services -> JDBC Connector

      

   2. Add a JDBC driver.

      

      In this example, I added 2 JDBC drivers: (The vendor-specific JDBC drivers can be downloaded from the vendor's website. Make sure the driver version corresponds to the database version to be accessed.)

      • Oracle requires ojdbc14.jar
      • SQLServer requires msbase.jar, mssqlserver.jar and msutil.jar
   3. Create a JDBC datasource.

      

      Following are 3 examples of datasources:

      • Oracle JDBC Version 1.x (no XA support)

        
        Driver Name: Oracle10 (based on configuration in the previous step)
        JDBC Version: 1.x (no XA support)
        Driver Class: oracle.jdbc.driver.OracleDriver
        Database URL: jdbc:oracle:thin:@localhost:1521:orcl
        Enter valid userid and password for the database

        

      • Oracle JDBC Version 2.0 (with XA support)

        
        Driver Name: Oracle10 (based on configuration in the previous step)
        JDBC Version: 2.0 (with XA support)
        Object Factory: oracle.jdbc.pool.OracleDataSourceFactory
        DataSource Type: XADataSource
        XADS Classname: oracle.jdbc.xa.client.OracleXADataSource

        
        url: jdbc:oracle:thin:@localhost:1521:orcl
        Enter valid userid and password for the database

      • SQLServer JDBC Version 1.x (no XA support)

        
        Driver Name: SQLServer (based on configuration in the previous step)
        JDBC Version: 1.x (no XA support)
        Driver Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
        Database URL: jdbc:microsoft:sqlserver://localhost:1433;databaseName=xirig
        Enter valid userid and password for the database

        

2. Develop a generic java class to access any datasource and execute any SQL statement.

   An example source code is below:

   package com.sap.bl.utils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource;  public class TableLookUp {      public String lookUp(String dataSource, String sqlStmt) {           String value = "Okay";           Connection conn = null;           Context ctx = null;           DataSource ds = null;           Statement stmt = null;           ResultSet rst = null;           try {                 ctx = new InitialContext();                if (ctx == null) {                     value = "Error - No Context";                     return value;                }                 ds = (DataSource) ctx.lookup(dataSource);                if (ds == null) {                     value = "Error - No dataSource: " + dataSource;                     return value;                }            }           catch (Exception e) {                value = e.getMessage();                return value;           }            try {                     conn = ds.getConnection();                     if (conn != null) {                          stmt = conn.createStatement();                          rst = stmt.executeQuery(sqlStmt);                          if (rst.next()) {                               value = rst.getString(1);                          }                          conn.close();                     }            }           catch (Exception e) {                value = e.getMessage();           }           finally {                if (rst != null) {                     try {                          rst.close();                     }                     catch (Exception e) {                          value = e.getMessage();                     }                }                 if (stmt != null) {                     try {                          stmt.close();                     }                     catch (Exception e) {                          value = e.getMessage();                     }                }                if (conn != null) {                     try {                          conn.close();                     }                     catch (Exception e) {                          value = e.getMessage();                     }                }            }            return value;        } }

   Create a ZIP or JAR file and import it as an Imported Archive for mapping.

3. Develop a simple user-function in Message Mapping to use the java class from the previous step.

   

   With the LookUp user-function

   

   Using the test tool, we receive the following result:

   

   The number of rows in "table1" is 2.