Op 25 okt. 2011 17:50 schreef "Carlucci, Tony" <[email protected]> het volgende: > > 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.
OpenJPA doesn't cache queries that involve paging or multiple tables so in most cases we won't notice the difference. 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 :) Good method names are even more helpful :) I'll go for the Criteria in this case, thanks! Jasha P.s. if my reply messes up the email format, it's my mobile phone > > Tony > > --- > Anthony Carlucci | SW App Dev Eng, Sr. | R501 / KW App Development & Maint > e: [email protected] | 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:[email protected]] > Sent: Tuesday, October 25, 2011 9:11 AM > To: [email protected] > 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:[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 >
