JDeveloper R12 Calling SQL Statement Inside Java Code

Calling SQL Statment Inside Java Code

One of the important way for developing is calling a sql statment inside your java code and I will explain how to do this:
1- call a select statment
asume we want to make a function take department Id and return its name so we will make this function

	public String getDeptName(int deptId)
	{
	PreparedStatement stat = null;
	ResultSet rs = null;
	try
	{
	String sql = "select dept_name from departments where dept_id=" + deptId;
	stat = getAm().getDBTransaction().createPreparedStatement(sql, 1);
	rs = stat.executeQuery();
	if (rs.next())
	{
	return rs.getString(1);
	}
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	if (rs != null)
	try
	{
	rs.close();
	}
	catch (Exception e)
	{
	}
	if (stat != null)
	try
	{
	stat.close();
	}
	catch (Exception e)
	{
	}
	}
	return null;
	}

2- Call an updatable statment as (Create, Insert, Update and Delete)asume we want to make a function take department id and delete it

	public void deleteDepartment(int deptId)
	{
	PreparedStatement stat = null;
	try
	{
	String sql = "DELETE FROM Dept WHERE dept_id=" + deptId;
	stat = getAm().getDBTransaction().createPreparedStatement(sql, 1);
	stat.executeUpdate();
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	if(stat != null)
	try
	{
	stat.close();
	}
	catch(Exception e) { }
	}
	return null;
	}

where getAm() is a method return your Application Module as :

	public AppModuleImpl getAm()
	{
	AppModuleImpl am = (AppModuleImpl) ADFUtils.getApplicationModuleForDataControl("AppModuleDataControl");
	return am;
	}

If you want to use java connection (don't want to use AppModule) you can use something like this:

	private static Connection getConnection()
	{
	Connection connection = null;
	try
	{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "hr");
	}
	catch (Exception e)
	{
	}
	return connection;
	}
	public static void InsertDep()
	{
	PreparedStatement stat = null;
	Connection conn = null;
	try
	{
	conn = getConnection();
	String sql ="Insert into DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values (300,'Security',200,1700)";
	stat = conn.prepareStatement(sql);
	stat.executeQuery();
	conn.commit();
	}
	catch (Exception e)
	{
	e.printStackTrace();
	}
	finally
	{
	try
	{
	stat.close();
	conn.close();
	}
	catch (Exception e)
	{
	// TODO: Add catch code
	e.printStackTrace();
	}
	}
	}

Oracle ADF Prepared Statements for Calling Select Statement

Oracle ADF Prepared Statements for Calling Select Statement

public boolean checkForEmployeeRec(String fname,String lname)
{
   PreparedStatement cps=getDBTransaction().createPreparedStatement("select * from emp where firstname=? and lastname=?",0);
Boolean result = Boolean.FALSE;
ResultSet resultSet = null;
try {
  cps.setString(1,fname);
  cps.setString(2,lname);
  resultSet = cps.executeQuery();
  if (resultSet.next() )
  {
     result = Boolean.TRUE;
  }
 }catch (SQLException e) {
 e.printStackTrace();
} finally {
   try {
    resultSet.close();
    cps.close();
  }catch(SQLException sqle) {
     sqle.printStackTrace();
}
}
return result;
}

public void insertStudent(String name) {

String strQuery = "insert into Student values(std_seq.nextval,'"+name+"')";

DBTransaction dbTransaction = this.getDBTransaction();

Statement stmt = dbTransaction.createStatement(1);

int result=0;

try {

result = stmt.executeUpdate(strQuery);
dbTransaction.commit();

} catch (SQLException e) {
e.printStackTrace();
}
}

Test1

1. Selected row value

2. Select Query result

//AM Method

    public Integer executeQuery(String query) {

            ResultSet rs;

            Number departmentId =

                ((DepartmentsVORowImpl)(this.getDepartments2().getCurrentRow())).getDepartmentId();

            System.out.println("### departmentId ="+departmentId+" ###");

            System.out.println("*** Calling Start AM Query *****"+query);

            Integer code = null;

            try {

                rs = getDBTransaction().createStatement(0).executeQuery(query);

                if (rs.next()) {

                    code = ((BigDecimal) rs.getObject(1)).intValue();

                }

                rs.close();

                System.out.println("*** Calling End AM Query ***** code="+code);

                return code;

            } catch (SQLException e) {

                throw new JboException(e);

            }

        }

// Bean Method for Page

    public String b3_action() {

        // Get bindings associated with the current scope, then access the one that we have assigned to our table - e.g. OpenSupportItemsIterator

        DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();

        DCIteratorBinding dcItteratorBindings = bindings.findIteratorBinding("Departments2Iterator");

        // Get an object representing the table and what may be selected within it

        ViewObject voTableData = dcItteratorBindings.getViewObject();

        // Get selected row

        Row rowSelected = voTableData.getCurrentRow();

        // Display attribute of row in console output - would generally be bound to a UI component like a Label and or used to call another proces

        Object Odeptno=rowSelected.getAttribute("DepartmentId");

        int ddeptno=Integer.parseInt(Odeptno.toString());

        System.out.println("*** Department Number ="+ddeptno+" ***");

        BindingContainer bindings1 = getBindings();

        OperationBinding operationBinding = bindings1.getOperationBinding("executeQuery");

        operationBinding.getParamsMap().put("query","select count(*) from employees\n" +

        "where department_id="+ddeptno);

        operationBinding.execute();

        int result=0;

        if (operationBinding.getResult() != null) {

                      result = (Integer) operationBinding.getResult(); // cast to the expected result type

           }

         System.out.println("Result is2 : "+result);

        return null;

    }

How to progrmatically List of Values in SelectOneChoice

This blog explains about how to programatically populate list of values dynamically in a <af:SelectOneChoice> component


Step1 : Drag <af:selectOneChoice> component from component pallet to page

Step2 : JSFF - Bind <f:selectItems> to a backing bean method as shown below.

 <af:selectOneChoice value="#{bindings.CustomerID.inputValue}"
             label=""
             required="#{bindings.CustomerID.hints.mandatory}"
             shortDesc="#{bindings.CustomerID.hints.tooltip}"
             id="cusID" autoSubmit="true"
             valuePassThru="true">
       <f:selectItems 
                value="#{pageFlowScope.EquipmentPoolBean.customerList}"
                id="si2"/>
       </af:selectOneChoice>
            

 Step2:  Implement corresponding backing bean method to return List<SelectItem> as shown below

   

      

   public List getCustomerList()

  {

// Prepare list of values based on your requirement

    List customerList = new ArrayList();

    customerList.add(new SelectItem("CUST1","Customer1");

    customerList.add(new SelectItem("CUST2","Customer2");

    customerList.add(new SelectItem("CUST3","Customer3");

    return customerList;

  }

      

af|inputListOfValues{

 -tr-stretch-search-dialog: true;

}

af|inputComboboxListOfValues{

 -tr-stretch-search-dialog: true;

}

LOV based VO.xml

ListRangeSize="10"

// in the context of a backing bean
        Object data = AdfFacesContext.getCurrentInstance().getPageFlowScope().get("objectID");

public void insertRegionListener(ActionEvent actionEvent) {

    // GET A METHOD FROM PAGEDEF AND EXECUTE IT

    // get the binding container

    BindingContainer bindings =

        BindingContext.getCurrent().getCurrentBindingsEntry();

    // get an ADF attributevalue from the ADF page definitions

    AttributeBinding attrId =

        (AttributeBinding)bindings.getControlBinding("newRegionId1");

    Integer id = (Integer)attrId.getInputValue();

    AttributeBinding attrName =

        (AttributeBinding)bindings.getControlBinding("newRegionName1");

    String name = (String)attrName.getInputValue();

    // get an Action or MethodAction

    OperationBinding method = bindings.getOperationBinding("insertRegion");

    if (method == null) {

        FacesMessage msg =

            new FacesMessage(FacesMessage.SEVERITY_ERROR, "Method insertRegion not found!",

                             "");

        FacesContext.getCurrentInstance().addMessage(null, msg);

        return;

    }

    // if there are parameters to set...

    Map paramsMap = method.getParamsMap();

    paramsMap.put("id", id);

    paramsMap.put("name", name);

    // execute the method

    method.execute();

    List errors = method.getErrors();

    if (!errors.isEmpty()) {

        Exception e = (Exception)errors.get(0);

        FacesMessage msg =

            new FacesMessage(FacesMessage.SEVERITY_ERROR, e.getMessage(),

                             "");

        FacesContext.getCurrentInstance().addMessage(null, msg);

        return;

        // handle errors here errors is a list of exceptions!

    }

    // Get a attribute value of the current row of iterator

    DCBindingContainer dcBindings = (DCBindingContainer)bindings;

    DCIteratorBinding iterBind =

        (DCIteratorBinding)dcBindings.get("RegionsLovIterator");

    iterBind.executeQuery();

    // gat the form for refresh

    UIComponent ui = (UIComponent)actionEvent.getSource();

    ui = ui.getParent();

    ResetUtils.reset(ui);

    attrId.setInputValue(null);

    attrName.setInputValue(null);

    // PPR refresh a jsf component

    AdfFacesContext.getCurrentInstance().addPartialTarget(ui);

}

package com.prateek.adf.oracle;

import java.util.ArrayList;
import java.util.List;

import javax.faces.model.SelectItem;

public class BackingBeanAdf {

      private List<SelectItem> programmaticallyLOV;
    private String selectValue;

       public void setProgrammaticallyLOV(List<SelectItem> programmaticallyLOV) {
        this.programmaticallyLOV = programmaticallyLOV;
    }

    public List<SelectItem> getProgrammaticallyLOV() {
        if(programmaticallyLOV==null){
          programmaticallyLOV = new ArrayList<SelectItem>();
          programmaticallyLOV.add(new SelectItem("SUNDAY", "SUNDAY"));
          programmaticallyLOV.add(new SelectItem("MONDAY", "MONDAY"));
          programmaticallyLOV.add(new SelectItem("TUESDAY", "TUESDAY"));
          programmaticallyLOV.add(new SelectItem("WEDNESDAY", "WEDNESDAY"));
          programmaticallyLOV.add(new SelectItem("THURSDAY", "THURSDAY"));
          programmaticallyLOV.add(new SelectItem("FRIDAY", "FRIDAY"));
          programmaticallyLOV.add(new SelectItem("SATURDAY", "SATURDAY"));
        }
        return programmaticallyLOV;
    }

    public void setSelectValue(String selectValue) {
        this.selectValue = selectValue;
    }

    public String getSelectValue() {
        return selectValue;
    }
}
 Programmatically populate the af:selectOneChoice component in Oracle ADF?

Step1

Create a Java List of SelectItems and create getter setter for that as below

    List<SelectItem> customList;

    public void setCustomList(List<SelectItem> customList) {

        this.customList = customList;

    }

    public List<SelectItem> getCustomList() {

        return customList;

    }

Step2

Modify the getter method with the logic to return the list of items

    public List<SelectItem> getCustomList() {

        if (customList == null) {

            customList = new ArrayList<SelectItem>();

            customList.add(new SelectItem("Value 1","Label 1"));

            customList.add(new SelectItem("Value 2","Label 2"));

            customList.add(new SelectItem("Value 3","Label 3"));

            customList.add(new SelectItem("Value 3","Label 4"));

            customList.add(new SelectItem("Value 5","Label 5"));

        }

        return customList;

    }

Step3 

Configure the jsff/jspx as below to refer the select items from the bean getter method created above

 <af:selectOneChoice label="Custom List" id="soc1">

     <f:selectItems value="#{CustomerBean.customList}" id="si1"/>

</af:selectOneChoice>

public void buttonPressed(ActionEvent actionEvent) {

// Get the binding

BindingContainer bindings =

BindingContext.getCurrent().getCurrentBindingsEntry();

// Get the sepecific list binding

JUCtrlListBinding listBinding =

(JUCtrlListBinding)bindings.get("DepartmentId");

// Get the value which is currently selected

Object selectedValue = listBinding.getSelectedValue();

System.out.println(selectedValue);

}

han in versionValueChangeListener you'll access the map something like this :

myMap.get(valueChangeEvent.getNewValue().toString());

<af:selectOneChoice value="#{bindings.Deptno.inputValue}" label="Select Department"

                            required="true" shortDesc="#{bindings.Deptno.hints.tooltip}"

                            id="soc1" autoSubmit="true">

          <f:selectItems value="#{bindings.Deptno.items}" id="si1"/>

</af:selectOneChoice>

<af:outputText value = "Selected Value: #{bindings.Deptno.attributeValue}" id="ot1" partialTriggers="soc1"/>

<af:outputText value = "Display Value: #{bindings.Deptno.selectedValue ne ' ' ? bindings.Deptno.selectedValue.attributeValues[1] : ''}" id="ot2" partialTriggers="soc1"/>

public void valueChanged(ValueChangeEvent valueChangeEvent) {

    this.setValueToEL("#{bindings.Deptno.inputValue}", valueChangeEvent.getNewValue()); //Updates the model

    System.out.println("\n******** Selected Value: "+resolveExpression("#{bindings.Deptno.attributeValue}"));

    System.out.println("\n******** Display Value: "+resolveExpression("#{bindings.Deptno.selectedValue ne ' ' ? bindings.Deptno.selectedValue.attributeValues[1] : ''}"));

}

public Object resolveExpression(String el) {      

    FacesContext facesContext = FacesContext.getCurrentInstance();

    ELContext elContext = facesContext.getELContext();

    ExpressionFactory expressionFactory =  facesContext.getApplication().getExpressionFactory();        

    ValueExpression valueExp = expressionFactory.createValueExpression(elContext,el,Object.class);

    return valueExp.getValue(elContext);

}

public void setValueToEL(String el, Object val) {

    FacesContext facesContext = FacesContext.getCurrentInstance();

    ELContext elContext = facesContext.getELContext();

    ExpressionFactory expressionFactory =   facesContext.getApplication().getExpressionFactory();

    ValueExpression exp = expressionFactory.createValueExpression(elContext, el, Object.class);

    exp.setValue(elContext, val);

}

public void onSubtypLvllTypValChnge(ValueChangeEvent valueChangeEvent) {

        valueChangeEvent.getComponent().processUpdates(FacesContext.getCurrentInstance());
        Map p = ((UIComponent)valueChangeEvent.getSource()).getAttributes();
        System.out.println("Selected Value"+p.get("rowIndexVal"));
  }

BindingContainer bindings =BindingContext.getCurrent().getCurrentBindingsEntry();
JUCtrlListBinding listBinding =(JUCtrlListBinding)bindings.get("DepartmentId");
Row selectedValue = (Row) listBinding.getSelectedValue();
System.out.println(selectedValue.getAttribute("DepartmentId"));

CREATE OR REPLACE FUNCTION FN_GET_EMPNAME(EMP_NO NUMBER)

RETURN VARCHAR2 IS

   EMP_NAME VARCHAR2(50) := 'N';

BEGIN

   SELECT FIRST_NAME||' '||LAST_NAME into EMP_NAME

   FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;

   RETURN EMP_NAME;

END;

import java.sql.CallableStatement;

import java.sql.SQLException;

import java.sql.Types;

import oracle.jbo.JboException;

    /**Method to call Database function

     * @param sqlReturnType (Return type of Function)

     * @param stmt (Function Name with Parameters)

     * @param bindVars (Parameter's Value)

     * @return

     */

    protected Object callStoredFunction(int sqlReturnType, String stmt, Object[] bindVars) {

        CallableStatement cst = null;

        try {

            //Creating sql statement

            cst = this.getDBTransaction().createCallableStatement("begin ? := " + stmt + ";end;", 0);

            //Register dataType for return value

            cst.registerOutParameter(1, sqlReturnType);

            //Pass input parameters value

            if (bindVars != null) {

                for (int z = 0; z < bindVars.length; z++) {

                    cst.setObject(z + 2, bindVars[z]);

                }

            }

            cst.executeUpdate();

            //Finally get returned value

            return cst.getObject(1);

        } catch (SQLException e) {

            throw new JboException(e.getMessage());

        } finally {

            if (cst != null) {

                try {

                    cst.close();

                } catch (SQLException e) {

                    e.printStackTrace();

                }

            }

        }

    }

  /**Method to call stored PL/SQl function to get Employee Name

     * @param empId

     * @return

     */

    public String getEmployeeName(Integer empId) {

        String empNm = "No Employee found";

        Object empName = callStoredFunction(Types.VARCHAR, "FN_GET_EMPNAME(?)", new Object[] { empId });

        if (empName != null) {

            empNm = empName.toString();

        }

        return empNm;

    }

CREATE OR REPLACE PROCEDURE PROC_GET_EMPNAME(EMP_NO IN NUMBER, F_NAME OUT VARCHAR2,L_NAME OUT VARCHAR2) IS

BEGIN

   SELECT FIRST_NAME INTO F_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;

    SELECT LAST_NAME INTO L_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_NO;

END;

/**Method to call Database function

     * @param stmt

     * @param bindVars

     * @return

     */

    protected void callStoredProcOut(String stmt, Object[] bindVars) {

        CallableStatement st = null;

        try {

          //Creating sql statement

            st = this.getDBTransaction().createCallableStatement("begin " + stmt + ";end;", 0);

            //  Set the bind values of the IN parameters (Employee Id is Input parameter here)

            st.setObject(1, bindVars[0]);

           //Register out parameters and their types (In this case procedure has 2 out parameters)

            st.registerOutParameter(2, Types.VARCHAR);

            st.registerOutParameter(3, Types.VARCHAR);

            // Execute the statement

            st.executeUpdate();

           // Print Return Values of out parameters

            System.out.println("First Name-" + st.getString(2) +" Last Name-"+st.getString(3) );

        } catch (SQLException e) {

            throw new JboException(e.getMessage());

        } finally {

            if (st != null) {

                try {

                    st.close();

                } catch (SQLException e) {

                }

            }

        }

    }

//Call PL/SQL procedure using this helper method

    public void callStoredProcedure() {

       //Here we will pass only input parameter but write procedure signature for all parameters (IN and OUT)

       callStoredProcOut("PROC_GET_EMPNAME(?,?,?)", new Object[] { 110 });

    }

Create or Replace Procedure

greeting_proc(text_param IN VARCHAR2, msg_text OUT VARCHAR2) IS

BEGIN

  msg_text:='Hello '||text_param;

END;

public void callGreetingsFunction() {

 CallableStatement st=null;

 try {

  // String sql = "begin greeting_proc (text_param=?,msg_text=?); end;";

   String sql = "begin greeting_proc (:text_param,:msg_text); end;";

   st=getDBTransaction().createCallableStatement(sql, this.getDBTransation().DEFAULT);

   st.setObject("text_param","Scott");

   st.registerOutParameter("msg_text",Types.VARCHAR);

   st.execute();

   sop("String="+(String)st.getObject("msg_text"));

  } catch(SQLException e) {

    throw new JboException(e);

  }

  finally

  {

    if(st!=null) {

       try {

           st.close();

           }

           catch(SQLException e) {}

     }

   }

}