Preface
Recently I came across a use case having to access MongoDB from IBM Integration Bus v9(henceforth referred as IIB) . MongoDB does not have columns or rows like RDBMS. MongoDB is a NoSQL DB which unlike a RDBMS stores data in form of JSON documents in Collections(similar to tables).
IIB does not have a native support for MongoDB unlike a RDMBS like Oracle or IBM DB2 which allow ODBC connectivity. In this post I will try to cover how I have gone about working on accessing MongoDB, matching documents betweend different collections via keys and retrieval.
IIB does not have a native support for MongoDB unlike a RDMBS like Oracle or IBM DB2 which allow ODBC connectivity. In this post I will try to cover how I have gone about working on accessing MongoDB, matching documents betweend different collections via keys and retrieval.
Driver to access MongoDB and IIB Project
With IIB not having a native ODBC connectivity for MongoDB, we used the java driver to access MongoDB. The driver can be found at the following link. Download the driver and import the jar file into your project-in my case it was a Message Flow Application. The driver should be shown in your project as shown below.
Next up create a simple message flow that will access the MongoDB. For simplicity I am using a HTTPRequest and a HTTPReply as the input and output with a Java Compute node that will access the MongoDB. The URI is specified as '/MongoDBQuery'.
I have renamed the java class for the JCN as 'AccessMongoDB_JCN' and now double click to open the JCN to open the class.
The image is self describing about the properties for the class. The package 'com.MongoDB' has been created to encompass all the java classes that we will use. The JCN will be constructing a response message class for the query so I have selected 'Modifying message class' and click Next.
Specify the MongoDB java driver file.
Click on Next and it will show up the project in this case I have kept the name default 'MFA_MongoDBAccessJava' and click finish to complete the task.
The project should look like this.
The MFA_MongoDBAccessJava project is created and can be viewed from the Java perspective. Open the Java perspective to familiarize yourself. The java project may not have the MongoDB jar file in the project reference so add the jar file to the project reference.
It is always a good practice to create a Util class in your Java project to separate the actual code that does the business task-in this case query- from the detail tasks-connecting the DB, querying the DB, fetching the DB and returning the data. So create a MongoDBUtil.java class in the 'MFA_MongoDBAccessJava' project and it will encompass the major shareable code.
Before we move any further I would like to call out this link which will talks about a simple helloworld application access to MongoDB. If the use case is all about accessing MongoDB and getting data this link should suffice your needs.
Note: This use case uses MongoDB with SSL connectivity. This would require that the certificate assigned to the MongoDB should be available in the trust keystore of the JVM being used. Failure to do so will result in SSL handshake error. To import the certificate in the trust store I used porticle and the following instructions to go with that.
Connecting to MongoDB
First up is the method to connect to MongoDB.
public static MongoClient mongoClient = null;
public static DB getConnection(String dbHost, String dbName,
String UserName, String PassWord) throws UnknownHostException {
try {
if (mongoClient == null) {
String[] DbServer = dbHost.split("/");
String replicaSet = DbServer[0];
String[] DbServerSeeds = DbServer[1].split(",");
String seed1 = DbServerSeeds[0];
String seed2 = DbServerSeeds[1];
MongoClientURI uri = new MongoClientURI("mongodb://" + UserName
+ ":" + PassWord + "@" + seed1 + "," + seed2
+ "/?replicaSet=" + replicaSet
+ "&ssl=true&authSource=" + dbName
+ "&authMechanism=MONGODB-CR");
mongoClient = new MongoClient(uri);
}
} catch (Exception e) {
try {
throw new MbRecoverableException(null, "MongoConnectivity",
"MongoDB Application Exception", e.getMessage(), "2951",
null);
} catch (Exception e1) {
e.printStackTrace();
}
}
return mongoClient.getDB(dbName);
}
We are using the connection mechanism for a replicaset and has two seeds as part of the set. The MongoClient object is kept alive while we connect and interact with the database and will be used from the method that will query the database. The dbHost variable is of the following format -
dbHost = "ReplicaSetName/Server01Name:27017,Server02Name:27017,Server03Name:27017";
Querying MongoDB
Now that we have the MongoClient object available we move to querying the MongoDB. The following method queries the Application collection with the supplied App ID -
public String getApplication(String repSet, String appID) {
boolean ApplicationExists = false;
String ApplicationObject = "{ \"ApplicationHierarchy\" :[";
DB DbConnection_Obj = null;
String NativeObj_AppId = "";
String ApplicationJSON = null;
try {
// Establishing DB Connection
MongoClient mongoClient = getMongoClient(repSet);
DbConnection_Obj = mongoClient.getDB("SystemDB");
DBCollection Application_Collection = DbConnection_Obj
.getCollection("Application");
JSONObject Application_JObject = null;
BasicDBObject Application_Query = null;
DBCursor Application_Query_Cursor = null;
// CONDITION 1 : Querying the "Application" Collection by APP
// ID
Application_Query = new BasicDBObject("ApplicationID", appID);
Application_Query_Cursor = Application_Collection
.find(Application_Query);
if (Application_Query_Cursor.count() == 0) {
ApplicationExists = false;
ApplicationObject = ApplicationObject
+ "{ \"ERROR\" : { \"Application\" : { \"Error\" : \"Application ID does not exist in Database: "
+ appID + "\"}}}";
} else {
ApplicationExists = true;
Application_JObject = new JSONObject(Application_Query_Cursor
.next().toMap());
ApplicationJSON = "{ \"Appplication\" :"
+ Application_JObject.toString() + "},";
Application_Query_Cursor.close();
}
} catch (Exception e) {
e.printStackTrace();
}
return ApplicationObject;
}
In the above method, an 'appID' is passed to the method getApplication. In this to return the following object we would have to pass the ApplicationID as 296.
{
"_id" : {
"time" : 1436482668000,
"new" : false,
"inc" : 339207661,
"timeSecond" : 1436482668,
"machine" : 100319251
},
"ID" : 1,
"CreatedDate" : 1436482668000,
"UpdatedDate" : 1436545569479,
"CreatedUser" : "John Doe",
"UpdatedUser" : "John Doe",
"Name" : "IBM Sterling Order Management System",
"Description" : "IBM Sterling Order Management System",
"ApplicationID" : "296",
"SystemCode" : "OMS",
"Owner" : {
"Domain" : "Retail",
"Name" : "IBM"
}
}
That completes the basic query and return of the collection.
Relational check and ObjectMapper
The followup on this use is to search for a hierarchical object across two collections. Now that we have the Application object and if we need to search in User collections for the prospective users for this application. The object in User collection are related by the '_id' field in the Application JSON to the '_id' field in the Application Item of the following object in User collection. An example of the User JSON object -
{
"_id" : {
"time" : 1436483596000,
"new" : false,
"inc" : 339207665,
"timeSecond" : 1436483596,
"machine" : 100319251
},
"ID" : 1,
"CreatedDate" : -62135596800000,
"UpdatedDate" : 1436585563718,
"CreatedUser" : "Jake Doe",
"UpdatedUser" : "Jake Doe",
"Name" : "E-Commerce",
"Description" : "E-Commerce project team",
"Owner" : "James Hunt",
"LicenseStart" : "01-Jan-2010",
"LicensePaid" : "Yes",
"Application" : {
"_id" : {
"time" : 1436482668000,
"new" : false,
"inc" : 339207661,
"timeSecond" : 1436482668,
"machine" : 100319251
}
},
"Infrastructure" : {
"RequestedDate" : 1435723200000,
"ChangeOrderNumber" : "10000",
"CreatedDate" : 1435723200000
}
}
For this change the else block of the aforementioned getApplication method will undergo a change and be as follows -
} else {
ApplicationExists = true;
Application_JObject = new JSONObject(Application_Query_Cursor
.next().toMap());
ApplicationJSON = "{ \"Appplication\" :"
+ Application_JObject.toString() + "},";
NativeObj_AppId = Application_JObject.get("_id").toString();
// We have the application, now look for the User for that
// application//
Application_JObject = null;
Application_Query = null;
Application_Collection = null;
Application_Query_Cursor.close();
String userString = new String();
userString = getUser(DbConnection_Obj, NativeObj_AppId);
ApplicationJSON = ApplicationJSON + userString;
ApplicationObject = ApplicationObject + ApplicationJSON;
ApplicationObject = ApplicationObject + "]}";
Application_Query_Cursor.close();
}
The getUser method will be as follows -
StringBuffer userJSONStrBuff = new StringBuffer();
try {
ObjectMapper om = new ObjectMapper();
Map<String, Object> inputMap = null;
Map<String, Object> outputMap = null;
DBCursor User_Query_Cursor = null;
JSONObject UserJSONObject = null;
JSONObject applnJSONObject = null;
JSONObject idJSONObject = null;
boolean foundUser = false;
inputMap = (Map<String, Object>) (om.readValue(appID, Map.class));
DBCollection User_Collection = DbConnection_Obj
.getCollection("User");
User_Query_Cursor = User_Collection.find();
userJSONStrBuff.append("{ \"User\": [");
while (User_Query_Cursor.hasNext()) {
UserJSONObject = new JSONObject(User_Query_Cursor.next()
.toMap());
applnJSONObject = new JSONObject(UserJSONObject.get(
"Application").toString());
idJSONObject = applnJSONObject.getJSONObject("_id");
outputMap = (Map<String, Object>) (om.readValue(
idJSONObject.toString(), Map.class));
if (inputMap.equals(outputMap)) {
if (foundUser) {
userJSONStrBuff.append(",");
userJSONStrBuff.append(UserJSONObject.toString());
} else {
userJSONStrBuff.append(UserJSONObject.toString());
foundUser = true;
}
}
}
userJSONStrBuff.append("]},");
} catch (Exception e) {
try {
throw new MbRecoverableException(null, "getSubcription()",
"Application Exception", e.getMessage(), "2951", null);
} catch (Exception e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
return userJSONStrBuff;
}
Before we get to ahead of ourselves, the getUser method uses the Jackson APIs. We need them when we try to match the '_id' between the two collections as it is made of a composite keys like this -
"_id" : {
"time" : 1436483596000,
"new" : false,
"inc" : 339207665,
"timeSecond" : 1436483596,
"machine" : 100319251
}
Using Jackson APIs we externalize the requirement to match the MongoDB created '_id' element. The ObjectMapper along with Map class is used to construct the '_id' and then do the matching between the two JSON Objects.
Note: IBM Integration Bus v10 FP6 now has support for MongoDB access using the LoopbackNode. A detailed work is available in here.
Update, using loopback node in IIB v10
Interacting with MongoDB using IBM Integration Bus LoopBackRequest node - https://developer.ibm.com/integration/blog/2016/09/02/interacting-with-mongodb-using-ibm-integration-bus-loopbackrequest-node/
Basic Introduction to loopback node with MongoDB - https://developer.ibm.com/integration/blog/2016/09/01/basic-introduction-to-using-the-loopbackrequest-node/
Interacting with MongoDB using IBM Integration Bus LoopBackRequest node - https://developer.ibm.com/integration/blog/2016/09/02/interacting-with-mongodb-using-ibm-integration-bus-loopbackrequest-node/
Using some of the more advanced features of the LoopBackRequest node - https://developer.ibm.com/integration/blog/2016/09/13/using-some-of-the-more-advanced-features-of-the-loopbackrequest-node/
