华水

public class SearchRule {
	private String field;   //查询字段   
    private String op;      //查询操作   
    private String data;    //选择的查询值   
	public String getField() {
		return field;
	}
	public void setField(String field) {
		this.field = field;
	}
	public String getOp() {
		return op;
	}
	public void setOp(String op) {
		this.op = op;
	}
	public String getData() {
		return data;
	}
	public void setData(String data) {
		this.data = data;
	}
}

public class FilterSearch {
	private String groupOp; //多字段查询时分组类型，主要是AND或者OR   
 
	private List<SearchRule> rules; //多字段查询时候，查询条件的集合 
 
	public String getGroupOp() {
		return groupOp;
	}
	public void setGroupOp(String groupOp) {
		this.groupOp = groupOp;
	}
	public List<SearchRule> getRules() {
		return rules;
	}
	public void setRules(List<SearchRule> rules) {
		this.rules = rules;
	}
 
}

public class JqGridHandler {
 
	private HttpServletRequest request = null;
 
	private String _search = "false";
	private String searchField;
	private String searchOper;
	private String searchString;
	private String filters;
	private String sidx = "1";
	private String sord = "desc";
 
	// 存储总体的search
	FilterSearch filterSearch = null;
 
	public JqGridHandler() {
 
	}
 
 
	public JqGridHandler(HttpServletRequest request) {		
		this.request = request;
 
	}
 
	public String getWheres(String prefix, boolean isWhere) {
		conditions();
		if(tranToSQL(prefix).trim().equals("")){
			return "";
		}
		if (!isWhere) {
			return new StringBuilder(" where ").append(tranToSQL(prefix))
					.toString();
		}
		return new StringBuilder(" and ").append(tranToSQL(prefix)).toString();
	}
 
	public String getOrders(String prefix, boolean isOrder) {
		init();
		StringBuilder sb = new StringBuilder();
		if (isOrder) {
			if (null != prefix) {
				sb.append(prefix).append(".");
			}
 
		} else {
			sb.append(" order by ");
			if (null != prefix) {
				sb.append(prefix).append(".");
			}
		}
		return sb.append(doTables(sidx)).append(" ").append(sord).toString();
	}
 
	// 根据conditions转换成sql格式
	public String tranToSQL(String prefix) {
		StringBuilder sb = new StringBuilder("");
 
		if (null != filterSearch) {
			List<SearchRule> rules = filterSearch.getRules();
			int count = 0;
			if (null != rules && (count = rules.size()) > 0) {
				for (SearchRule rule : rules) {
					if (null != rule.getField() && null != rule.getData()
							&& null != rule.getOp()) {
						if ("eq".equalsIgnoreCase(rule.getOp())) {
 
							sb.append(rule.getField()).append(" = ")
									.append("'").append(rule.getData()).append(
											"'");
 
						} else if ("nq".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" != ").append(
									"'").append(rule.getData()).append("'");
						} else if ("lt".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" < ")
									.append("'").append(rule.getData()).append(
											"'");
						} else if ("le".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" <= ").append(
									"'").append(rule.getData()).append("'");
						} else if ("gt".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" > ")
									.append("'").append(rule.getData()).append(
											"'");
						} else if ("ge".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" >= ").append(
									"'").append(rule.getData()).append("'");
						} else if ("bw".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" like ").append(
									"'").append(rule.getData()).append("%")
									.append("'");
						} else if ("ew".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" like ").append(
									"'").append("%").append(rule.getData())
									.append("'");
						} else if ("cn".equalsIgnoreCase(rule.getOp())) {
							if (null != prefix) {
								sb.append(prefix).append(".");
							}
							sb.append(rule.getField()).append(" like ").append(
									"'").append("%").append(rule.getData())
									.append("%").append("'");
						} else {
 
						}
						count--;
						if (count > 0) {
							if (null != filterSearch.getGroupOp()) {
								if (filterSearch.getGroupOp().equals("and"))
									sb.append(" and ");
								else
									sb.append(" or ");
							}
 
						}
					}
 
				}
			}
		}
		return sb.toString();
	}
 
	// 装载
	@SuppressWarnings("unchecked")
	private void conditions() {
		// 初始化，如果request为空，说明是从set进来的。
		init();
 
		// 分拆，全部写入filersearch
		if (null != _search && "true".equalsIgnoreCase(_search)) {
			// 先写多选择的，一般有多选择就不会有单选择。
			if (null != filters && filters.length() > 0) {
				Map m = new HashMap();
				m.put("rules", SearchRule.class);
				filterSearch = (FilterSearch) JsonUtils.getDTOList(filters,
						FilterSearch.class, m);
 
			} else {
				if (null != searchOper && null != searchString
						&& null != searchField) {
					SearchRule rule = new SearchRule();
					rule.setData(searchString);
					rule.setOp(searchOper);
					rule.setField(doTables(searchField));
					filterSearch = new FilterSearch();
					filterSearch.setGroupOp(null);
					List<SearchRule> rules = new ArrayList<SearchRule>();
					rules.add(rule);
					filterSearch.setRules(rules);
				}
			}
		}
 
	}
 
	private  void init() {
		if (request != null) {
			_search = request.getParameter("_search");
			searchOper = request.getParameter("searchOper");
			searchString = request.getParameter("searchString");
			searchField = request.getParameter("searchField");
			filters = request.getParameter("filters");
			sidx = request.getParameter("sidx");
			sord = request.getParameter("sord");
		}
	}
 
	public HttpServletRequest getRequest() {
		return request;
	}
 
	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}
 
	public String get_search() {
		return _search;
	}
 
	public void set_search(String _search) {
		this._search = _search;
	}
 
	public String getSearchField() {
		return searchField;
	}
 
	public void setSearchField(String searchField) {
		this.searchField = searchField;
	}
 
	public String getSearchOper() {
		return searchOper;
	}
 
	public void setSearchOper(String searchOper) {
		this.searchOper = searchOper;
	}
 
	public String getSearchString() {
		return searchString;
	}
 
	public void setSearchString(String searchString) {
		this.searchString = searchString;
	}
 
	public String getFilters() {
		return filters;
	}
 
	public void setFilters(String filters) {
		this.filters = filters;
	}
 
	public String getSidx() {
		return sidx;
	}
 
	public void setSidx(String sidx) {
		this.sidx = sidx;
	}
 
	public String getSord() {
		return sord;
	}
 
	public void setSord(String sord) {
		this.sord = sord;
	}
 
	public FilterSearch getFilterSearch() {
		return filterSearch;
	}
 
	public void setFilterSearch(FilterSearch filterSearch) {
		this.filterSearch = filterSearch;
	}
 
	private String doTables(String str){
		if(str.startsWith("__")){
			str = str.substring(2);
			return str.replaceAll("_", ".");
		} else {
			return str;
		}
 
	}
}