How to make this short in Grails or Hibernate

I've got query that looks something like this:

SomeDomain.executeQuery("""
    from
        SomeDomain x
    where
        x.linkToSomeA = coalesce(:a, x.linkToSomeA) and
        x.linkToSomeB = coalesce(:b, x.linkToSomeB) and
        ...
    """,
    [a: someA, b: someB, ...]
)

The intended behaviour is as follows: once null is supplied for a criterion parameter, this criterion should be ignored. If anything other than null is supplied, the criterion is used.

Everything would work fine, except that Hibernate doesn't allow supplying null where domain class instances are expected. So in the end this is not a valid query (in fact NullPointerException will be thrown if any nulls are supplied).

I haven't fo开发者_如何学Pythonund any way to rewrite this without using a ton of if-else. Does anybody have any idea how to make this about equally brief and valid at the same time?

NOTE: The real query is more complicated than that so please, do not suggest rewriting this using anything else than executeQuery.

This is generally for this kind of problem that the Criteria api is used: it allows building a query dynamically:

Criteria c = session.createCriteria(SomeDomain.class, "x");
if (a != null) {
    c.add(Restrictions.eq("x.linkToSomeA", a));
}
if (b != null) {
    c.add(Restrictions.eq("x.linkToSomeB", b));
}
// ...

This answer doesn't follow your request of not using anything else than executeQuery, but you might also ask how to build a house using nothing else than a screwdriver, and the best answer would still be "buy some other tools").

I'm wondering if you can use a map and findAll(), like this:

def query = [
    'a': 'x.linkToSomeA = :a',
    'b': 'x.linkToSomeB = :b'
].findAll{ k, q -> params[k] != null }.values().join(' and ')

SomeDomain.executeQuery("""
    from
        SomeDomain x
    where
        ${query}
    """,
    params
)

This should return a list of all query elements that have non-null parameters in the params map (this should be changed to match the map used in the query). It's a simple job of filtering out based on the "key" of the main map, then joining the values with and.

Note, there's one potential issue, and that's if there's no params at all. In that case, you'll want to check that query is non-empty or provide a fallback method.