Fast Call 2 Database 4 DDL, DML, function and Procedure

public String print() {

        System.out.println(Calls2DatabaseHelper.exeucteSql("procedure", "Pro_Get_Employee_Name(?,?)", new String[] { "100" },

                                                           1));

        System.out.println(Calls2DatabaseHelper.exeucteSql("function", "fn_Get_Employee_Name(?)", new String[] { "100" },

                                                           1));

        String sql ="SELECT FIRST_NAME || ' ' ||  LAST_NAME Emp_Name FROM EMPLOYEES WHERE EMPLOYEE_ID = 100";

        System.out.println(Calls2DatabaseHelper.exeucteSql("select", sql, new String []{},

                                                           0));

        sql ="SELECT FIRST_NAME || ' ' ||  LAST_NAME Emp_Name FROM EMPLOYEES WHERE EMPLOYEE_ID = ?";

        System.out.println(Calls2DatabaseHelper.exeucteSql("select", sql, new String []{"100"},

                                                           0));

        sql ="update EMPLOYEES set  LAST_NAME = ? WHERE EMPLOYEE_ID = ?";

        System.out.println(Calls2DatabaseHelper.exeucteSql("update", sql, new String []{"MAhmoud","100"},

                                                           0));
        return "";


    }

public static DBTransaction getConnection() throws SQLException {

        return ADFUtils.getAppImpl().getDBTransaction();

    }

  

    public static String exeucteSql(String operaition, String signature, String[] inValues, int numOutValues) {
        String returnValue = ""
        String begin = " begin ";
        String end = " end; ";
        String sql = "";
        System.out.println(sql);
        if (operaition == "procedure") {
            signature+=";";
              sql = begin + signature + end;
            try (CallableStatement cs = getConnection().createCallableStatement(sql, 0)) {
                for (int i = 1; i <= inValues.length; i++) {
                    cs.setString(i, inValues[i - 1]);
                }
                for (int i = inValues.length + 1; i <= numOutValues + 1; i++) {
                    cs.registerOutParameter(i, OracleTypes.VARCHAR);
                }
                cs.execute();
                returnValue = cs.getString(2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            else  if (operaition == "function") {
            signature+=";";
            signature="?:= "+signature;
            sql = begin + signature + end;
            System.out.println(sql);
            try (CallableStatement cs = getConnection().createCallableStatement(sql, 0)) {
                cs.registerOutParameter(1, OracleTypes.VARCHAR);
                for (int i = 2; i <= inValues.length+1; i++) {
                    cs.setString(i, inValues[i - 2]);
                }
                cs.execute();
                returnValue = cs.getString(1);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        else if(operaition== "select"){
            if(inValues.length>0){
            try( PreparedStatement cs = getConnection().createPreparedStatement(signature, 0);) {
                for (int i = 1; i <= inValues.length; i++) {
                    cs.setString(i, inValues[i - 1]);
                }
                ResultSet rs = cs.executeQuery();
                if (rs.next()) {
                 returnValue =   rs.getString("Emp_Name");
                }
            } catch (SQLException e) {
                e.printStackTrace();
             } 
        }  else{
          Statement stmt;
            try {
                stmt = getConnection().createStatement(0);
                ResultSet rs = stmt.executeQuery(signature);
                if (rs.next()) {
                 returnValue =   rs.getString("Emp_Name");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        }
            else {
                try( PreparedStatement cs = getConnection().createPreparedStatement(signature, 0);) {
                    if(inValues.length>0){
                    for (int i = 1; i <= inValues.length; i++) {
                        cs.setString(i, inValues[i - 1]);
                    }
                    }
                    int numChangedRows = cs.executeUpdate();
                    getConnection().commit();
                    System.out.println("numChangedRows="+numChangedRows);
                } catch (SQLException e) {
                    e.printStackTrace();
                 } 
            }
        return returnValue;
        }

Download Link

http://sameh-nassar.blogspot.com/2010/12/calling-sql-statment-inside-java-code.html

Enum

An enum type is a special data type that enables for a variable to be a set of predefined constants.

• Enums are type-safe.
• You can define methods or fields in an enum definition,
• You need to define enum elements first before any other attribute in an enum class.
• For the enum constructor; only private is permitted.
• The compiler automatically adds some special methods when it creates an enum.
  For example, they have a static values method that returns an array containing all of the values of the enum in the order they are declared. 
• All enums implicitly extend java.lang.Enum. 
• You can not create instance of enums by using new operator in Java because constructor of Enum in Java can only be private.
• Two new collection classes EnumMap and EnumSet are added into collection package to support Java Enum.
• Enum in Java can implement the interface.
•  You can define abstract method and let each instance of Enum to define 

public class EnumTest {
    public enum days {
        STAURDAY,
        SUNDAY
    }
    public static void main(String[] args) {
        days day = days.SUNDAY;
        switch (day) {
        case STAURDAY:
            {
                System.out.println("day=STAURDAY");
                break;
            }
        case SUNDAY:
            {
                System.out.println("day=SUNDAY");
                break;
            }
        }
    }
}

Enum With Private Constructor:

public class EnumTest {
    public enum days {
        STAURDAY(0),
        SUNDAY(1);
        int value;
         private days(int _value){
            value = _value;
        }
    }

    public static void main(String[] args) {
        days day = days.SUNDAY;
        switch (day) {
        case STAURDAY:
            {
                System.out.println("day=STAURDAY="+day.value);
                break;
            }
        case SUNDAY:
            {
                System.out.println("day=SUNDAY="+day.value);
                break;
            }
        }
    }
} 

https://docs.oracle.com/javase/tutorial/java/javaOO/enum.html

Sorting , Filtering

Filtering

• setQuery
  

  public void filter(){ setQuery(getQuery()+ " where department_id=1"); executeQuery();System.out.println(getQuery());
  }

• setWhereClause
  

  - If the view object (VO) has where clause then set where clause will be joined ( anded ) with the actual vo where clause.
  - setWhereClause(null) doesn't effect the actual where clause written inside VO xml but affect only the written in VO implementation class.
  

  public void filter(){
  System.out.println(getQuery());
  setWhereClause("department_id=1");
  System.out.println(getQuery());
  executeQuery();
  System.out.println(getQuery());
  }
  

  Example
  If actual view object contains where clause department_id= 1,then output after set where clause programmatically is:
  

  select ....... where ( department_Name='Administration' ) and ((( department_id= 1 )))

• View Criteria
  - Once we have the view object with the rows populated from the database,we can sort, filter, search with the existing rows in memory without re-querying the database.This will greatly help us to minimize the database round-trip.
  ViewCriteria.CRITERIA_MODE_QUERY  // Uses database.
  ViewCriteria.CRITERIA_MODE_CACHE  // Uses rows in memory without querying the database.
  - After we change the SQL mode, new setting will take effect the next time we call the executeQuery() method.
  

  
  

  
  public void applyViewCriteriaAtRunTime(){
  ViewCriteria vc = createViewCriteria();
  //Try this line when commented and then remove commented to see the diffident output
  //vc.setCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);
  ViewCriteriaRow vcr = vc.createViewCriteriaRow();
  vcr.setAttribute("ManagerId", "200");
  //ViewCriteriaItem jobItem = vcr ensureCriteriaItem("ChannelId");
  //vci.setOperator(JboCompOper.OPER_ON_OR_AFTER);
  //jobItem.setOperator("=");
  //jobItem.getValues().get(0).setValue(channelId);
  vcr.setAttribute("FirstName","LIKE 'R%'");
  vc.addRow(vcr);// same as vc.add(vcr);
  System.out.println(getQuery());
  applyViewCriteria(vc);
  System.out.println(getQuery());
  executeQuery();
  System.out.println(getQuery());
  }
  

Test Case
Put two instances and apply view criteria for one of them by pressing edit, we see that:

The applied view criteria effects only the view that we applied the view criteria on it.

• When you choose Database:
  New row created in first instance  will be added also to second  rowset due to the "association consistency".
  New row created in 2nd instance  will  Not be added also to second  rowset. 

• When you choose In Memory:

New row created in first instance  will be added also to second  rowset due to the "association consistency"( better naming for this "new row consistency" )
New row created in 2nd instance  will  be  added  to the 1st if it matches the criteria.

Maintaining New Row Consistency in View Objects Based on the Same Entity
When multiple instances of entity-based view objects in an application module are based on the same underlying entity object, a new row created in one of them can be automatically added (without having to re-query) to the row sets of the others to keep your user interface consistent or simply to consistently reflect new rows in different application pages for a pending transaction.

In-memory filtering with RowMatch
Don't use RowMatch if you have the option to filter the values at database level.
In-memory filtering is much slower than filtering done at the database layer.
Sorting

• setSortBy

Used when you want to sort transient attributes because it is done in memory, If you have table built using a transient ViewObject , a click on the sort icon for a column would call ViewObjectImpl::setSortBy(String sortBy) to perform in memory sorting.

 public void sortInMemory(){ 
        setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
        setSortBy("ViewObjectAttributeName");   // setOrderByClause("Table_Column_Name")
        executeQuery();
        System.out.println(getQuery());
        System.out.println(getWhereClause());
}

http://bpetlur.blogspot.com/2013/11/adf-view-criteria-execution-modes.html