JavaWeb——操作MySQL数据库实现增删改查操作

置顶 nine06

已于 2023-12-20 15:19:05 修改

阅读量4.9k

点赞数 32

文章标签：数据库 mysql

于 2023-10-10 11:02:21 首次发布

本文链接：https://blog.csdn.net/nine06/article/details/133676214

版权

接着完善此网页
https://blog.csdn.net/nine06/article/details/133355867?spm=1001.2014.3001.5501https://blog.csdn.net/nine06/article/details/133355867?spm=1001.2014.3001.5501

实现增删改查功能

1、查询

1.1 查询所有用户

1.1.1、Dao层

    /**
     * 查询所有用户
     */
    public List<User> findUserAll(){
        Connection connection = DBUtils.getConnection();
        String sql = "select id,username,password,gender,hobby,address,remark from user";
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList<User> users = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setGender(rs.getString(4));
                user.setHobby(rs.getString(5));
                user.setAddress(rs.getString(6));
                user.setRemark(rs.getString(7));

                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBUtils.closeDB(connection,ps,rs);
        return users;
    }

1.1.2、Service层

     /**
      *查询所有用户
     **/
    public List<User> showAllUser(){
        List<User> users = new UserDao().findUserAll();
        return users;
    }

1.1.3、Controller层

        /**
         * 查询所有用户信息
         */
        if ("showAllUser".equals(userAction)){
            List<User> users = new UserService().showAllUser();
            if (users.isEmpty()){
                req.setAttribute("infor","未查询到相关用户信息");
                req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
            }else{
                req.setAttribute("users",users);
                req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
            }
        }

1.1.4、效果

查询到的所有用户

1.2 通过ID查询用户

1.2.1、Dao层

    /**
     * 通过id查询用户
     */
        public User findUserByID(Integer id){
            Connection connection = DBUtils.getConnection();
            String sql = "select id,username,password,gender,hobby,address,remark from user WHERE id = ?";
            PreparedStatement ps = null;
            ResultSet rs = null;
            User user = null;
            try {
                ps = connection.prepareStatement(sql);
                ps.setInt(1,id);
                rs = ps.executeQuery();
                while (rs.next()){
                    user = new User();
                    user.setId(rs.getInt(1));
                    user.setUsername(rs.getString(2));
                    user.setPassword(rs.getString(3));
                    user.setGender(rs.getString(4));
                    user.setHobby(rs.getString(5));
                    user.setAddress(rs.getString(6));
                    user.setRemark(rs.getString(7));


                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtils.closeDB(connection,ps,rs);
            return user;
        }

1.2.2、Service层

    /**
     * 通过ID查找用户(数据回显)
     */
    public User showUserBack(Integer id){
        return new UserDao().findUserByID(id);

    }

1.2.3、Controller层

        /**
         * 回显用户信息
         */
            if ("showUserBack".equals(userAction)){
                String id = req.getParameter("id");
                //回显
                UserService userService = new UserService();
                User user = userService.showUserBack(Integer.parseInt(id));
                req.setAttribute("user",user);
                req.getRequestDispatcher("showuserback.jsp").forward(req,resp);
            }

1.2.4、效果

这里的通过id查询用户主要用在添加用户时让数据回显没办法直观看到效果，所以是在IDEA控制台输出的效果。

2、添加

2.1 添加用户

2.1.1、Dao层

    /**
     * 添加用户
     */
    public int addUser(User user){
         //连接数据库
        Connection connection = DBUtils.getConnection();
        String sql = "INSERT INTO USER(username,password,gender,hobby,address,remark) VALUE (?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
             ps = connection.prepareStatement(sql);
             ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getGender());
            ps.setString(4,user.getHobby());
            ps.setString(5,user.getAddress());
            ps.setString(6,user.getRemark());
            int i = ps.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBUtils.closeDB(connection,ps,null);
        return 0;
    }

2.1.2、Service层

/**
 * 添加用户
 **/ 
public int register(User user){
        UserDao userDao = new UserDao();
        boolean flag = userDao.findUserByName(user.getUsername());
        int state = 0;
        if (flag){
            state =  2;
        }else {
            int i = userDao.addUser(user);
            if (i > 0){
                state =  1;
            }else {
                state =  0;
            }
        }

    return state;
    }

2.1.3、Controller层

   /**
    * 添加用户
    **/
        if ("register".equals(userAction)){
            User user = new User();
            try {
                BeanUtils.populate(user,req.getParameterMap());
                user.setHobby(ArrayToString.arraytostring(req.getParameterValues("hobby")));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
            //验证数据
            Map<String, String> error = ValidateDemo.validateUser(user);
            if (error.isEmpty()){
                UserService userService = new UserService();
                int register = userService.register(user);
                if (register == 0){
                    resp.sendRedirect("/servlet/registerfail.jsp");
                }
                if (register == 1){
                    resp.sendRedirect("/servlet/refresh.jsp");
                }
                if (register == 2){
                    resp.sendRedirect("/servlet/register.jsp?registerfail="+URLEncoder.encode("该用户已存在!","utf-8"));
                }
            }else{
                req.setAttribute("error",error);
                req.getRequestDispatcher("register.jsp").forward(req,resp);
            }
        }

2.1.4、效果

3、修改

3.1 修改用户

3.1.1、Dao层

    /**
     * 修改用户信息
     */
        public int updateUser(User user){
            Connection connection = null;
            String sql = null;
            PreparedStatement ps = null;
            int i = 0;
            try {
                connection = DBUtils.getConnection();
                sql = "UPDATE USER SET username=?,password=?,gender=?,hobby=?,address=?,remark=? where id = ?";
                ps = connection.prepareStatement(sql);
                ps.setString(1,user.getUsername());
                ps.setString(2,user.getPassword());
                ps.setString(3,user.getGender());
                ps.setString(4,user.getHobby());
                ps.setString(5,user.getAddress());
                ps.setString(6,user.getRemark());
                ps.setInt(7,user.getId());

                i = ps.executeUpdate();

            } catch (SQLException e) {
                e.printStackTrace();
            }
            DBUtils.closeDB(connection,ps,null);
            return i;
        }

3.1.2、Service层

    /**
     * 用户修改
     */
    public boolean modifyUser(User user){
        int i = new UserDao().updateUser(user);
        if (i > 0){
            return true;
        }else {
            return false;
        }
    }

3.1.3、Controller层

        /**
         * 修改用户信息
         */
        if ("modifyUser".equals(userAction)){
            User user = new User();
            try {
                BeanUtils.populate(user,req.getParameterMap());
                boolean flag = new UserService().modifyUser(user);
                if (flag){
                    List<User> users = new UserService().showAllUser();
                    if (users.isEmpty()){
                        req.setAttribute("infor","未查询到相关用户信息");
                        req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
                    }else{
                        req.setAttribute("users",users);
                        req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
                    }
                }else {
                    resp.getWriter().print("修改失败");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }

3.1.4、效果

修改了地址

4、删除

4.1 删除用户

4.1.1、Dao层

    /**
     * 删除用户信息
     */
    public int deleteUserByID(Integer id){
        Connection connection = DBUtils.getConnection();
        String sql = "DELETE FROM USER WHERE id = ?";
        PreparedStatement ps = null;
        int i = 0;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBUtils.closeDB(connection,ps,null);
        return i;
    }

4.1.2、Service层

    /**
     * 删除用户
     */
        public boolean deleteUser(Integer id){
            int i = new UserDao().deleteUserByID(id);
            if (i > 0){
                return true;
            }else {
                return false;
            }
        }

4.1.3、Controller

        /**
         * 删除用户
         */
        if ("deleteUser".equals(userAction)){
            String id = req.getParameter("id");
            UserService userService = new UserService();
            boolean flag = userService.deleteUser(Integer.parseInt(id));
            if (flag){
                List<User> users = new UserService().showAllUser();
                if (users.isEmpty()){
                    req.setAttribute("infor","未查询到相关用户信息");
                    req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
                }else{
                    req.setAttribute("users",users);
                    req.getRequestDispatcher("showalluser.jsp").forward(req,resp);
                }
            }else {
                resp.getWriter().print("删除失败");
            }
        }