ajax异步加载查询数据库

时间:2021-08-03 04:05:56
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org" xmlns:sec="http://www.thymeleaf.org/thymeleaf-extras-springsecurity3">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>数据</title>
        <link rel="stylesheet" href="static/css/style.css" type="text/css">
        <!-- jQuery-easyUI start -->
        <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/default/easyui.css">
        <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/icon.css">
        <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/themes/color.css">
        <link rel="stylesheet" type="text/css" href="https://www.jeasyui.com/easyui/demo/demo.css">
        <script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>
        <script type="text/javascript" src="https://www.jeasyui.com/easyui/jquery.easyui.min.js"></script>
        <!-- jQuery-easyUI end -->
        <script type="text/javascript">
            document.createElement("section");
            document.createElement("article");
            document.createElement("footer");
            document.createElement("header");
            document.createElement("hgroup");
            document.createElement("nav");
            document.createElement("menu");
        </script>
        <script type="text/javascript">
            function Save() {
                document.getElementById("PageOfficeCtrl1").WebSave();
            }
        </script>
    </head>
    <body>
        <header>
            <div class="w12 header">
                <a class="db logo fl"><img src="static/images/logo.jpg"
                    width="327" height="94" alt="" />
                </a>
                <div class="fr logofr">
                    <a href="#" class="blk">返回首页</a> |<a href="#" class="blk">客服中心</a><br>
                    如注册遇到问题请拨打:<strong style="font-size:14px;">400-000-0000</strong>
                </div>
            </div>
        </header>
        <div class="head_border"></div>
        <section class="w12 login"> <em class="fr">当前用户:张三 </em> </section>
        <section class="main w12">
            <div class="title">
                <a class="title1 db fl">审核报告</a><a class="title2 db fl">审核合同</a>
            </div>
            <div class="fr tit2">
                <span class="arr"></span>
            </div>
        </section>
        <br /><br />
        
        <div style="text-align:content;margin-left: 300px;">
            <table id="dg" title="北京某某科技有限公司" class="easyui-datagrid" style="width:700px;height:250px;,margin: auto;"
                    url="get_users.php" toolbar="#toolbar" pagination="true" rownumbers="true" fitColumns="true" singleSelect="true">
                <thead>
                    <tr id="insertSpareEpt">
                        <th field="firstname" width="50">单位编号</th>
                        <th field="lastname" width="50">单位名称</th>
                        <th field="phone" width="50">单位性质</th>
                        <th field="email" width="50">单位地址</th>
                    </tr>
                </thead>
            </table>
        </div>
        
        <div id="toolbar">
            <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-add" plain="true" onclick="newUser()">添加</a>
            <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-edit" plain="true" onclick="editUser()">修改</a>
            <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-remove" plain="true" onclick="destroyUser()">删除</a>
        </div>
        <div id="divTab"></div>
        <!-- 弹窗 -->
        <div id="dlg" class="easyui-dialog" style="width:400px" data-options="closed:true,modal:true,border:'thin',buttons:'#dlg-buttons'">
            <form id="fm" method="post" novalidate style="margin:0;padding:20px 50px">
                <h3>单位信息</h3>
                <div style="margin-bottom:10px">
                    <input name="firstname" class="easyui-textbox" required="true" label="单位编号:" style="width:100%">
                </div>
                <div style="margin-bottom:10px">
                    <input name="lastname" class="easyui-textbox" required="true" label="单位名称:" style="width:100%">
                </div>
                <div style="margin-bottom:10px">
                    <input name="phone" class="easyui-textbox" required="true" label="单位性质:" style="width:100%">
                </div>
                <div style="margin-bottom:10px">
                    <input name="email" class="easyui-textbox" required="true" validType="email" label="单位地址:" style="width:100%">
                </div>
            </form>
        </div>
        <div id="dlg-buttons">
            <a href="javascript:void(0)" class="easyui-linkbutton c6" iconCls="icon-ok" onclick="saveUser()" style="width:90px">添加</a>
            <a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-cancel" onclick="javascript:$('#dlg').dialog('close')" style="width:90px">取消</a>
        </div>
        
        <script type="text/javascript">
            var url;
            function newUser(){
                $('#dlg').dialog('open').dialog('center').dialog('setTitle','New User');
                $('#fm').form('clear');
                url = 'save_user.php';
            }
            function editUser(){
                var row = $('#dg').datagrid('getSelected');
                if (row){
                    $('#dlg').dialog('open').dialog('center').dialog('setTitle','Edit User');
                    $('#fm').form('load',row);
                    url = 'update_user.php?id='+row.id;
                }
            }
            function saveUser1(){
                $('#fm').form('submit',{
                    url: saveInfo,
                    onSubmit: function(){
                        return $(this).form('validate');
                    },
                    success: function(result){
                        var result = eval('('+result+')');
                        if (result.errorMsg){
                            $.messager.show({
                                title: 'Error',
                                msg: result.errorMsg
                            });
                        } else {
                            $('#dlg').dialog('close');        // close the dialog
                            $('#dg').datagrid('reload');    // reload the user data
                        }
                    }
                });
            }
            
            /* 保存  */
            function saveUser(){
                var info;
                var firstname = $("input[name='firstname']").val();
                var lastname = $("input[name='lastname']").val();
                var phone = $("input[name='phone']").val();
                var email = $("input[name='email']").val();
                $.ajax({
                    type : 'get',
                    url : 'saveInfo',
                    data:{
                        "firstname":firstname,
                        "lastname":lastname,
                        "phone":phone,
                        "email":email
                    },
                    cache : false,
                    async : false,
                    dataType : 'json',
                    success : function(data){
                        $("#dg").datagrid("loadData",data);// 数据加载jQuery easyUI表格中
                    
                    /* var tabStr = "";
                    $.each(data, function(index,value){
                        var item = data[index];
                        var itemFirstname = item.firstname;
                        var itemLastname = item.lastname;
                        var itemPhone = item.phone;
                        var itemEmail = item.email;
                        var itemStr = "<table border='1' cellpadding='0' cellspacing='0'><tr>"+
                                      "<td name='firstname' width=50>"+itemFirstname+"</td>"+
                                      "<td name='lastname' width=50>"+itemLastname+"</td>"+
                                       "<td name='phone' width=50>"+itemPhone+"</td>"+
                                       "<td name='email' width=50>"+itemEmail+"</td>"+
                                  "</tr></table>";
                            tabStr += itemStr;
                    })
                    document.getElementById("divTab").innerHTML = tabStr; */
                    
                    } , error:function(data){
                        alert("加载数据失败!"+data);
                    }
                });
                // 关闭添加窗口
                $('#dlg').dialog('close');
            }
            
            function destroyUser(){
                var row = $('#dg').datagrid('getSelected');
                if (row){
                    $.messager.confirm('Confirm','Are you sure you want to destroy this user?',function(r){
                        if (r){
                            $.post('destroy_user.php',{id:row.id},function(result){
                                if (result.success){
                                    $('#dg').datagrid('reload');    // reload the user data
                                } else {
                                    $.messager.show({    // show error message
                                        title: 'Error',
                                        msg: result.errorMsg
                                    });
                                }
                            },'json');
                        }
                    });
                }
            }
        </script>
    </body>
</html>

package com.wyebd.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;
import com.alibaba.fastjson.JSON;
import com.wyebd.bean.Contract;
import com.zhuozhengsoft.pageoffice.OpenModeType;
import com.zhuozhengsoft.pageoffice.PageOfficeCtrl;
import com.zhuozhengsoft.pageoffice.wordwriter.WordDocument;

@RestController
public class ReadDataController {

    private static Logger log = Logger.getLogger(ReadOnlyController.class);
    private final String DB_URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "root";
    private static Connection con = null;
    private static PreparedStatement ps = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;
    
    /**
     * 跳转至查询页面
     * @param request
     * @param response
     * @param map
     * @return
     */
    @RequestMapping(value = "/readData", method = RequestMethod.GET)
    public ModelAndView readData() {
        ModelAndView mv = new ModelAndView("readData");
        return mv;
    }
    
    /**
     * 保存
     * @param request
     * @param response
     * @param map
     * @return
     */
    @RequestMapping("saveInfo")
    public String saveInfo(HttpServletRequest request, HttpServletResponse response, Map<String, Object> map) {
        List<Contract> list = new ArrayList<Contract>();
        String firstname = request.getParameter("firstname");
        String lastname = request.getParameter("lastname");
        String phone = request.getParameter("phone");
        String email = request.getParameter("email");
        String json = null;
        int id = 0;
        try {
            getConnection();
            ps = con.prepareStatement("insert into test values (?,?,?,?,?)");
            ps.setInt(1, id);
            ps.setString(2, firstname);
            ps.setString(3, lastname);
            ps.setString(4,phone);
            ps.setString(5, email);
            ps.executeUpdate();
            log.info("添加成功");
            rs = stmt.executeQuery("select * from test");
            while(rs.next()){
                Contract con = new Contract();
                con.setFirstname(rs.getString("firstname"));
                con.setLastname(rs.getString("lastname"));
                con.setPhone(rs.getString("phone"));
                con.setEmail(rs.getString("email"));
                list.add(con);
            }
            // list转为json传入前端
            json = JSON.toJSONString(list);
            log.info(json);
            // 取得流向JSP传递数据
            response.setContentType("text/html;charset=UTF-8");
            response.getWriter().println(json);
            response.reset();
        } catch (Exception e) {
            log.info("SQLException: " + e.getMessage());
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
         return json;
    }

    /**
     * 连接数据库
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public void getConnection() throws ClassNotFoundException, SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = con.createStatement();
        } catch (Exception e) {
            System.out.println("SQLException: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
}