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
