Collections of web application techniques

Thursday, September 3, 2009

Custom Pagination With Seam - Simplified

As it turned out, it is extremely easy to enhance the pagination that comes with Seam to do all the things below:
- selectable page sizes
- current page and total number of pages are displayed
- total number results is displayed
- paging is not limited to just first, previous, next and last result sets but much more flexible as below.



The easiest way to start is to use seam-gen to generate the code. Once you have the generated code working, do the following to enhance the default pagination.

1. Open the file that ends with “List” such as PersonList and add the code below to your class. PageCalc source is being provided at the end of this blog.


private PageCalc pageCalc;

private int pageIndex;

@Override
public Long getResultCount() {
Long count = super.getResultCount();
if (pageCalc == null) {
pageCalc = new PageCalc();
pageCalc.setTotal(count);
pageCalc.setUpPaging(pageIndex, getMaxResults());
}
return count;
}

public int getPageIndex() {
return pageIndex;
}

public void setPageIndex(int pageIndex) {
this.pageIndex = pageIndex;
setFirstResult((pageIndex - 1) * getMaxResults());
}

public PageCalc getPageCalc() {
return pageCalc;
}


2. Open the xhtml file such as PersonList.xhtml and add these lines just before the dataTable tag. This will render all the controls above the table. The code for paginationControl.xhtml is also included at the end of this blog.


<ui:include src="paginationControl.xhtml">
<ui:param name="bean" value="#{personList}"/>
<ui:param name="pageNumber" value="pageIndex"/>
<ui:param name="dataListener" value="data"/>
</ui:include>


Note that #{personList} should be replaced with the name of your backing bean; something like managerList, departmentList,… whatever your page is about.

This also uses ajax to render only the part that needs to be re-rendered. In this example I have wrapped the data table with the outputPanel tag. The id is being passed to the included file so it knows what to re-render.


<a:outputPanel id="data">
<rich:dataTable …
</a:outputPanel>


The pageIndex is also being passed to the paginationControl where in PersonList.page.xml you should have a line like this to tie the current page to the backing bean:


<param name="pageIndex" value="#{personList.pageIndex}"/>


3. Test it out and enjoy.

Code for paginationControls.xhtml:


<ui:composition xmlns="http://www.w3.org/1999/xhtml"
xmlns:s="http://jboss.com/products/seam/taglib"
xmlns:ui="http://java.sun.com/jsf/facelets"
xmlns:f="http://java.sun.com/jsf/core"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:rich="http://richfaces.org/rich"
xmlns:a="http://richfaces.org/a4j"
xmlns:c="http://java.sun.com/jstl/core"
>
<style>
#pagination a:link, #pagination a:active, #pagination a:visited {
padding-left:2px;
padding-right:2px;
text-decoration:none;
}
</style>

<div style="clear:both">
<span style="float:left">
<h:outputText value="Total Number of Matches: #{bean.resultCount}"/>
</span>

<span style="float:right">
<h:outputLabel value="Page Size:">
<h:selectOneMenu id="pageSize" value="#{bean.maxResults}">
<f:selectItem itemLabel="10" itemValue="10" />
<f:selectItem itemLabel="15" itemValue="15" />
<f:selectItem itemLabel="20" itemValue="20" />
<f:selectItem itemLabel="25" itemValue="25" />
<f:selectItem itemLabel="50" itemValue="50" />
<f:selectItem itemLabel="100" itemValue="100" />
<a:support event="onchange" action="#{bean.setFirstResult(0)}" reRender="data" />
</h:selectOneMenu>
</h:outputLabel>
</span>
</div>

<div id="pagination" style="clear:both">
<h:outputText value="Page #{bean.pageCalc.pageIndex} of #{bean.pageCalc.lastPageIndex} - " />
<h:panelGroup rendered="#{bean.pageCalc.leftArrows}">
<s:link reRender="#{dataListener}">
<h:outputText value="&lt;&lt; "/>
<f:param name="#{pageNumber}" value="1" />
<f:param name="maxResults" value="#{bean.maxResults}" />
</s:link>

<s:link reRender="#{dataListener}">
<h:outputText value="&lt; "/>
<f:param name="#{pageNumber}" value="#{bean.pageCalc.pageIndex - 1}" />
<f:param name="maxResults" value="#{bean.maxResults}" />
</s:link>
</h:panelGroup>

<h:outputText value="... " rendered="#{bean.pageCalc.leftDots}" />

<h:panelGroup rendered="#{bean.pageCalc.pageListSize > 1}">
<ui:repeat value="#{bean.pageCalc.pageList}" var="iter">
<s:link reRender="#{dataListener}"
value="#{iter}"
rendered="#{bean.pageCalc.pageIndex != iter}">
<f:param name="#{pageNumber}" value="#{iter}"/>
<f:param name="maxResults" value="#{bean.maxResults}" />
</s:link>

<h:outputText value=" #{iter} "
rendered="#{bean.pageCalc.pageIndex == iter}"
style="font-weight:bold;"/>
</ui:repeat>
</h:panelGroup>

<h:outputText value="... " rendered="#{bean.pageCalc.rightDots}" />

<h:panelGroup rendered="#{bean.pageCalc.rightArrows}">
<s:link reRender="#{dataListener}">
<h:outputText value="&gt; "/>
<f:param name="#{pageNumber}" value="#{bean.pageCalc.pageIndex + 1}" />
<f:param name="maxResults" value="#{bean.maxResults}" />
</s:link>

<s:link reRender="#{dataListener}">
<h:outputText value="&gt;&gt; "/>
<f:param name="#{pageNumber}" value="#{bean.pageCalc.lastPageIndex}" />
<f:param name="maxResults" value="#{bean.maxResults}" />
</s:link>
</h:panelGroup>
</div>

</ui:composition>


Code for PageCalc.java:


package com.inventasoft.ui;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

/**
* For number of steps=2, lastPageIndex=8:
* pageIndex=1 1 2 3 ... > >>
* pageIndex=2 << < 1 2 3 4 ... > >>
* pageIndex=3 << < 1 2 3 4 5 ... > >>
* pageIndex=4 << < ... 2 3 4 5 6 ... > >>
* pageIndex=5 << < ... 3 4 5 6 7 ... > >>
* pageIndex=6 << < ... 4 5 6 7 8 > >>
* pageIndex=7 << < ... 5 6 7 8 > >>
* pageIndex=8 << < ... 6 7 8
*
* Left arrows: pageIndex > 1
* Left dots: pageIndex > (#steps + 1)
* Right dots: pageIndex < (lastPageIndex - (#steps + 1))
* Right arrows: pageIndex < (lastPageIndex - 1)
*
* Loop start: pageIndex - #steps < 1 ? 1 : pageIndex - #steps
* Loop end: pageIndex + #steps > lastPageIndex ?
* lastPageIndex : pageIndex + #steps
*
* @author SN
*/
public class PageCalc implements Serializable {
public static final int DEFAULT_PAGE_SIZE = 8;
public static final int DEFAULT_STEP_SIZE = 10;
public static final int MIN_PAGE_SIZE = 2;
public static final int MAX_PAGE_SIZE = 100;
private int pageIndex;
private int pageSize;
private int lastPageIndex;
private Long total;
private int numSteps = DEFAULT_STEP_SIZE;
private boolean leftDots;
private boolean rightDots;
private boolean leftArrows;
private boolean rightArrows;
private int startIndex;
private int endIndex;
private int firstResult;
private List pageList;

public void setUpPaging(int index, int size) {
int myIndex = index;

if (myIndex <= 0) {
myIndex = 1;
}

pageIndex = myIndex;

// Make sure that the page size is within its limits
if (size < MIN_PAGE_SIZE || size > MAX_PAGE_SIZE) {
pageSize = DEFAULT_PAGE_SIZE;
} else {
pageSize = size;
}

// calculate the number of pages and set last page index
lastPageIndex = (int) Math.ceil(total.doubleValue() / pageSize);

// make sure that the page index in not out of scope
if (pageIndex > lastPageIndex) {
pageIndex = 1;
}

firstResult = pageSize * (pageIndex - 1);

leftArrows = pageIndex > 1;
leftDots = pageIndex > (numSteps + 1);
rightDots = pageIndex < (lastPageIndex - numSteps);
rightArrows = pageIndex < lastPageIndex;
if (pageIndex - numSteps < 1) {
startIndex = 1;
} else {
startIndex = pageIndex - numSteps;
}
if (pageIndex + numSteps > lastPageIndex) {
endIndex = lastPageIndex;
} else {
endIndex = pageIndex + numSteps;
}
setUpPageList(startIndex, endIndex);
}

private void setUpPageList(int start, int end) {
pageList = new ArrayList();
for (int i = start; i <= end; i++) {
pageList.add(new Integer(i));
}
}

public int getPageIndex() {
return pageIndex;
}

public int getLastPageIndex() {
return lastPageIndex;
}

public long getTotal() {
return total;
}

public boolean isRightDots() {
return rightDots;
}

public void setRightDots(boolean rightDots) {
this.rightDots = rightDots;
}

public int getPageSize() {
return pageSize;
}

public int getNumSteps() {
return numSteps;
}

public boolean isLeftDots() {
return leftDots;
}

public boolean isLeftArrows() {
return leftArrows;
}

public int getStartIndex() {
return startIndex;
}

public int getEndIndex() {
return endIndex;
}

public void setPageIndex(int pageIndex) {
this.pageIndex = pageIndex;
}

public boolean isRightArrows() {
return rightArrows;
}

public List getPageList() {
return pageList;
}

public int getPageListSize() {
if (pageList != null) {
return pageList.size();
}
return 0;
}

public int getFirstResult() {
return firstResult;
}

public void setTotal(Long total) {
this.total = total;
}
}

Tuesday, August 11, 2009

How to Create HTML Scrollable Data Table

This blog describes how you can create a scrollable data table for your web applications. The scrollable table allows user to scroll vertically to see all the rows while maintaining the column headers and footers in fixed positions.

Below is an example of the output table:



Setting up this kind of table is fairly simple. The main thing you have to do is to specify the width of each column and make sure that the header and the data column have the same width. The scrollable table above actually consists of three different tables: the first one is for the table caption and column headers, the second one is for all the data row, and the last one is for the footer.

For the table above, I defined these styles:


.col1 {width:6.0em; vertical-align:top; padding:2px;}
.col2 {width:6.0em; vertical-align:top; padding:2px;}
.col3 {width:15.0em; vertical-align:top; padding:2px;}
.col4 {width:6.0em; vertical-align:top; padding:2px;}
.col5 {width:6.0em; vertical-align:top; padding:2px;}
.col6 {width:6.0em; vertical-align:top; padding:2px;}
.col7 {width:6.0em; vertical-align:top; padding:2px;}
.col8 {width:6.0em; vertical-align:top; padding:2px;}


The total width is then 57.0em. But to make things look good on IE 7 and FireFox 3, I bump it up to 60.5em and wrap each table in a div tag.

Header:

Note the width and the overflow in the div tag.


<div style="border: 1px solid #CCCCCC; width:60.5em; overflow:none; margin-top:5px; padding:0;">
<table style="background:#FFFFFF; margin:0; padding:0;" cellpadding="0" cellspacing="1" summary="Header layout for data">
<caption style="padding: 4px;font-weight:bold">
Assigned Stations Statistics
</caption>
<th scope="col" class="col1" style="background: url(../fpps-toolbar/images/toolbarbg.png);">
<h:outputLink onclick="setSelected(true); return false;">All</h:outputLink>
<span style="padding-left:2px; padding-right:4px">/</span>
<h:outputLink onclick="setSelected(false); return false;">None</h:outputLink>
</th>
<th scope="col" class="col2">Station</th>
<th scope="col" class="col3">Role(s)</th>
<th scope="col" class="col4">Assigned</th>
<th scope="col" class="col5">Awaiting</th>
<th scope="col" class="col6">In Process</th>
<th scope="col" class="col7">Rejected</th>
<th scope="col" class="col8">Error</th>
</table>
</div>


Data:
Although the data table below is rendered using the Java Server Faces tag which might not make sense if you are not familiar but the important things to note are:
  • the table is wrapped in a div that allows vertical scrolling only via the overflow-y
  • the with is 62em to account for the vertical scrollbar
  • the style classes for each of the column (columnClasses attribute) are exactly the same as the header column.


    <div style="border: 1px solid #CCCCCC; height:20em; width:62em; overflow-y:scroll; background: #FFFFFF; margin:0; padding:0;">
    <h:dataTable id="homeStatTable"
    var="data"
    rowClasses="rowOdd,rowEven"
    cellpadding="0"
    cellspacing="1"
    columnClasses="col1 center,col2 center,col3,col4 right,col5 right,col6 right,col7 right,col8 right"
    footerClass="footer"
    value="#{homePageUserData.userFacilities.values.toArray()}">
    <h:column>
    <h:selectBooleanCheckbox id="active" value="#{data.active}"/>
    <f:facet name="footer"></f:facet>
    </h:column>
    <h:column>
    #{data.code}
    </h:column>
    <h:column>
    CLERK
    </h:column>
    <h:column>
    #{data.assigned}
    </h:column>
    <h:column>
    #{data.awaiting}
    </h:column>
    <h:column>
    #{data.inProcess}
    </h:column>
    <h:column>
    #{data.rejected}
    </h:column>
    <h:column>
    #{data.error}
    </h:column>
    </h:dataTable>
    </div>


    Footer:
    The footer is similar to the header which is also wrapped in the same div style with the overflow as none. Again each of the footer column has the same style as its corresponding header and data columns.


    <div style="border: 1px solid #CCCCCC; width:60.5em; overflow:none; margin:0; padding:0;">
    <table style="background:#FFFFFF; margin:0; padding:0;" cellpadding="0" cellspacing="1" summary="Layout for summary data">
    <th scope="col" class="col1"></th>
    <th scope="col" class="col2"></th>
    <th scope="row" class="col3 right" style="text-align:right">Total:</th>
    <th scope="col" class="col4 right">#{homePageUserData.claimsAssigned}</th>
    <th scope="col" class="col5 right">#{homePageUserData.claimsAwaiting}</th>
    <th scope="col" class="col6 right">#{homePageUserData.claimsInProcess}</th>
    <th scope="col" class="col7 right">#{homePageUserData.claimsRejected}</th>
    <th scope="col" class="col8 right">#{homePageUserData.claimsError}</th>
    </table>
    </div>
  • Friday, July 24, 2009

    Using JasperReports in JSF Applications

    JasperReports is a reporting tool that can be used to generate reports in multiple formats such as PDF for printing and CSV for manipulation in a spreadsheet tool. This blog describes how you can integrate a JapserReport file (.jrxml) into a JSF-based application.

    Although the jrxml template is just a text file, it is easier to use a tool such as iReport. iReport is good for general layout and can save you from tedious work but it is not perfect. Occasionally it is better to edit the xml file directly. GVim is an excellent text editor if you have a Linux background but any other text editor will do the job.

    The jrxml file should be copy into a directory readable by the class loader. I put mine in /WEB-INF/reports. You can use the ReportGenerator class included to generate the desired report.

    Typically a report is generated when a user clicks a button or a link which invoke an action listener. The action listener method then calls the ReportGenerator’s printReport() and a report in the desired format (HTML, PDF, CSV, EXEL) is returned to the user. PDF format with JasperReports is excellent as it uses absolute positioning. However HTML and EXEL are not very good as the outcomes can be surprisingly different. I recommend that you provide PDF for printing and CSV for data manipulations to the users as those two formats seem to work best.

    Below is an example on the variation that a report should be opened on a new window after user fill out the criteria on a form. To make it a little more user friendly, error checks were performed on the back end and will be displayed on the same page. Therefore, we can’t just set the target to _blank to force the browser to open a new window whenever an action is submitted.

    In your form, you might have a button like this:

    <h:commandButton value="Generate" action="#{report.submit}"/>


    Your action listener then validates the inputs and if there is any error, it will be sent back to the same page:

    public final String submit() {
    if (!criteria.isValid()) {
    facesMessages.add(Severity.ERROR, "Some error messages"
    return ERROR;
    }

    return NEXT;
    }


    When everything is well, we can pass control to the report generator. Wait! What if users had some errors previously which where displayed on the form? Clearing them would be good.


    Here is an example that works for JBoss Seam (if you are not, you definitely need to check it out). The trick is to reload the form and open the new window for the report. The navigtion rule below specifies that the view will be report.jsp.


    <navigation from-action="#{report.submit}">
    <rule if-outcome="next">
    <redirect view-id="/app/report/report.jsp"/>
    </rule>
    </navigation>


    Below is the content for report.jsp. It refreshes the main form (reportForm.jsp) and opens display.jsp.

    <ui:composition xmlns="http://www.w3.org/1999/xhtml"
    xmlns:s="http://jboss.com/products/seam/taglib"
    xmlns:ui="http://java.sun.com/jsf/facelets"
    xmlns:f="http://java.sun.com/jsf/core"
    xmlns:h="http://java.sun.com/jsf/html"
    >

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <script language="JavaScript">
    function displayReport() {
    reportUrl = "display.x";
    newWin = window.open(reportUrl,"_blank",
    "location=0,resizable=1,status=1,scrollbars=1,menubar=1,toolbar=0");
    newWin.focus();

    document.getElementById("report:location").click();
    }
    </script>
    </head>
    <body onload='displayReport()'>
    <h:form id="report">
    <s:button id="location" style="display:none" view="/app/report/reportForm.jsp" value="return" propagation="join">
    </s:button>
    </h:form>
    </body>
    </html>

    </ui:composition>


    Below is the content for display.jsp:

    <ui:composition xmlns="http://www.w3.org/1999/xhtml"
    xmlns:s="http://jboss.com/products/seam/taglib"
    xmlns:ui="http://java.sun.com/jsf/facelets"
    xmlns:f="http://java.sun.com/jsf/core"
    xmlns:h="http://java.sun.com/jsf/html"
    xmlns:rich="http://richfaces.org/rich"
    >

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Expires" content="-1"/>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <script language="JavaScript">
    function displayReport() {
    document.getElementById("report:location").click();
    }
    </script>
    </head>
    <body onload="document.body.style.cursor='wait'; displayReport();">
    <h:form id="report" >
    <div style="width:90%; padding:5px; margin:40px; border: 1px solid #FFCC00; background-color: #F0F8FF;">
    <h:outputText value="Please wait..."/>
    <h:commandButton id="location" style="display:none" action="#{report.display}"/>
    </div>
    </h:form>
    </body>
    <header>
    <meta http-equiv="Expires" content="-1"/>
    </header>
    </html>
    </ui:composition>


    Displaying a message with some progress indicator would be even better on this last page. To do that, you can specify a refresh rate in this last page to get the progress. This is much easier than create a thread on the server end.

    <meta http-equiv="refresh" content="1"/>

    And here is the method display invoking the report generator:

    public final String display() {
    ReportPreparer prep = new ReportPrepFactory().getPreparer(criteria);

    String template = "sample"; // Suppose your jrxml file is sample.jrxml

    Map p = new HashMap(); // if your report has parameters, they should be set here

    ReportGenerator gen = new ReportGenerator();
    if (gen.printReport(
    "P", // P for PDF
    p,
    new StringBuffer("/WEB-INF/reports/").append(template)
    .append(".jrxml").toString(),
    template)) {
    return NEXT;
    }

    return ERROR;
    }


    This is the code to generate report:

    Wednesday, July 22, 2009

    A Comparison of Java Webapp Report Tools

    I recently completed the conversion from Crystal report to Jasper Report. The Crystal reports were causing me too much pain when using in my Java web application. This is not to say that Crystal is bad, actually using Crystal is somewhat better than iReport (a tool to generate Jasper Report). The only complain I have against it is that coming from Java, I just don’t like its expression language and deploying Crystal reports on Business Object to server reports to end users is a nightmare.

    Crystal Jasper Report Birt
    Pros
  • Feature rich
  • Non programmers can do it (Java is not required except for the parameter passing but we already figured it out)
  • Can overlay elements on background pictures when designing reports.
  • More people are familiar with this tool

  • Free – open source
  • Absolute coordinates
  • Java syntax out of the box
  • iReport is a Swing (newer version is based on NetBeans platform) application to design reports
  • Reports data can be provided from application via Hibernate O/R or just from report’s SQL
  • Re-deployment can be just as simple as dropping a file into live application (no down time)

  • Free – open source
  • Newer than Jasper with slicker designer interface. Developers especially those familiar with Eclipse will feel at home
  • Can embed reports in applications or deploy on BIRT report server as Crystal
  • Probably can be used in a lot of cases but I could not got it to work with a complex report with multiple different backgrounds.
  • Cons
  • Crystal’s own language or VB for function expressions
  • DB fine grained access forced us to use stored procedure. Mistakes can be easily made.
  • Needs Business Object Server. Reports cannot be embedded in the application
  • Potential security issues
  • Harder to deploy. Easier to make mistakes on deployment. Bugs have been written due to deployment issues
  • Costs money

  • Documentation for iReport costs money (should be much, much less than Crystal though)
  • No multiple headers or detail sections
  • Can’t overlay elements on pictures on iReport. Placement is a trial and error process
  • Non Java programmer might not like it.
  • iReport is not error free
  • No substitute for “Underlay Following Sections” feature in Crystal

  • HTML/CSS layout. Viewing in different formats produced drastically different results
  • Can’t overlay data on images. Features on images seem less robust than iReport
  • Non programmers might not like it because they have to deal with Eclipse IDE
  • I didn’t figure out the deployment part yet but that shouldn’t be a show stopper
  • Friday, July 10, 2009

    Leveraging ASP Window Authentication in Java Web Applications


    For web applications running inside a corporate intranet where users are maintained by a Microsoft Windows Active Directory, a seamless authentication method is often desired. If your web application consists of only Microsoft technologies, then just surf on Microsoft web site and follow their instructions.


    Authentication in ASP is extremely simple; all you need to secure your asp page is to configure IIS to set the directory where your page resides to use Integrated Windows Authentication. Then in your ASP page, you just need to add this line:


    userId = request.ServerVariables("LOGON_USER")


    That’s all! You’ll get the NT login ID that identifies who the user is and then you can move on to authorization. All the work is done for you behind the scence.


    But if you’re a Java guy who also wants to give users a seamless authentication experience, you’ll have to look elsewhere.


    JCIFS is an answer - almost. I’ve used it and my users got authenticated just fine for most of the time. Occasionally, my users could not be authenticated due to some changes in the network, their environments, whatever… In my experience, the one liner in ASP worked when JCIFS failed. As an application developer who would rather concentrate on developing front end applications, I’ve given up on getting the right network IP addresses to configure the NtlmHttpFilter for JCIFS. In fact, when users could not be authenticated, nobody seems to know what happens. Trying to understand the JCIFS source code is just fruitless when dealing with frustrated users. So here is a way to leverage it: let Microsoft technologies say with Microsoft technologies. They can be a black box - a reliable black box. Who cares as long as it works?




    Monday, May 18, 2009

    Custom Pagination With JBoss Seam

    Seam has its own pagination and you can get paginations for practically free. All you need to do is to extend your component from org.jboss.seam.framework.EntityQuery. In fact if you use seam-gen to generate the UI, you'll get all the search form, sorting and paging without knowing much. But this is not much fun for a software developer trapped in a cubicle, so I had to create something.

    My custom pagination described here has these additional features:
    - Selectable page sizes
    - Total counts are displayed
    - Navigations are not limited to just first, previous, next and last result sets. Mine shows the current and a fixed number of pages before or after the current similarly to how Google does it.

    Below is an example of the output:



    import com.inventasoft.ui.Pagination;
    import com.inventasoft.ui.PageControls;
    import com.inventasoft.ui.QueryHelper;

    public class YourBean implements QueryHelper {
    private Pagination pagination;
    private PageControls pageControls = new PageControls();

    public void setUp() {
    pagination = new Pagination
    }

    public List anActionListener() {
    pagination.execute(pageControls.getPage(), pageControls.getPageSize());
    return pagination.getResults();
    }

    public Query getCountQuery() {
    Object o2 = Component.getInstance("dataEM");
    EntityManager em = (EntityManager) o2;

    queryEntity.setEjbql("from AResultClass");
    queryEntity.setRestrictions(Arrays.asList(RESTRICTIONS));
    queryEntity.setOrder(selection.getOrder());

    String sqlStr = "select count(*) " + queryEntity.getCountQuery();
    Query query = em.createQuery(sqlStr);
    return query;
    }

    public Query getSelectQuery(int start, int pageSize) {
    Object o2 = Component.getInstance("dataEM");
    EntityManager em = (EntityManager) o2;

    String selectQuery = queryEntity.getQuery();
    Query query = em.createQuery(selectQuery);
    query.setFirstResult(start);
    query.setMaxResults(pageSize);
    return query;
    }
    }

    Friday, May 15, 2009

    JBoss Seam and Native Query

    I have been working with Oracle for years and feel more familiar with its SQL. Seam and Hibernate are more of a new thing for me. In some cases, I couldn’t find the right syntax to get Hibernate Query Language (HQL) to work but an equivalent in the underlying database would be more obvious. Perhaps I wasn’t look hard enough but it’s sure nice to have such option thank to the EntityManager’s createNativeQuery method.

    For instance instead of inheriting from org.jboss.seam.framework.EntityQuery and have these restrictions:


    private static final String[] RESTRICTIONS = {
    "lower(appUser.userId) like concat(lower(#{appUserList.appUser.userId}),'%')",
    "lower(appUser.firstName) like concat(lower(#{appUserList.appUser.firstName}),'%')",
    "lower(appUser.lastName) like concat(lower(#{appUserList.appUser.lastName}),'%')",};


    I would like to have restrictions similarly to below:


    private static final String[] RESTRICTIONS = {
    "begin_date >= to_date(''#{employeeList.selection.searchParams.beginDate}'',''MM/DD/YYYY'')",
    "end_date <= to_date(''#{employeeList.selection.searchParams.endDate}'',''MM/DD/YYYY'')",};


    Where the query will become something like “…to_date('05/14/2009','MM/DD/YYYY')…” which is the syntax for Oracle. So I came up with two classes NativeQueryEntity and NativeQueryParser which are really Seam code with some minor modifications.

    To use them, my bean would use composition instead and look something like:


    public class EmployeeList {

    private NativeQueryEntity queryEntity = new NativeQueryEntity();
    private static final String[] RESSTRICTIONS ....

    public Query getCountQuery() {
    queryEntity.setEjbql("from employees");
    queryEntity.setRestrictions(Arrays.asList(RESTRICTIONS));
    String sqlStr = "select count(*) " + queryEntity.getCountQuery();
    Query query = em.createNativeQuery(sqlStr);
    return query;
    }
    }


    Once you have the query, you can proceed to execute and get the results as usual.





    package com.inventasoft.ui;

    import java.util.List;
    import java.util.ArrayList;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;

    import java.io.Serializable;
    import java.text.MessageFormat;

    import org.jboss.seam.persistence.QueryParser;
    import org.jboss.seam.core.Expressions.ValueExpression;

    /**
    * This class is adapted from Seam's EntityQuery and Query classes.
    * Changed to composition instead of inheritance usage.
    */
    public class NativeQueryEntity implements Serializable {

    private static final Pattern FROM_PATTERN = Pattern.compile(
    "(^|\\s)(from)\\s", Pattern.CASE_INSENSITIVE);

    private static final Pattern ORDER_PATTERN = Pattern.compile(
    "\\s(order)(\\s)+by\\s", Pattern.CASE_INSENSITIVE);

    private static final Pattern WHERE_PATTERN = Pattern.compile(
    "\\s(where)\\s", Pattern.CASE_INSENSITIVE);

    private String parsedEjbql;

    private List restrictions;

    private List conditions;

    private String order;

    private String ejbql;

    private String renderedQuery;

    public void setEjbql(String ejbql) {
    this.ejbql = ejbql;
    }

    // Unparsed list of restrictions
    public void setRestrictions(List restrictions) {
    this.restrictions = restrictions;
    }

    public void setOrder(String order) {
    this.order = order;
    }

    private void parseEjbql() {
    QueryParser qp = new QueryParser(ejbql);
    List queryParameters = qp.getParameterValueBindings();

    parsedEjbql = qp.getEjbql();

    List parsedRestrictions =
    new ArrayList(restrictions.size());

    List restrictionParameters =
    new ArrayList(restrictions.size());

    conditions = new ArrayList();
    for (String restriction : restrictions) {
    NativeQueryParser rqp = new NativeQueryParser(restriction,
    queryParameters.size() + restrictionParameters.size());
    if (rqp.getParameterValueBindings().size() != 1) {
    throw new IllegalArgumentException(
    "there should be exactly one value binding in a restriction: "
    + restriction);
    }




    package com.inventasoft.ui;

    import java.io.Serializable;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.StringTokenizer;

    import org.jboss.seam.core.Expressions.ValueExpression;
    import org.jboss.seam.core.Expressions;

    /**
    * This is modified from Seam QueryParser to work better with native
    * query by using parameter substitutions.
    */
    public class NativeQueryParser implements Serializable {

    /** The parameter value bindings. */
    private List parameterValueBindings = new ArrayList();

    private StringBuilder ejbqlBuilder;

    public static String getParameterName(int loc) {
    return "{" + loc + "}";
    }

    public String getEjbql() {
    return ejbqlBuilder.toString();
    }

    public List getParameterValueBindings() {
    return parameterValueBindings;
    }

    public NativeQueryParser(String ejbql) {
    this(ejbql, 0);
    }

    public NativeQueryParser(String ejbql,
    int startingParameterNumber) {
    StringTokenizer tokens = new StringTokenizer(ejbql, "#}", true);
    ejbqlBuilder = new StringBuilder(ejbql.length());
    while (tokens.hasMoreTokens()) {
    String token = tokens.nextToken();
    if ("#".equals(token)) {
    if (!tokens.hasMoreTokens()) {
    throw new IllegalArgumentException(
    "query fragment terminates in #");
    }
    String expressionToken = tokens.nextToken();
    if (!expressionToken.startsWith("{")) {
    throw new IllegalArgumentException(
    "missing { after # in query fragment");
    }
    if (!tokens.hasMoreTokens()) {
    throw new IllegalArgumentException(
    "missing } after expression in query fragment");
    }
    String expression = token + expressionToken
    + tokens.nextToken();
    ejbqlBuilder.append(getParameterName(startingParameterNumber
    + parameterValueBindings.size()));
    parameterValueBindings.add(Expressions.instance()
    .createValueExpression(expression));
    } else {
    ejbqlBuilder.append(token);
    }
    }
    }
    }