这里是学习了Servlet后结合数据库进行增删查改–登录等作为练手项目非常适合

准备工作：

1.数据准备

这张表是用户表，用于登录

CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(25) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这张表是具体的用户数据

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(25) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `gender` varchar(3) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `code` varchar(25) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据根据需要自行插入

2. 数据库连接

这里是properties文件，需要在resource文件夹下

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/db_test3?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username = root
password = 123456
initialSize=5
minIdle=5
maxActive=10
maxWait=3000

德鲁伊数据库连接

public class DruidUtils {
 private static Properties properties = new Properties();
 private static DruidDataSource dataSource = null;

 static {
     try {
         properties.load(DruidUtils.class.getResourceAsStream("/db.properties"));
          dataSource  = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
          dataSource.setDriverClassName(properties.getProperty("driver"));
          dataSource.setUrl(properties.getProperty("url"));
          dataSource.setUsername(properties.getProperty("username"));
          dataSource.setPassword(properties.getProperty("password"));
     } catch (IOException e) {
         e.printStackTrace();
     } catch (Exception e) {
         e.printStackTrace();
     }
 }

 public static Connection getConn() {
     try {
         return dataSource.getConnection();
     } catch (SQLException e) {
         e.printStackTrace();
     }
     return null;
 }
//这里是为第三方的DButil建立数据源
 public static DataSource getdataSource(){
     return dataSource;
 }
}

3.登录

userDao

public interface UserDao {

 /**
  * 登录
  * @param aname 账号
  * @param apass 密码
  * @return
  */
 User login(String aname, String apass);
}

Dao层实现类

public class UserDaoImpl implements UserDao {
    private static QueryRunner queryRunner = new QueryRunner(DruidUtils.getdataSource());
    //登录
    @Override
    public User login(String aname, String apass) {
        String sql = "select * from admin_user where aname = ? and apass = ?";
        try {
           return queryRunner.query(sql,new BeanHandler<User>(User.class),aname,apass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

Service业务层

public interface UserService {

    User login(String aname, String apass);
}

Service实现类

public class UserServiceImpl implements UserService {
    private static UserDao userDao = new UserDaoImpl();

    @Override
    public User login(String aname, String apass) {
        User user = userDao.login(aname, apass);
        return user;
    }
}

Controller层

package com.fjp.usersys.controller;

import com.fjp.usersys.pojo.User;
import com.fjp.usersys.service.UserService;
import com.fjp.usersys.service.impl.UserServiceImpl;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet(name = "UserServlet", value = "/UserServlet")
public class UserServlet extends HttpServlet {
    private static UserService userService = new UserServiceImpl();
    private static UsersServlet usersServlet = new UsersServlet();
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        login(request, response);
        response.sendRedirect("UsersServlet");
    }
    protected void login(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");

        String uname = request.getParameter("uname");
        String pass = request.getParameter("pass");

        User user = userService.login(uname, pass);
        request.setAttribute("user", user);
        // 跳转到用户列表页面
        //request.getRequestDispatcher("UsersServlet").forward(request,response);

        //response.getWriter().println("登录成功");
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

4.用户信息的增删查改

Dao层及实现类

public interface UsersDao {

    /**
     * 查找全部用户信息
     * @return
     */
    List<Users> findAll();

    /**
     * 通过id查找
     * @param id
     * @return
     */
    Users findById(int id);

    /**
     * 更新
     * @param users
     * @return
     */
    int updateUsers(Users users);

    int deleteUsers(int id);

    int addUser(Users users);
}

public class UsersDaoImpl implements UsersDao {
    private static QueryRunner queryRunner = new QueryRunner(DruidUtils.getdataSource());
    @Override
    public List<Users> findAll() {
        String sql = "select * from user";
        try {
           return queryRunner.query(sql,new BeanListHandler<Users>(Users.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Users findById(int id) {
        String sql = "select * from user where id = ?";
        try {
           return queryRunner.query(sql,new BeanHandler<Users>(Users.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateUsers(Users users) {
        String sql = "update user set username =? , name = ?, gender = ?, state =?, code =? , email =? where id =?";
        try {
          return queryRunner.update(sql ,users.getUsername(),users.getName(),users.getGender(),users.getState(),users.getCode(),users.getEmail(),users.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteUsers(int id) {
        String sql = "delete from user where id =?";
        try {
           return queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int addUser(Users users) {
        String sql = "insert into user values(?,?,?,?,?,?,?)";
        try {
           return queryRunner.update(sql, users.getId(),users.getUsername(),users.getName(),users.getGender(),users.getState(),users.getCode(),users.getEmail());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}

Service层及实现类

public interface UsersService {

    /**
     * 查找全部
     * @return
     */
    List<Users> findAll();

    /**
     * id查找
     * @param id
     * @return
     */
    Users findById(int id);
    /**
     * 更新
     * @param users
     * @return
     */
    boolean updateUsers(Users users);

    boolean deleteUsers(int id);

    boolean addUsers(Users users);

}

public class UsersServiceImpl implements UsersService {
    private static UsersDao usersDao = new UsersDaoImpl();
    @Override
    public List<Users> findAll() {
        List<Users> users = usersDao.findAll();
        return users;
    }

    @Override
    public Users findById(int id) {
        Users users = usersDao.findById(id);
        return users;
    }

    @Override
    public boolean updateUsers(Users users) {
        int i = usersDao.updateUsers(users);
        return i>0;
    }

    @Override
    public boolean deleteUsers(int id) {
        int i = usersDao.deleteUsers(id);
        return i>0;
    }

    @Override
    public boolean addUsers(Users users) {
        int i = usersDao.addUser(users);
        return i>0;
    }
}

Controller层

@WebServlet(name = "UsersServlet", value = "/UsersServlet")
public class UsersServlet extends HttpServlet {
    private static UsersService usersService = new UsersServiceImpl();
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=UTF-8");
        String action = request.getParameter("action");
        if (action == null) {
            findAll(request, response);
        }else if ("findById".equals(action)) {
            findById(request, response);
        }else if("updateUsers".equals(action)){
            updateUsers(request, response);
        }else if ("delete".equals(action)){
            deleteUsers(request, response);
            findAll(request, response);
        }else if ("add".equals(action)) {
            add(request, response);
        }else if ("addUsers".equals(action)){
            addUsers(request, response);
        }

    }

    protected void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        List<Users> users = usersService.findAll();
        PrintWriter writer = response.getWriter();
        writer.print("<!DOCTYPE html>\n" +
                "<html lang=\"en\">\n" +
                "<head>\n" +
                "    <meta charset=\"UTF-8\">\n" +
                "    <title>用户管理</title>\n" +
                "</head>\n" +
                "<body>\n" +"<table align='center' width='85%' border='1'>\n" +
                "    <tr>\n" +
                "        <td>编号</td>\n" +
                "        <td>昵称</td>\n" +
                "        <td>姓名</td>\n" +
                "        <td>性别</td>\n" +
                "        <td>状态</td>\n" +
                "        <td>邀请码</td>\n" +
                "        <td>邮箱</td>\n" +
                "<td>操作&nbsp&nbsp&nbsp<a href='UsersServlet?action=add'>新增</a></td>"+
                "    </tr>");
        for (Users user : users) {
            writer.print(" <tr>\n" +
                    "        <td>"+user.getId()+"</td>\n" +
                    "        <td>"+user.getUsername()+"</td>\n" +
                    "        <td>"+user.getName()+"</td>\n" +
                    "        <td>"+user.getGender()+"</td>\n" +
                    "        <td>"+user.getState()+"</td>\n" +
                    "        <td>"+user.getCode()+"</td>\n" +
                    "        <td>"+user.getEmail()+"</td>\n" +
                    "        <td><a href='UsersServlet?id="+user.getId()+"&action=findById'>修改</a>&nbsp&nbsp&nbsp<a href='UsersServlet?id="+user.getId()+"&action=delete'>删除</a></td>\n" +
                    "    </tr>");
        }
        writer.print("</table>\n" +
                "</body>\n" +
                "</html>");
    }
    protected void updateUsers(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //findById(request, response);
        String id = request.getParameter("id");
        int uid = id == null ? 0: Integer.parseInt(id);
        String username = request.getParameter("username");
        String name = request.getParameter("name");
        String gender = request.getParameter("gender");
        String state = request.getParameter("state");
        String code = request.getParameter("code");
        String email = request.getParameter("email");

        Users users = new Users();
        users.setId(uid);
        users.setUsername(username);
        users.setName(name);
        users.setGender(gender);
        users.setState(state);
        users.setCode(code);
        users.setEmail(email);

        boolean b = usersService.updateUsers(users);
        if (b) {
            response.sendRedirect("UsersServlet");
        }

    }
    protected void findById(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id = request.getParameter("id");
        int uid = id== null?0: Integer.parseInt(id);
        Users users = usersService.findById(uid);
        PrintWriter writer = response.getWriter();
        writer.print("<form method=\"post\" action=\"UsersServlet?action=updateUsers\">\n" +
                "        编号：<input type=\"text\" name=\"id\"readonly=\"readonly\" value=\""+users.getId()+"\">\n" +
                "        昵称：<input type=\"text\" name=\"username\" value=\""+users.getUsername()+"\">\n" +
                "        姓名：<input type=\"text\" name=\"name\" value=\""+users.getName()+"\">\n" +
                "        性别：<input type=\"text\" name=\"gender\" value=\""+users.getGender()+"\">\n" +
                "        状态：<input type=\"text\" name=\"state\" value=\""+users.getState()+"\">\n" +
                "        邀请码：<input type=\"text\" name=\"code\" value=\""+users.getCode()+"\">\n" +
                "        邮箱：<input type=\"text\" name=\"email\" value=\""+users.getEmail()+"\">\n" +
                "        <input type=\"submit\" value=\"修改\">\n" +
                "    </form>");
        //updateUsers(request, response);
        //updateUsers(request, response);
    }


    protected void deleteUsers(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id = request.getParameter("id");
        int uid = id == null ? 0:Integer.parseInt(id);
        boolean b = usersService.deleteUsers(uid);
    }
    protected void addUsers(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id = request.getParameter("id");
       int uid = Integer.parseInt(id);
        String username = request.getParameter("username");
        String name = request.getParameter("name");
        String gender = request.getParameter("gender");
        String state = request.getParameter("state");
        String code = request.getParameter("code");
        String email = request.getParameter("email");
        Users users = new Users();
        users.setId(uid);
        users.setUsername(username);
        users.setName(name);
        users.setGender(gender);
        users.setState(state);
        users.setCode(code);
        users.setEmail(email);
        boolean b = usersService.addUsers(users);
        if (b) {
            response.sendRedirect("UsersServlet");
        }
    }
    protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Users users = new Users();
        PrintWriter writer = response.getWriter();
        writer.print("<form method=\"post\" action=\"UsersServlet?action=addUsers\">\n" +
                "        编号：<input type=\"text\" name=\"id\" value=\""+users.getId()+"\">\n" +
                "        昵称：<input type=\"text\" name=\"username\" value=\""+users.getUsername()+"\">\n" +
                "        姓名：<input type=\"text\" name=\"name\" value=\""+users.getName()+"\">\n" +
                "        性别：<input type=\"text\" name=\"gender\" value=\""+users.getGender()+"\">\n" +
                "        状态：<input type=\"text\" name=\"state\" value=\""+users.getState()+"\">\n" +
                "        邀请码：<input type=\"text\" name=\"code\" value=\""+users.getCode()+"\">\n" +
                "        邮箱：<input type=\"text\" name=\"email\" value=\""+users.getEmail()+"\">\n" +
                "        <input type=\"submit\" value=\"添加\">\n" +
                "    </form>");
    }
        @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request, response);
    }
}

4.前端

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title></title>
</head>
<body>
<form action="UserServlet" method="post">
    用户名：<input type="text" name="uname"><br/>
    密码：<input type="password" name="pass"><br/>
    <input type="submit" value="登录">
</form>
</body>
</html>

需要的jar包

代码结构**