servlet操作增删改查
-
数据库准备工作
创建用户信息表 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的值递增添加
-
创建一个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>
- 创建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;
}
}
- 创建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();
}
}
}
}
- 创建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;
}
}
- 创建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>
- 创建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>
- 然后再写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运行。