How to execute sql statement in oaf controller

How to execute sql statement in oaf controller

 

 In this post , I will share the OAF code to execute Sql statements in the Controller. We often need this kind of things in the Controller to execute the Sql statements to do the comparison of the data on which we can write our custom code so this is quite common Requirment for OA framework developer. To execute the Sql statement from OAF controller first important thing we need Database connection so for this we first create the Handle in the OAF controller with OADBTranaction Bean. Then  to prepare the sql statement in controller the second important things we uses 'PreparedStatement' object of Controller. With this object , we can execute the sql statement in the DB from controller Environment.

 

Here below I am sharing the detail code to execute sql statement in OAF controller.

Number rptHdrId;

rptHdrId = new Number(paramOAPageContext.getDecryptedParameter("ReportHeaderId"));

if (rptHdrId!=null)

Connection conn = paramOAPageContext.getApplicationModule(paramOAWebBean).getOADBTransaction().getJdbcConnection();

String Query = "SELECT NVL(expense_status_code,'NEW') status FROM ap_expense_report_headers_all WHERE report_header_id=TO_NUMBER(:1)";

 

PreparedStatement stmt = conn.prepareStatement(Query);

String RptHdrId = ""+rptHdrId;

stmt.setString(1,RptHdrId);

for(ResultSet resultset = stmt.executeQuery(); resultset.next();)

                                   {

  result = resultset.getString("status");

paramOAPageContext.writeDiagnostics(this, "comparison"+comparison,2);

 if("REJECTED".equals(result))

                                                {

Here below I will write the code to perform some action if we found expense report status ‘Rejected’

                                   }

}