servlet04_测试前面的知识(servlet前端与后台操作增删改查)

servlet操作增删改查

  1. 数据库准备工作

     创建用户信息表 Users
      create table Users(
      userId int primary key auto_increment, #用户编号
      userName varchar(50),     #用户名称
      password varchar(50),     #用户密码
      sex      char(1),         #用户性别 '男'或者'女'
      email    varchar(50)      #用户邮箱
      )
      auto_increment,自增序列 i++
      在插入的时候如果不给定具体用户的编号,此时根据auto_increment的值递增添加
    
  2. 创建一个maven项目:加入依赖:

 <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId> junit</artifactId>
            <version>4.11</version>
        </dependency>
        <!--加入依赖servlet-api-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version> 2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat</groupId>
            <artifactId>tomcat-catalina</artifactId>
            <version>9.0.43</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId> mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
    </dependencies>
  1. 创建entity包,里面放Users(里面的属性与数据库列表中的属性一致)
package com.xw.entity;
/**
 * @author xw
 * @date 2021-03-21 19:24.
 */
public class Users {
    private Integer userId;
    private String  userName;
    private String  password;
    private String  sex;
    private String  email;
//--------setter and getter------
    public Users() {
    }

    public Users(Integer userId, String userName, String password, String sex, String email) {
        this.userId = userId;
        this.userName = userName;
        this.password = password;
        this.sex = sex;
        this.email = email;
    }
    
    public Users(String userName, String password, String sex, String email) {
        this.userName = userName;
        this.password = password;
        this.sex = sex;
        this.email = email;
    }
}
  1. 创建util包,里面存放Jdbc的工具类。
    注意:这里设置自己的数据库和自己数据库的密码(这里使用的8.0.22版本,如果使用的是低版本,注意自己URL和Driver)
package com.xw.util;
import java.sql.*;
/**
 * @author xw
 * @date 2021-05-18 18:50.
 */
public class JdbcUtil {
    final String URL="jdbc:mysql://localhost:3306/**?serverTimezone=UTC";
    final String USERNAME="root";
    final String PASSWORD="*****";
    PreparedStatement ps=null;
    Connection con=null;
    //将jar包中的driver实现类加载到JVM中
    static{
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public Connection getCon(){
        try {
            con= DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }
    public PreparedStatement creatStatement(String sql){
        try {
            ps=getCon().prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }
    //ps与con销毁适合:insert update delete
    public void close(){
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //销毁查询语句的ps,con,rs
    public void close(ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
  1. 创建dao包,里面放入UserDao类。(里面存放增删改查的方法)
package com.xw.dao;
import com.xw.entity.Users;
import com.xw.util.JdbcUtil;
import org.apache.tomcat.jni.User;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xw
 * @date 2021-05-18 18:48.
 */
public class UserDao {
    JdbcUtil util=new JdbcUtil();
    //Login登录界面
    public int login(String userName,String password){
        ResultSet rs=null;
        int result=0;
        //1.sql语句进行数据库内的数据查询
        String sql="select count(*) from users where userName=? and password=?";
        //2.获取数据库操作对象
        PreparedStatement ps=util.creatStatement(sql);
        //3.把sql语句放在框架中进行预编译
        try {
            ps.setString(1,userName);
            ps.setString(2,password);
            //4.执行sql语句
            rs=ps.executeQuery();
            //5.处理查询结果集
            while (rs.next()){
               result=rs.getInt("count(*)");
            }
            } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
    }
    return result;
    }
    //添加信息
    public int  add(Users users){
        int result=0;
        String sql="insert into users (userName,password,sex,email)values(?,?,?,?)";
        //获取数据库操作
        PreparedStatement ps = util.creatStatement(sql);
        //把sql语句放在框架中进行预编译
        try {
            ps.setString(1,users.getUserName());
            ps.setString(2,users.getPassword());
            ps.setString(3,users.getSex());
            ps.setString(4,users.getEmail());
            result = ps.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }
    //查找信息
    public List findAll(){
        String sql="select * from users";
        //1.获取数据库操作对象
        PreparedStatement ps = util.creatStatement(sql);
        ResultSet rs=null;
        //2.创建一个集合储存数据
        List list=new ArrayList();
        try {
            rs=ps.executeQuery();
            while(rs.next()){
                Integer userId=rs.getInt("userId");
                String userName=rs.getString("userName");
                String password =rs.getString("password");
                String sex=rs.getString("sex");
                String email=rs.getString("email");
                Users users=new Users(userId,userName,password,sex,email);
                list.add(users);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs);
        }
        return list;
    }
    //删除用户信息
    public int delete(String userId){
        String sql="delete from  users where userId=?";
        PreparedStatement ps=util.creatStatement(sql);
        int result=0;
        try {
            ps.setString(1,userId);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close();
        }
        return result;
    }
}
  1. 创建controller包,里面放入登录(Login),用户添加(UsersAdd),用户查找(UserAdd),用户删除(UsersDelete)。然后在设置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
         version="4.0">
    <!--欢迎资源文件-->
    <welcome-file-list>
        <welcome-file>Login.html</welcome-file>
    </welcome-file-list>

    <servlet>
        <servlet-name>Login</servlet-name>
        <servlet-class>com.xw.controller.Login</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>Login</servlet-name>
        <url-pattern>/login</url-pattern>
    </servlet-mapping>
    <servlet>
        <servlet-name>UserAdd</servlet-name>
        <servlet-class>com.xw.controller.UserAdd</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>UserAdd</servlet-name>
        <url-pattern>/add</url-pattern>
    </servlet-mapping>
    <servlet>
        <servlet-name>FindAdd</servlet-name>
        <servlet-class>com.xw.controller.FindAll</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>FindAdd</servlet-name>
        <url-pattern>/find</url-pattern>
    </servlet-mapping>
    <servlet>
        <servlet-name>UserDelete</servlet-name>
        <servlet-class>com.xw.controller.UsersDelete</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>UserDelete</servlet-name>
        <url-pattern>/delete</url-pattern>
    </servlet-mapping>
</web-app>
  1. 创建Login.html(登录界面),Login_error(登录失败界面),index.html(首页),left.html(首页左面的页面),right.html(首页右边的页面),top.html(首页上面的页面)

Login.html页面代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Login</title>
</head>
<style>
    body{
        background-color: cornsilk;
    }
    .login{
        position: relative;
        top: 250px;
    }
    .login center form table{
        border:1px solid red;
    }
    .login center form table tr:last-child input{
        margin-left: 50px;
    }
</style>
<body>
<div class="login">
    <center>
        <form action="/login" method="post">
            <table>
                <tr>
                    <td colspan="2" align="center">在线考试系统</td>
                </tr>
                <tr>
                    <td>登录账号:</td>
                    <td><input type="text" name="userName" autocomplete="off"></td>
                </tr>
                <tr>
                    <td>用户密码:</td>
                    <td><input type="password" name="password"></td>
                </tr>
                <tr>
                    <td colspan="2"><input type="submit" value="登录"><input type="reset"></td>
                </tr>
            </table>
        </form>
    </center>
</div>
</body>
</html>

Login_error.html代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Login</title>
</head>
<style>
    body{
        background-color: cornsilk;
    }
    .login{
        position: relative;
        top: 250px;
    }
    .login center form table{
        border:1px solid red;
    }
    .login center form table tr:last-child input{
        margin-left: 50px;
    }
</style>
<body>
<div class="login">
    <center>
        <font style="font-size: 40px" color="red" >账号或者密码错误请重新登录</font>
        <form action="/login" method="post">
            <table>
                <tr>
                    <td colspan="2" align="center">在线考试系统</td>
                </tr>
                <tr>
                    <td>登录账号:</td>
                    <td><input type="text" name="userName" autocomplete="off"></td>
                </tr>
                <tr>
                    <td>用户密码:</td>
                    <td><input type="password" name="password"></td>
                </tr>
                <tr>
                    <td colspan="2"><input type="submit" value="登录"><input type="reset"></td>
                </tr>
            </table>
        </form>
    </center>
</div>
</body>
</html>

index.html代码:注意要去掉body标签

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>index</title>
</head>
<frameset rows="15%,85%">
    <frame name="top" src="/top.html">
    <frameset cols="15%,85%">
        <frame name="left" src="left.html" >
        <frame name="right">
    </frameset>
</frameset>
</html>

left.html页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>index_left</title>
</head>
<body>
<ul>
    <li>用户信息查询</li>
    <ol>
        <li><a href="/right.html" target="right">用户信息注册</a></li>
        <li><a href="/find" target="right">用户信息查询</a></li>
    </ol>
</ul>
</body>
</html>

top.html页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>index_top</title>
</head>
<body style="background-color: chartreuse">
<center>
    <font style="color: red;font-size: 40px">xw考试管理系统</font>
</center>
</body>
</htm

right.html页面代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>index_top</title>
</head>
<body style="background-color: chartreuse">
<center>
    <font style="color: red;font-size: 40px">xw考试管理系统</font>
</center>
</body>
</htm

right.html页面代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>index_right</title>
</head>
<body>
<center>
    <form action="/add" method="get">
        <table border="2">
            <tr>
                <td>用户姓名</td>
                <td><input type="text" name="userName"></td>
            </tr>
            <tr>
                <td>用户密码</td>
                <td><input type="password" name="password"></td>
            </tr>
            <tr>
                <td>用户性别</td>
                <td>
                    <input type="radio" name="sex" value="男"><input type="radio" name="sex" value="女"></td>
            </tr>
            <tr>
                <td>用户邮箱</td>
                <td><input type="text" name="email"></td>
            </tr>
            <tr>
                <td><input type="submit" value="用户注册"></td>
                <td><input type="reset"></td>
            </tr>
        </table>
    </form>
</center>
</body>
</html>
  1. 然后再写controller包内的代码:

Login类中的代码

package com.xw.controller;
import com.xw.dao.UserDao;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
 * @author xw
 * @date 2021-05-18 18:25.
 */
public class Login extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String userName,password;
        UserDao dao=new UserDao();
        int result;
        userName=req.getParameter("userName");
        password=req.getParameter("password");
        result = dao.login(userName, password);
        if(result==1){
            resp.sendRedirect("/index.html");
        }else{
            resp.sendRedirect("/Login_error.html");
        }
        System.out.println(userName+","+password);
    }
}

UserAdd类中的代码

package com.xw.controller;

import com.xw.dao.UserDao;
import com.xw.entity.Users;
import org.apache.tomcat.jni.User;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;

/**
 * @author xw
 * @date 2021-05-18 18:47.
 */
public class UserAdd extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String userName,password,sex,email;
        UserDao userDao=new UserDao();
        userName=req.getParameter("userName");
        password=req.getParameter("password");
        sex=req.getParameter("sex");
        email=req.getParameter("email");
        Users users=new Users(userName,password,sex,email);
        int result = userDao.add(users);
        PrintWriter out;
        resp.setContentType("text/html;charset=utf-8");
        out=resp.getWriter();
        if(result==1){
            out.print("<font style='font-size: 40px;color: red'>用户注册成功</font>");
        }else{
            out.print("<font style='font-size: 40px;color: red'>服务器异常,用户注册失败,请稍后重试!!</font>");
        }
    }
}

FindAll类中的代码

package com.xw.controller;

import com.xw.dao.UserDao;
import com.xw.entity.Users;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

/**
 * @author xw
 * @date 2021-05-18 22:39.
 */
public class FindAll extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        UserDao dao=new UserDao();
        PrintWriter out;
        //设置浏览器显示格式
        resp.setContentType("text/html;charset=utf-8");
        List<Users> usersList = dao.findAll();
        //设置响应对象将用户信息结合<table>标签命令以二进制的形式写入到响应体
        out=resp.getWriter();
        out.print("<table border='2' align='center'>");
        out.print("<tr>");
        out.print("<td>用户编号</td>");
        out.print("<td>用户姓名</td>");
        out.print("<td>用户密码</td>");
        out.print("<td>用户性别</td>");
        out.print("<td>用户邮箱</td>");
        out.print("<td>操作</td>");
        out.print("</tr>");
        for (Users users:usersList){
            out.print("<tr>");
            out.print("<td>"+users.getUserId()+"</td>");
            out.print("<td>"+users.getUserName()+"</td>");
            out.print("<td>******</td>");
            out.print("<td>"+users.getSex()+"</td>");
            out.print("<td>"+users.getEmail()+"</td>");
            out.print("<td><a href='/delete?userId="+users.getUserId()+"'>删除用户</a></td>");
            out.print("</tr>");
        }
        out.print("</table>");
    }
}

UserDelete类中的代码

package com.xw.controller;

import com.xw.dao.UserDao;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;

/**
 * @author xw
 * @date 2021-05-19 02:47.
 */
public class UsersDelete extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String userId;
        UserDao dao=new UserDao();
        int result;
        PrintWriter out;
        //调用请求对象,读取请求头参数(用户编码)
        userId=req.getParameter("userId");
        //调用dao将用户编码填充到delete命令并发送到数据库
        result = dao.delete(userId);
        //调用响应对象处理结果以二进制的形式写入到响应体
        resp.setContentType("text/html;charset=utf-8");
        out = resp.getWriter();
        if(result==1){
            resp.sendRedirect("/find");
        }else{
            out.print("<font style='font-size:40px;color:red'>删除失败</font>");
        }
    }
}

到这里代码就完成了,然后启动Tomcat运行。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值