Best way to create JPA query that might contain a parameter or might not

Question

there's method that creates JPA query like this

        String queryString = "SELECT i FROM Item i";

        if (null != search) {
            queryString += " WHERE i.name LIKE :pattern";
        }

        TypedQuery<Item> query = entityManager.createQuery(queryString, Item.class);

        if (null != search) {
            query.setParameter("pattern", "%" + search + "%");
        }

and there's 2 checks if query needs to have optional search field (if null != search) what's most common way to avoid that repeat? With single parameter there could be 2 named queries, or probably Criteria API allows to avoid that (because there is no query string), but is there other ways?

use QueryDSL! it's a solution for this 'code smell' and many others.. look: http://www.querydsl.com/ — Rafael Zeffa, Mar 05 '15 at 17:46
that's a new lib, haven't hear about it before, will definitely look at it. — Dainius, Mar 05 '15 at 17:53

score 5 · Accepted Answer · answered Mar 05 '15 at 17:46

5

If you don't have to write the query in such a way that you're writing a query string, you could use the JPA Criteria API. You can see the class I call "ExampleDao" which I use for research and examples here.

You would then add an optional where clause to it, see example below:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Item> criteria = cb.createQuery(Item.class);
Root<Item> root = criteria.from(Item.class);
criteria.select(root);

if (null != search) {
    String pattern = "%pattern here%";
    criteria.where(cb.like(root.get("name"), pattern));
}

answered Mar 05 '15 at 17:46

Schaka

772
9
20

I think you need to have something like "root.get("name")" to compile, but that's pretty elegant example. – Dainius Mar 07 '15 at 09:00
I don't like very much crteria api because of meta model and much more difficult to read it, but looks like no one else will suggest anything completely different. – Dainius Mar 07 '15 at 09:07
This will definitely compile. You can just use static strings in your entity and then, instead of using root.get("name"), you can use Item.FI_NAME, which has the value "name". So that you only have to change it in your entity, if ever and you can never make typos either. – Schaka Mar 07 '15 at 11:56
When I tried to compile original post it complained something like can't cast path to expresion (but as it's just type conversion it might be that my default settings dosn't allow that). Having extra static field looks a bit optional, but it probably would save time in long run and much better then have entire meta model. – Dainius Mar 07 '15 at 15:36
At work, we originally worked with Hibernate SessionFactory and Critera all the time. You need to tell Hibernate Criteria which fields to use certain restrictions on as well and we've always used the extra static field, instead of generating Metamodel files. Personally, I find metalmodels to be a great concept, but also extremely annoying to maintain. So that's why when I first started my own project, I used static strings the way I learned at work, within the entity class. It might not be the right way, but it does the trick. – Schaka Mar 07 '15 at 15:49

Best way to create JPA query that might contain a parameter or might not

1 Answers1

Linked