Java Buzz Forum - A quick and dirty EJB3 query console

Last week I was consulting for a customer working on an EJB3 project. They were having some query problems, and it was quickly apparent that putting a query in the code and going through a compile/run cycle for each variant was just not going to cut it. Since the Hibernate console doesn't have EJB3 query integration yet, I decided to put together a quick component they could add to their application to test out queries.

I started with a quick Seam component that takes a query in and puts out a list of objects. To make the UI simpler, I made sure the query always returned a list of lists. (or arrays) Beyond that there's nothing tricky about it:

@Stateful
@Name("query")
@Interceptor(SeamInterceptor.class)
public class QueryAction
    implements Query,
               Serializable
{
    @PersistenceContext
    EntityManager em;

    String query = "";

    @Out(required=false)
    List results;

    public String getQuery() {
        return query;
    }

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

    public String search() {
        try {
            List tmp = new ArrayList();

            for(Object line: em.createQuery(query).getResultList()) {
                if (line.getClass().isArray() || line instanceof Collection) {
                    tmp.add(line);
                } else {
                    List newline = new ArrayList();
                    newline.add(line);
                    tmp.add(newline);
                }
            }

            results = tmp;
        } catch (Exception e) {
            results = null;

            FacesContext.getCurrentInstance().addMessage("form:query",
                                                  new FacesMessage(e.getMessage()));
            e.printStackTrace();
        }

        return null;
    }

    @Destroy
    @Remove
    public void destroy() {
    }
}

The facelets view is quite simple. It presents a search form with a box to enter to the EJB3 QL query string and an error box to display error information for failed queries. Below that is a table that displays the results in table form.

<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      xmlns:c="http://java.sun.com/jstl/core"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:h="http://java.sun.com/jsf/html">
    <head><title>query tester</title></head>
    <body>
        <h:form id="form">
            Query: <h:inputText id="query" size="80" required="true" value="#{query.query}" /><br />
            <h:message for="query" /><br />

            <h:commandButton action="#{query.search}" value="do it" />
        </h:form>

        <c:if test="${results != null}">
            Results:

            <table border="1">
                <c:forEach items="#{results}" var="line">
                    <tr>
                        <c:forEach items="#{line}" var="item">
                            <td>#{item}</td>
                        </c:forEach>
                    </tr>
                </c:forEach>
            </table>
        </c:if>
    </body>
</html>

If you are performing a simple query like "from Product p", then the output might not be terribly helpful unless you have somewhat useful toString methods. I like:

Alternatively, it's not hard to alter a query to include the interesting data. Instead of "from Product p", try "select p,p.id from Product p" or "select p.id, p.name, p.price from Product p".

I've found this quite useful in debugging complex queries. Setting hibernate.show_sql to true makes it even more interesting, especially when you want to test the effects of a join fetch or some other query optimization. I'd like to be able to attach a listener to the underlying hibernate session and capture the SQL for display on the screen, but I'm not enough of a Hibernate guru to know if this is possible. I'm content enough to keep my eyes on the console log.

If you are doing EJB3 persistence, I hope this helps. If you are using Seam+Facelets you can pop these in (along with the Query interface) and be good to go. Other frameworks would require a bit more code, but it shouldn't be a terribly large task in any environment.


	Web Artima.com