idea下使用servlet连接数据库在网页反应增删改查项目的简单基本实现

悠然少年心

已于 2022-03-01 09:35:24 修改

阅读量3.5k

点赞数 3

分类专栏：数据库文章标签：数据库 java jdbc mysql

于 2020-08-17 01:54:14 首次发布

本文链接：https://blog.csdn.net/qq_44116353/article/details/108047699

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

在这里插入图片描述
这是需要导入的包
还有如果是第一次做这种WEB连接SQL的项目记得找到你的tomcat文件下面的lib目录去把连接MySQL的jar包导进去，而不是单纯创建一个lib目录导入，不这样做会导致找不到网页从而发生404

一个超级简单的数据库
在这里插入图片描述

findAll.jsp
这里的代码其实是这样的
在这里插入图片描述

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>//这个C标签是需要导入c.tld文件的
<html>
<head>
    <title>国家级自贸网站</title>
    <script src="js/jquery-3.3.1.min.js"></script>
    <script type="text/javascript">

        function delCop(id) {


            $.ajax({
                url: "/delInfo.do",
                data: {"id": id},
                dataType: "json",
                type: "POST",
                success: function (res) {
                    alert("删除成功")
                },
                error: function () {
                    alert("删除成功")
                }


            })
        }

    </script>
</head>
<body>
<table>
    <tr>
        <th>组织名称</th>
        <th>地址</th>
        <th>组织管理人员</th>
        <th>组织id</th>
        <th>删除</th>
    </tr>
    <c:forEach items="${requestScope.list}" var="find">
        <tr>
            <td>${find.username}</td>
            <td>${find.address}</td>
            <td>${find.man}</td>
            <td>${find.id}</td>
            <td><input type="button" value="删除" onclick="delCop(${find.id})"></td>
        </tr>
    </c:forEach>
</table>
</body>
</html>

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <meta charset="UTF-8">
    <title>$Title$</title>
  </head>
  <body>
  <a href="findAll.do">查询组织的所有信息</a>
  <a href="updata.jsp">更新组织信息</a>
  <form action="insert.do"  method="post">
    请输入组织名字<input type="text" name="username"><br>
    请输入地址<input type="text" name="address"><br>
    请输入组织人员<input type="text" name="man"><br>
    请输入更新id<input type="text" name="id"><br>
    <input type="submit" value="增加">
  </form>
  </body>
</html>

updata.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<form action="up.do"  method="post">
    请输入组织名字<input type="text" name="username"><br>
    请输入地址<input type="text" name="address"><br>
    请输入组织人员<input type="text" name="man"><br>
    请输入更新id<input type="text" name="id"><br>
    <input type="submit" value="更新">
</body>
</html>

ClassFind

public class ClassFind {

    private int id;

    @Override
    public String toString() {
        return "ClassFind{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", address='" + address + '\'' +
                ", man='" + man + '\'' +
                '}';
    }

    public ClassFind(int id, String username, String address, String man) {
        this.id = id;
        this.username = username;
        this.address = address;
        this.man = man;
    }

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getMan() {
        return man;
    }

    public void setMan(String man) {
        this.man = man;
    }

    private String username;
    private String address;
    private String man;


}

Delinfo

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 java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/delInfo.do")
public class DelInfo  extends HttpServlet {
    private static final long serialVersionUID=1L;

    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

//1导入驱动jar包
//2注册驱动
        request.setCharacterEncoding("utf-8");
        String id = request.getParameter("id");
        System.out.println("id:"+id);
        try {
            Class.forName("com.mysql.cj.jdbc.Driver") ;//这里会抛出异常 throws Exception直接全部抛出 只抛出一个的话后面还是会接着叫你抛出异常
//3获取连接对线
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//“jdbc:mysql://localhost:3306(这里数据库端口)/db3（这里是要连接的数据库） ？useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC（8.x.x以后连接数据库需要在后面这一串加入不然会报错说你时区不对）”,“root（这里是账号）”, “root（这里是密码)”
//4定义SQL语句
            System.out.println("连接成功");

            String sql ="delete from zuzi where id = "+id;
//5获取执行SQL的对象 statement
            PreparedStatement pst =connection.prepareStatement(sql);
//            执行SQL语句返回提交
            int result=pst.executeUpdate();
            if (result>0){
                System.out.println("返回成功");
                request.getRequestDispatcher("/findAll.do").forward(request,response);
            }
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

FindServlet

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 java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/findAll.do")
public class FindServlet extends HttpServlet {
    private static final long serialVersionUID=1L;
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

//1导入驱动jar包
//2注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver") ;//这里会抛出异常 throws Exception直接全部抛出 只抛出一个的话后面还是会接着叫你抛出异常
//3获取连接对线
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//“jdbc:mysql://localhost:3306(这里数据库端口)/db3（这里是要连接的数据库） ？useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC（8.x.x以后连接数据库需要在后面这一串加入不然会报错说你时区不对）”,“root（这里是账号）”, “root（这里是密码)”
//4定义SQL语句
            System.out.println("连接成功");
            String sql ="select * from zuzi";
//5获取执行SQL的对象 statement
            PreparedStatement pst=connection.prepareStatement(sql);
//            执行SQL语句返回提交
            ResultSet result= pst.executeQuery();
            List<ClassFind>list=new ArrayList<>();
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String address = result.getString("address");
                String man = result.getString("man");
                ClassFind find =new ClassFind(id,username,address,man);
                list.add(find);
            }
            connection.close();
            //绑定数据d
            request.setAttribute("list",list);
          //请求转发到find
            request.getRequestDispatcher("findAll.jsp").forward(request,response);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }

}

InsertServlet


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 java.io.IOException;
import java.sql.*;

@WebServlet("/insert.do")
public class InsertServlet extends HttpServlet {
    private static final long serialVersionUID=1L;
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        String username=request.getParameter("username");
        String address=request.getParameter("address");
        String man=request.getParameter("man");
        System.out.println("组织名称"+username+"地址" +address+"组织人员"+man);
//1导入驱动jar包
//2注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver") ;//这里会抛出异常 throws Exception直接全部抛出 只抛出一个的话后面还是会接着叫你抛出异常
//3获取连接对线
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//“jdbc:mysql://localhost:3306(这里数据库端口)/db3（这里是要连接的数据库） ？useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC（8.x.x以后连接数据库需要在后面这一串加入不然会报错说你时区不对）”,“root（这里是账号）”, “root（这里是密码)”
//4定义SQL语句
            System.out.println("连接成功");
            String sql = "insert into zuzi(username,address,man) values(?,?,?)";
//创建预编译对象
            PreparedStatement pst =connection.prepareStatement(sql);

            pst.setString(1,username);
            pst.setString(2,address);
            pst.setString(3,man);
            int result=pst.executeUpdate();
            if (result>0){
                System.out.println("返回成功");
                request.getRequestDispatcher("/findAll.do").forward(request,response);
            }
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

UpServlet

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 java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/up.do")
public class UpServlet extends HttpServlet {
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1导入驱动jar包
//2注册驱动
       request.setCharacterEncoding("utf-8");
        String username=request.getParameter("username");
        String address=request.getParameter("address");
        String man =request.getParameter("man");
        String id =request.getParameter("id");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver") ;//这里会抛出异常 throws Exception直接全部抛出 只抛出一个的话后面还是会接着叫你抛出异常
//3获取连接对线
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC", "root", "root");
//“jdbc:mysql://localhost:3306(这里数据库端口)/db3（这里是要连接的数据库） ？useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC（8.x.x以后连接数据库需要在后面这一串加入不然会报错说你时区不对）”,“root（这里是账号）”, “root（这里是密码)”
//4定义SQL语句
            System.out.println("连接成功");

//5获取执行SQL的对象 statement

            String sql ="update zuzi set username=?,address=?,man=? where id=?";

//            PreparedStatement pst = connection.prepareStatement("");
            执行SQL语句返回提交
//            pst.setString(1,username);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,address);
            preparedStatement.setString(3,man);
            preparedStatement.setString(4,id);
            int result=preparedStatement.executeUpdate();
            preparedStatement.executeUpdate();
            if (result>0){
                System.out.println("返回成功");
                request.getRequestDispatcher("/findAll.do").forward(request,response);
            }
            else {
                System.out.println("跟新失败");
            }
            connection.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }