Thursday, December 2, 2010

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



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




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




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


7 comments:

  1. As in your post used getAm().getDbtConnection()
    how we can create and wht is it Can you expain.

    ReplyDelete
  2. Hi Suresh,
    getAm() is a method that return your Application Module. In getAm() method you should write your Data Control Name as "AppModuleDataControl" so the method will be
    public AppModuleImpl getAm()
    {
    AppModuleImpl am =
    (AppModuleImpl) ADFUtils.getApplicationModuleForDataControl("AppModuleDataControl");
    return am;
    }

    and about this line :
    stat = getAm().getDbtConnection().createPreparedStatement(sql, 1);

    I modified it to
    stat = getAm().getDBTransaction().createPreparedStatement(sql, 1);

    because Application Module has a DBTransaction Not DbtConnection.

    ReplyDelete
  3. Dear Sameh,
    Nice post,
    But I 've problem with

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

    I put this method in the AppModuleImpl code
    but I got error within
    ADFUtils
    Not found !!

    ReplyDelete
    Replies
    1. Hi,
      ADFUtils class comes with Fusion Order Demo application you can download application from
      http://www.oracle.com/technetwork/developer-tools/jdev/index-095536.html
      and take ADFUtils.java class. any where you this method
      public static ApplicationModule getApplicationModuleForDataControl(String name)
      {
      FacesContext facesContext = getFacesContext();
      Application app = facesContext.getApplication();
      ExpressionFactory elFactory = app.getExpressionFactory();
      ELContext elContext = facesContext.getELContext();
      ValueExpression valueExp = elFactory.createValueExpression(elContext, "#{data." + name + ".dataProvider}", Object.class);
      return (ApplicationModule) valueExp.getValue(elContext);
      }

      Delete
  4. dear Sameh

    its good post and Iam confusing where I can wright my code
    example
    I wrote this method

    public String getEname(int empid){
    String fname="";
    ResultSet rs;
    String plSql="Select first_name from employees where employee_id=?";
    DBTransaction trans =getDBTransaction();
    CallableStatement stat=null;
    stat=trans.createCallableStatement(plSql,2);
    try {
    stat.setInt(1,empid);
    rs=stat.executeQuery();
    while(rs.next())
    {
    fname=rs.getString("first_name");
    }
    } catch (Exception e) {
    // TODO: Add catch code
    e.printStackTrace();
    } finally {
    }
    return fname;
    }

    In AppModuleImpl
    and create my page and
    I try to call it from page bean
    I got this error message

    Application module AppModuleImpl_151 is not a root app module but has no parent


    appreciate your help

    ReplyDelete
    Replies
    1. Hi,
      First: don't forget to close rs and stat in finally block.

      Second: how you call the method in AppModuleImpl from bean??
      You can't instantiate an application module to access method. you should expose the method to the clientinterface of the application module and add it in the pageDef. then you can call this method like:
      BindingContainer bindingContainer= BindingContext.getCurrent().getCurrentBindingsEntry();
      OperationBinding operationBinding=bindingContainer.getOperationBinding("methodAction");
      operationBinding.execute();

      or you can move this method from AM and add it directin bean

      Delete