HQLUtils { public static void appendFilterCriteria(final StringBuilder toAppendTo, final List<Object> params, final String selector, final Map<String, List> filter) { if (filter != null) { final List joinObj = filter.remove(SearchContext.JOIN); final String join; if (joinObj != null && !joinObj.isEmpty() && SearchContext.JoinMode.OR.matches(joinObj.get(0))) { join = " or "; } else { join = " and "; } boolean hasOneCriteria = false; for (final Map.Entry<String, List> props : filter.entrySet()) { final List prop = props.getValue(); if (CollectionUtils.isNotEmpty(prop)) { for (final Object val : prop) { String matchProp; String matchMode = " like "; boolean nullCheck = false; Object matchValue; if (val instanceof String) { if (StringUtils.isBlank((String) val)) { continue; } matchProp = " lower(" + selector + "." + props.getKey() + ") "; matchValue = HQLUtils.criteriaIlikeAnywhere((String) val); } else if (val instanceof Pair) { final SearchContext.MatchMode mm = (SearchContext.MatchMode) ((Pair) val).getFirst(); switch (mm) { case LT: matchMode = " < "; break; case LE: matchMode = " <= "; break; case EQ: matchMode = " = "; break; case GE: matchMode = " >= "; break; case GT: matchMode = " > "; break; case ANY: matchMode = " in "; break; case NULL: matchMode = " is null "; nullCheck = true; break; case NOTNULL: matchMode = " is not null "; nullCheck = true; break; case EMPTY: matchMode = " = '' "; nullCheck = true; break; case NOTEMPTY: matchMode = " != '' "; nullCheck = true; break; } matchProp = " " + selector + "." + props.getKey() + " "; if (!nullCheck) { matchValue = ((Pair) val).getSecond(); if (matchValue == null) { continue; } final boolean isString = matchValue instanceof String; if (isString) { if (StringUtils.isBlank((String) matchValue)) { continue; } matchProp = " lower(" + selector + "." + props.getKey() + ") "; matchValue = HQLUtils.criteriaIeq((String) matchValue); } } else { matchValue = null; } } else { matchProp = " " + selector + "." + props.getKey() + " "; matchValue = val; } final int pIdx = params.size() + 1; if (hasOneCriteria) { toAppendTo.append(join); } else { if (pIdx == 1) { toAppendTo.append(" where (\n"); } else { toAppendTo.append(" and (\n"); } } if (nullCheck) { toAppendTo.append(" (" + matchProp + matchMode + ") \n"); } else { toAppendTo.append(" (" + matchProp + matchMode + "?" + pIdx + ") \n"); params.add(matchValue); } hasOneCriteria = true; } } } if (hasOneCriteria) { toAppendTo.append(")"); } } } private  HQLUtils(); static String criteriaIlikeAnywhere(final String value); static String criteriaLikeAnywhere(final String value); static String criteriaIeq(final String value); static String criteriaEq(final String value); static Object criteriaInTest(final Collection value); static Collection criteriaIn(final Collection value); static void appendBlacklistCriteria(final StringBuilder toAppendTo,
                                               final List<Object> params,
                                               final String selector,
                                               final List<Pair<String, List>> blacklist); static void appendFilterCriteria(final StringBuilder toAppendTo,
                                            final List<Object> params,
                                            final String selector,
                                            final Map<String, List> filter);  }
@Test public void testAppendFilterCriteria() throws Exception { final Map<String, List> filter = new LinkedHashMap<>(); assertWithFilter(null, "select from XEntity e "); filter.put("prop1", Collections.singletonList("Val1")); filter.put("prop2", null); filter.put("prop3", Collections.singletonList("")); assertWithFilter(filter, "select from XEntity e where (\n ( lower(e.prop1) like ?1) \n)", "%val1%"); filter.clear(); filter.put("prop1", Collections.singletonList("Val1")); filter.put("prop2", Collections.singletonList(SearchContext.MatchMode.ANY.toParam(Collections.singleton("val1")))); assertWithFilter(filter, "select from XEntity e where (\n ( lower(e.prop1) like ?1) \n and ( e.prop2 in ?2) \n)", "%val1%", Collections.singleton("val1")); filter.clear(); SearchContext.JoinMode.OR.setMode(filter); filter.put("prop1", Collections.singletonList("Val1")); filter.put("prop2", Collections.singletonList(SearchContext.MatchMode.ANY.toParam(Collections.singleton("val1")))); assertWithFilter(filter, "select from XEntity e where (\n ( lower(e.prop1) like ?1) \n or ( e.prop2 in ?2) \n)", "%val1%", Collections.singleton("val1")); filter.clear(); final Instant to = Instant.now(); final Instant from = to.minusMillis(100L); filter.put("prop1", Arrays.asList(SearchContext.MatchMode.LE.toParam(from), SearchContext.MatchMode.GT.toParam(to))); assertWithFilter(filter, "select from XEntity e where (\n ( e.prop1 <= ?1) \n and ( e.prop1 > ?2) \n)", from, to); filter.clear(); filter.put("prop1", Collections.singletonList("Val1")); filter.put("prop2", Collections.singletonList(SearchContext.MatchMode.ANY.toParam(Collections.singleton("val1")))); filter.put("prop3", Arrays.asList(SearchContext.MatchMode.LE.toParam(from), SearchContext.MatchMode.GT.toParam(to))); assertWithFilter(filter, "select from XEntity e where (\n ( lower(e.prop1) like ?1) \n and ( e.prop2 in ?2) \n and ( e.prop3 <= ?3) \n and ( e.prop3 > ?4) \n)", "%val1%", Collections.singleton("val1"), from, to); filter.clear(); filter.put("prop1", Collections.singletonList(SearchContext.MatchMode.EQ.toParam("Val1"))); assertWithFilter(filter, "select from XEntity e where (\n ( lower(e.prop1) = ?1) \n)", "val1"); filter.clear(); filter.put("prop1", Collections.singletonList(SearchContext.MatchMode.NOTNULL.toParam(null))); filter.put("prop2", Collections.singletonList(SearchContext.MatchMode.NULL.toParam(null))); assertWithFilter(filter, "select from XEntity e where (\n ( e.prop1 is not null ) \n and ( e.prop2 is null ) \n)"); filter.clear(); filter.put("prop1", Collections.singletonList(SearchContext.MatchMode.NOTEMPTY.toParam(null))); filter.put("prop2", Collections.singletonList(SearchContext.MatchMode.EMPTY.toParam(null))); assertWithFilter(filter, "select from XEntity e where (\n ( e.prop1 != '' ) \n and ( e.prop2 = '' ) \n)"); }