在开发中如果一个grid控件的查询语句包含了N张表和N个子查询，SQL查询语句中通常列名都用了表的别名例如： a.column1,b.column2,(select xx from ...)column3....。而前端grid控件的column的index通常是不带表的别名的。
那么为了grid能实现高级查询和x列排序以及条件组合模式(AND 或 OR)，通常后台有两种处理方式：
1.比较通用的处理方式是把查询语句作为子查询再根据前端传入的动态条件参数、排序参数和组合模式生成WHERE条件语句、ORDER语句直接加在WHERE后面，这样就不用处理表别名。
但是这种方法效率非常低。通常grid控件的查询语句都要执行两条，一条是计算数据总数，另一条是获取当前页得数据。
在计算数据总数SQL语句中通常是SELECT count(*) + FROM语句+WHERE语句,然而这种方式把查询语句作为子查询了：SELECT count(*) from (SELECT a.column1,b.column2,(select xx from ...)column3....。
column越多执行SQL时间越长。再来看看动态WHERE条件参数被放到子查询外面，这个是比较要命的，在数据量大、grid查询语句里还包含有子查询、动态WHERE添加参数多的情况下你会发现你的grid越来越慢。
2.根据前端传入的动态条件参数和排序参数判断表别名，再把条件参数加上表别名然后再生成WHERE语句和ORDER语句直接加到grid查询语句后面。这种方式需要事先知道各个column对应的表别名而起在以下几种情况是无法生成正确的SQL语句：
a) 条件参数含有子查询列.
b) 排序参数中含有子查询列.
c) 条件参数或排序参数含有别名的列.
为了提高SQL查询效率和避免在每个grid的后台代码里进行复杂的判断和SQL语句组装，前段时间我做了一个通用的高级查询处理器，根据前端传入的JqGrid参数，用正则表达式计算出当前column的表别名、是否子查询列，再根据组合模式动态生成查询语句。下面举个例子说明处理后生成的查询语句：(注：为了简单易懂，查询条件不是代码里要求的格式)
select a.id,a.a1,b.b1,c.c1,(select ...)sub1,(select ...) as sub2 from a,b,c, where a.id=b.aid and b.id=c.bid order by a.id
当查询条件为：{group=AND,filter:{a1=?,b1=?,c1=?}}
生成SQL语句为：select a.id,a.a1,b.b1,c.c1,(select ...)sub1,(select ...)sub2 from a,b,c, where a.id=b.aid and b.id=c.bid and a.a1=? and b.b1=? and c.c1=? order by a.id
当查询条件为：{group=AND,filter:{a1=?,b1=?,c1=?,sub1=?}}
生成SQL语句为：select * from (select a.id,a.a1,b.b1,c.c1,(select ...)sub1,(select ...)sub2 from a,b,c, where a.id=b.aid and b.id=c.bid and a.a1=? and b.b1=? and c.c1=? order by a.id)t where 1=1 and sub1=?
当查询条件为：{group=AND,filter:{a1=?,b1=?,c1=?,sub1=?},orColumn=sub2,ord=desc}
生成SQL语句为：select * from (select a.id,a.a1,b.b1,c.c1,(select ...)sub1,(select ...)sub2 from a,b,c, where a.id=b.aid and b.id=c.bid and a.a1=? and b.b1=? and c.c1=?)t where 1=1 and sub1=? order by sub2 desc
当查询条件为：{group=OR,filter:{a1=?,b1=?,c1=?,sub1=?},orColumn=sub2,ord=desc}
生成SQL语句为：select * from (select a.id,a.a1,b.b1,c.c1,(select ...)sub1,(select ...)sub2 from a,b,c, where a.id=b.aid and b.id=c.bid)t where 1=1 and (a1=? OR b1=? OR c1=? OR sub1=?) order by sub2 desc
JqGridParamModel.java
filters参数格式为json格式如：String filter = "{\"groupOp\":\"AND\",\"rules\":[{\"field\":\"order_code\",\"op\":\"cn\",\"data\":\"5101\"}, {\"field\":\"name\",\"op\":\"cn\",\"data\":\"chengdu\"},{\"field\":\"create_date\",\"op\":\"ge\",\"data\":\"2013-03-15\"}]}";
package com.mtw.web.model;
import java.io.Serializable;
/**
* @filename：JqGridParamModel.java
* @author：martinwu
* @version：v1.0
* @Description：
*/
public class JqGridParamModel implements Serializable{
private static final long serialVersionUID = -2947651640240143391L;
private int _page=1;
private int _rows=15;
//过滤条件
private String filters;
private String _search;
private String page;
private String rows;
//排序字段
private String sidx;
//排序关键字
private String sord;
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public String getPage() {
if(null!=page&&!"".equals(page)){
this.page=Integer.toString(_page);
}
return page;
}
public void setPage(String page) {
this.page = page;
}
public String getRows() {
if(null!=rows&&!"".equals(rows)){
this.rows=Integer.toString(_rows);
}
return rows;
}
public void setRows(String rows) {
this.rows = rows;
}
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;
}
}
JqGridSearchParamHandler.java
如果有需要自定义处理的列可以在调用方法里传入CallBack对象如:
Map<String, String> sqlMap = JqGridSearchParamHandler.processSqltoMap(colums.toString(), from.toString(), order.toString(),jqGridParam, new CallBack() {
public String executeQuery(String f, String o, String d) {
if("xxx".equals(f)){
/*
*to process f
*to process o
*to process d
*/
return JqGridSearchParamHandler.processOperater(f, o, d);
}
}
});
自定义子查询Order列
String sidx = jqGridParam.getSidx();
if("xxx".equals(sidx)){
jqGridParam.setSidx("(SELECT xxx FROM table1 t1 WHERE t1.t_id=t.id ...)");
}
package com.mtw.tools;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.mtw.web.model.JqGridParamModel;
/**
* @filename：JqGridSearchParamHandler.java
* @author：martinwu
* @version：v1.0
* @Description：
*/
public class JqGridSearchParamHandler {
public static final String SQL_COLUMNS = "columns";
public static final String SQL_FROM = "from";
private static Map<String, String> operator = new HashMap<String, String>();
private static ObjectMapper mapper = new ObjectMapper();
public static interface CallBack {
public String executeQuery(String f, String o, String d);
}
static {
operator = new HashMap<String, String>();
// ['eq','ne','lt','le','gt','ge','bw','bn','in','ni','ew','en','cn','nc']
operator.put("eq", " = ");
operator.put("ne", " <> ");
operator.put("lt", " < ");
operator.put("le", " <= ");
operator.put("gt", " > ");
operator.put("ge", " >= ");
operator.put("bw", " LIKE ");
operator.put("bn", " NOT LIKE ");
operator.put("in", " IN ");
operator.put("ni", " NOT IN ");
operator.put("ew", " LIKE ");
operator.put("en", " NOT LIKE ");
operator.put("cn", " LIKE ");
operator.put("nc", " NOT LIKE ");
}
public static String processSql(String columns, String from,
String order, JqGridParamModel jqGridParam) throws Exception {
return processSql(columns, from, order, jqGridParam, null);
}
public static Map<String,String> processSqltoMap(String columns, String from,
String order, JqGridParamModel jqGridParam) throws Exception {
return processSqltoMap(columns, from, order, jqGridParam, null);
}
public static String processSql(String columns, String from,
String order, JqGridParamModel jqGridParam, CallBack callback) throws Exception {
Map<String,String> map = processSqltoMap(columns, from, order, jqGridParam,callback);
return map.get(JqGridSearchParamHandler.SQL_COLUMNS)+map.get(JqGridSearchParamHandler.SQL_FROM);
}
@SuppressWarnings({ "unchecked"})
public static Map<String,String> processSqltoMap(String columns, String from,
String order, JqGridParamModel jqGridParam, CallBack callback) throws Exception {
Map<String,String> result = new HashMap<String, String>();
StringBuffer rFrom = new StringBuffer();
List<Map<String,String>> subSelectColumn = new ArrayList<Map<String,String>>();
String filter = jqGridParam.getFilters();
StringBuffer query = new StringBuffer();
Map<String, Object> _filter = null;
boolean isOrderISub = isSubSelectColumn(jqGridParam.getSidx(), columns);
String group = "";
if (filter!=null&&!"".equals(filter)&&(_filter = mapper.readValue(filter, Map.class)) != null) {
group = (String) _filter.get("groupOp");
List<Map> rules = (List<Map>) _filter.get("rules");
int i = 0;
for (Map<String, String> o : rules) {
if(o.get("field")==null||"".equals(o.get("field"))) continue;
String field = o.get("field").trim();
String op = o.get("op").trim();
String data = o.get("data").trim();
String _query = null;
if ((op != null && !"".equals(op.trim()))&& (data != null && !"".equals(data.trim()))) {
i++;
if (null != callback) {
_query = callback.executeQuery(field, op, data);
}
if (_query!=null&&_query.length()!=0) {
query.append(" ").append(group).append(" ").append(_query);
} else {
String aliasColumn = (field.indexOf(".")!=-1) ? field : getAliasColumn(columns, o, subSelectColumn);
//如果为空则是子查询列
if (aliasColumn!=null&&aliasColumn.length()!=0) {
_query = processOperater(aliasColumn, op, data);
query.append(" ").append(group).append(" ").append(_query);
}
}
}
}
}
//处理子查询列
if(subSelectColumn.size()>0||isOrderISub){
StringBuffer subSelectCondition = new StringBuffer(")t where 1=1");
for (int j = 0; j < subSelectColumn.size(); j++) {
Map<String,String> rule = subSelectColumn.get(j);
subSelectCondition.append(" ").append(group).append(" ").append(processOperater(rule.get("field"), rule.get("op"), rule.get("data")));
}
if("OR".equals(group)&&subSelectCondition.length()>12&&query.length()>0){
subSelectCondition.append(query.toString().replaceAll("\\w+\\.", ""));
processGroup(group,subSelectCondition,true);
query.delete(0, query.length());
}
else{
processGroup(group,subSelectCondition,true);
processGroup(group,query,false);
}
result.put(SQL_COLUMNS, "SELECT *");
order = processOrder(order,columns,jqGridParam,isOrderISub);
//处理自定义比较复杂的子查询作为 order
if((order.toLowerCase().indexOf("(")!=-1||!isOrderISub)&&subSelectCondition.length()>12){
rFrom.append(" FROM (").append(columns).append(" ").append(from).append(query).append(" ").append(order).append(subSelectCondition);
}
else{
rFrom.append(" FROM (").append(columns).append(" ").append(from).append(query).append(subSelectCondition).append(" ").append(order);
}
result.put(SQL_FROM, rFrom.toString());
return result;
}
processGroup(group,query,false);
result.put(SQL_COLUMNS, columns);
order = processOrder(order,columns,jqGridParam,isOrderISub);
rFrom.append(" ").append(from).append(query).append(" ").append(order).toString();
result.put(SQL_FROM, rFrom.toString());
return result;
}
public static String processOperater(String field, String op, String data) {
StringBuffer condition = new StringBuffer();
condition.append(field).append(operator.get(op));
if (op.equals("in") || op.equals("ni")) {
condition.append("(").append(data).append(")");
} else if (op.equals("bw") || op.equals("bn")) {
condition.append("'").append(data).append("%'");
} else if (op.equals("ew") || op.equals("en")) {
condition.append("'%").append(data).append("'");
} else if (op.equals("cn") || op.equals("nc")) {
condition.append("'%").append(data).append("%'");
} else {
condition.append("'").append(data).append("'");
}
return condition.toString();
}
//处理Order
private static String processOrder(String order,String columns,JqGridParamModel jqGridParam,boolean isOrderISub){
String sidx = jqGridParam.getSidx();
String sord = jqGridParam.getSord();
if(sidx!=null &&!"".equals(sidx)){
if(sidx.indexOf(".")==-1&&!isOrderISub){
Map<String,String> map = new HashMap<String, String>();
map.put("field", sidx);
sidx = getAliasColumn(columns, map, null);
}
order = " ORDER BY " + sidx +" " +sord;
}
return order;
}
//处理Group
private static StringBuffer processGroup(String group,StringBuffer conditions,boolean isSub){
if("OR".equals(group)&&conditions.length()!=0&&!isSub){
conditions.replace(1, group.length()+2,"AND (").append(")");
}
else if("OR".equals(group)&&conditions.length()>12&&isSub){
conditions.replace(13, 14+group.length(), "AND (").append(")");
}
return conditions;
}
private static boolean isSubSelectColumn(String column,String columns){
if(column==null||"".equals(column.trim()))
return false;
String patternString = "\\) *(as +)?"+column+"(( *,)|( +from)|( *$))";
Pattern pattern = Pattern.compile(patternString);
Matcher matcher = pattern.matcher(columns);
if (matcher.find()) {
return true;
}
return false;
}
private static String getAliasColumn(String select,Map<String,String> rule,List<Map<String,String>> subSelectColumn){
String aliasColumn = rule.get("field").trim().toLowerCase();
String _select = select.toLowerCase();
String patternString = "(\\w{1,}\\." + aliasColumn+")(( *,)|( +from)|( *$))";
Pattern pattern = Pattern.compile(patternString);
System.out.println(patternString);
Matcher matcher = pattern.matcher(_select.replaceAll("(\\(select[^\\)]+\\))( *\\w+)(( *,)|( *$))", ""));
if (matcher.find()) {
System.out.println(matcher.group(1) +" Found In Setup one!");
return matcher.group(1);
}
patternString = "(\\w{1,}\\.\\w{1,})( +(as +)?"+aliasColumn+")";
pattern = Pattern.compile(patternString);
matcher = pattern.matcher(_select);
if (matcher.find()) {
System.out.println(matcher.group(1) +" Found In Setup two!");
return matcher.group(1);
}
patternString = "\\) *(as +)?"+aliasColumn+"(( *,)|( +from)|( *$))";
pattern = Pattern.compile(patternString);
matcher = pattern.matcher(_select);
if (matcher.find()&&rule.get("op")!=null) {
System.out.println(matcher.group(0) +"Found In Setup three!");
subSelectColumn.add(rule);
return null;
}
return aliasColumn;
}
}
相关推荐
jqGrid详解及高级应用
jqGrid 高级简化配置 使jqGrid页面代码更简化
jqGrid 按多个条件 或单个条件进行查询
jqgriddemo,样式漂亮，可以直接使用，满足多种复杂的前端表格操作功能，有需要的小伙伴可以学习学习
jquery.jqGrid-4.6.0（jquery表格插件）.zip----------jqGrid4.6.0包，官网下载的，原封不动的在这里。 jqgrid_demo40-----可用的-使用方法请查看README文件.rar-----------经我修改过的demo40，官网上面demo40下载...
jqGrid 学习之 皮肤 2 jqGrid 学习 2 jQuery 学习之：jqGrid 表格插件 参数配置 4 jqGrid 学习 自定义搜索 14 jqGrid 学习 搜索工具栏 16 jqGrid 学习 搜索 17 jqGrid 学习 自定义格式化类型 19 jqGrid 学习 格式化 ...
jquery jqgrid 参数详
jqgrid
jqGrid表格内容查询读取代码是一款列表详细页表格布局，生成表格内容，表格查询代码等。
代码简介：jqGrid表格内容查询读取代码是一款列表详细页表格布局，生成表格内容，表格查询代码等。
jqgrid中文文档API，使用JQGRID
jquery.jqGrid-4.8.2（jquery表格插件）.zip----------jqGrid4.8.2包，官网下载的，原封不动的在这里。 jqgrid_demo40-----可用的-使用方法请查看README文件. jqgrid_3.6.5_API_en.chm------附加放在这里的其它资料...
JqGrid插件+JqGridDemo+JqGrid主题，自己项目里引入的文件
大名鼎鼎的jquery的jqgrid php版本 我做了修改，改成了sql2000数据库版本
jquery.jqgrid
JqGrid中文API文档，包含了基础的JqGrid控件的属性方法事件等，有如何使用JqGrid的Demo.
jqgrid 导出成为 JSON, XML, CSV, TSV, TXT, SQL, Word, Excel, PNG，PDF格式
jqGrid5.5 学习
jqGrid demo
jqgrid+jqueryui.theme