On 25 October 2011 15:10, Cooper, Sean D. <[email protected]> wrote:
> 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. > Actually you can use constants but they can't be private. I've just checked in Widget.java that uses constants because I found myself typing the same strings over and over again. > > -Sean > > -----Original Message----- > From: Jasha Joachimsthal [mailto:[email protected]] > Sent: Tuesday, October 25, 2011 9:07 AM > To: [email protected] > 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 >
