How to execute SQL DML statements in Oracle ADF

Hi,

In some use cases it is required to execute just a simple DML (Insert, Update, Delete) statements in application.  so how to execute those in ADF? I will describe a one way to do this.

The steps are:

1. Create a method in Application Module (using DBTransaction and CallableStatement)
2. Expose that method to client interface
3. Drag and drop the method to your page
4. And optionally you can set the partial triggers and write a code to refresh your iterator bindings.

Here is the sample method in AppModuleImpl.java

  public int increaseSalary(int deptId, int increamentPercent) {
    
      DBTransaction trans = getDBTransaction();

      CallableStatement statement = null;
      int rows = 0;
      
      String plsql =
            " BEGIN " 
          + " UPDATE EMPLOYEES "
          + " SET SALARY = SALARY + (SALARY * ? / 100)"
          + " WHERE DEPARTMENT_ID = ?;"  
          + " END; ";
    
    
      statement = trans.createCallableStatement(plsql, 2);
      try {
          statement.setInt(1, increamentPercent);
          statement.setInt(2, deptId);
  
         rows = statement.executeUpdate();

      } catch (SQLException s) {
          throw new JboException(s);
      } finally {
          try {
              if (statement != null)
                  statement.close();
              return rows;
          } catch (SQLException s) { /* ignore */
          }
      }
  return rows;
  } 

Download the sample code

Watch this video for more info



Happy JDeveloping,
Zeeshan Baig