完成基于Servlet的对user表的增删改查

基于Servlet的增删改查

1.开发环境

• IDEA
• JDK1.8
• Tomcat8.5
• Mysql 8.0.12

2.数据库

2.1表创建

 

2.2表数据

 

3.JavaWeb代码

3.1目录结构

 

3.2util包下代码

JdbcUtil完成对数据库的连接和资源释放

JsonResult对返回前端资源的封装

JdbcUtil代码：

/*
数据库连接板帮助类
*/
    public class JdbcUtil {
        private static final String DRIVERNAME="com.mysql.cj.jdbc.Driver";
        private static final String URL="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai";
        private static final String USERNAME="root";
        private static final String PASSWORD="root";
        static {
            try {
//       1.加载驱动类
                Class.forName(DRIVERNAME);
            } catch (ClassNotFoundException e) {
                System.out.println("驱动加载错误："+ e);
            }
        }
        //  2.返回数据库连接对象
        public static Connection getJDBCConnection(){
            Connection connection=null;
            try {
                connection= DriverManager.getConnection(URL,USERNAME,PASSWORD);
            } catch (SQLException e) {
                System.out.println("创建连接对象错误："+ e);
            }
            finally {
                return connection;
            }
        }

        //    3.关闭资源
        public static void closeJDBC(ResultSet resultSet, Statement statement, Connection connection){
            try {
                if (resultSet!=null)
                    resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            finally {
                if (statement!=null) {
                    try {
                        statement.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                    finally {
                        try {
                            if (connection!=null)
                                connection.close();
                        } catch (SQLException throwables) {
                            throwables.printStackTrace();
                        }
                        finally {
                            System.out.println("close success！");
                        }
                    }
                }
            }

        }


    }

JsonResult代码：

public class JsonResult {
    private Integer code;
    private String message;
    private Object data;

    public JsonResult(Integer code, String message, Object data) {
        this.code = code;
        this.message = message;
        this.data = data;
    }

    public JsonResult() {
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public Object getData() {
        return data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return "JsonResult{" +
                "code:" + code.toString() +
                ", message:" + message +
                ", data:" + data+
                '}';
    }
}

3.3entity包下代码

User实体类代码

User代码：

public class User implements Serializable {
private Integer id;
private String name;
private String password;
private String sex;
private Date birthday;

    public User(Integer id, String name, String password, String sex, Date birthday) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.sex = sex;
        this.birthday = birthday;
    }

    public User() {
    }


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id:" + id +
                ", name:" + name +
                ", password:" + password +
                ", sex:" + sex +
                ", birthday:" + birthday +
                '}';
    }
}

3.4dao包下代码

dao层完成对数据库user表的增删改查操作

UserDao定义的是接口

UserDao'Impl定义的是接口的实现类。

注意：基于事务的操作。

UserDao代码：

public interface UserDao {

    public boolean save(User user);
    public boolean update(User user);
    public boolean delete(Integer id);
    public User getUserById(Integer id);
    public List<User> listAll();
}

UserDaoImpl代码：

public class UserDaoImpl implements UserDao {
    @Override
    public boolean save(User user) {
        Connection connection=JdbcUtil.getJDBCConnection();
        String sql="insert  into user(`name`,`password`,`sex`,`birthday`) values(?,?,?,?)";
        PreparedStatement statement=null;
        try {
            statement=connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            statement =connection.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setString(2,user.getPassword());
            statement.setString(3,user.getSex());
            statement.setDate(4,user.getBirthday());
            int line=statement.executeUpdate();
            connection.commit();
            if (line>=1)
                return true;
            else
                return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                return false;
            }
            return false;
        }
        finally {
           try {
               statement.close();
               connection.close();
           }
           catch (SQLException e) {
               e.printStackTrace();
               return false;
           }
        }
    }

    @Override
    public boolean update(User user) {
        Connection connection=JdbcUtil.getJDBCConnection();
        String sql="update   user set `name`=?,`password`=?,`sex`=?,`birthday`=? where `id`=?";
        PreparedStatement statement=null;
        try {
            statement=connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            statement =connection.prepareStatement(sql);
            statement.setString(1,user.getName());
            statement.setString(2,user.getPassword());
            statement.setString(3,user.getSex());
            statement.setDate(4,user.getBirthday());
            statement.setInt(5,user.getId());
            int line=statement.executeUpdate();
            connection.commit();
            if (line>=1)
                return true;
            else
                return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                return false;
            }
            return false;
        }
        finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return false;
            }

        }
    }

    @Override
    public boolean delete(Integer id) {
        Connection connection=JdbcUtil.getJDBCConnection();
        String sql="delete from  user where `id`=?";
        PreparedStatement statement=null;
        try {
            statement=connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            statement.setInt(1,id);
            int line=statement.executeUpdate();
            connection.commit();
            if (line>=1) {
                return true;
            }
            else
                return false;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e) {
                return false;
            }
            return false;
        }
        finally {
            try {
                statement.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                return false;
            }

        }
    }
    @Override
    public User getUserById(Integer id) {
        Connection connection=JdbcUtil.getJDBCConnection();
        String sql="select * from  user where `id`=?";
        User user=new User();
        PreparedStatement statement=null;
        try {
            statement=connection.prepareStatement(sql);
            statement.setInt(1,id);
            ResultSet set=statement.executeQuery();
            if (set.next()){
                user.setId(set.getInt("id"));
                user.setName(set.getString("name"));
                user.setPassword(set.getString("password"));
                user.setSex(set.getString("sex"));
                user.setBirthday(set.getDate("birthday"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            return user;
        }
    }

    public static void main(String[] args) {
        System.out.println(new UserDaoImpl().delete(8));
    }
    @Override
    public List<User> listAll() {
        List<User> list=new ArrayList<User>();
        Connection connection=JdbcUtil.getJDBCConnection();
        String sql="select  * from  user ";
        Statement statement=null;
        try {
            statement=connection.createStatement();
            ResultSet set=statement.executeQuery(sql);
            while (set.next()){
                User user=new User();
                user.setId(set.getInt("id"));
                user.setName(set.getString("name"));
                user.setPassword(set.getString("password"));
                user.setSex(set.getString("sex"));
                user.setBirthday(set.getDate("birthday"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            return list;
        }

    }

}

3.5servic包下代码

UserService以及UserServiceImpl完成对业务逻辑的封装

UserService代码：

public interface UserService {
    public boolean save(User user);
    public boolean update(User user);
    public boolean delete(Integer id);
    public User getUserById(Integer id);
    public List<User> listAll();
}

UserServiceImpl代码：

public class UserServiceImpl implements UserService {
    private UserDao userDao=new UserDaoImpl();
    @Override
    public boolean save(User user) {
        return userDao.save(user);
    }

    @Override
    public boolean update(User user) {
        return userDao.update(user);
    }

    @Override
    public boolean delete(Integer id) {
        return userDao.delete(id);
    }

    @Override
    public User getUserById(Integer id) {
        return userDao.getUserById(id);
    }

    @Override
    public List<User> listAll() {
        return userDao.listAll();
    }
}

3.6controller代码

采用基于Servlet的请求转发

UserController代码：

@WebServlet(name = "userController",urlPatterns = {
        "/save",
        "/update",
        "/delete",
        "/findAll",
        "/getUserById"
})
public class UserController extends HttpServlet {
        private UserService userService=new UserServiceImpl();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String option =req.getParameter("option");
        JsonResult jsonResult=new JsonResult();
        if (option.equals("findAll")){
            jsonResult.setCode(200);
            jsonResult.setMessage("获取成功！");
            jsonResult.setData(userService.listAll());
        }
        else if (option.equals("getUserById")){
            Integer id=Integer.parseInt(req.getParameter("id"));
            jsonResult.setCode(200);
            jsonResult.setMessage("获取成功！");
            jsonResult.setData(userService.getUserById(id));
        }
        else {
            jsonResult.setCode(20000);
            jsonResult.setMessage("获取失败！");
            jsonResult.setData(null);
        }
        resp.getWriter().println(jsonResult.toString());
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String option =req.getParameter("option");
        JsonResult jsonResult=new JsonResult();
        if (option.equals("save")){
            User user=new User();
            user.setName(req.getParameter("name"));
            user.setSex(req.getParameter("sex"));
            user.setPassword(req.getParameter("password"));
           SimpleDateFormat format= new SimpleDateFormat("yyyy-MM-dd");
            if (!req.getParameter("birthday").equals(""))
            {
                try {
                    user.setBirthday(new Date(format.parse(req.getParameter("birthday")).getTime()));
                } catch (ParseException e) {
                    user.setBirthday(null);
                }
            }
            if (userService.save(user)){
                jsonResult.setCode(200);
                jsonResult.setMessage("添加成功！");
                jsonResult.setData(null);
            }
            else {
                jsonResult.setCode(20000);
                jsonResult.setMessage("添加失败！");
                jsonResult.setData(null);
            }
        }
        else {
            jsonResult.setCode(20000);
            jsonResult.setMessage("添加失败！");
            jsonResult.setData(null);
        }
        resp.getWriter().println(jsonResult.toString());
    }

    @Override
    protected void doDelete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String option =req.getParameter("option");
        JsonResult jsonResult=new JsonResult();
        if (option.equals("delete")){
            Integer id=Integer.parseInt(req.getParameter("id"));
            if (userService.delete(id)){
                jsonResult.setCode(200);
                jsonResult.setMessage("删除成功！");
                jsonResult.setData(null);
            }
            else {
                jsonResult.setCode(20000);
                jsonResult.setMessage("删除失败！");
                jsonResult.setData(null);
            }
        }
        else {
            jsonResult.setCode(20000);
            jsonResult.setMessage("删除失败！");
            jsonResult.setData(null);
        }
        resp.getWriter().println(jsonResult.toString());

    }

    @Override
    protected void doPut(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String option =req.getParameter("option");
        JsonResult jsonResult=new JsonResult();
        if (option.equals("update")){
            User user=new User();
            user.setId(Integer.parseInt(req.getParameter("id")));
            user.setName(req.getParameter("name"));
            user.setSex(req.getParameter("sex"));
            user.setPassword(req.getParameter("password"));
            SimpleDateFormat format= new SimpleDateFormat("yyyy-MM-dd");
            if (!req.getParameter("birthday").equals(""))
            {
                try {
                    user.setBirthday(new Date(format.parse(req.getParameter("birthday")).getTime()));
                } catch (ParseException e) {
                    user.setBirthday(null);
                }
            }
            if (userService.update(user)){
                jsonResult.setCode(200);
                jsonResult.setMessage("更新成功！");
                jsonResult.setData(null);
            }
            else {
                jsonResult.setCode(20000);
                jsonResult.setMessage("更新失败！");
                jsonResult.setData(null);
            }
        }
        else {
            jsonResult.setCode(20000);
            jsonResult.setMessage("添加失败！");
            jsonResult.setData(null);
        }
        resp.getWriter().println(jsonResult.toString());
    }
}

4.接口测试

4.1findAll操作

 

4.2getUserById操作

 

4.3save操作

 

4.4update操作

 

4.5delete操作

 

5.乱码问题解决

5.1解决方式1

在所有请求的方法中加入

 req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");

 

5.1解决方式2

需要配置过滤的路径

注意：/*不能写成/**

使用过滤器的方式

@WebFilter("/*")
public class EncodingFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
            servletRequest.setCharacterEncoding("UTF-8");
            servletResponse.setCharacterEncoding("UTF-8");
            filterChain.doFilter(servletRequest,servletResponse);
    }

    @Override
    public void destroy() {

    }
}

 

如果有错误，欢迎大家批评指正！！!