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>

Be aware that this code doesn't do any kind of exception handling and is just for a proof of concept.