Servlet与Jsp的结合使用实现信息管理系统二

2022-03-20 23:11:03

PS：前面说了抽取框架的搭建，接着就要我们开始进入网址的时候就要查到全部信息并显示在首页，我们用到的MySql数据库，具体步骤是：

创建数据库，创建表，添加信息
项目中调入mysql的jar包 mysql-connector-java-5.1.18-bin.jar
单独一个类写JDBC连接数据库

查询的Sql语句及方法
删除sql语句及方法
增加sql语句及方法
修改sql语句及方法

xml配置文件的设置
mainservlet中写相应操作

1：创建数据库，创建表，添加信息

2：项目中调入mysql的jar包 mysql-connector-java-5.1.18-bin.jar

自行导入即可

3：单独一个类写JDBC连接数据库

 /**

     * 开启数据库连接

     * */

    public void getConnect() {

        String driver = "com.mysql.jdbc.Driver";

// URL指向要访问的数据库名，设置编码

        String url = "jdbc:mysql://127.0.0.1:3306/ceshi1?useUnicode=true&characterEncoding=utf-8";

// MySQL配置时的用户名

        String user = "root";

// Java连接MySQL配置时的密码

        String password = "root";

        try {

// 加载驱动程序

            Class.forName(driver);

// 连续数据库

            conn = DriverManager.getConnection(url, user, password);

            if (!conn.isClosed())

                System.out.println("Succeeded connecting to the Database!");

// statement用来执行SQL语句

            statement = conn.createStatement();

// 要执行的SQL语句

            String sql = "select * from information";

        } catch (Exception e) {

            System.out.println("连接失败");

        }

    }

    /**

     * 断开数据库连接*/

    public void disConnect() throws SQLException {

        if(conn!=null){

            conn.close();

            statement.close();

        }

    }

下面是增删查改，查询数据和查询单条数据都是返回一个list集合，原本是可以写成一个方法的，但为了清楚显示，就单独写了。在这之前呢，要先写一个model类

public class Information {

    String name,banji,qq,shouji,zhuangtai;

    int id,age;

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public String getBanji() {

        return banji;

    }

    public void setBanji(String banji) {

        this.banji = banji;

    }

    public String getQq() {

        return qq;

    }

    public void setQq(String qq) {

        this.qq = qq;

    }

    public String getShouji() {

        return shouji;

    }

    public void setShouji(String shouji) {

        this.shouji = shouji;

    }

    public String getZhuangtai() {

        return zhuangtai;

    }

    public void setZhuangtai(String zhuangtai) {

        this.zhuangtai = zhuangtai;

    }

}

/**

     * 查询全部数据,返回list集合

     * String sql = "select * from information";

     */

    public List<Information> queryAllData() throws SQLException {

        List<Information> list = new ArrayList<Information>();

        String sql = "select * from information";

        ResultSet rs = statement.executeQuery(sql);

        while (rs.next()) {

            System.out.print(rs.getInt("id"));

            information = new Information();

            information.setId(rs.getInt("id"));

            information.setName(rs.getString("name"));

            information.setAge(rs.getInt("age"));

            information.setBanji(rs.getString("banji"));

            information.setQq(rs.getString("qq"));

            information.setShouji(rs.getString("shouji"));

            information.setZhuangtai(rs.getString("zhuangtai"));

            list.add(information);

        }

        return list;

    }

    /***

     *插入数据

     */

    public int addData(String sql) {

        int a = ;

        try {

            a = statement.executeUpdate(sql);

            System.out.print("插入成功");

        } catch (SQLException e) {

            System.out.print("插入失败");

            e.printStackTrace();

        }

        return a;

    }

    /***

     *删除数据

     */

    public void deleteData(String sql) {

        try {

            statement.executeUpdate(sql);

            System.out.print("删除成功");

        } catch (SQLException e) {

            System.out.print("删除失败");

            e.printStackTrace();

        }

    }

    /**

     * 查询单条数据,返回一个list集合,为了是在用户修改的时候,把数据带到指定页面

     */

    public List<Information> updataData1(String sql) {

        try {

            list1 = new ArrayList<Information>();

            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {

                information = new Information();

                information.setId(rs.getInt("id"));

                information.setName(rs.getString("name"));

                information.setAge(rs.getInt("age"));

                information.setBanji(rs.getString("banji"));

                information.setQq(rs.getString("qq"));

                information.setShouji(rs.getString("shouji"));

                information.setZhuangtai(rs.getString("zhuangtai"));

                list1.add(information);

            }

            System.out.print("查成功");

        } catch (SQLException e) {

            System.out.print("查失败");

            e.printStackTrace();

        }

        return list1;

    }

    /**

     * 更改数据,如果成功返回1

     */

    public void updataData2(String sql) {

        try {

            int num = statement.executeUpdate(sql);

            System.out.print("更改成功");

        } catch (SQLException e) {

            System.out.print("更改失败");

            e.printStackTrace();

        }

    }

4：xml配置文件设置拦截，只要是后面带.do的都拦截。

 <servlet-mapping>

        <servlet-name>mainservlet</servlet-name>

       <url-pattern>*.do</url-pattern>

    </servlet-mapping>

    <servlet>

        <servlet-name>mainservlet</servlet-name>

        <servlet-class>control.MainServlet</servlet-class>

    </servlet>

    <welcome-file-list>

        <welcome-file>index.jsp</welcome-file>

    </welcome-file-list>

5：mainservlet中写相应操作

5.1在网页加载的时候就调用数据库查询全部数据，并返回到首页，首先要对页面编码设置

　　　　　response.setContentType("text/html;charset=utf-8");

        request.setCharacterEncoding("UTF-8");

        response.setCharacterEncoding("UTF-8");

然后要获取地址栏中的url地址，该地址是8080后面的字符串

String str = request.getRequestURI();

拦截字符串进行equals比较，

//查询全部:
　　　　　connecDatabase();//连接数据库

        if (str.equals("/MyTest.do")) {

            queryAllData(request, response);

        }

queryAllData(request,response)方法，把获取到的数据转发到指定jsp

private void queryAllData(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        listAll = new ArrayList<>();

        try {

            listAll = daoConnection.queryAllData();

            daoConnection.disConnect();

            request.getSession().setAttribute("listAll", listAll);

            request.getRequestDispatcher("index.jsp").forward(request, response);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }

/**

     * 连接数据库

     * */

    public void connecDatabase(){

        daoConnection = new DaoConnection();

        daoConnection.getConnect();

    }

首页代码：其中获取servlet传过来的值是forEach

<%@ page contentType="text/html;charset=UTF-8" language="java" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>

<head>

    <title>Title</title>

    <link rel="stylesheet" href="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="http://cdn.static.runoob.com/libs/jquery/2.1.1/jquery.min.js"></script>

    <script src="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/js/bootstrap.min.js"></script>

</head>

<body>

<p>

    <a href="addData.jsp" class="btn btn-primary btn-lg" role="button">添加数据</a>

</p>

<!--项目列表 主页-->

<table class="table table-striped" style="width: 100%">

    <caption>信息全部详情</caption>

    <thead>

    <tr>

        <th>ID</th>

        <th>姓名</th>

        <th>年龄</th>

        <th>班级</th>

        <th>QQ</th>

        <th>手机</th>

        <th>状态</th>

        <th>操作</th>

    </tr>

    </thead>

    <tbody>

    <c:forEach items="${listAll}" var="listall">

        <tr>

            <td>${listall.id}</td>

            <td>${listall.name}</td>

            <td>${listall.age}</td>

            <td>${listall.banji}</td>

            <td>${listall.qq}</td>

            <td>${listall.shouji}</td>

            <td>${listall.zhuangtai}</td>

            <td><a href="/bianji.do?id=${listall.id}" id="bianji" >编辑</a>/<a href="/delete.do?id=${listall.id}" >删除</a></td>

        </tr>

    </c:forEach>

    </tbody>

</table>

</div>

</body>

</html>

效果：

5.2添加数据

添加中，主要是ajax回调，$('#addform').serialize()可以从上往下找到表格里的值并作为数据发送给servlet，回调成功后会弹出一个友好提示框再返回到servlet中，是layui.js,去官网上直接下载即可，不过要注意的是，在form表单中，提交按钮type不能是submit，只能是button，否则不能用（一闪而过）。

<script >

    function tijiao() {

//        alert("ssss");

        var AjaxURL = "/adddata.do";

//        alert($('#addform').serialize());

        $.ajax({

            type: "POST",

            dataType: "html",

            url: AjaxURL,

            data: $('#addform').serialize(),

            success: function (result) {

                var strresult = result;

                layer.msg('插入成功,2秒返回主页', {

                    time: 2000 //2秒关闭（如果不配置，默认是3秒）

                }, function(){

                    location.href="/homepage.do";

                });

            },

            error: function (data) {

                layer.open({

                    title: '插入提示'

                    ,content: '插入失败'

                });

            }

        })

    }

</script>

页面代码：<td><a href="/bianji.do?id=${listall.id}" id="bianji" >编辑</a>/<a href="/delete.do?id=${listall.id}" >删除</a></td>这里面可以直接对编辑，删除进行处理（在跳转到servlet中处理）。

<%--

  Created by IntelliJ IDEA.

  User: liuzhitong

  Date: 17/12/2

  Time: 下午8:45

  To change this template use File | Settings | File Templates.

--%>

<%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>

<head>

    <title>Title</title>

    <script src="http://cdn.static.runoob.com/libs/jquery/2.1.1/jquery.min.js"></script>

    <link rel="stylesheet" href="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/js/bootstrap.min.js"></script>

    <script src="layui/layui.all.js"></script>

</head>

<body>

<caption>增加信息,(*为必填)</caption>

<form class="form-horizontal" role="form"  accept-charset="utf-8" id="addform" >

    <div class="form-group">

        <label for="id_id" class="col-sm-2 control-label">(数字)*ID:</label>

        <div class="col-sm-10">

            <input type="number" class="form-control" name="dataid" id="id_id" placeholder="请输入ID">

        </div>

    </div>

    <div class="form-group">

        <label for="id_name" class="col-sm-2 control-label">*姓名:</label>

        <div class="col-sm-10">

            <input type="text" name="dataname" class="form-control" id="id_name" placeholder="请输入名字">

        </div>

    </div>

    <div class="form-group">

        <label for="id_age" class="col-sm-2 control-label">(数字)年龄:</label>

        <div class="col-sm-10">

            <input type="number" name="dataage" class="form-control" id="id_age" placeholder="请输入年龄">

        </div>

    </div>

    <div class="form-group">

        <label for="id_banji" class="col-sm-2 control-label">班级:</label>

        <div class="col-sm-10">

            <input type="text" name="databanji" class="form-control" id="id_banji" placeholder="请输入班级">

        </div>

    </div>

    <div class="form-group">

        <label for="id_qq" class="col-sm-2 control-label">QQ:</label>

        <div class="col-sm-10">

            <input type="text" name="dataqq" class="form-control" id="id_qq" placeholder="请输入QQ">

        </div>

    </div>

    <div class="form-group">

        <label for="id_shouji" class="col-sm-2 control-label">手机:</label>

        <div class="col-sm-10">

            <input type="text" name="datashouji" class="form-control" id="id_shouji" placeholder="请输入手机">

        </div>

    </div>

    <div class="form-group">

        <label for="id_zhuangtai" class="col-sm-2 control-label">状态:</label>

        <div class="col-sm-10">

            <input type="text" name="datazhuangtai" class="form-control" id="id_zhuangtai" placeholder="请输入状态">

        </div>

    </div>

    <div class="form-group">

        <div class="col-sm-offset-2 col-sm-10">

            <button type="button" class="btn btn-default"   onclick="tijiao()" >保存</button>

           <a href="homepage.jsp" class="btn btn-default" role="button">返回</a>

        </div>

    </div>

</form>

</body>

<script >

    function tijiao() {

//        alert("ssss");

        var AjaxURL = "/adddata.do";

//        alert($('#addform').serialize());

        $.ajax({

            type: "POST",

            dataType: "html",

            url: AjaxURL,

            data: $('#addform').serialize(),

            success: function (result) {

                var strresult = result;

//                alert("插入成功");

                layer.msg('插入成功,2秒返回主页', {

                    time: 2000 //2秒关闭（如果不配置，默认是3秒）

                }, function(){

                    location.href="/homepage.do";

                });

            },

            error: function (data) {

//                alert("error:" + data.responseText);

                layer.open({

                    title: '插入提示'

                    ,content: '插入失败'

                });

            }

        })

    }

</script>

</html>

MainServlet.java中对接收的数据进行处理，接收数据直接用String name = request.getParameter("dataname");

else if(str.equals("/adddata.do")){

            addServletData(request, response);

        }

private void addServletData(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String id=request.getParameter("dataid");

        System.out.print("ceshi---"+id);

        String name = request.getParameter("dataname");

        String banji = request.getParameter("databanji");

        String dataqq = request.getParameter("dataqq");

        String datashouji = request.getParameter("datashouji");

        String datazhuangtai = request.getParameter("datazhuangtai");

        int age =Integer.parseInt(request.getParameter("dataage"));

        if (name.equals("") || id.equals("") ) {

        } else {

            String sql = "INSERT INTO information VALUES('" + id + "','" + name + "'," + age + ",'" + banji + "','" + dataqq + "','" + datashouji + "','" + datazhuangtai + "')";

            panduan = daoConnection.addData(sql);

        }

        response.getWriter().write("yue");

    }

到这里就插入成功了。

5.3编辑数据

在编辑jsp中也有ajax回调，用法和插入是一样的。当用户点击编辑的时候，首先url会带着该条数据的ID进入MainServlet.java进行查找该id的数据并且再带着数据list返回到编辑页面，在编辑页面点击保存按钮的时候再次进入servlet处理，而不是点击就直接进入编辑页面。

处理代码：

else if(str.equals("/bianji.do")){

            bianjiData(request,response);//仅仅是把之前的数据待到编辑.jsp页面。

        }

private void bianjiData(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String id=request.getParameter("id");

        System.out.print("------"+id);

        String chaSql="SELECT * FROM information WHERE id = '"+id+"'";

        List<Information> listDan=daoConnection.updataData1(chaSql);

        request.getSession().setAttribute("listDan",listDan);

        request.getRequestDispatcher("bianji.jsp").forward(request,response);

    }

bianji.jsp点击保存按钮处理

else if(str.equals("/update.do")){

            updateData(request,response);

        }

private void updateData(HttpServletRequest request, HttpServletResponse response) {

        String id=request.getParameter("dataid");

        System.out.print("ceshi---"+id);

        String name = request.getParameter("dataname");

        String banji = request.getParameter("databanji");

        String dataqq = request.getParameter("dataqq");

        String datashouji = request.getParameter("datashouji");

        String datazhuangtai = request.getParameter("datazhuangtai");

        int age =Integer.parseInt(request.getParameter("dataage"));

        if (name.equals("") || id.equals("") ) {

        } else {

           String sql = "UPDATE information set age="+age + ",banji='"+banji+"',qq='"+dataqq+"',shouji='"+datashouji+"',zhuangtai='"+datazhuangtai+"' where id='"+id+"'";

            daoConnection.updataData2(sql);

            flushpage(request,response);

        }

    }

 private void flushpage(HttpServletRequest request, HttpServletResponse response) {
        listAll = new ArrayList<>();
        try {
            listAll = daoConnection.queryAllData();
            request.getSession().setAttribute("listAll", listAll);
            request.getRequestDispatcher("homepage.jsp").forward(request, response);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ServletException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

编辑页面jsp全部代码：

<%--

  Created by IntelliJ IDEA.

  User: liuzhitong

  Date: 17/12/2

  Time: 下午8:45

  To change this template use File | Settings | File Templates.

--%>

<%@ page language="java" import="java.util.*" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>

<head>

    <title>Title</title>

    <%--<script src="http://cdn.static.runoob.com/libs/jquery/2.2.1/jquery.min.js"></script>--%>

    <%--<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>--%>

    <script src="js/jquery.min.js"></script>

    <link rel="stylesheet" href="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/css/bootstrap.min.css">

    <script src="http://cdn.static.runoob.com/libs/bootstrap/3.3.7/js/bootstrap.min.js"></script>

<script src="layui/layui.all.js"></script>

</head>

<body>

<caption>编辑页面,(*为必填)</caption>

<form class="form-horizontal" role="form"  accept-charset="utf-8" id="addform" >

<c:forEach items="${listDan}" var="listdan">

    <div class="form-group">

        <label for="id_id" class="col-sm-2 control-label">(数字)*ID:</label>

        <div class="col-sm-10">

            <input type="number" readonly="true" class="form-control" name="dataid" value="${listdan.id}" id="id_id" placeholder="请输入ID">

        </div>

    </div>

    <div class="form-group">

        <label for="id_name" class="col-sm-2 control-label">*姓名:</label>

        <div class="col-sm-10">

            <input type="text" readonly="true" name="dataname" class="form-control" value="${listdan.name}" id="id_name" placeholder="请输入名字">

        </div>

    </div>

    <div class="form-group">

        <label for="id_age" class="col-sm-2 control-label">(数字)年龄:</label>

        <div class="col-sm-10">

            <input type="number" name="dataage" class="form-control" value="${listdan.age}" id="id_age" placeholder="请输入年龄">

        </div>

    </div>

    <div class="form-group">

        <label for="id_banji" class="col-sm-2 control-label">班级:</label>

        <div class="col-sm-10">

            <input type="text" name="databanji" class="form-control" value="${listdan.banji}" id="id_banji" placeholder="请输入班级">

        </div>

    </div>

    <div class="form-group">

        <label for="id_qq" class="col-sm-2 control-label">QQ:</label>

        <div class="col-sm-10">

            <input type="text" name="dataqq" class="form-control" value="${listdan.qq}" id="id_qq" placeholder="请输入QQ">

        </div>

    </div>

    <div class="form-group">

        <label for="id_shouji" class="col-sm-2 control-label">手机:</label>

        <div class="col-sm-10">

            <input type="text" name="datashouji" class="form-control" value="${listdan.shouji}" id="id_shouji" placeholder="请输入手机">

        </div>

    </div>

    <div class="form-group">

        <label for="id_zhuangtai" class="col-sm-2 control-label">状态:</label>

        <div class="col-sm-10">

            <input type="text" name="datazhuangtai" class="form-control" value="${listdan.zhuangtai}" id="id_zhuangtai" placeholder="请输入状态">

        </div>

    </div>

    <div class="form-group">

        <div class="col-sm-offset-2 col-sm-10">

            <button type="button" class="btn btn-default"   onclick="tijiao()" >保存</button>

            <a href="homepage.jsp" class="btn btn-default" role="button">返回</a>

        </div>

    </div>

</c:forEach>

</form>

</body>

<script >

    function tijiao() {

        var AjaxURL = "/update.do";

        $.ajax({

            type: "POST",

            dataType: "html",

            url: AjaxURL,

            data: $('#addform').serialize(),

            success: function (result) {

                var strresult = result;

                layer.alert('修改成功', function(index){

                    layer.close(index);

                    location.href="/homepage.do";

                });

            },

            error: function (data) {

//                alert("error:" + data.responseText);

                layer.alert('修改失败', function(index){

                    layer.close(index);

                });

            }

        })

    }

</script>

</html>

5.4：删除一行数据，和编辑一样也是先带着id进入servlet处理，

else if(str.equals("/delete.do")){

            deleteData(request,response, id);

        }

    private void deleteData(HttpServletRequest request, HttpServletResponse response, String ID) throws ServletException, IOException {

        String deleteSql = "DELETE FROM information WHERE id = '" + ID + "'";

        daoConnection.deleteData(deleteSql);

        flushpage(request, response);

    }

到此，增删查改就全部写完了，上面代码看似复杂，其实没一个功能都是独立的，也就是只写一个增加功能，也是可以运行的。哦对了，还有一个wendang.jsp，这个大家可以仿着来。

总效果图：

码农公寓