首页手记 JavaScript系列：ajax异步加载jqgrid

JavaScript系列：ajax异步加载jqgrid

标签：

Java JavaScript Docker

ajax简介
Ajax 即“Asynchronous Javascript And XML”（异步 JavaScript 和 XML），是指一种创建交互式网页应用的网页开发技术。
Ajax = 异步 JavaScript 和 XML（标准通用标记语言的子集）。
Ajax 是一种用于创建快速动态网页的技术。
Ajax 是一种在无需重新加载整个网页的情况下，能够更新部分网页的技术。 1
通过在后台与服务器进行少量数据交换，Ajax 可以使网页实现异步更新。这意味着可以在不重新加载整个网页的情况下，对网页的某部分进行更新。
传统的网页（不使用 Ajax）如果需要更新内容，必须重载整个网页页面。
jqgrid简介
jqGrid 是一个用来显示网格数据的jQuery插件，通过使用jqGrid可以轻松实现前端页面与后台数据的ajax异步通信。
jqgrid的重要选项
url ：jqGrid控件通过这个参数得到需要显示的数据，具体的返回值可以使XML也可以是Json。
datatype ：这个参数用于设定将要得到的数据类型。类型包括：json 、xml、xmlstring、local、javascript、function。
mtype : 定义使用哪种方法发起请求，GET或者POST。
height ：Grid的高度，可以接受数字、%值、auto，默认值为150。
width ：Grid的宽度，如果未设置，则宽度应为所有列宽的之和；如果设置了宽度，则每列的宽度将会根据shrinkToFit选项的设置，进行设置。
shrinkToFit ：此选项用于根据width计算每列宽度的算法。默认值为true。如果shrinkToFit为true且设置了width值，则每列宽度会根据width成比例缩放；如果shrinkToFit为false且设置了width值，则每列的宽度不会成比例缩放，而是保持原有设置，而Grid将会有水平滚动条。
autowidth ：默认值为false。如果设为true，则Grid的宽度会根据父容器的宽度自动重算。重算仅发生在Grid初始化的阶段；如果当父容器尺寸变化了，同时也需要变化Grid的尺寸的话，则需要在自己的代码中调用setGridWidth方法来完成。
pager ：定义页码控制条Page Bar，在上面的例子中是用一个div(<div id=”pager”></div>)来放置的。
sortname ：指定默认的排序列，可以是列名也可以是数字。此参数会在被传递到Server端。
viewrecords ：设置是否在Pager Bar显示所有记录的总数。
caption ：设置Grid表格的标题，如果未设置，则标题区域不显示。
rowNum ：用于设置Grid中一次显示的行数，默认值为20。正是这个选项将参数rows（prmNames中设置的）通过url选项设置的链接传递到Server。注意如果Server返回的数据行数超过了rowNum的设定，则Grid也只显示rowNum设定的行数。
rowList ：一个数组，用于设置Grid可以接受的rowNum值。例如[10,20,30]。
colNames ：字符串数组，用于指定各列的题头文本，与列的顺序是对应的。
colModel ：最重要的数组之一，用于设定各列的参数。
prmNames ：这是一个数组，用于设置jqGrid将要向Server传递的参数名称。
jsonReader ：这又是一个数组，用来设定如何解析从Server端发回来的json数据。
主要API接口getGridParam、setGridParam
getGridParam方法：

　　getGridParam("url")：获取当前的AJAX的URL
　　getGridParam("sortname")：排序的字段
　　getGridParam("sortorder")：排序的顺序
　　getGridParam("selrow")：得到选中行的ID
　　getGridParam("page")：当前的页数
　　getGridParam("rowNum")：当前有多少行
　　getGridParam("datatype")：得到当前的datatype
　　getGridParam("records")：得到总记录数
　　getGridParam("selarrrow")：可以多选时，返回选中行的ID
　
　　setGridParam方法：

　　setGridParam({url:newvalue})：可以设置一个grid的ajax url，可配合trigger("reloadGrid")使用
　　setGridParam({sortname:newvalue})：设置排序的字段
　　setGridParam({sortorder:newvalue})：设置排序的顺序asc or desc
　　setGridParam({page:newvalue})：设置翻到第几页
　　setGridParam({rowNum:newvalue})：设置当前每页显示的行数
　　setGridParam({datatype:newvalue})：设置新的datatype(xml,json)
关于jqgrid的API还有很多，暂时不一一介绍了，本文主要介绍jqgrid1最基本的表格初始化阶段，，注：本文也包括了简单的用户登录和注册功能！

代码实例
jsp界面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<html>
<head>
    <title>系统 用户管理</title>
    <link rel="stylesheet" href="css/homepage.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">
    <link type="text/css" rel="stylesheet" href="css/bootstrap.min.css">
</head>
<body>
<div class="headerColor">
    <span>系统>用户管理</span>
</div>
<div class="frame_border">
    <div class="titleColor">
        <span>用户管理</span>
    </div>
    <div class="row col-sm-12">
        <div class="col-sm-4">
            <div class="frame_large_4">
                <div class="titleColor">
                    <span>基本信息</span>
                </div>
                <form  id="form"  action="UserServlet" method="post">
                    <div class="row col-sm-12 div_space">
                        <div class="col-sm-5" align="right">
                            <span>用户名：</span>
                        </div>
                        <div class="col-sm-7" align="left">
                            <input  name="username" class="input_large"/>
                        </div>
                    </div>
                    <div class="row col-sm-12" style="top: 15px">
                        <div class="col-sm-5" align="right">
                            <span>级别：</span>
                        </div>
                        <div class="col-sm-7" align="left">
                            <select name="grade" class="input-large">
                                <option value="0">系统管理员</option>
                                <option value="1">系统操作员</option>
                                <option value="2">普通操作员</option>
                            </select>
                        </div>
                    </div>
                    <div class="row col-sm-12 div_space20">
                        <div class="col-sm-5" align="right">
                            <span>新密码：</span>
                        </div>
                        <div class="col-sm-7" align="left">
                            <input name="password" class="input_large"/>
                        </div>
                    </div>
                    <div class="row col-sm-12 div_space25">
                        <div class="col-sm-5" align="right">
                            <span>确认新密码：</span>
                        </div>
                        <div class="col-sm-7" align="left">
                            <input type="text" name="password_affirm" class="input_large"/>
                        </div>
                    </div>
                    <div class="row col-sm-12 div_space30">
                        <div class="col-sm-12" align="middle">
                            <button type="submit" name="button" value="add" class="btn_info" style="width: 100px">增加/修改</button>
                            <button type="submit" name="button" value="delete" class="btn-danger">删除</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>
        <div class="col-sm-8">
            <div class="frame_large_8">
                <div class="titleColor">
                    <span>用户列表</span>
                </div>
                <div>
                    <table id='gridTable'></table>
                    <div id="gridPager"></div>
                </div>
            </div>
        </div>
    </div>
    <div align="middle" style="margin-top:30px">
        <button class="btn-danger">退出</button>
        <button class="btn_info">保存</button>
    </div>
</div>
<script  type="text/javascript" class="lazyload" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB/AAffA0nNPuCLAAAAAElFTkSuQmCC" data-original="js/JqGrid/js/jquery.min.js"></script>
<script  type="text/javascript" class="lazyload" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB/AAffA0nNPuCLAAAAAElFTkSuQmCC" data-original="js/JqGrid/js/bootstrap.min.js"></script>
<script  type="text/javascript" class="lazyload" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB/AAffA0nNPuCLAAAAAElFTkSuQmCC" data-original="js/JqGrid/js/grid.locale-en.js"></script>
<script  type="text/javascript" class="lazyload" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB/AAffA0nNPuCLAAAAAElFTkSuQmCC" data-original="js/JqGrid/js/jquery.jqGrid.min.js"></script>
<script  type="text/javascript" class="lazyload" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAANSURBVBhXYzh8+PB/AAffA0nNPuCLAAAAAElFTkSuQmCC" data-original="js/JqGrid/js/jquery.jqGrid.user.js"></script>
</body>
</html>

jqgrid插件

$(document).ready(function () {
    $("#gridTable").jqGrid({
        colNames:['用户名','级别', '密码'],
        colModel:[
            {
                name:'username',
                index:'username',
                width: 120,
            },{
                name:'grade',
                index:'grade',
                width: 100,
                formatter : function(cellvalue,options,rowObject){
                    var str=""
                    if(cellvalue=="0"){
                        str="系统管理员";
                    }else if(cellvalue=="1"){
                        str="系统操作员";
                    }else {
                        str="普通操作员";
                    }
                    return str
                },
            },{
                name:'password',
                index:'password',
                width: 120,
            },
        ],
        viewrecords: true,
        width: 995,
        height: 395,
        rowNum: 20,
        datatype: 'text',
        pager: "#gridPager",
    });

    jf_initJqgrid();

    function jf_initJqgrid() {
        $.ajax({
            url:"UserServlet",
            async:true,   //是否为异步请求
            cache:false,  //是否缓存结果
            type:"GET",
            dataType:"json",
            success : function(data){
                for(var i=0;i<=data.length;i++){
                    $("#gridTable").jqGrid('addRowData',i+1,data[i]);
                }
            }
        })
    }
    $('[id^=jqgh_gridTable_]').css("height","20px");
});

servlet层

package com.test.servlet;

import com.test.entity.User;
import com.test.service.UserService;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class UserServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws  IOException {
      response.setContentType("text/html");
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        String username = request.getParameter("username");
        int grade = Integer.parseInt(request.getParameter("grade"));
        String password = request.getParameter("password");
        String password_affirm = request.getParameter("password_affirm");
        String button = request.getParameter("button");
        User u = new User();
        u.setUsername(username);
        u.setGrade(grade);
        u.setPassword(password);
        UserService service = new UserService();
        if (button.equals("add")) {
            int result = service.regist(u);
            if (username != "" && password != "" && password.equals(password_affirm)) {
                System.out.println(result);
                if (result == 0) {
                    response.sendRedirect(request.getContextPath() + "html/regist/regist_success.jsp");
                } else if (result == 1) {
                    response.sendRedirect(request.getContextPath() + "html/regist/login_success.jsp");
                } else {
                    response.sendRedirect(request.getContextPath() + "html/regist/regist_fail.jsp");
                }
            } else {
                response.sendRedirect(request.getContextPath() + "html/regist/regist_fail.jsp");
            }
        } else if (button.equals("delete")) {
            if (username != "" && password != "" && password.equals(password_affirm)) {
                service.deleteUser(username);
                response.sendRedirect(request.getContextPath() + "html/regist/delete_success.jsp");
            }else {
                response.sendRedirect(request.getContextPath() + "html/regist/delete_fail.jsp");
            }
        }
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws javax.servlet.ServletException, IOException {
        response.setContentType("text/html");
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        UserService service = new UserService();
        String list = service.selectUser();
        response.getWriter().print(list);
    }
}

service层

package com.test.service;

import com.test.dao.UserDao;
import com.test.entity.User;

public class UserService {
    public int regist(User user){
        UserDao dao= new UserDao();
        int result = dao.insert(user);
        return result;
    }

    public void deleteUser(String username){
        UserDao userDao = new UserDao();
        userDao.deleteUser(username);
    }

    public String selectUser(){
        UserDao userDao = new UserDao();
        String data = userDao.selectUser();
        return data;
    }
}

dao层

package com.test.dao;

import com.test.entity.User;
import com.test.util.ConnectionFactory;

public class UserDao {
    public int insert(User user){
        int flag = 3;
        String username = user.getUsername();
        int grade = user.getGrade();
        String password = user.getPassword();
        try {
            String sql_exist = String.format(
                    "select 1 from kj_user where  username='%s'and grade = %s  and password='%s'",
                    username,grade,password
            );
            String sql_regist = String.format(
                    "select 1 from kj_user where username = '%s'",
                    username
            );
            String res = ConnectionFactory.queryNoPage(sql_exist);
            String regist = ConnectionFactory.queryNoPage(sql_regist);
            if(res.indexOf("1") == -1){
                System.out.println("登录失败");
                if (regist.indexOf("1") == -1){
                    System.out.println("用户未注册，请注册！");
                    String sql = String.format(
                            "insert into kj_user(username,grade,password) select '%s',%s,'%s' from dual",
                            username,grade,password
                    );
                    System.out.println(sql);
                    ConnectionFactory.executeSQL(sql);
                    flag = 1;
                }else{
                    System.out.println("请换不同ID注册！");
                    flag = 2;
                }
            }else {
                System.out.println("登录成功");
                flag = 0;
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return flag;
    }

    public void deleteUser(String username){
        String sql = String.format(
                "delete from  kj_user where username = '%s'",username
        );
        ConnectionFactory.executeSQL(sql);
    }

    public String selectUser(){
        String sql = "select username,grade,password from kj_user";
        String data = ConnectionFactory.queryData(sql);
        return data;
    }
}

实体类层

package com.test.entity;

public class User {
    private String username;
    private int grade;
    private String password;

    public User() {
    }

    public User(String username, int grade, String password) {
        this.username = username;
        this.grade = grade;
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

连接池

package com.test.util;

import java.sql.*;

public class ConnectionFactory {
    private static String USERNAMR = "mine";
    private static String PASSWORD = "mine";
    private static String DRVIER = "oracle.jdbc.OracleDriver";
    private static String URL = "jdbc:oracle:thin:@192.168.100.103:1521:orcl";

    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 "[]";
        }
        return buff.toString();
    }

    public static String queryData(String sql){
        Connection conn      = null;
        Statement  stat      = null;
        StringBuffer   buff = new StringBuffer();
        try {
            conn   =  getConnection();
            stat   =  conn.createStatement();
        }
        catch (Exception e) {
            e.printStackTrace();
            return "[]";
        }
        try {
            stat = conn.prepareStatement(sql);
            ResultSet rs =stat.executeQuery(sql);
            //得到数据集的列数
            ResultSetMetaData rsmd = rs.getMetaData();
            int    colCount      = rsmd.getColumnCount();
            String ret = "";
            buff.append("[");
            while(rs.next()){
                String username = rs.getString("username");
                String grade = rs.getString("grade");
                String password = rs.getString("password");
                ret += String.format("{\"username\" : \"%s\"", username);
                ret += String.format(",\"grade\" : %s", grade);
                ret += String.format(",\"password\" : \"%s\"},", password);
            }
            buff.append(ret);
            buff.append("]");
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
            return "[]";
        }
        try {
            if (stat != null) stat.close();
            if (conn != null) conn.close();
        }
        catch (Exception e) {
            e.printStackTrace();
            return "[]";
        }
        String str = buff.toString().replace(",]","]");
        return str;
    }