首先,jqGrid 是一个用来显示网格数据的jQuery插件,通过使用jqGrid可以轻松实现前端页面与后台数据的ajax异步通信。
一、jqGrid特性
- 基于jquery UI主题,开发者可以根据客户要求更换不同的主题。
- 兼容目前所有流行的web浏览器。
- Ajax分页,可以控制每页显示的记录数。
- 支持XML,JSON,数组形式的数据源。
- 提供丰富的选项配置及方法事件接口。
- 支持表格排序,支持拖动列、隐藏列。
- 支持滚动加载数据。
- 支持实时编辑保存数据内容。
- 支持子表格及树形表格。
- 支持多语言。
- 目前是免费的。
二、jqGrid使用方式
- 首先,您需要到jqGrid官网下载最新版本的程序包,下载地址为:http://www.trirand.com/blog/?page_id=6
- 下载jqGrid皮肤,下载地址为:http://jqueryui.com/themeroller/
- 放在java项目的jquery目录下,如下图
使用是在jsp文件 引用<script src="<%=url%>/page/jquery/jqgrid.xmhj.js" type="text/javascript"></script>
- 具体用法可以参照官网中文网址:http://blog.mn886.net/jqGrid/ 基础用法和例子都有
- 基础例子 前端jsp文件: 有充值表:cy_cz 需要显示字段data_zd:id name KH ..... 通过url传参 onSearch是需要查询条件的 如以姓名name 卡号KH 模糊查询
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%> <% String url_path = request.getContextPath(); String url = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + url_path + "/"; pageContext.setAttribute("url", url); %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta name="viewport" content="width=device-width,height=device-width, initial-scale=1 user-scalable=0"/> <script src="<%=url%>/page/jquery/jqgrid.xmhj.js" type="text/javascript"></script> <%@ include file="/page/include/JQMobile.jsp"%> <title>表格</title> <script> //传递给服务器用的参数 var post_data={ tablename: "cy_cz", data_zd: "id,name,KH,JyType,JyRQ,dealtime,ssje,OldJE,NewJE,LsH,gonghao,bak",//显示的数据 //data_name: "id,empid,name,checktime,type,jh,bak",//显示的数据 sel_zd:" T.*,e.name,e.gonghao",//查询的数据 page: "1",//默认页数 rows: "10",//每页记录数 total: 0,//默认0 sidx : "JyRQ",//初始化的时候排序的字段 sord : "desc",//排序方式,可选desc,asc code: "string,string,string",//传参类型--搜索时共用 codename: "e.name,KH,JyType",//传参名 codevalue: " , ,2",//传参值 lookup_table:"LEFT JOIN empinfo e on e.EmpId=T.empid" }; setTimeout('showLoader()', 100);//这里要延迟一下,直接调用无法显示加载器 newsList(); function newsList(){ //ajax访问数据库 $.ajax({ url:'${url}jqgrid/getAjaxList?', type: "post", data : post_data, dataType : "json", success : function(data){ // {"page":"1","total":"0","records":"0","rows":[]} //{"page":1,"total":3,"records":25,"rows":[{"id":"1","cell":["1","144","","刷卡禁止通过: 没有权限","2019-01-11","0","",""]}}]} var page=data["page"];//第几页 var total=data["total"];//总页数 var records=data["records"];//记录数 if(records=="0"){ var obj=document.createElement("li"); obj.innerHTML="暂无任何数据!"; $("#ul_dataInfo").append(obj); } post_data.total=total; var rows=data["rows"];//数据 for(var i=0;i<rows.length;i++){ //alert(rows[i]); var cell=rows[i]["cell"]; var ids=cell[1]; var name=cell[2]; var KH=cell[3]; var dateTime=cell[5]+" "+cell[6]; var ssje=cell[7]; var NewJE=cell[9];var LsH=cell[10]; var gonghao=cell[11];var bak=cell[12]; /* for(var j=0;j<cell.length;j++){ var obj=document.createElement("li"); obj.innerHTML="<a href='#'>"+cell[j]+"</a>"; } */ var obj=document.createElement("li"); //给ul元素添加内容 //样式1 单纯内容 //obj.innerHTML=""+name+""; //样式2 基础内容 //obj.innerHTML="<a href='#'>"+name+"</a>"; //样式3 气泡数字 //obj.innerHTML="<a href='#'>"+name+"<span class='ui-li-count'>25</span></a>"; //样式4 大标题加小标题 var mx_url="${url}jqgrid/getPage?url=/page/xfgl/czmxInfo.jsp&id="+ids+"&tableName=cy_cz"; /*obj.innerHTML="<a href='"+mx_url+"'><p>"+name+"</p><p>"+KH+"</p><p>"+ssje+"元"+ "</p><p class='ui-li-aside'><strong>"+dateTime+"</strong></p></a>"; */ obj.innerHTML="<a href='"+mx_url+"'><h2>"+name+" 充值金额:<font color='red'>"+ssje+ "</font> 余额:<font color='greed'>"+NewJE+"</font></h2><p>"+"工号:"+gonghao+" 卡号:"+KH+" 流水号:"+LsH+ " 备注:"+bak+"</p><p class='ui-li-aside'><strong>"+dateTime+"</strong></p></a>"; //样式5 图片加内容 //obj.innerHTML="<a href='#'>"+name+"</a>"; $("#ul_dataInfo").append(obj); } //刷新样式,必须写 $('#ul_dataInfo').listview('refresh'); //setTimeout('hideLoader()', 2000);//根据实际情况看是否需要增加延时 }, error: function(){ alert("请求失败啦!"); setTimeout('hideLoader()', 100); }, statusCode:{ "404":function(){ alert("404表示页面没有找到"); }, "500":function(){ alert("500表示服务器内部错误"); }, "200":function(){ //alert("200表示请求成功"); setTimeout('hideLoader()', 100); } } }); } //点击更多 function moreData(){ var page=post_data.page; var total=post_data.total; if(page<total){ post_data.page=++post_data.page; setTimeout('showLoader()', 100);//这里要延迟一下,直接调用无法显示加载器 newsList(); }else{ alert("没有更多记录!"); } } //显示加载器.for jQuery Mobile 1.2.0 function showLoader() { $.mobile.loading('show', { text: '正在加载中...', //加载器中显示的文字 textVisible: true, //是否显示文字 theme: 'a', //加载器主题样式a-e textonly: false, //是否只显示文字 html: "" //要显示的html内容,如图片等 }); } //隐藏加载器.for jQuery Mobile 1.2.0 function hideLoader() { $.mobile.loading('hide'); } //搜索事件 function onSearch(val){ //alert(val); $('#ul_dataInfo li').remove(); post_data.page=1; post_data.code="string,string"; post_data.codename="e.name,JyType"; post_data.codevalue=""+val+",,2"; setTimeout('showLoader()', 100);//这里要延迟一下,直接调用无法显示加载器 newsList(); } </script> </head> <body> <div data-role="page" > <div data-role="header"> <a href="#" onClick="javascript :history.back(-1);" data-role="button">返回</a> <a href="${url}jqgrid/addPage?url=/page/xfgl/czInfoEdit.jsp&tableName=cy_cz" data-role="button">新增</a> <h1>充值记录</h1> </div> <div data-role="content" style="margin:0px;padding:0px"> <input type="search" name="search" id="search_bar" value="" placeholder="搜索姓名..." onchange="onSearch(this.value);"/> <ul id="ul_dataInfo" data-role="listview" data-inset="true" > </ul> </div> <div data-role="footer"> <a href="#" onClick="moreData()" style="width:90%;margin-left:1%" class="ui-btn ui-shadow ui-corner-all" data-role="button">更多....</a> </div> </div> </body> </html>
后台java控制器: getPageList() 获取分页数据 getAjaxList() json数据通过ajax交互8
-
package com.aicard.controller; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Set; import com.jfinal.core.Controller; import com.jfinal.plugin.activerecord.Db; import com.jfinal.plugin.activerecord.Record; import com.baseDev.jdbc.JDBO; import com.baseDev.conn.Dpc; import com.aicard.conn.PageModel; public class JqGridController extends Controller { public void getPageList(){ String tablename = this.getPara("tablename"); String code = this.getPara("code"); String codeval = this.getPara("codevalue"); String codename = this.getPara("codename"); String lookup_table = this.getPara("lookup_table"); int count = 0; Dpc dpc = new Dpc(); String sqlfrom = " FROM "+tablename+" T " ; String sql=" where 1=1 "; String sql2= " where 1=1 "; if(lookup_table!=null&&!lookup_table.equals("")){ if(lookup_table.indexOf("where")>-1||lookup_table.indexOf("WHERE")>-1){ sql2= " "; } } if(code!=null&&code!=""){ String strs[]=code.split(","); String name[]=codename.split(","); String val[]=codeval.split(","); for(int i=0;i<strs.length;i++){ String str=strs[i]; if(val[i]!=null&&(val[i].equals("all")||val[i].equals(" "))){ continue; } String sqq=sql; boolean flag=false; if(name[i]!=null){ String names=name[i]; if(names.indexOf(".")>-1){ if(names.indexOf("T.")>-1){ }else{ sqq= sql2; flag=true; } } } if(str!=null&&str.equals("string")){ sqq = sqq + " AND "+name[i]+" like '%"+val[i]+"%' "; }else if(str!=null&&str.equals("string2")){ sqq = sqq + " AND "+name[i]+" = '"+val[i]+"' "; }else if(str!=null&&str.equals("int")){ sqq = sqq + " AND "+name[i]+" = "+val[i]+" "; }else if(str!=null&&str.equals("double")){ sqq = sqq + " AND "+name[i]+" = "+val[i]+" "; }else if(str!=null&&str.equals("date")){ sqq = sqq + " and DATE_FORMAT("+name[i]+",'yyyy-MM-dd') = '" + val[i] + "' "; }else if(str!=null&&str.equals("datef")){ sqq = sqq + " and DATE_FORMAT("+name[i]+",'yyyy-MM-dd') >= '" + val[i] + "' "; }else if(str!=null&&str.equals("datet")){ sqq = sqq + " and DATE_FORMAT("+name[i]+",'yyyy-MM-dd') <= '" + val[i] + "' "; } if(flag){ sql2=sqq; }else{ sql=sqq; } } } String resultset="{\"page\":\"1\",\"total\":\"0\",\"records\":\"0\",\"rows\":[]}"; String orderby = ""; String data_zd = this.getPara("data_zd"); String sel_zd = this.getPara("sel_zd"); String page = this.getPara("page"); String sidx = this.getPara("sidx"); String sord = this.getPara("sord"); String rows = this.getPara("rows"); orderby = " ORDER BY " + sidx + " " +sord; count = JDBO.DataCount(" SELECT "+sel_zd+" FROM (SELECT T.* " + sqlfrom + sql + ") T " + lookup_table + " "+ sql2 ); if(count>0){ dpc = DpcYes(count,Integer.valueOf(rows)); dpc.setCurrentPage(Integer.valueOf(page)); resultset= JDBO.JqPage(dpc, " SELECT "+sel_zd+" FROM (SELECT T.* " + sqlfrom + sql + ") T " + lookup_table + " "+ sql2 + orderby,data_zd); } this.renderJson(resultset); } public void getAjaxList(){ String tablename = this.getPara("tablename"); String code = this.getPara("code"); String codeval = this.getPara("codevalue"); String codename = this.getPara("codename"); String lookup_table = this.getPara("lookup_table"); int count = 0; Dpc dpc = new Dpc(); String sqlfrom = " FROM "+tablename+" T " ; String sql=" where 1=1 "; String sql2= " where 1=1 "; if(lookup_table!=null&&!lookup_table.equals("")){ if(lookup_table.indexOf("where")>-1||lookup_table.indexOf("WHERE")>-1){ sql2= " "; } } if(code!=null&&code!=""){ String strs[]=code.split(","); String name[]=codename.split(","); String val[]=codeval.split(","); for(int i=0;i<strs.length;i++){ String str=strs[i]; if(val[i]!=null&&(val[i].equals("all")||val[i].equals(" "))){ continue; } String sqq=sql; boolean flag=false; if(name[i]!=null){ String names=name[i]; if(names.indexOf(".")>-1){ if(names.indexOf("T.")>-1){ }else{ sqq= sql2; flag=true; } } } if(str!=null&&str.equals("string")){ sqq = sqq + " AND "+name[i]+" like '%"+val[i]+"%' "; }else if(str!=null&&str.equals("string2")){ sql = sqq + " AND "+name[i]+" = '"+val[i]+"' "; }else if(str!=null&&str.equals("int")){ sqq = sqq + " AND "+name[i]+" = "+val[i]+" "; }else if(str!=null&&str.equals("double")){ sqq = sqq + " AND "+name[i]+" = "+val[i]+" "; }else if(str!=null&&str.equals("date")){ sqq = sqq + " and date_format("+name[i]+",'yyyy-MM-dd') = '" + val[i] + "' "; }else if(str!=null&&str.equals("datef")){ sqq = sqq + " and date_format("+name[i]+",'yyyy-MM-dd') >= '" + val[i] + "' "; }else if(str!=null&&str.equals("datet")){ sqq = sqq + " and date_format("+name[i]+",'yyyy-MM-dd') <= '" + val[i] + "' "; } if(flag){ sql2=sqq; }else{ sql=sqq; } } } String resultset="{\"page\":\"1\",\"total\":\"0\",\"records\":\"0\",\"rows\":[]}"; String orderby = ""; String data_zd = this.getPara("data_zd"); String sel_zd = this.getPara("sel_zd"); String page = this.getPara("page"); String sidx = this.getPara("sidx"); String sord = this.getPara("sord"); String rows = this.getPara("rows"); orderby = " ORDER BY " + sidx + " " +sord; count = JDBO.DataCount(" SELECT "+sel_zd+" FROM (SELECT T.* " + sqlfrom + sql + ") T " + lookup_table + " "+ sql2 ); if(count>0){ dpc = DpcYes(count,Integer.valueOf(rows)); dpc.setCurrentPage(Integer.valueOf(page)); resultset= JDBO.JqPage(dpc, " SELECT "+sel_zd+" FROM (SELECT T.* " + sqlfrom + sql + ") T " + lookup_table + " "+ sql2 + orderby,data_zd); } this.renderJson(resultset); } public void getPage(){ String url=this.getPara("url"); String id=this.getPara("id"); String tableName=this.getPara("tableName"); if(tableName!=null&&!tableName.equals("")){ Record pojo = Db.findById(tableName,id); this.setAttr("pojo", pojo); } this.setAttr("id", id); this.render(url); } public void addPage(){ String url=this.getPara("url"); String id=this.getPara("id"); String tableName=this.getPara("tableName"); if(tableName!=null&&!tableName.equals("")&&id!=null&&!id.equals("")){ Record pojo = Db.findById(tableName,id); this.setAttr("pojo", pojo); } this.setAttr("id", id); this.setAttr("tableName", tableName); this.render(url); } public void savePage(){ String url=this.getPara("url"); String id=this.getPara("id"); String tableName=this.getPara("tablename"); if(tableName!=null&&!tableName.equals("")){ this.renderText("-1"); } Record pojo = new Record(); Record rd=null; if(tableName!=null&&!tableName.equals("")&&id!=null&&!id.equals("")){ rd = Db.findById(tableName,id); if(rd!=null) pojo=rd; } String code = this.getPara("code"); String codeval = this.getPara("codevalue"); String codename = this.getPara("codename"); if(code!=null&&code!=""){ String strs[]=code.split(","); String name[]=codename.split(","); String val[]=codeval.split(","); for(int i=0;i<strs.length;i++){ String str=strs[i]; if(val[i]==null){ continue; } if(str=="date"||str.equalsIgnoreCase("date")){ pojo.set(name[i],Timestamp.valueOf(val[i])); }else{ pojo.set(name[i],val[i]); } } } boolean result=false; if(rd!=null){ result=Db.update(tableName, pojo); }else{ result=Db.save(tableName, pojo); } if(result) this.renderText("1"); else this.renderText("0"); } //操作提示 public void Msg(String statusCode,String message,String callbackType,String rel){ this.setAttr("statusCode", statusCode); this.setAttr("message", message); this.setAttr("callbackType", callbackType); //closeCurrent this.setAttr("rel", rel); //closeCurrent this.render("/public/ajaxDone.jsp"); } //操作提示 public void MsgAll( String statusCode, String message, String navTabId, String rel, String callbackType, String forwardUrl, String confirmMsg) { this.setAttr("statusCode", statusCode); this.setAttr("message", message); this.setAttr("navTabId", navTabId); //navTabId this.setAttr("rel", rel); //rel this.setAttr("callbackType", callbackType); //closeCurrent this.setAttr("forwardUrl", forwardUrl); //forwardUrl this.setAttr("confirmMsg", confirmMsg); //confirmMsg this.render("/public/ajaxDone.jsp"); } //列表值为空时,默认分页值 public Dpc DpcNo(){ Dpc dpc = new Dpc(); int numPerPage = 20; try{ numPerPage = Integer.valueOf(this.getPara("numPerPage")); } catch(Exception e){ numPerPage = 20; } dpc.setAllCount(0); dpc.setCurrentPage(1); dpc.setNumPerPage(numPerPage); dpc.setPageCount(0); return dpc; } //列表值不为空时,默认分页值 public Dpc DpcYes(int allcount){ Dpc dpc = new Dpc(); int numPerPage = 20; //每页记录 int CurrentPage = 1; //当前页 int PageCount = 1; //总页数 //每页记录 try{ numPerPage = Integer.valueOf(this.getPara("numPerPage")); } catch(Exception e){ numPerPage = 20; } if(numPerPage==0) numPerPage = 20; //当前页 try{ CurrentPage = Integer.valueOf(this.getPara("pageNum")); }catch(Exception e){ CurrentPage = 1; } if(CurrentPage==0) CurrentPage = 1; //总页数 PageCount = (allcount + numPerPage - 1)/numPerPage; dpc.setAllCount(allcount); dpc.setCurrentPage(CurrentPage); dpc.setNumPerPage(numPerPage); dpc.setPageCount(PageCount); return dpc; } //列表值不为空时,默认分页值带参数 public Dpc DpcYes(int allcount,int numPerPage){ Dpc dpc = new Dpc(); dpc.setAllCount(allcount); dpc.setCurrentPage(PageModel.StringToInt(this.getPara("pageNum"), 1)); dpc.setNumPerPage(PageModel.StringToInt(this.getPara("numPerPage"),numPerPage)); dpc.setPageCount((allcount+dpc.getNumPerPage() - 1)/dpc.getNumPerPage()); return dpc; } //数据列表分页 public List<Record> Page(Dpc dpc,String sql){ List<Record> page = JDBO.oraclePage(dpc, sql); return page; } //数据列表分页 public List<Record> getTask(){ List<Record> pojosTask = null; pojosTask = Db.find("select * from TASK_TYPE t order by t.DISP_ORDER"); return pojosTask; } }
- 页面效果如下:
该功能是要用于微信端的 网页看起来较丑,使用了jfinal框架 ,引入jqGrid 和 jqGridMobile.
后台的代码写得有些乱,还有待优化。