ajax异步加载jqgrid之动态创建
之前写过一篇过于ajax异步加载jqgrid的文章，那个只是一个特殊的情况，如果创建不同数据库表的jqgrid，必须分别写servlet，dao层和连接池，很麻烦，今天我写了一个万能的写法，客官只要在前台js中写入表格名称和字段名称即可实现功能！ 下面就是代码实例，关于jqgrid，ajax的介绍这里暂时不多说，有不懂的可以参考我之前的文章！ 代码实例 JSP层 <%@ page contentType="text/html;charset=UTF-8" language="java" %> <!DOCTYPE html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>定义>类型</title> <link rel="stylesheet" href="css/homepage.css"> <link type="text/css" rel="stylesheet" href="css/bootstrap.min.css"> <link type="text/css" rel="stylesheet" href="js/JqGrid/css/font-awesome.min.css"> <link type="text/css" rel="stylesheet" href="js/JqGrid/css/ui.jqgrid.css"> <link type="text/css" rel="stylesheet" href="js/JqGrid/css/jquery-ui.min.css"> </head> <body> <div class="headerColor"> <span>定义>类型</span> </div> <div class="row col-sm-12"> <div class="frame_border"> <div class="titleColor"> <span>已定义模拟量测点信息</span> <div id="typeDefines"> <table id="typeDefine"></table> <div id="typeDefinePager"></div> </div> </div> </div> </div> <script type="text/javascript" src="js/JqGrid/js/jquery.min.js"></script> <script type="text/javascript" src="js/JqGrid/js/jquery.jqGrid.min.js"></script> <script type="text/javascript" src="js/JqGrid/js/bootstrap.min.js"></script> <script type="text/javascript" src="js/JqGrid/js/grid.locale-en.js"></script> <script type="text/javascript" src="js/JqGrid/js/grid.locale-cn.js"></script> <script type="text/javascript" src="js/JqGrid/js/jquery.jqgrid.typedefine.js"></script> <script type="text/javascript"> jf_initJqgrid(); function jf_initJqgrid(){ $("#typeDefine").jqGridTypeDefine({ pager_selector:"#typeDefinePager",//分页 height:"360",//表格高度 width:$("#typeDefines"),//表格宽度 where:{},//初始化查询条件 wherestring:{}, oper:"jqGridCre", tableid : "kj_typedefine", }); } </script> </body> </html> JavaScript篇 ;(function($){ $.fn.jqGridTypeDefine = function(options){ //获取jquery对象 var grid_selector = this; var initDate = "{oper: ""," + "height: ""," + "width: ""," + "pager_selector: "typeDefinePager"," + "where: ""," + "wherestring: {}," + "fileds: ["id", "property", "name", "sensortype", "unit","highvalue"]," + "table:"kj_typedefine"}"; $.ajax({ url:"MyServlet", async:true, //是否为异步请求 cache:false, //是否缓存结果 type:"GET", dataType:"json", data :{ "data" : initDate, }, success : function(data){ for(var i=0;i<=data.length;i++){ $("#typeDefine").jqGrid('addRowData',i+1,data[i]); } } }) switch(options.oper){ case 'jqGridCre':jqgridCre(initDate,grid_selector); break; case 'jqGridFre':jqGridFre(initDate,grid_selector); break; } $('[id^=jqgh_typeDefine_]').css("height","20px"); } function jqgridCre(initDate,grid_selector) { jQuery(grid_selector).jqGrid({ colNames:['标识符','属性', '名称','传感器种类','单位/0态名称','高值/1态名称'], colModel:[ { name:'id', index:'id', width: 100, },{ name:'property', index:'property', width: 100, },{ name:'name', index:'name', width: 100, },{ name:'sensortype', index:'sensortype', width: 100, },{ name:'unit', index:'unit', width: 100, },{ name:'highvalue', index:'highvalue', width: 100, } ], sortname : "id", sortorder : "desc", viewrecords : true, shrinkToFit : true, width: 1645, height: 500, rowNum : 20, rowList : [ 10,15,30 ], datatype: 'text', pager: "#typeDefinePager", onSelectRow:function(rowid){ grid_selectRow = $(grid_selector).jqGrid("getRowData",rowid); }, }); } })(jQuery); servlet层 package com.test.servlet; import com.test.entity.MyEntity; import com.test.service.MyService; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; public class MyServlet extends HttpServlet { MyService service = new MyService(); protected void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException{ response.setContentType("text/html"); request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException{ response.setContentType("text/html"); request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); String jqgridData = request.getParameter("data"); MyEntity myEntity = new MyEntity(); myEntity.setJqgridData(jqgridData); String str = service.getJqgridData(myEntity); response.getWriter().print(str); } } service层 package com.test.service; import com.test.dao.MyDao; import com.test.entity.MyEntity; public class MyService { MyDao myDao = new MyDao(); public String getJqgridData(MyEntity myEntity) { String jqgridData = myDao.selectJqgridData(myEntity); return jqgridData; } } dao层 package com.test.dao; import com.test.entity.MyEntity; import com.test.entity.SecondData; import com.test.util.MySQLFactory; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; public class MyDao { public String selectJqgridData(MyEntity myEntity) { String str = myEntity.getJqgridData(); String data = ""; try { JSONObject obj = new JSONObject(str); String oper = String.valueOf(obj.get("oper")); String fileds = String.valueOf(obj.get("fileds")).replace(""","") .replace("[","") .replace("]",""); String table = String.valueOf(obj.get("table")); String sql = String.format( "select %s from %s ",fileds,table ); System.out.println(sql); data = MySQLFactory.queryData(sql); } catch (JSONException e) { e.printStackTrace(); } return data; } } entity层 package com.test.entity; public class MyEntity { private String jqgridData; public MyEntity() { } public MyEntity(String jqgridData) { this.jqgridData = jqgridData; } public String getJqgridData() { return jqgridData; } public void setJqgridData(String jqgridData) { this.jqgridData = jqgridData; } } 连接池 package com.test.util; import java.sql.*; public class MySQLFactory { static final String DRVIER = "com.mysql.jdbc.Driver"; static final String URL = "jdbc:mysql://localhost:3306/mysql"; static final String USERNAMR = "root"; static final String PASSWORD = "root"; private static Connection conn = null; public static Connection getConnection(){ try { Class.forName(DRVIER); conn = DriverManager.getConnection(URL, USERNAMR, PASSWORD); System.out.println("成功连接数据库"); } catch (ClassNotFoundException e) { throw new RuntimeException("class not find !", e); } catch (SQLException e) { throw new RuntimeException("get connection error!", e); } return conn; } public static void executeSQL(String sql) { Statement stat = null; try { conn = getConnection(); stat = conn.createStatement(); } catch (Exception e) { e.printStackTrace(); return; } try { stat.execute(sql); } catch (Exception e) { e.printStackTrace(); return; } try { if (stat != null) stat.close(); if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } public static String queryNoPage(String sql) { Connection conn = null; Statement stat = null; ResultSet rows = null; StringBuffer buff = new StringBuffer(); try { conn = getConnection(); stat = conn.createStatement(); } catch (Exception e) { e.printStackTrace(); return "[]"; } try { rows = stat.executeQuery(sql); //得到数据集的列数 ResultSetMetaData rsmd = rows.getMetaData(); int colCount = rsmd.getColumnCount(); boolean first = true; buff.append("["); while (rows.next()) { String rowStr = ""; for (int i = 1; i <= colCount; i ++ ) { if (i>1) rowStr += ","; String tempValue = rows.getString(i); rowStr += String.format("%s", tempValue); } rowStr = String.format(""%s"", rowStr); if (first) first = false; else buff.append(","); buff.append(rowStr); } buff.append("]"); }catch (Exception e) { e.printStackTrace(); return "[]"; }finally { try { rows.close(); stat.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return buff.toString(); } public static String queryData(String sql){ Connection conn = null; Statement stat = null; ResultSet rows = null; StringBuffer buff = new StringBuffer(); try { conn = getConnection(); stat = conn.createStatement(); } catch (Exception e) { e.printStackTrace(); return "[]"; } try { rows = stat.executeQuery(sql); //得到数据集的列数 ResultSetMetaData rsmd = rows.getMetaData(); int colCount = rsmd.getColumnCount(); buff.append("["); while (rows.next()) { String rowStr = ""; buff.append("{"); for (int i = 1; i <= colCount; i ++ ) { if (i>1) rowStr += ","; String name = rsmd.getColumnName(i); String tempValue = rows.getString(i); rowStr += String.format(""%s":"%s"", name,tempValue); } rowStr = String.format("%s", rowStr); buff.append(rowStr); buff.append("},"); } buff.append("]"); } catch (SQLException e) { e.printStackTrace(); return "[]"; } String str = buff.toString().replace(",]","]"); return str; } } 效果展示
