Using webMethods JDBC Adapter Connection in custom Java code

Sometimes, webMethods developers may come across a situation while writing some JDBC related java services where they want to make use of an existing webMethods JDBC adapter connection to interact with DB instead of going with custom implementation. This way, we can leverage some of the good connection management properties that webmethods JDBC connection gives, like Connection pooling, Administration/Management of connections on IS Admin console and many more. This also makes the custom code seamless/ transparent to Admin/Infra/Ops teams as they will still see the connections in usual place on IS admin and removes the headache of maintaining custom connections in a separate place like properties file or database.

In this post, you will see a code sample that shows how to get hold of a “java.sql.Connection” object from an existing JDBC Adapter connection. It uses some classes that are part of WmArt and WmJDBCAdapter libraries. So, the package in which the java service implementing this code sample resides should have a dependency set on WmArt and WmJDBCAdapter packages. Only then Integration server will be able to share the classes while loading and the sample works.

You can download working copy of sample IS package containing the below code with package dependencies set from here.

Update: webMethods JDBC Adapter provides an out of box template to achieve the same thing. When creating an adapter service choose “Execute Service” template. This will take a service name as input and passes in the java.sql.Connection object as input to that service. I suggest this as the recommended way to work on as it is supported by Software AG as opposed to approach using non-public API shown in this article. However, I am still leaving this article open for people who are interested in knowing a way to achieve this in custom manner for whatever reasons.

import com.softwareag.util.IDataMap;
import javax.resource.cci.Connection;
import com.wm.pkg.art.ns.ConnectionDataNode;
import com.wm.pkg.art.ns.ConnectionDataNodeManager;
import com.wm.pkg.art.ns.ConnectionResource;
import com.wm.pkg.art.transaction.ConnectionState;
import com.wm.adapter.wmjdbc.connection.ConnectionInfo;
import com.wm.adk.cci.connection.WmConnection;
import com.wm.adapter.wmjdbc.connection.JDBCConnection;
import java.sql.Statement;
import java.sql.ResultSet;

public static final void getJDBCConnection(IData pipeline) throws ServiceException {
IDataMap ipipe = new IDataMap(pipeline);
String connName = ipipe.getAsString("connectionName");
if(connName != null && !connName.trim().isEmpty()){
try{
ConnectionDataNode node = ConnectionDataNodeManager.getConnectionDataNode(connName);
ConnectionResource resource = null;
if (node != null) {
resource = node.getConnectionResource();
}
Connection connection = null;
ConnectionState connectionState = ConnectionState.getConnectionState();
connection = connectionState.getConnection(resource, null);

WmConnection wmConnection = (WmConnection)connection;
ConnectionInfo cInfo =((JDBCConnection)wmConnection.getManagedConnection()).getConnectionInfo();

/* java.sql.Connection */
java.sql.Connection dbCon = cInfo.getConnection();
/* Above step is the place where we get hold of connection */

Statement stmt = dbCon.createStatement();
String sql;
sql = "SELECT 8 from dual";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt(1);
//Display values
ipipe.put("dual", id);
}
rs.close();
stmt.close();
}
catch(Exception e){
throw new ServiceException(e);
}
}
else{
throw new ServiceException("connectionName is mandatory");
}
}
Advertisements

3 thoughts on “Using webMethods JDBC Adapter Connection in custom Java code

  1. Why do you do it in this very complicated way using not documented classes and methods if there is a special JDBC adapter type for doing exactly this (using an existing connection in Java code)?

    Liked by 1 person

    1. You are right AL. I wasn’t aware of that at the time of writing this code. Only realized at later point that there is one already coming as part of adapter templates. So I agree that must be the way to go as it is clean and simple. I will update this article with this info and still keep the current code snippet leaving the choice of its usage to developers imagination 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s