Thursday, 15 July 2010

SQL Worksheet using JSF 2 / JSTL Result

Having used JSTL / JSP often enough I thought I would quickly try and get a Facelet page to use a JSTL Result object to provide a VERY basic SQL Worksheet demo which allowed the user to query the database and display the data regardless of what the query data was. Found a few gotchas on the Facelet / JSF 2.0 side which were handy to solve.

Problems

1. Using c:if for conditional output seemed to always result in FALSE when clearly that wasn't the case. There are a few options in JSF 2.0 world but the easiest was to now use ui:fragment as shown below.

<ui:fragment rendered="#{queryBean.rowcount > 0}">

2. As was the case with c:if , c:forEach didn't work for me either. I believe it had something to do with it being run when the component tree is being built. So now I will be using ui:repeat instead, which is close to identical but now use the attribute value instead of items.
<ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}"> 

So here is the Facelet page, Managed bean and a quick HTML screen show of how it worked.

Query.xhtml
<?xml version="1.0" encoding="UTF-8"?>
<!--
To change this template, choose Tools | Templates
and open the template in the editor.
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<ui:composition
      xmlns="http://www.w3.org/1999/xhtml"
      xmlns:h="http://java.sun.com/jsf/html"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      template="/pages/templates/EmpTemplate.xhtml"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:c="http://java.sun.com/jsp/jstl/core">

    <f:metadata>
        <f:viewParam name="refresh" value="#{employeeBean.refresh}" />
        <f:viewParam name="deptno" value="#{employeeBean.deptno}" />
        <f:viewParam name="empno" value="#{employeeBean.empno}" />
    </f:metadata>

    <ui:define name="title">
        #{msg.browserheading}
    </ui:define>

    <ui:define name="header">
        <ui:include src="/pages/employees/header.xhtml" />
    </ui:define>

    <ui:define name="body">
        <b>Enter SQL query below without semi colon</b>
        <p />
        <h:message for="query" style="color: #336699"/>
        <h:form>
            <h:inputTextarea rows="8"
                             cols="100"
                             value="#{queryBean.query}"
                             required="true"
                             requiredMessage="You must an SQL select statement to execute"
                             validator="#{queryBean.validateQuery}"
                             id="query"/>
            <br />
            <h:commandButton value="Submit Query" action="#{queryBean.executeQuery}"/>
            <h:commandButton value="Clear" action="#{queryBean.clearScreen}"/>
            <p />
        </h:form>
        <p />
        <ui:fragment rendered="#{queryBean.rowcount > 0}">
            <i>Total of #{queryBean.rowcount} record(s) found</i>
            <p />
            <table border="1">
                <thead>
                  <tr>
                    <ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}">
                        <th class="heading">#{columnName}</th>
                    </ui:repeat>
                  </tr>
                </thead>
                <tbody>
                    <ui:repeat var="row" value="#{queryBean.queryData.rows}" varStatus="loop">
                        <tr class="${((loop.index % 2) == 0) ? 'even' : 'odd'}">
                          <ui:repeat var="columnName" value="#{queryBean.queryData.columnNames}">
                            <td>#{row[columnName]}</td>
                          </ui:repeat>
                        </tr>
                    </ui:repeat>
                </tbody>
            </table>
            <p />
        </ui:fragment>
    </ui:define>
    
    <ui:define name="footer">
        <ui:include src="/pages/employees/footer.xhtml" />
    </ui:define>

</ui:composition>

QueryBean.java
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package oracle.jsf.demo.managedbeans;

import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ManagedProperty;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.validator.ValidatorException;
import javax.servlet.jsp.jstl.sql.Result;
import oracle.jsf.demo.dao.emp.EmployeeService;

/**
 *
 * @author papicell
 */
@ManagedBean
public class QueryBean
{
    private Result queryData;
    private String query;
    private int rowcount;

    @ManagedProperty(value="#{employeeServiceImpl}")
    private EmployeeService service;

    public QueryBean()
    {
      rowcount = 0;
    }

    public String getQuery()
    {
        return query;
    }

    public void setQuery(String query)
    {
        this.query = query;
    }

    public Result getQueryData()
    {
        return queryData;
    }

    public void setQueryData(Result queryData)
    {
        this.queryData = queryData;
    }

    public EmployeeService getService()
    {
        return service;
    }

    public void setService(EmployeeService service)
    {
        this.service = service;
    }

    public int getRowcount()
    {
        return rowcount;
    }

    public void setRowcount(int rowcount)
    {
        this.rowcount = rowcount;
    }

    /*
     * Custom Validation method for query field
     */
    public void validateQuery(FacesContext context,
                                     UIComponent componentToValidate,
                                     Object value) throws ValidatorException
    {
        String statementSQL = ((String)value);
        if (!statementSQL.toLowerCase().startsWith("select"))
        {
            FacesMessage message =
                new FacesMessage("Not a valid SQL Select statement entered");
            throw new ValidatorException(message);
        }
    }

    public void executeQuery ()
    {
       queryData = service.executeQuery(getQuery());
       setRowcount(queryData.getRowCount());
    }

    public void clearScreen ()
    {
       queryData = null;
       setRowcount(0);
    }
} 

Browser Ouput

No comments: