DB Call in JAVA Compute Node in Message Broker



The main Purpose of this post is to hit a database(in my case DB2) table and retrieve the other details like Company,Salary on issuing the ID as an input.  For this,I have used 

MQInput node-->JavaCompute node-->MQOutput node

 The code in the JCN looks like this:-
.......................................................................
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 MDEAI.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>1234</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>MICROSOFT</COMPANY>
<SALARY>999999</SALARY>
</Response>