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>


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

6 comments:

AndyAn said...

Good day! Can you send me a sample roject. PLS(andyan1966@gmail.com)

Muhammed Soyer said...

Hi Andy,
Have you tried the same code here? If you faced any issues could you let me know, I can try to clarify them..

AndyAn said...

Hi Muhammes!
I try build dynamic table like you code! View object create ok! But in jspx page table is empty!!!







ApplicationModule am = getApplicationModule("AppModule");
ViewObject vo = null;
am.findViewObject("myVO1").remove();
vo = createViewObjectWithHintsFromQueryStmt(am, "myVO1", "select TOPO_NUMBER,WATER_TYPE_NAME from WATER_TYPES",
new String[][]{
{"TOPO_NUMBER","LABEL","ID"},
{"TOPO_NUMBER","TOOLTIP","The identification number of the employee"},
{"WATER_TYPE_NAME","LABEL","NAME"},
{"WATER_TYPE_NAME","TOOLTIP","The identification number of the employee"}});
((ViewObjectImpl)vo).setKeyAttributeDefs( new int[]{0} );
vo.executeQuery();









Where is my mistake!
Thanks if you help me :)

Muhammed Soyer said...

I checked-in my code , you may checkout using SVN

svn checkout http://msoyer.googlecode.com/svn/trunk/

Or by downloading the zipped version from below link...

http://msoyer.googlecode.com/svn/trunk/SqlQueryTool.zip

Roberto said...

Hello Muhammed,
thank you very much for your code, it was really useful to me. I have a question about this example: in this dynamic table is it possible to activate the column filter/sorting functionality? I could not find a way to do that. Thanks!

interfete_evoluate_2007 said...

Good Day! Great post, Thank you very much for the great example!