JqGrid完整例子

「爱情、让人受尽委屈。」 2021-08-30 00:04 384阅读 0赞

之前写过一篇过于ajax异步加载jqgrid的文章，那个只是一个特殊的情况，如果创建不同数据库表的jqgrid，必须分别写servlet，dao层和连接池，很麻烦，今天我写了一个万能的写法，客官只要在前台js中写入表格名称和字段名称即可实现功能！  
下面就是代码实例，关于jqgrid，ajax的介绍这里暂时不多说，有不懂的可以参考我之前的文章！

## 一、代码实例 ##

### 1、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>

### 2、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);

### 3、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);
        }
    }

### 4、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;
        }
    }

### 5、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;
        }
    }

### 6、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;
        }
    }

### 7、连接池 ###

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;
        }
    }

## 二、效果展示 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b3J1aV9qYXZh_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d1b3J1aV9qYXZh_size_16_color_FFFFFF_t_70]: /images/20210813/aea6afc4d24644a9b49013ee8b38f277.png