当前位置: 首页 > 工具软件 > jQWidgets > 使用案例 >

*jqwidgets jqxgrid 使用心得 jqxgrid教程 jqxgrid+SpringMVC(初级) 集成JAVA SSM

东方英豪
2023-12-01

jqwidgets jqxgrid 使用心得(初级)及集成JAVA SSM框架通用后台配置(急速列表开发)

项目采用Spring springmvc mybatis框架开发
主要功能需求

  1. 服务器端分页
  2. 服务器端查询(筛选)
  3. 服务器端排序
  4. 简单配置无需重复编写后台

    实现思路

  5. 前端提供分页所需字段:页码、每页显示行数、过滤条件、排序条件、表名、初始过滤条件(除了表名外,其他字段由插件自行提供)
  6. 服务器端通过表名及其他字段拼装Mybatis的查询语句,并返回控件所需的json数据,Controller层 Service层、Dao层使用通用List作为接收容器。
  7. 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>
 类似资料: