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);
         }
     }
 }
}