在开发中如果一个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;
}
}
相关推荐
然后，执行SQL查询并返回结果集给jqGrid展示。 总的来说，jqGrid的查询功能通过JSON格式的参数实现了灵活的数据过滤。在后端，开发者需要理解这些参数的结构，并编写相应的代码来处理这些查询请求，将其转化为...
jqGrid 高级简化配置 使jqGrid页面代码更简化
通过研究其代码和实际运行效果，可以快速掌握jqGrid的基本用法和高级技巧。对于初学者来说，可以从以下几个方面入手： 1. **安装和引入**：了解如何将jqGrid添加到HTML页面中，以及需要引用哪些CSS和JS文件。 2. *...
1. **SQL查询优化**：可能采用了更兼容SQL Server 2000的查询语句，如避免使用不支持的函数或特性和SQL Server 2000版本相关的优化技巧。 2. **数据连接**：连接字符串和SQL命令可能已根据SQL Server 2000的配置进行...
jqGrid是一个强大的jQuery插件，主要用于在网页上以表格形式展示数据。它拥有全面的文档支持，并提供中文版本，方便开发者使用。jqGrid的主要特性包括： 1. 完全的JavaScript API控制：开发者可以通过JavaScript ...
在"jqGrid表格内容查询读取代码.zip"压缩包中，我们可以找到实现jqGrid表格内容查询读取的相关代码。 首先，让我们深入理解jqGrid的核心概念： 1. **初始化表格**：在HTML页面中，我们需要创建一个空的表格元素，...
jquery jqgrid 参数详
3. 排序与筛选：用户可以通过点击表头进行列排序，同时JqGrid还提供了高级的筛选功能，如条件筛选、多条件组合筛选等。 4. 编辑功能：JqGrid支持行内编辑、弹出式编辑和表单编辑等多种模式，方便用户对表格数据进行...
9. 性能优化：jqGrid5.5版本在处理大量数据时进行了性能优化，提高了数据加载速度，降低了内存占用，提升了用户体验。 10. 文档和示例：jqGrid拥有详尽的文档和丰富的示例代码，帮助开发者快速上手并解决遇到的问题...
在实际应用中，还需要进一步完善代码逻辑，例如添加异常处理、优化 SQL 查询语句、使用 PreparedStatement 替代 Statement 来防止 SQL 注入等。此外，对于前端 jqGrid 的配置也需要进行相应的调整，以确保能够正确...
在这个“jqGrid表格内容查询读取代码”中，我们将深入探讨如何使用jqGrid来构建一个具有查询和读取功能的表格。 首先，我们需要引入jqGrid的JavaScript和CSS文件。这些文件通常在`jqgrid`目录下，包括`jquery....
6. **SQL**: 结构化查询语言，是用于管理关系数据库的标准语言。jqGrid 可以生成 SQL 语句，将表格数据转化为数据库导入语句，方便数据入库。 7. **Word**: Microsoft Word 文件，适合生成报告或文档。jqGrid 可以...
创建一个存储过程或SQL查询，用于获取需要显示在jqGrid中的数据。 3. **配置jqGrid** 在HTML中定义一个div元素作为jqGrid的容器，并通过JavaScript设置其配置项。这些配置项包括列定义、数据源、分页参数等。例如...
9. **自定义功能**：jqGrid 的强大之处在于其高度可定制性，开发者可以通过编写插件或扩展其 API 来实现更复杂的功能需求。 通过理解和运用这些知识点，你可以利用 jqGrid4.6 完整包构建出功能强大的、交互友好的...
本文将深入探讨jqGrid的核心特性、功能、以及3.3.2版本的改进之处。 首先，jqGrid作为一款强大的表格插件，为网页开发者提供了丰富的数据展示和管理工具。它支持多种操作，包括数据的增删查改、排序、分页、过滤和...
jQgrid中文文档API jQgrid是一个功能强大且流行的JavaScript插件，用于创建交互式表格。它基于jQuery库，提供了许多强大的功能，例如排序、过滤、编辑、分页等，使得开发者可以快速创建复杂的表格应用程序。本文将...
JqGrid还提供了其他高级功能，如行选择、列自适应、导出数据、树形结构展示等。通过配置`multiselect`、`autoresizeAllColumns`、`gridview`等参数，可以启用这些特性。 通过深入学习JqGrid中文API文档，开发者可以...
三、jqGrid高级特性 1. 行内编辑与保存：通过`editRow`和`saveRow`方法实现行内编辑，通过`beforeSaveRow`和`afterSaveRow`事件处理数据验证和后端交互。 2. 表头过滤：使用`filterToolbar`方法开启表头过滤功能，...
通过事件处理器，如`loadComplete`、`beforeSelectRow`等，可以实现自定义行为，增强JQGrid的功能。 9. **主题与外观** JQGrid允许自定义样式，可以使用内置的主题，如`ui.jqgrid.css`，或者创建自己的CSS样式来...
jquery.jqGrid-4.8.2（jquery表格插件）.zip----------jqGrid4.8.2包，官网下载的，原封不动的在这里。 jqgrid_demo40-----可用的-使用方法请查看README文件. jqgrid_3.6.5_API_en.chm------附加放在这里的其它资料...