(WMB) DB Call in JavaCompute Node

This post shows how to connect to DB in Java compute node & how to construct output message from database result set.
Below diagram shows the database table :

Database (TEST) Schema ( ADMIN) Table (EMP)

Schema :

mxsd

Here is the evaluate method :

public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbMessage inMessage = inAssembly.getMessage();
MbMessage outMessage = new MbMessage();
MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly,outMessage);
try {
copyMessageHeaders(inMessage, outMessage);
MbElement inRootElement = inMessage.getRootElement();
List<MbElement>  idList =  (List) inRootElement.getLastChild().evaluateXPath("/ID");
int id = (Integer)idList.get(0).getValue();

//Creating Output data
MbElement outRootElement = outMessage.getRootElement();
MbElement messageType = outRootElement.getFirstChild().getFirstElementByPath("MessageType");
messageType.setValue("Response");
MbElement parser = outRootElement.createElementAsLastChild("MRM");

// Conventional way of connecting to DB.
Class.forName("com.ibm.db2.jcc.DB2Driver");  //Driver for DB2
String url = "jdbc:db2://localhost:50000/TEST"; // Database name : TEST
 Connection conn = DriverManager.getConnection(url,"username","password"); 

//Connection conn = getJDBCType4Connection("MYDB",JDBC_TransactionType.MB_TRANSACTION_AUTO); 
   // For this need to configure the configurable service.
  Statement stmt = conn.createStatement
  (ResultSet.TYPE_SCROLL_INSENSITIVE,  
   ResultSet.CONCUR_READ_ONLY);                                                 

 ResultSet resultSet = stmt.executeQuery("SELECT COMPANY, SALARY FROM ADMIN.EMP WHERE ID ="+id);

while(resultSet.next())
{
parser.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"COMPANY",resultSet.getString("COMPANY"));
parser.createElementAsLastChild(MbElement.TYPE_NAME_VALUE,"SALARY",resultSet.getInt("SALARY"));
}

out.propagate(outAssembly);

} 
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally {


// clear the outMessage even if there's an exception
outMessage.clearMessage();
}
}


Input :
<Request><ID>834</ID></Request>


Output :
<?xml version="1.0"?>
<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<COMPANY>GALAXY</COMPANY>
<SALARY>900000</SALARY>
</Response>