本系统实现对用户的增删改查
使用的技术 JSP+Servlet+MySQL
本系统采用mvc分层模式实现,jsp作为前端页面展示,serlvet实现控制层效果,mysql实现数据存储。
实体类bean
public class User {
private static final long serialVersionUID = 1L;
private int id;
private String username;
private String password;
public User() {
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public User(String username, String password) {
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
工具类DBUtil
public class DBUtil {
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection conn = null;
String url = "jdbc:mysql://localhost:3306/t_user?useUnicode=true&characterEncoding=UTF-8" +
"&useSSL=false&serverTimezone=GMT%2B8";
String username = "root";
String password = "root";
try {
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据访问层 dao
public class UserDAO {
//查询用户列表
public List<User> showUsers() throws Exception {
List<User> users = new ArrayList<>();
ResultSet rs;
Connection conn = DBUtil.getConnection();
String sql = "select * from user";
PreparedStatement psmt = conn.prepareStatement(sql);
rs = psmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
User user = new User(id,username,password);
users.add(user);
//System.out.println(users);
}
DBUtil.closeConnection(conn);
return users;
}
//查询一个用户信息
public User getUser(int id) {
User user = new User();
Connection conn = DBUtil.getConnection();
ResultSet rs;
String sql = "select * from user where id = ?";
PreparedStatement psmt = null;
try {
psmt = conn.prepareStatement(sql);
psmt.setInt(1, id);
rs = psmt.executeQuery();
while (rs.next()) {
String username = rs.getString("username");
String password = rs.getString("password");
user.setUsername(username);
user.setPassword(password);
user.setId(id);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeConnection(conn);
return user;
}
//添加用户
public int insertUser(User user) {
int flag = 0;
Connection conn = DBUtil.getConnection();
String sql = "insert into user (username, password) values (?,?)";
PreparedStatement psmt = null;
try {
psmt = conn.prepareStatement(sql);
psmt.setString(1, user.getUsername());
psmt.setString(2, user.getPassword());
flag = psmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeConnection(conn);
return flag;
}
// 修改用户
public void updateUser(User user) {
Connection conn = DBUtil.getConnection();
String sql = "update user set username = ?, password = ? where id = ?";
PreparedStatement psmt;
try {
psmt = conn.prepareStatement(sql);
psmt.setString(1, user.getUsername());
psmt.setString(2, user.getPassword());
psmt.setInt(3, user.getId());
psmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeConnection(conn);
}
public void deleteUser(int id) {
Connection conn = DBUtil.getConnection();
String sql = "delete from user where id = ?";
PreparedStatement psmt;
try {
psmt = conn.prepareStatement(sql);
psmt.setInt(1,id);
psmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
DBUtil.closeConnection(conn);
}
}
服务层 service
public class UserService {
private UserDAO userDAO = new UserDAO();
// 查询用户列表
public List<User> showUsers() {
try {
return userDAO.showUsers();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
// 查询单个用户
public User getUserById(int id) {
return userDAO.getUser(id);
}
// 添加用户
public int insertUser(User user) {
return userDAO.insertUser(user);
}
// 修改用户
public void updateUser(User user) {
userDAO.updateUser(user);
}
// 删除用户
public void deleteUser(int id) {
userDAO.deleteUser(id);
}
}
控制层 controller
@WebServlet("/jsp/AddUserServlet")
public class AddUserServlet extends HttpServlet {
private UserService userService = new UserService();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
// 从前端页面获取到用户名和密码
String username = request.getParameter("username");
String password = request.getParameter("password");
// 创建一个用户,并添加用户名和密码
User user = new User(username,password);
int flag = userService.insertUser(user);
System.out.println(user.toString());
System.out.println(flag);
if (flag != 0) {
// 跳转到UserServlet
response.sendRedirect("UserServlet");
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
@WebServlet("/jsp/DeleteUserServlet")
public class DeleteUserServlet extends HttpServlet {
private UserService userService = new UserService();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 从前端页面获取id
int id = Integer.parseInt(request.getParameter("id"));
// 根据id调用 userService.getUserById方法删除用户
userService.deleteUser(id);
// 跳转到UserServlet
response.sendRedirect("UserServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request,response);
}
}
@WebServlet("/jsp/EditUserServlet")
public class EditUserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private UserService userService = new UserService();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
// 从前端页面获取id,username,password
int id = Integer.parseInt(request.getParameter("id"));
String username = request.getParameter("username");
String password = request.getParameter("password");
User user = new User(id,username, password);
// 设置用户
userService.updateUser(user);
System.out.println(user.toString());
// 跳转到UserServlet
response.sendRedirect("UserServlet");
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
// 从前端页面获取id
int id = Integer.parseInt(request.getParameter("id"));
// 根据id调用 userService.getUserById方法查询用户
User user = userService.getUserById(id);
request.setAttribute("user",user);
System.out.println(user.toString());
// 跳转到/jsp/edit_user.jsp
request.getRequestDispatcher("/jsp/edit_user.jsp").forward(request, response);
}
}
@WebServlet("/jsp/UserServlet")
public class UserServlet extends HttpServlet {
private UserService userService = new UserService();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<User> users = userService.showUsers();
request.getSession().setAttribute("users",users);
request.getRequestDispatcher("/jsp/user.jsp").forward(request,response);
}
}
前端页面
<%@ page contentType="text/html;charset=UTF-8" language="java"%>
<html>
<head>
<title>添加用户</title>
</head>
<body>
<h3>添加用户</h3>
<form action="AddUserServlet" method="post">
<table>
<tr>
<td>用户名:</td>
<td><input type="text" name="username"></td>
</tr>
<tr>
<td>密码:</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td colspan="2" align="right">
<input type="reset" value="重置">
<input type="submit" value="提交">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false"%>
<html>
<head>
<title>修改用户</title>
</head>
<body>
<h3>修改用户</h3>
<form action="EditUserServlet" method="post">
<table>
<input type="hidden" name="id" value="${user.id}">
<tr>
<td>用户名:</td>
<td><input type="text" name="username" value="${user.username}"></td>
</tr>
<tr>
<td>密码:</td>
<td><input type="password" name="password" value="${user.password}"></td>
</tr>
<tr>
<td colspan="2" align="right">
<input type="reset" value="重置">
<input type="submit" value="提交">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="false" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<title>用户列表</title>
</head>
<body>
<table border="1" cellpadding="15" cellspacing="0">
<tr>
<th>用户id</th>
<th>用户名</th>
<th>密码</th>
<td>操作 | <button onclick="window.location.href='add_user.jsp'">新增</button></td>
</tr>
<c:forEach items="${sessionScope.users}" var="user">
<tr>
<td>${user.id}</td>
<td>${user.username}</td>
<td>${user.password}</td>
<td>
<a href="${pageContext.request.contextPath}/jsp/EditUserServlet?id=${user.id}">修改</a>
|
<a href="${pageContext.request.contextPath}/jsp/DeleteUserServlet?id=${user.id}">删除</a>
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
经过不断地修改,最终系统还是达到预期的效果,其中有一些坎坷还是有必要说明一下。其它的功能都没有什么问题,但是在修改用户数据的时候,出现了一个逻辑错误。
就是不知道在修改用户数据的时候,数据回显和修改用户如何做,最后通过慢慢尝试,最后还是做出来了。
写在最后:路漫漫其修远兮,吾将上下而求索。