jqwidgets jqxgrid 使用心得（初级）及集成JAVA SSM框架通用后台配置（急速列表开发）
项目采用Spring springmvc mybatis框架开发
主要功能需求
- 服务器端分页
- 服务器端查询（筛选）
- 服务器端排序
简单配置无需重复编写后台
实现思路
- 前端提供分页所需字段：页码、每页显示行数、过滤条件、排序条件、表名、初始过滤条件（除了表名外，其他字段由插件自行提供）
- 服务器端通过表名及其他字段拼装Mybatis的查询语句，并返回控件所需的json数据，Controller层 Service层、Dao层使用通用List
Controller层对参数进行处理，拼装where条件
注意
1.前端写表名可能存在服务器安全问题；
2.jqxgrid默认get提交方式可能存在sql注入问题；
3.需要进行本地化
4.引入js时不要图简单直接使用all，all文件过大 会导致效率极低 且容易卡死低版本浏览器
5.异步加载数据时，列表其他操作，如本地化等一定要放在bindingcomplete 事件中，否则会报错
以下附上代码
需要引入的文件（官网demo内的）
<script type="text/javascript" src="${ctx}/scripts/jquery.min.js"></script>
<link rel="stylesheet" href="${ctx}/plugins/jqwidgets/styles/jqx.base.css" type="text/css">
<link rel="stylesheet" href="${ctx}/plugins/jqwidgets/styles/jqx.arctic.css" type="text/css">
<%-- <script type="text/javascript" src="${ctx}/plugins/jqwidgets/jqx-all.js"></script>--%>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxcore.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxdata.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxbuttons.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxscrollbar.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxmenu.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxlistbox.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxdropdownlist.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.selection.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.columnsresize.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.filter.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.sort.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.pager.js" > </script>
<script type = "text/javascript"src ="${ctx}/plugins/jqwidgets/jqxgrid.grouping.js" > </script>
<script type="text/javascript" src="${ctx}/plugins/jqwidgets/scripts/localization.js"></script>前端html
<div id="grid"></div>JQXGrid相关配置文件
$(document).ready(function () {
//数据源信息 ********************可以修改
var source =
{
//数据类型
datatype: "json",
//字段信息********************需修改
datafields: [
{name: 'GUID', type: 'string'},
{name: 'LOGINNAME', type: 'string'}
],
id: 'GUID',
pager: function (pagenum, pagesize, oldpagenum) {
// 页码变更执行
},
//服务器过滤
filter: function () {
// update the grid and send a request to the server.
$("#grid").jqxGrid('updatebounddata', 'filter');
},
//启动服务器排序
sort: function() {
$("#grid").jqxGrid('updatebounddata','sort');
},
//默认排序字段********************需修改
sortcolumn: 'GUID',
//默认排序方式********************可以修改
sortdirection: 'asc',
//数据源名称
root: 'rows',
//设置行数
beforeprocessing: function (data) {
source.totalrecords = data.total;
},
//数据源URL********************可以修改
url: "${ctx}/JQXGridUtils/get?source=Sys_UserInfo"
};
//绑定数据
var dataAdapter = new $.jqx.dataAdapter(source);
//增加初始过滤条件
var addfilter = function () {
var filtergroup = new $.jqx.filter();
var filter_or_operator = 0;//表示并且
var filtervalue = 'zhangyi';//过滤的值*************需要修改
var filtercondition = 'equal';//过滤的条件 contains 包含
var filter1 = filtergroup.createfilter('stringfilter', filtervalue, filtercondition); //定义过滤器
//第二个过滤条件 如果有的话
/* filtervalue = 'Andrew';
filtercondition = 'contains';
var filter2 = filtergroup.createfilter('stringfilter', filtervalue, filtercondition);*/
filtergroup.addfilter(filter_or_operator, filter1);
/*filtergroup.addfilter(filter_or_operator, filter2);*/
// add the filters.
$("#grid").jqxGrid('addfilter', 'LOGINNAME', filtergroup);//添加过滤器
// apply the filters.
$("#grid").jqxGrid('applyfilters');//应用所有过滤器
}
//生成表格********************可以修改
$("#grid").jqxGrid(
{
ready: function () {
addfilter();
},
sortable: true,//是否允许排序
filterable: true,//是否允许过滤
altrows: true,
source: dataAdapter,//数据源
columnsresize: true,//是否允许改变宽度
pageable: true,//是否有分页
theme: "Bootstrap",//主题样式
//pagermode: 'simple',
virtualmode: true,//分页用虚拟模型
autoshowfiltericon: true,//默认显示过滤图标
showfiltercolumnbackground:true,
//localization: getLocalization('zh-CN'),
//设置表格内容数据
rendergridrows: function (params) {
//这里的返回值需要根绝实际情况作调整。如果params.data获取不到。可以用dataadapter来获取，如dataadapter.recordids[0].*等
return params.data;
},
//是否根据行数适应高度
autoheight: true,
//是否允许多行选中
selectionmode: 'multiplerowsextended',
//字段展示信息***************************************************可以修改
columns: [
{text: '主键', datafield: 'GUID', width: 250,align:"center",cellsalign: 'right'},
{text: '用户名', datafield: 'LOGINNAME', width: 250, cellsalign: 'right'},
//**************************************************可以修改
{ text: '操作', editable: false, datafield: '',
//转义方法
cellsrenderer: function (index, datafield, value, defaultvalue, column, rowdata) {
return "<button onclick=\"chagne('"+rowdata.GUID+"')\">删除"+rowdata.GUID+"</button>";
}
}
]
});
//数据加载完成后
$("#grid").on("bindingcomplete", function (event) {
//设置自定义本地化语言
$("#grid").jqxGrid('localizestrings', localization);
})
});Controller文件代码
因为我比较懒，装配过滤器sql的方法getWhereOfFilter 直接引用了
轻度愉悦博客中的内容
官网提供的插件提交操作所提交的字段说明
sortdatafield - 排序列的数据字段。
sortorder - 排序顺序 - “asc”，“desc”或“'
pagenum - 当分页功能启用时的当前页码。
pagesize - 页面大小，代表视图中显示的行数。
groupscount - 网格中的组数
组 - 组的名称。第一组的组名是'group0'，第二组是'group1'，依此类推。
filterscount - 应用于网格的滤镜数量
filtervalue - 过滤器的值。第一个过滤器的过滤器名称是“filtervalue0”，第二个过滤器的名称是“filtervalue1”，依此类推。
filtercondition - 过滤条件。条件可以是以下任何一种：“包含”，“包含”，“等于”，“等于”，“等于”，“ ，“ENDS_WITH”，“ENDS_WITH_CASE_SENSITIVE”，“NULL”，“NOT_NULL”，“EMPTY”，“NOT_EMPTY”
filterdatafield - 过滤器列的数据字段
filteroperator - 过滤器的操作符 - 0表示“AND”，1表示“OR”
import com.yawei.platform.grid.model.JQXGridModel;
import com.yawei.platform.grid.service.IGridUtilsService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.Map;
/**
* JQGrid插件工具类
* 愤怒的小马哥
* 2017/12/28
*/
@Controller
@RequestMapping("JQXGridUtils")
public class JQXGridUtilsController {
@Resource(name = "gridUtilsServiceImpl")
private IGridUtilsService gridutils;
@ResponseBody
@RequestMapping("get")
public String getData(JQXGridModel grid, HttpServletRequest request){
String where = getWhereOfFilter(request.getParameterMap());
if(where !=null&&!where.equals(""))
grid.where ="and "+where;
String json = gridutils.getJQGXridData(grid);
return json;
}
//拼接过滤条件
public String getWhereOfFilter(Map<String, String[]> map){// 参数是 request.getParameterMap()
//总共有多少个过滤条件(一个过滤面板会有一个或两个过滤条件)
Integer filterscount=Integer.valueOf(map.get("filterscount")[0]);
StringBuilder where=new StringBuilder();
for(int i=0;i<filterscount;i++){
//填写的过滤值
String filtervalue =map.get("filtervalue" + i)[0].trim();
//在过滤面板的条件下拉框选的值
String filtercondition =map.get("filtercondition" + i)[0];
//当前过滤字段
String filterdatafield =map.get("filterdatafield" + i)[0];
//当前过滤字段和下一个过滤字段的关系 0为and 1为or
String filteroperator =map.get("filteroperator" + i)[0];
//假设过滤字段为 filterdatafield为field,过滤值为value
switch(filtercondition)
{
case "EMPTY":
// field is null or field=''
where.append(filterdatafield).append(" is null or ")
.append(filterdatafield).append("='' ");
break;
case "NOT_EMPTY":
// field is not null or field<>''
where.append(filterdatafield).append(" is not null or")
.append(filterdatafield).append("<>'' ");
break;
case "CONTAINS":
// field like '%value%'
where.append(filterdatafield).append(" like '%")
.append(filtervalue).append("%' ");
break;
case "CONTAINS_CASE_SENSITIVE":
// field COLLATE Chinese_PRC_CS_AI like '%value%' 大小写敏感包含
where.append(filterdatafield)
.append(" COLLATE Chinese_PRC_CS_AI like '%")
.append(filtervalue).append("%' ");
break;
case "DOES_NOT_CONTAIN":
// field not like '%value%'
where.append(filterdatafield).append(" not like '%")
.append(filtervalue).append("%' ");
break;
case "DOES_NOT_CONTAIN_CASE_SENSITIVE":
// field COLLATE Chinese_PRC_CS_AI not like '%value%' 大小写敏感不包含
where.append(filterdatafield)
.append(" COLLATE Chinese_PRC_CS_AI not like '%")
.append(filtervalue).append("%' ");
break;
case "EQUAL":
// field='value'
where.append(filterdatafield).append("='").append(filtervalue).append("' ");
break;
case "EQUAL_CASE_SENSITIVE":
//field COLLATE Chinese_PRC_CS_AI = 'value'
where.append(filterdatafield).append(" COLLATE Chinese_PRC_CS_AI ='")
.append(filtervalue).append("' ");
break;
case "NOT_EQUAL":
// field<>'value'
where.append(filterdatafield).append("<>'").append(filtervalue).append("' ");
break;
case "NOT_EQUAL_CASE_SENSITIVE":
// field COLLATE Chinese_PRC_CS_AI <>'value'
where.append(filterdatafield).append(" COLLATE Chinese_PRC_CS_AI <>'")
.append(filtervalue).append("' ");
break;
case "GREATER_THAN":
// field>'value'
where.append(filterdatafield).append(">'").append(filtervalue).append("' ");
break;
case "LESS_THAN":
// field<'value'
where.append(filterdatafield).append("<'").append(filtervalue).append("' ");
break;
case "GREATER_THAN_OR_EQUAL":
// field>='value'
where.append(filterdatafield).append(">='").append(filtervalue).append("' ");
break;
case "LESS_THAN_OR_EQUAL":
// field<='value'
where.append(filterdatafield).append("<='").append(filtervalue).append("' ");
break;
case "STARTS_WITH":
// field like 'value%'
where.append(filterdatafield).append(" like '").append(filtervalue).append("%' ");
break;
case "STARTS_WITH_CASE_SENSITIVE":
// field COLLATE Chinese_PRC_CS_AI like 'value%'
where.append(filterdatafield).append(" COLLATE Chinese_PRC_CS_AI like '")
.append(filtervalue).append("%' ");
break;
case "ENDS_WITH":
// field like '%value'
where.append(filterdatafield).append(" like '%").append(filtervalue).append("' ");
break;
case "ENDS_WITH_CASE_SENSITIVE":
// field COLLATE Chinese_PRC_CS_AI like '%value'
where.append(filterdatafield).append(" COLLATE Chinese_PRC_CS_AI like '%")
.append(filtervalue).append("' ");
break;
case "NULL":
// field is null
where.append(filterdatafield).append(" is null ");
break;
case "NOT_NULL":
//field is not null
where.append(filterdatafield).append(" is not null ");
break;
}
if("0".equals(filteroperator)){
where.append(" and ");
}else if("1".equals(filteroperator)){
where.append(" or ");
}
}
//删除最末尾的and或or
int length=where.length();
if(where.lastIndexOf("or")+3==length){
where.delete(where.lastIndexOf("or"), length);
}else if(where.lastIndexOf("and")+4==length){
where.delete(where.lastIndexOf("and"), length);
}
return where.toString();
}
}
Model类
/**
* @包名 com.yawei.platform.grid.model
* @文件名 GridModel.java
* @作者 愤怒的小马哥
* @创建日期 2016年11月3日
* @版本 V 1.0
*/
public class JQXGridModel
{
//查询条件
public String where;
//排序字段
public String sortdatafield ;
//排序方式
public String sortorder ;
//页数
public int pagenum ;
//行数
public int pagesize ;
//分组数量 the number of groups in the Grid
public String groupscount;
//分组名称 the group's name. The group's name for the first group is 'group0', for the second group is 'group1' and so on.
public String group;
public String filterscount;// - the number of filters applied to the Grid
public String filtervalue; //- the filter's value. The filtervalue name for the first filter is "filtervalue0", for the second filter is "filtervalue1" and so on.
public String filtercondition; //- the filter's condition. The condition can be any of these: "CONTAINS", "DOES_NOT_CONTAIN", "EQUAL", "EQUAL_CASE_SENSITIVE", NOT_EQUAL","GREATER_THAN", "GREATER_THAN_OR_EQUAL", "LESS_THAN", "LESS_THAN_OR_EQUAL", "STARTS_WITH", "STARTS_WITH_CASE_SENSITIVE", "ENDS_WITH", "ENDS_WITH_CASE_SENSITIVE", "NULL", "NOT_NULL", "EMPTY", "NOT_EMPTY"
public String filterdatafield; //- the filter column's datafield
public String filteroperator; //- the filter's operator - 0 for "AND" and 1 for "OR"
//表名-mapper名称
public String tableName;
//起始行数
public int beginRow;
//结束行数
public int endRow;
//数据源--表名
public String source;
//总行数
public int totalRow;
public String getWhere() {
return where;
}
public void setWhere(String where) {
this.where = where;
}
public String getSortdatafield() {
return sortdatafield;
}
public void setSortdatafield(String sortdatafield) {
this.sortdatafield = sortdatafield;
}
public String getSortorder() {
return sortorder;
}
public void setSortorder(String sortorder) {
this.sortorder = sortorder;
}
public int getPagenum() {
return pagenum;
}
public void setPagenum(int pagenum) {
this.pagenum = pagenum;
}
public int getPagesize() {
return pagesize;
}
public void setPagesize(int pagesize) {
this.pagesize = pagesize;
}
public String getGroupscount() {
return groupscount;
}
public void setGroupscount(String groupscount) {
this.groupscount = groupscount;
}
public String getGroup() {
return group;
}
public void setGroup(String group) {
this.group = group;
}
public String getFilterscount() {
return filterscount;
}
public void setFilterscount(String filterscount) {
this.filterscount = filterscount;
}
public String getFiltervalue() {
return filtervalue;
}
public void setFiltervalue(String filtervalue) {
this.filtervalue = filtervalue;
}
public String getFiltercondition() {
return filtercondition;
}
public void setFiltercondition(String filtercondition) {
this.filtercondition = filtercondition;
}
public String getFilterdatafield() {
return filterdatafield;
}
public void setFilterdatafield(String filterdatafield) {
this.filterdatafield = filterdatafield;
}
public String getFilteroperator() {
return filteroperator;
}
public void setFilteroperator(String filteroperator) {
this.filteroperator = filteroperator;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public int getBeginRow() {
return beginRow;
}
public void setBeginRow(int beginRow) {
this.beginRow = beginRow;
}
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
public int getTotalRow() {
return totalRow;
}
public void setTotalRow(int totalRow) {
this.totalRow = totalRow;
}
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
}
Service代码
String getJQGXridData(JQXGridModel model);Service 实现类
json转换使用的alibaba的fastjson
@Override
public String getJQGXridData(JQXGridModel model) {
if(model.pagenum==0)
model.pagenum=1;
model.beginRow = (model.pagenum-1)*model.pagesize;
// TODO Auto-generated method stub
List <?> list = gridutils.getJQxGridData(model);
String json = JSON.toJSONString(list);
String total = gridutils.getJQxGridDataTotal(model);
if(json==null||json.equals("[]")){
json="[]";
};
if(total==null||total.equals("[]")){
total="0";
}
json = "{\"total\":" + total + ",\"rows\":" + json + "}";
return json;
}
Dao层
//获取JQXgrid列表内容
List<?> getJQxGridData(JQXGridModel model);
//获取Jqgrid列表总行数
String getJQxGridDataTotal(JQXGridModel model);Dao实现类
@Override
public List<?> getJQxGridData(JQXGridModel model) {
return session.selectList("JQXGridUtilsMapper.getData", model);
}
@Override
public String getJQxGridDataTotal(JQXGridModel model) {
return session.selectOne("JQXGridUtilsMapper.getDataTotal", model);
}Mapper 采用MySql 数据，其他数据库同理自己拼
<select id="getData" resultType="java.util.Map" parameterType="java.lang.String">
select * from ${source}
where 1=1
<if test="where!='' and where!=null ">
${where}
</if>
order by ${sortdatafield} ${sortorder}
limit #{beginRow,jdbcType=INTEGER},#{pagesize,jdbcType=INTEGER}
</select>
<select id="getDataTotal" resultType="java.lang.String" parameterType="java.lang.String">
select count(*) from ${source}
where 1=1
<if test="where!='' and where!=null ">
${where}
</if>
</select>
3618
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
