RE: Growing amount of queries

[Carlucci, Tony]

I've used CriteriaQuery a little bit, and like Jasha mentioned it isn't as 
clear to read, but they are very powerful and flexible.  The downside is you 
lose the pre-verification and caching of the named query statement.  However, I 
believe situations like this (i.e. dynamic order by clause) is what they are 
designed to be used for so I don't think there is any harm in using them.  Good 
comments always make ugly code better readable too :)

Tony

---
Anthony Carlucci | SW App Dev Eng, Sr. | R501 / KW App Development & Maint
e: acarlu...@mitre.org | v: 781.271.2432 | f: 781.271.3299
The MITRE Corporation | 202 Burlington Rd | Bedford, MA 01730-1420


-----Original Message-----
From: Cooper, Sean D. [mailto:secoo...@mitre.org] 
Sent: Tuesday, October 25, 2011 9:11 AM
To: rave-dev@incubator.apache.org
Subject: RE: Growing amount of queries

Yeah, JPA NamedQuerys are pre-generated.  You can't even use static final 
variables in them to keep field name consistency!  I don't know of a better way 
of doing this, if someone bounces a better solution to you, can you please 
forward it to me so I can see it as well.

-Sean

-----Original Message-----
From: Jasha Joachimsthal [mailto:j.joachimst...@onehippo.com] 
Sent: Tuesday, October 25, 2011 9:07 AM
To: rave-dev@incubator.apache.org
Subject: Growing amount of queries

For the admin interface I wanted to add search on either the widget
title/description or type or status and add sorting options of the widget
table columns (title, status, type)

For the (optional) search parameters the NamedQuery contains statements
like:
:type is null OR :type = '' OR w.type = :type

As it turns out, it's not possible to dynamically set the sort field and
order with NamedQueries. That would mean I have to create 6 queries to
support both asc and desc order for title, status and type :(

The current setup:
Widget.java:
        @NamedQuery(name =
Widget.WIDGET_GET_BY_STATUS_AND_TYPE_AND_FREE_TEXT,
                query = Widget.SELECT_W_FROM_WIDGET_W +
Widget.WHERE_CLAUSE_STATUS_TYPE_FREE_TEXT)

JpaWidgetRepository.java:
        TypedQuery<Widget> query =
manager.createNamedQuery(Widget.WIDGET_GET_BY_STATUS_AND_TYPE_AND_FREE_TEXT,
                Widget.class);
        query.setParameter(Widget.PARAM_STATUS, widgetStatus);
        query.setParameter(Widget.PARAM_TYPE, type);
        setFreeTextSearchTerm(query, searchTerm);
        return getPagedResultList(query, offset, pageSize);

Instead of using NamedQueries we can also use criteria. The new code in
JpaWidgetRepository would be:
        final CriteriaBuilder cb = manager.getCriteriaBuilder();
        final CriteriaQuery<Widget> query = cb.createQuery(Widget.class);
        Root<Widget> widget = query.from(Widget.class);
        List<Predicate> predicates = new ArrayList<Predicate>();
            predicates.add(
                    cb.or(
                            cb.like(cb.lower(widget.<String>get("title")),
getLowerCaseWildCardSearchTerm(searchTerm)),

cb.like(cb.lower(widget.<String>get("description")),
getLowerCaseWildCardSearchTerm(searchTerm))
                    )
            );
            predicates.add(cb.and(cb.equal(widget.<String>get("type"),
type)));

predicates.add(cb.and(cb.equal(widget.<WidgetStatus>get("widgetStatus"),
widgetStatus)));
        query.where(predicates.toArray(new Predicate[predicates.size()]));
        return
manager.createQuery(query).setFirstResult(offset).setMaxResults(pageSize).getResultList();

CriteriaQuery#orderBy() can be dynamically added so that would solve the
issue of having many named queries. The syntax is a bit more difficult to
understand than the named query.
Are there any other ideas that could solve this issue of having a flexible
number of criteria (type or widgetStatus may not be queried) and flexible
sort options?

Jasha Joachimsthal

Europe - Amsterdam - Oosteinde 11, 1017 WT Amsterdam - +31(0)20 522 4466
US - Boston - 1 Broadway, Cambridge, MA 02142 - +1 877 414 4776 (toll free)

www.onehippo.com