业务场景： 一个数据表格， n 个搜索框，点击搜索按钮后，刷新数据表格。 查询条件可能很多， 也可能会变动。
为了减少 这部分 样板代码 的开发工作，通过 拦截器生成 sql where 条件。
例如：
<input name='search_EQ_username' value = 'chuang' > <input name='search_LIKE_phone' value = '152' > <input name='search_GTE_age' value = '25' > ... 更多搜索框 拦截器生成 where 语句， where username = 'chuang' and phone like '%152%' and age >= 25 ， 前端页面增加 变动 搜索框，不改后端代码。
// 普通 controller
@Before(SearchSql.class)
public void query() {
// 分页参数
int pageNumber = getAttr("pageNumber");
int pageSize = getAttr("pageSize");
// where 条件
String where = getAttr(Constant.SEARCH_SQL);
// 查询数据
Page<SysUser> sysUserPage = SysUser.dao.page(pageNumber, pageSize, where);
renderDatagrid(sysUserPage);
}/** * 拦截器， 可扩展更多 sql 生成逻辑 * 只使用 mysql, 其它数据库自行修改 生成sql 语句逻辑 * * @author 佚名 */ public class SearchSql implements Interceptor { public void intercept(Invocation ai) { Controller c = ai.getController(); // 自定义 查询字段前缀 String prefix = "search_"; Map<String, Object> searchParams = getParametersStartingWith(c.getRequest(), prefix); Map<String, SearchFilter> filters = SearchFilter.parse(searchParams); String whereSql = buildFilter(filters.values()); c.setAttr(Constant.SEARCH_SQL, whereSql); // 自定义分页字段名 int pageNumber = c.getParaToInt("page", 1); int pageSize = c.getParaToInt("rows", 1); c.setAttr("pageNumber", pageNumber); c.setAttr("pageSize", pageSize); ai.invoke(); } /** * 取得带相同前缀的Request Parameters, copy from spring WebUtils. * 返回的结果的Parameter名已去除前缀. */ private Map<String, Object> getParametersStartingWith( HttpServletRequest request, String prefix) { Enumeration<String> paramNames = request.getParameterNames(); Map<String, Object> params = new TreeMap<>(); if (prefix == null) { prefix = ""; } while (paramNames != null && paramNames.hasMoreElements()) { String paramName = paramNames.nextElement(); if ("".equals(prefix) || paramName.startsWith(prefix)) { String unprefixed = paramName.substring(prefix.length()); String[] values = request.getParameterValues(paramName); //noinspection StatementWithEmptyBody if (values == null || values.length == 0) { // Do nothing, no values found at all. } else if (values.length > 1) { params.put(unprefixed, values); } else { params.put(unprefixed, values[0]); } } } return params; } /** * 按属性条件列表创建查询字句 */ private String buildFilter(final Collection<SearchFilter> filters) { StringBuilder sb = new StringBuilder(); if (null != filters && filters.size() > 0) { for (SearchFilter filter : filters) { if (sb.length() > 0) { sb.append(" and "); } sb.append(filter.fieldName); // 此处 可能要根据数据库类型 修改 switch (filter.operator) { case EQ: // 相等 条件 sb.append(" ='").append(filter.value).append("'"); break; case LIKE: // 模糊查询，首尾添加 % sb.append(" like ").append("'%").append(filter.value).append("%'"); break; case LIKECUST: // 模糊匹配，* 替换为 % sb.append(" like ").append("'").append(filter.value.toString().replaceAll("\\*","%")).append("'"); break; case GT: sb.append(" >'").append(filter.value).append("'"); break; case LT: sb.append(" <'").append(filter.value).append("'"); break; case GTE: sb.append(" >='").append(filter.value).append("'"); break; case LTE: sb.append(" <='").append(filter.value).append("'"); break; case NEQ: // 不等于 sb.append(" !='").append(filter.value).append("'"); break; case INN: // in 数字 sb.append(" in (").append(filter.value).append("')"); break; case INS: // in 字符串 sb.append(" in ('").append(filter.value.toString().replaceAll(",", "','")).append("')"); break; case IS: // is null sb.append(" is null "); break; case ISNOT: // is not null sb.append(" is not null "); break; } } } return sb.toString(); } } class SearchFilter { // 查询字段名 public final String fieldName; // 查询字段值 public final Object value; // 查询条件 public final Operator operator; public SearchFilter(String fieldName, Operator operator, Object value) { this.fieldName = fieldName; this.value = value; this.operator = operator; } /** * searchParams中key的格式为OPERATOR_FIELDNAME */ public static Map<String, SearchFilter> parse(Map<String, Object> searchParams) { Map<String, SearchFilter> filters = new HashMap<>(); for (Map.Entry<String, Object> entry : searchParams.entrySet()) { // 过滤掉空值 String key = entry.getKey(); Object value = entry.getValue(); if (StrKit.isBlank((String) value)) { continue; } // 拆分operator与field String[] names = key.split("_"); if (names.length < 2) { throw new IllegalArgumentException(key + " is not a valid search filter name"); } // field 中可能有查询条件 String filedName; StringBuilder filedNameTemp = new StringBuilder(); for (int i = 1; i < names.length; i++) { filedNameTemp.append(names[i]).append("_"); } if (filedNameTemp.substring(filedNameTemp.length() - 1).equals("_")) { filedNameTemp = new StringBuilder(filedNameTemp.substring(0, filedNameTemp.length() - 1)); } filedName = filedNameTemp.toString(); // 查询条件 Operator operator = Operator.valueOf(names[0]); // 创建searchFilter SearchFilter filter = new SearchFilter(filedName, operator, value); filters.put(key, filter); } return filters; } public enum Operator { EQ, LIKE,LIKECUST, GT, LT, GTE, LTE, NEQ, INS, INN, IS, ISNOT } }
