IBM Curam – How to implement Dynamic Query

---

Introduction

---

Dynamic Query Implementation is a very important concept in curam. Dynamic queries help reduce the development effort significantly. Using Dynamic Query, developers can retrieve data from various tables using hand-crafted SQL within Java program. Also, developers can take advantage of this concept when input criteria is not fixed and tend to vary over time and condition.

Example: Suppose a Developer wants to read FinancialComponentDtls based on passed FinancialComponentID as comma separated String. This can be achieved using Dynamic queries. Otherwise, without using dynamic query approach, they will end up hitting the Database several times.

Please find various Examples provided below –

 

---

Example

---

We want to read sum of amount based on FinancialComponentID which we can achieve using modeled class easily.

Using Dynamic Query implementation we will implement it.

Step1: Construct SQL using StringBuilder

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT");
        stringBuilder.append(" SUM(AMOUNT)");
        stringBuilder.append(" INTO ");
        stringBuilder.append(" :paymentTotal");
        stringBuilder.append(" FROM");
        stringBuilder.append(" FINANCIALCOMPONENT FD");
        stringBuilder.append(" WHERE");
        stringBuilder.append(" FD.FINANCIALCOMPONENTID");
        stringBuilder.append(" IN  ");
        stringBuilder.append("(");
        int count = 0;
        for(String nfFinancialld : financialCompIDList){
            count++;
            long financialCompID = Long.valueOf(nfFinancialld);
            
            stringBuilder.append(financialCompID);
            if(financialCompIDList.size() < count){
                stringBuilder.append(",");
            }                    
        }
        stringBuilder.append(")");
        
        System.out.println("SQL : "+stringBuilder.toString());

 

Step2: Call Curam OOTB API to execute Dynamic Query –
        TotalAmountStruct totalAmountStruct = new TotalAmountStruct();
        
  totalAmountStruct =     (TotalAmountStruct)DynamicDataAccess.executeNs(TotalAmountStruct.class, null, false, stringBuilder.toString());

totalAmountStruct Struct will contain totalamount which we can use any where using helper classes.

 

Please find full method below –

public Money getTotalFinCompAmount(StringList financialCompIDList){
        
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT");
        stringBuilder.append(" SUM(AMOUNT)");
        stringBuilder.append(" INTO ");
        stringBuilder.append(" :paymentTotal");
        stringBuilder.append(" FROM");
        stringBuilder.append(" FINANCIALCOMPONENT FD");
        stringBuilder.append(" WHERE");
        stringBuilder.append(" FD.FINANCIALCOMPONENTID");
        stringBuilder.append(" IN  ");
        stringBuilder.append("(");
        int count = 0;
        for(String nfFinancialld : financialCompIDList){
            count++;
            long financialCompID = Long.valueOf(nfFinancialld);
            
            stringBuilder.append(financialCompID);
            if(financialCompIDList.size() < count){
                stringBuilder.append(",");
            }                    
        }
        stringBuilder.append(")");
        
        System.out.println("SQL : "+stringBuilder.toString());
        
        TotalAmountStruct totalAmountStruct = new TotalAmountStruct();
        
        totalAmountStrut = (TotalAmountStruct)DynamicDataAccess.executeNs(TotalAmountStrut.class, null, false, stringBuilder.toString());
    
        return totalAmountStrut;
    }