javaweb+servlet+mysql+ajax+json+echarts简单实例

MySQL数据库设计

 

 

数据库名user1

 

项目结构

 

 

 

util层

DBUtil.java

package util;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtil {
    
    private static String Driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8";
    private static String user = "root";
    private static String psd = "root";
    private static Connection conn = null;
    
    // 连接数据库
    public static Connection getConnection() {
        if (conn == null) {
            try {
                Class.forName(Driver);
                conn = DriverManager.getConnection(url, user, psd);
                System.out.println("开始数据库连接");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    
    // 关闭数据库连接
    public static void CloseConnection() {
        if (conn != null) {
            try {
                conn.close();
                System.out.println("关闭数据库连接");
            } catch (Exception e) {
                e.printStackTrace();
            }  
        }
    }
}

JSONUtil.java

package util;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

public class JSONUtil {
    
    // 从输入流中获取JSON数据
    public static JSONObject getJSON (HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 输入流
        StringBuffer sb = new StringBuffer();
        String line = null;
        BufferedReader br = request.getReader();
        while ((line = br.readLine()) != null) {
            sb.append(line);
        }
        // 返回JSONObject
        String jsonStr = sb.toString();
        JSONObject jsonObj = JSONObject.fromObject(jsonStr);
        return jsonObj;
    }
    
    // 返回JSON格式的数据
    public static void returnJSON (HttpServletRequest request, HttpServletResponse response, JSONObject jsonObj)
            throws ServletException, IOException {
        // 设置编码格式，解决乱码
        response.setCharacterEncoding("utf-8");  

        PrintWriter out = response.getWriter() ;
        out.write(jsonObj.toString());
        out.flush();
        out.close();
    }
    
}

dao层

UserDao.java

package dao;

import java.util.List;

import domain.User;

public interface UserDao {
    
    /** 显示用户账号密码*/
    public List<User> list();
    
    /** 显示所有用户 */
    public List<User> listAll();
    
    /** 添加一个用户 */
    public boolean add(User user);
    
    /** 根据id删除一个用户 */
    public boolean deleteById(Integer id);
    
    /** 更新一个用户 */
    public boolean update(User user);
    
    /** 根据id查找一个用户 */
    public User getById(Integer id);
    
}

UserDaoImpl.java

package dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.UserDao;
import domain.User;
import util.DBUtil;

public class UserDaoImpl implements UserDao {

     @Override
        public List<User> list() {
            List<User> users = new ArrayList<User>();
            Connection conn = DBUtil.getConnection();
            String sql = "select account,password from user1";
            try {
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet rst = pst.executeQuery();
                while (rst.next()) {
                    User user = new User();
                    //user.setId(rst.getInt("id"));
                    user.setAccount(rst.getString("account"));
                    user.setPassword(rst.getString("password"));
                   // user.setName(rst.getString("name"));
                    //user.setInfo(rst.getString("info"));
                    //user.setCreateTime(rst.getTimestamp("gmt_create"));
                   // user.setModifiedTime(rst.getTimestamp("gmt_modified"));
                    users.add(user);
                }
                rst.close();
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return users;
        }

    
    @Override
    public List<User> listAll() {
        List<User> users = new ArrayList<User>();
        Connection conn = DBUtil.getConnection();
        String sql = "select * from user1";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()) {
                User user = new User();
                user.setId(rst.getInt("id"));
                user.setAccount(rst.getString("account"));
                user.setPassword(rst.getString("password"));
                user.setName(rst.getString("name"));
                user.setInfo(rst.getString("info"));
                //user.setCreateTime(rst.getTimestamp("gmt_create"));
               // user.setModifiedTime(rst.getTimestamp("gmt_modified"));
                users.add(user);
            }
            rst.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    @Override
    public boolean add(User user) {
        Connection conn = DBUtil.getConnection();
        String sql = "insert into user1(account, password, name, info) value (?, ?, ?, ?)";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, user.getAccount());
            pst.setString(2, user.getPassword());
            pst.setString(3, user.getName());
            pst.setString(4, user.getInfo());
            int count = pst.executeUpdate();
            pst.close();
            return count > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean deleteById(Integer id) {
        Connection conn = DBUtil.getConnection();
        String sql = "delete from user1 where id = ?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            int count = pst.executeUpdate();
            pst.close();
            return count > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean update(User user) {
        Connection conn = DBUtil.getConnection();
        String sql = "update user1 set account = ?, password = ?, name = ?, info = ? where id = ?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, user.getAccount());
            pst.setString(2, user.getPassword());
            pst.setString(3, user.getName());
            pst.setString(4, user.getInfo());
            pst.setInt(5, user.getId());
            int count = pst.executeUpdate();
            pst.close();
            return count > 0 ? true : false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public User getById(Integer id) {
        User user = new User();
        Connection conn = DBUtil.getConnection();
        String sql = "select * from user1 where id = ?";
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1, id);;
            ResultSet rst = pst.executeQuery();
            while (rst.next()) {
                user.setId(rst.getInt("id"));
                user.setAccount(rst.getString("account"));
                user.setPassword(rst.getString("password"));
                user.setName(rst.getString("name"));
                user.setInfo(rst.getString("info"));
                //user.setCreateTime(rst.getTimestamp("gmt_create"));
                //user.setModifiedTime(rst.getTimestamp("gmt_modified"));
            }
            rst.close();
            pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

}

service层

UserService.java

package service;

import java.util.List;

import domain.User;

public interface UserService {
     /** 显示用户账号密码 */
    public List<User> list();
    
    /** 显示所有用户 */
    public List<User> listAll();
    
    /** 添加一个用户 */
    public boolean add(User user);
    
    /** 根据id删除一个用户 */
    public boolean deleteById(Integer id);
    
    /** 更新一个用户 */
    public boolean update(User user);
    
    /** 根据id查找一个用户 */
    public User getById(Integer id);
    
}

UserServiceImpl.java

package service.Impl;

import java.util.List;

import dao.UserDao;
import dao.Impl.UserDaoImpl;
import domain.User;
import service.UserService;

public class UserServiceImpl implements UserService {

    @Override
    public List<User> list() {
        UserDao userDao = new UserDaoImpl();
        return userDao.list();
    }
    
    @Override
    public List<User> listAll() {
        UserDao userDao = new UserDaoImpl();
        return userDao.listAll();
    }

    @Override
    public boolean add(User user) {
        UserDao userDao = new UserDaoImpl();
        return userDao.add(user);
    }

    @Override
    public boolean deleteById(Integer id) {
        UserDao userDao = new UserDaoImpl();
        return userDao.deleteById(id);
    }

    @Override
    public boolean update(User user) {
        UserDao userDao = new UserDaoImpl();
        return userDao.update(user);
    }

    @Override
    public User getById(Integer id) {
        UserDao userDao = new UserDaoImpl();
        return userDao.getById(id);
    }

}

servlet层

UserServlet.java

package servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import domain.User;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import service.UserService;
import service.Impl.UserServiceImpl;
import util.JSONUtil;

@WebServlet("/UserServlet")
public class UserServlet extends HttpServlet {
    
    private static final long serialVersionUID = 1L;
       
    public UserServlet() {
        super();
    }
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 设置返回编码格式，解决中文乱码
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json");
        
        // 根据action的值来执行不同的动作，例如显示所有用户或显示单个用户
        String action = request.getParameter("action");
        
     // 显示用户账号密码
        if (action.equals("list")) {
            UserService userService = new UserServiceImpl();
            List<User> users = userService.list();
            
            // 返回JSON数据格式
            JSONArray jsonArr = JSONArray.fromObject(users);
            JSONObject jsonObjOut = new JSONObject();
            jsonObjOut.put("users", jsonArr);
            JSONUtil.returnJSON(request, response, jsonObjOut);
        }
        
        // 显示所有用户
        if (action.equals("listAll")) {
            UserService userService = new UserServiceImpl();
            List<User> users = userService.listAll();
            
            // 返回JSON数据格式
            JSONArray jsonArr = JSONArray.fromObject(users);
            JSONObject jsonObjOut = new JSONObject();
            jsonObjOut.put("users", jsonArr);
            JSONUtil.returnJSON(request, response, jsonObjOut);
        }
        
        // 显示单个用户
        if (action.equals("getById")) {
            int id = Integer.parseInt(request.getParameter("id"));
            UserService userService = new UserServiceImpl();
            User user = userService.getById(id);
            
            // 返回JSON数据格式
            JSONObject jsonObjOut = new JSONObject();
            jsonObjOut.put("user", user);
            JSONUtil.returnJSON(request, response, jsonObjOut);
        }
    }
    
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 获取JSON数据
        JSONObject jsonObjIn = JSONUtil.getJSON(request, response);

        User user = new User();
        user.setAccount(jsonObjIn.getString("account"));
        user.setPassword(jsonObjIn.getString("password"));
        user.setName(jsonObjIn.getString("name"));
        user.setInfo(jsonObjIn.getString("info"));
        
        UserService userService = new UserServiceImpl();
        boolean isSuccess = userService.add(user);
        
        // 返回JSON数据格式
        JSONObject jsonObjOut = new JSONObject();
        jsonObjOut.put("isSuccess", isSuccess);
        JSONUtil.returnJSON(request, response, jsonObjOut);
    }
    
    protected void doDelete(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 获取JSON数据
        JSONObject jsonObjIn = JSONUtil.getJSON(request, response);
        int id = jsonObjIn.getInt("id");

        UserService userService = new UserServiceImpl();
        boolean isSuccess = userService.deleteById(id);
        
        // 返回JSON数据格式
        JSONObject jsonObjOut = new JSONObject();
        jsonObjOut.put("isSuccess", isSuccess);
        JSONUtil.returnJSON(request, response, jsonObjOut);
        
    }
    
    protected void doPut(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 获取JSON数据
        JSONObject jsonObjIn = JSONUtil.getJSON(request, response);

        User user = new User();
        user.setId(jsonObjIn.getInt("id"));
        user.setAccount(jsonObjIn.getString("account"));
        user.setPassword(jsonObjIn.getString("password"));
        user.setName(jsonObjIn.getString("name"));
        user.setInfo(jsonObjIn.getString("info"));
        
        UserService userService = new UserServiceImpl();
        boolean isSuccess = userService.update(user);
        
        // 返回JSON数据格式
        JSONObject jsonObjOut = new JSONObject();
        jsonObjOut.put("isSuccess", isSuccess);
        JSONUtil.returnJSON(request, response, jsonObjOut);
    }
    
}

domain层

package domain;

public class User {
    
    public User() {
        
    }
    
    private Integer id;
    
    private String account;
    
    private String password;
    
    private String name;
    
    private String info;
    

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getPassword() {
        return password;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }


    @Override
    public String toString() {
        return "User [id=" + id + ", account=" + account + ", password=" + password + ", name=" + name + ", info="
                + info + "]";
    }
    
}

static:放css，js

 

 jquery-ui插件，主要使用其中的模态框功能

jquery.serializejson.min.js: jquery.serializejson插件，使用其中的serializajson方法将表单信息的序列化为JSON对象

JSON相关插件

 

 

View层

NewFile1.jsp

<%@ page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8"%>
<% String basePath = request.getScheme() + "://" + request.getServerName() + ":" 
  + request.getServerPort() + request.getContextPath() + "/"; %>
  
<!Doctype html>
<html>
<head>
  <base href="<%=basePath%>">
  <title>test
</title>
  <meta charset="utf-8">
  <link rel="stylesheet" href="static/css/style.css">
  <link rel="stylesheet" href="static/js/jquery-ui/jquery-ui.min.css">
  <script src="static/js/jquery-3.3.1.min.js"></script>
  <script src="static/js/jquery.serializejson.min.js"></script>
  <script src="static/js/jquery-ui/jquery-ui.min.js"></script>
  
   <!-- 引入 echarts.js -->
      <script type="text/javascript" src="http://echarts.baidu.com/build/dist/echarts-all.js"></script>
</head>
<body>
  <!-- 用户列表 -->
  <div class="tbl-title">用户列表</div>
  <button onclick="showAddUserModal()">添加新的用户</button>
  <table id="users" class="tbl-list">
    <thead>
      <tr>
        <th>账户</th>
        <th>密码</th>
        <th>操作</th>
      </tr>
    </thead>
    <tbody>
    </tbody>
  </table>
  <!-- 图表 -->
   <!-- 为ECharts准备一个具备大小（宽高）的Dom -->
    <div id="main" style="width: 600px;height:400px;"></div>
  
  <!-- 添加用户模态框 -->
  <div id="add-user-modal" title="添加用户" style="display:none;">
    <form id="add-user-form">
      <table class="modal-tbl">
        <tr><td>账户</td><td><input type="text" name="account"></td></tr>
        <tr><td>密码</td><td><input type="password" name="password"></td></tr>
        <tr><td>姓名</td><td><input type="text" name="name"></td></tr>
        <tr><td>信息</td><td><input type="text" name="info"></td></tr>
      </table>
    </form>
  </div>
  
  <!--删除用户对话模态框 -->
  <div id="delete-user-modal" title="删除用户" style="display:none;">
    <p>确定删除该用户吗？</p>
  </div>
  
  <!-- 更新用户模态框 -->
  <div id="update-user-modal" title="更新用户" style="display:none;">
    <form id="update-user-form">
      <table class="modal-tbl">
        <tr style="display: none"><td>ID</td><td><input type="text" name="id"></td></tr>
        <tr><td>账户</td><td><input type="text" name="account"></td></tr>
        <tr><td>密码</td><td><input type="password" name="password"></td></tr>
        <tr><td>姓名</td><td><input type="text" name="name"></td></tr>
        <tr><td>信息</td><td><input type="text" name="info"></td></tr>
      </table>
    </form>
  </div>
  
  <!-- 提示信息模态框 -->
  <div id="msg-modal" title="" style="display:none">
    <p></p>
  </div>
  
  <script>
  // 开始
  $(function() {
    list();
    show();
  });
  
  function list() {
        $.ajax({
          type: "GET",
          url: "UserServlet?action=list",
          dataType: "json",
          success: function(data) {
            $("#users tbody").empty();  // 每次载入前先清空显示区域，防止数据重复显示
            var users = eval(data.users);
            for (var i in users) {
              var user = users[i];
              var userStr = JSON.stringify(user);
              $("#users tbody").append('<tr><td>' + user.account + '</td>'
                + '<td>' + user.password + '</td>'
                + '<td><button onclick=\'showUpdateUserModal(' + userStr + ')\'>更新</button>&nbsp;'
                + '<button onclick="deleteUser(' + user.id + ')">删除</button></td></tr>');
            }
          },
          error: function() {
            console.log("ajax error");
          }
        });
      }
  
  // 显示所有用户
  function listAllUser() {
    $.ajax({
      type: "GET",
      url: "UserServlet?action=listAll",
      dataType: "json",
      success: function(data) {
        $("#users tbody").empty();  // 每次载入前先清空显示区域，防止数据重复显示
        var users = data.users;
        for (var i in users) {
          var user = users[i];
          var userStr = JSON.stringify(user);
          $("#users tbody").append('<tr><td>' + user.account + '</td>'
            + '<td>' + user.password + '</td>'
            + '<td><button onclick=\'showUpdateUserModal(' + userStr + ')\'>更新</button>&nbsp;'
            + '<button onclick="deleteUser(' + user.id + ')">删除</button></td></tr>');
        }
      },
      error: function() {
        console.log("ajax error");
      }
    });
  }
  
  // 显示添加用户模态框
  function showAddUserModal() {
    $("#add-user-form")[0].reset();  // 每次载入前先清空表单，防止显示之前的信息
    $("#add-user-modal").dialog({
      resizable: false,
      modal: true,
      buttons: {
        "提交": function() {
          addUser();
        },
        "取消": function() {
          $(this).dialog("close");
        },
      },
    });
  }
  
  // 添加用户
  function addUser() {
    // 获取序列化表单信息
    var user = $("#add-user-form").serializeJSON();
    var userStr = JSON.stringify(user);
    
    $.ajax({
      type: "POST",
      url: "UserServlet",
      data: userStr,
      dataType: "json",
      success: function(data) {
        if (data.isSuccess) {
          $("#add-user-modal").dialog("close");
          showMsg('添加成功！');
          list();
          show();
        } else {
          $("#add-user-modal").dialog("close");
          showMsg('添加失败！');
          list();
          show();
        }
      },
      error: function() {
        console.log("ajax error");
      },
    });
  }
  
  // 删除用户
  function deleteUser(id) {
    // 将id封装为JSON格式数据
    var data = {};
    data.id = id;
    var dataStr = JSON.stringify(data);
    // 显示删除用户模态框
    $("#delete-user-modal").dialog({
      resizable: false,
      modal: true,
      buttons: {
        "确认": function() {
          $.ajax({
            type: "DELETE",
            url: "UserServlet",
            data: dataStr,
            dataType: "json",
            success: function(data) {
              if (data.isSuccess) {
                $("#delete-user-modal").dialog("close");
                showMsg('删除成功！');
                list();
                show();
              } else {
                $("#delete-user-modal").dialog("close");
                showMsg('删除失败！');
                list();
              }
            },
            error: function() {
              console.log("ajax error");
            },
          });
        },
        "取消": function() {
          $(this).dialog("close");
        },
      },
    });
  }
  
  //显示更新用户模态框
  function showUpdateUserModal(user) {
    $("#update-user-form")[0].reset();  // 每次载入前先清空表单，防止显示之前的信息
    // 表单赋值
    $("#update-user-form input[name='id']").val(user.id);
    $("#update-user-form input[name='account']").val(user.account);
    $("#update-user-form input[name='password']").val(user.password);
    $("#update-user-form input[name='name']").val(user.name);
    $("#update-user-form input[name='info']").val(user.info);
    
    $("#update-user-modal").dialog({
      resizable: false,
      modal: true,
      buttons: {
        "提交": function() {
          updateUser();
        },
        "取消": function() {
          $(this).dialog("close");
        },
      },
    });
  }
  
  // 更新用户
  function updateUser() {
    // 获取序列化表单信息
    var user = $("#update-user-form").serializeJSON();
    var userStr = JSON.stringify(user);
    
    $.ajax({
      type: "PUT",
      url: "UserServlet",
      data: userStr,
      contentType: 'application/json;charset=utf-8',
      dataType: "json",
      success: function(data) {
        if (data.isSuccess) {
          $("#update-user-modal").dialog("close");
          showMsg('更新成功！');
          list();
          show();
        } else {
          $("#update-user-modal").dialog("close");
          showMsg('更新失败！');
          list();
          show();
        }
      },
      error: function() {
        console.log("ajax error");
      },
    });
  }
  
  //显示提示信息
  function showMsg(text) {
    $("#msg-modal p").text(''); // 每次载入前先清空显示区域，防止显示之前的信息
    $("#msg-modal p").text(text);
    $("#msg-modal").dialog({
      modal: true,
    });
    // 2s后消失
    setTimeout(function() {
      $("#msg-modal").dialog("close")
    },2000);
  }
  
  //显示图表
   // 利用dom，初始化echarts实例
        var myChart = echarts.init(document.getElementById('main'));         
        var account= [];         // 账户的数组
        var password= [];        // 的数组  
        function show() {        
       $.ajax({
            type: "GET",
            url: "UserServlet?action=list",
            dataType: "json",  
            success: function(result){                
                     var users = result.users;
                     //加载数据前先置空数组
                     account=[];
                     password=[];
                     for(var i in users){
                         //alert(users[i].account+"---"+users[i].password);
                         account.push(users[i].account);
                         password.push(users[i].password);   
                     }
                     //显示返回值
                     //document.write([account]+ "<br />");
                     //document.write([password]);                       
                       var option={
                               tooltip: {
                                show: true,
                                trigger: 'axis',
                            },
                            legend: {
                                data:['数量']
                            },    
                            toolbox: {
                             show : true,
                             feature : {
                                 mark : {show: true},        // 辅助线标志
                                 dataView : {show: true, readOnly: false},    // 数据视图
                                 magicType : {show: true, type: ['line', 'bar']},    // 动态类型转换（折线柱形互转）
                                 restore : {show: true},        // 还原
                                 saveAsImage : {show: true}        // 保存图片
                             }
                         },
                               xAxis: {
                                type: 'category',
                                data: account
                            },
                            yAxis: {
                                type: 'value'
                            },
                            series: [{
                                data: password,
                                name: '数量',
                                type: 'bar',
                            }]
                       }
                myChart.setOption(option,true);//重新加载
            }
        });
        }
  </script>
</head>
</html>

 运行结果：（把密码引为数量）

引用博客园地址：

 https://www.cnblogs.com/liufuyi/p/10804824.html