实体类 Users
package com.qf.entity;
public class Users {
private int id;
private String username;
private String pwd;
private String name;
private int age;
private String sex;
public Users() {
super();
}
public Users(int id, String username, String pwd, String name, int age, String sex) {
super();
this.id = id;
this.username = username;
this.pwd = pwd;
this.name = name;
this.age = age;
this.sex = sex;
}
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 getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Users [id=" + id + ", username=" + username + ", pwd=" + pwd + ", name=" + name + ", age=" + age
+ ", sex=" + sex + "]";
}
}
工具类 DBManager
package com.qf.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBManager {
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager
.getConnection("jdbc:mysql://localhost:3306/j1904?useUnicode=true&characterEncoding=utf-8",
"root", "root");
}
public static void closeAll(Connection connection, PreparedStatement statement) {
try {
if(statement != null) {
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeAll(Connection connection, PreparedStatement statement, ResultSet resultSet) {
try {
if(resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
closeAll(connection, statement);
}
}
}
数据库操作类 UsersDAO
package com.qf.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.qf.entity.Users;
import com.qf.util.DBManager;
public class UsersDAO {
public static boolean insert(Users user) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBManager.getConnection();
String sql = "INSERT INTO users(username, pwd, name, age, sex) VALUES (?, ?, ?, ?, ?);";
statement = connection.prepareStatement(sql);
statement.setString(1, user.getUsername());
statement.setString(2, user.getPwd());
statement.setString(3, user.getName());
statement.setInt(4, user.getAge());
statement.setString(5, user.getSex());
int count = statement.executeUpdate();
return count > 0;
} catch (Exception e) {
e.printStackTrace();
}finally {
DBManager.closeAll(connection, statement);
}
return false;
}
public static boolean update(Users user) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBManager.getConnection();
String sql = "UPDATE users SET username = ?, pwd = ?, name = ?, age = ?, sex = ? WHERE id = ?;";
statement = connection.prepareStatement(sql);
statement.setString(1, user.getUsername());
statement.setString(2, user.getPwd());
statement.setString(3, user.getName());
statement.setInt(4, user.getAge());
statement.setString(5, user.getSex());
statement.setInt(6, user.getId());
int count = statement.executeUpdate();
return count > 0;
} catch (Exception e) {
e.printStackTrace();
}finally {
DBManager.closeAll(connection, statement);
}
return false;
}
public static boolean delete(int id) {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBManager.getConnection();
String sql = "DELETE FROM users WHERE id = ?;";
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
int count = statement.executeUpdate();
return count > 0;
} catch (Exception e) {
e.printStackTrace();
}finally {
DBManager.closeAll(connection, statement);
}
return false;
}
public static Users findById(int id) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = DBManager.getConnection();
String sql = "SELECT * FROM users WHERE id = ?;";
statement = connection.prepareStatement(sql);
statement.setInt(1, id);
resultSet = statement.executeQuery();
while(resultSet.next()) {
return new Users(resultSet.getInt("id"),
resultSet.getString("username"), resultSet.getString("pwd"), resultSet.getString("name"), resultSet.getInt("age"), resultSet.getString("sex"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBManager.closeAll(connection, statement, resultSet);
}
return null;
}
public static List<Users> findAll() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Users> list = new ArrayList<>();
try {
connection = DBManager.getConnection();
String sql = "SELECT * FROM users";
statement = connection.prepareStatement(sql);
resultSet = statement.executeQuery();
while(resultSet.next()) {
Users user = new Users(resultSet.getInt("id"),
resultSet.getString("username"), resultSet.getString("pwd"), resultSet.getString("name"),
resultSet.getInt("age"), resultSet.getString("sex"));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBManager.closeAll(connection, statement, resultSet);
}
return list;
}
}
显示servlet ListServlet
package com.qf.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 com.qf.dao.UsersDAO;
import com.qf.entity.Users;
@WebServlet("/list")
public class ListServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
UsersDAO dao = new UsersDAO();
List<Users> list = dao.findAll();
request.setAttribute("list", list);
request.getRequestDispatcher("list.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
预修改servlet PreUpdateServlet
package com.qf.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 com.qf.dao.UsersDAO;
import com.qf.entity.Users;
@WebServlet("/preUpdate")
public class PreUpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
UsersDAO dao = new UsersDAO();
Users user = dao.findById(Integer.parseInt(id));
request.setAttribute("user", user);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
修改servlet UpdateServlet
package com.qf.servlet;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
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 com.qf.dao.UsersDAO;
import com.qf.entity.Users;
@WebServlet("/update")
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String id = request.getParameter("id");
String username = request.getParameter("username");
String pwd = request.getParameter("pwd");
String name = request.getParameter("name");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
UsersDAO dao = new UsersDAO();
dao.update(new Users(Integer.parseInt(id), username, pwd, name, Integer.parseInt(age), sex));
response.sendRedirect("list");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
删除servlet DeleteServlet
package com.qf.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 com.qf.dao.UsersDAO;
@WebServlet("/delete")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
UsersDAO dao = new UsersDAO();
dao.delete(Integer.parseInt(id));
response.sendRedirect("list");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
增加注册servlet InsertServlet
package com.qf.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 com.qf.dao.UsersDAO;
import com.qf.entity.Users;
@WebServlet("/insert")
public class InsertServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
String username = request.getParameter("username");
String pwd = request.getParameter("pwd");
String name = request.getParameter("name");
String age = request.getParameter("age");
String sex = request.getParameter("sex");
UsersDAO dao = new UsersDAO();
Users user = new Users(0,username, pwd, name, Integer.parseInt(age), sex);
dao.insert(user);
response.sendRedirect("list");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
首页jsp index.jsp
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<a href="list">账号管理</a>
</body>
</html>
显示jsp list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<table width="80%" align="center" border="1px" cellpadding="0px" cellspacing="0px">
<tr>
<th>编号</th>
<th>账号</th>
<th>密码</th>
<th>户主</th>
<th>年龄</th>
<th>性别</th>
<th>操作</th>
</tr>
<c:forEach items="${list}" var="user">
<tr>
<td>${user.id}</td>
<td>${user.username}</td>
<td>${user.pwd}</td>
<td>${user.name}</td>
<td>${user.age}</td>
<td>${user.sex}</td>
<td>
<a href="preUpdate?id=${user.id}">修改</a>
<a href="delete?id=${user.id}" οnclick="return confirm('确定要删除吗?');">删除</a>
<a href="insert.jsp">增加</a>
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
修改jsp update.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="update" method="post">
<input type="hidden" name="id" value="${user.id}" />
<input type="text" name="username" value="${user.username}" placeholder="账号"/><br/>
<input type="text" name="pwd" value="${user.pwd}" placeholder="密码"/><br/>
<input type="text" name="name" value="${user.name}" placeholder="户主"/><br/>
<input type="text" name="age" value="${user.age}" placeholder="年龄"/><br/>
<input type="text" name="sex" value="${user.sex}" placeholder="性别"/><br/>
<input type="submit" value="修改"/><br/>
</form>
</body>
</html>
增加注册jsp insert.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="insert" method="post">
<input type="text" name="username" value="username" placeholder="账号"/><br/>
<input type="text" name="pwd" value="pwd" placeholder="密码"/><br/>
<input type="text" name="name" value="name" placeholder="户主"/><br/>
<input type="text" name="age" value="age" placeholder="年龄"/><br/>
<input type="text" name="sex" value="sex" placeholder="性别"/><br/>
<input type="submit" value="注册"/><br/>
</form>
</body>
</html>