Wednesday, December 01, 2010

Adhoc SQL Query Addon For Your ADF 11g Application

It is very common that once you release your application you may need to execute some SQL queries through your application for trouble shooting purposes etc.


I wanted to try how possible to implement  this in ADF and here is what I found in a very primitive way.

I have a text area for the user to enter the SQL query and when the execute button is clicked the results will be displayed in the table below.



















In able to achieve this I am creating a View Object from a SQL statement in my AM.

AM code ...
    @Override
    protected void prepareSession(Session session) {
        super.prepareSession(session);
        
        if (findViewObject("SQLVo") == null) {
            createViewObjectFromQueryStmt("SQLVo",
                                                      " select 1 from dual  ");
        } 
        
         

    }
    
    public void executeSQL(String pSQLQuery){
        ViewObjectImpl vo = null;
        if (findViewObject("SQLVo") != null) {
            findViewObject("SQLVo").remove();
            System.out.println("Removing ");
        } 
        
        vo= (ViewObjectImpl) createViewObjectFromQueryStmt("SQLVo",pSQLQuery); 
        
        vo.executeQuery();
        System.out.println(vo.getEstimatedRowCount());
    }

   

My Page
<f:view>
    <af:document id="d1">
      <af:messages id="m1"/>
      <af:form id="f1">
        <af:panelStretchLayout topHeight="187px" id="psl1">
          <f:facet name="top">
            <af:panelFormLayout id="pfl1">
              <af:inputText value="#{bindings.pSQLQuery.inputValue}"
                            label="Query"
                            required="#{bindings.pSQLQuery.hints.mandatory}"
                            columns="60"
                            maximumLength="#{bindings.pSQLQuery.hints.precision}"
                            shortDesc="#{bindings.pSQLQuery.hints.tooltip}"
                            id="it1" rows="5">
                <f:validator binding="#{bindings.pSQLQuery.validator}"/>
              </af:inputText>
              <af:commandButton actionListener="#{bindings.executeSQL.execute}"
                                text="Execute"
                                disabled="#{!bindings.executeSQL.enabled}"
                                id="cb1" partialSubmit="true"/>
            </af:panelFormLayout>
          </f:facet>
          <f:facet name="center">
            <!-- id="af_one_column_header_stretched"  -->
            <af:panelCollection id="pc1" partialTriggers="::cb1">
              <f:facet name="menus"/>
              <f:facet name="toolbar"/>
              <f:facet name="statusbar"/>
              <af:table rows="#{bindings.SQLVo.rangeSize}"
                        fetchSize="#{bindings.SQLVo.rangeSize}" var="row"
                        rowBandingInterval="0"
                        value="#{bindings.SQLVo.collectionModel}"
                        rowSelection="single" id="t1" partialTriggers="::cb1">
                <af:forEach items="#{bindings.SQLVoIter.attributeDefs}" var="def">
                  <af:column headerText="#{def.name}" sortable="false" id="c1">
                    <af:outputText value="#{row[def.name]}" id="ot1"/>
                  </af:column>
                </af:forEach>
              </af:table>
            </af:panelCollection>
          </f:facet>
        </af:panelStretchLayout>
      </af:form>
    </af:document>
  </f:view>
My Page Definition

<executables>
    <iterator id="SQLVoIter" Binds="SQLVo" DataControl="AppModuleDataControl"
              Refresh="always" RangeSize="25"/>
    <variableIterator id="variables">
      <variable Type="java.lang.String" Name="executeSQL_pSQLQuery"
                IsQueriable="false"/>
    </variableIterator>
  </executables>
  <bindings>
    <methodAction id="executeSQL" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="executeSQL"
                  IsViewObjectMethod="false" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.dataProvider">
      <NamedData NDName="pSQLQuery" NDType="java.lang.String"
                 NDValue="${bindings.executeSQL_pSQLQuery}"/>
    </methodAction>
    <attributeValues IterBinding="variables" id="pSQLQuery">
      <AttrNames>
        <Item Value="executeSQL_pSQLQuery"/>
      </AttrNames>
    </attributeValues>
   
     <tree IterBinding="SQLVoIter" id="SQLVo">
      <nodeDefinition Name="Dummy">
      </nodeDefinition>
    </tree>
  </bindings>


Monday, March 01, 2010

Microsoft Code Camp

Last Saturday I attended a Microsoft developer's code camp event in Fort Lauderdale. You might be wondering why I am attending a MS event while working with ADF and Java. First of all these events are really great opportunities to see what is going on in the Microsoft world. Secondly I usually try to attend more generic sessions so that I can benefit more. I really enjoyed it again .. Those were the keywords from the event
  • Azure - MS's cloud computing platform
  • Silverlight -MS technology that corresponds to flash/ JavaFX
  • SQL
  • Sharepoint
  • WCF
 I wonder why Oracle or Sun doesn't organize events like this .. Is that that tough ? Microsoft is putting a great effort to build and support their developer's community. I expect something similar from Oracle as an Oracle Developer. 

Saturday, January 09, 2010

Custom Declarative Component- Multi-Select LOV

 I needed to implement a multi select LOV solution for a requirement and I thought that building a declarative component would be the ideal way to go ..
  Here I will give a sample of that component, you can enhance it according to your needs.

1-Start a new application and add a new generic application
2-From the new gallery pick "JSF Declarative Component"



3-Provide details for the component




4-Edit MultiSelect.jspx and add your components. I will use an inputText and an icon to simulate the LOV. When the icon is clicked I will launch a popup and in the popup I will use a af:query and af:table . LovModel has attributes for af:query and af:table. Those attributes will be utilized to fetch the lov data and search areas.

Make sure you set the Simple attribute to true. Otherwise it wont align well in panelFormLayout


Now we need to add the popup ..



Pay attention to the af table I used here. It is a dynamic table and it forms itself from the lovModel. It is set to allow multi select.

When the OK button is clicked I call a method on the component class.



5-Create a deployment profile to deploy this project as ADF Jar Library and deploy it.
6-Create a test Fusion application and by using File System Connection attach it to your view project. Once you do this you will start seeing your component in the component palette.


7-Create your model and create the LOVs as you do with regular lovs.We will utilize the same lovModel

   
For testing the app I added a transient attribute and I added the Countries Vo as LOV accessor here. Nothing special !!
8-Now create a form on your test page and drag this transient attribute InputTextListOfValues then delete countries field from page source. Make sure that you are not deleting the binding. We need the binding for LOV.
  Now dragNDrop your MultiSelect Component. It will ask two attributes. One for the comma separated list and the other is the LovModel.

Pick the attribute values as here.
You will end up with screen as below.



Now you can launch your app...


Enjoy !

I checked-in the source code on google code . You can get it here(MultiSelectLov.zip).