1、首页信息

2、查看操作

3、删除操作

4、修改操作

5、增加操作

6、DBUtil工具类

7、配置文件

路径配置文件

资源配置文件

8、IDEA整体目录结构

9、遇到的问题

已解决

未解决

1、首页信息

使用纯Servlet做一个单表的CRUD操作，这是单表的List页面

通过查询数据库，通过response.getWriter方法得到一个流将前端页面代码和数据都展示在List页面上（浏览器通过解析能响应出相应的页面），由于只使用了纯Servlet，前端代码写在后端程序当中，非常痛苦。

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeptListServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();

        String contextPath = request.getContextPath();

        out.print("");
        out.print("");
        out.print("	");
        out.print("		");
        out.print("		部门列表页面");
        out.print("	");
        out.print("	");

        out.print("");

        out.print("");
        out.print("		部门列表");
        out.print("		");
        out.print("		");
        out.print("			");
        out.print("				");
        out.print("				");
        out.print("				");
        out.print("				");
        out.print("			");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnertion();
            String sql = "select deptno as a,dname,loc from dept";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            int i = 0;
            while(rs.next()){
                String deptno = rs.getString("a");
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                out.print("");
                out.print("	");
                out.print("	");
                out.print("	");

                out.print("	");
                out.print("");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }  finally {
            DBUtil.close(conn,ps,rs);
        }
        out.print("		序号 部门编号 部门名称 操作
"+(++i)+" "+deptno+" "+dname+" ");
                out.print("		删除");
                out.print("		修改");
                out.print("		详情");
                out.print("	");
        out.print("		");
        out.print("		新增部门");
        out.print("	");
        out.print("");
    }
}

序号	部门编号	部门名称	操作
"+(++i)+"	"+deptno+"	"+dname+"	"); out.print(" 删除"); out.print(" 修改"); out.print(" 详情"); out.print("

2、查看操作

这一个动态获取部门信息的操作，前端发送请求后，在URL当中向后端传送了name和value信息，后端拿到这些信息后，通过数据库的查询，将其完整的信息响应到页面当中，在后退这个按钮当中使用了js代码

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeptDetailServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;

        out.print("");
        out.print("");
        out.print("    ");
        out.print("        ");
        out.print("        部门详情");
        out.print("    ");
        out.print("    ");
        out.print("        部门详情");
        out.print("        ");

        String deptno = request.getParameter("deptno");
        try {
            conn = DBUtil.getConnertion();
            String sql = "select dname,loc from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,deptno);
            rs = ps.executeQuery();
            while(rs.next()){
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                out.print("                部门编号："+deptno+" 
");
                out.print("                部门名称："+dname+"
");
                out.print("                部门位置："+loc+"
");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        out.print("");
        out.print("    ");
        out.print("");
    }
}

3、删除操作

这里使用了js代码，让用户确认是否真正删除，点击删除超链接后同样也会将name和value信息传到后端，当中后端通过sql语句完成数据的删除，删除后立即通过资源的转发跳转到了list页面

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeptDelServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String deptno = request.getParameter("deptno");

        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtil.getConnertion();
            conn.setAutoCommit(false);
            String sql = "delete from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,deptno);
            count = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
        if (count == 1) {
            request.getRequestDispatcher("/dept/list").forward(request,response);
        }else{
            request.getRequestDispatcher("/error.html").forward(request,response);
        }
    }
}

4、修改操作

第一步：用户在list页面点击修改超链接后，同样在URL中像后端发送需要修改的部门编号，后端通过getparameter方法拿到这些信息后，查询数据库，连同前端代码，一起响应到网页当中。

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeptEditServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String contextPath = request.getContextPath();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        out.print("");
        out.print("");
        out.print("	");
        out.print("		");
        out.print("		修改部门");
        out.print("	");
        out.print("	");
        out.print("		修改部门");
        out.print("		");
        out.print("		");

        String deptno = request.getParameter("deptno");
        try {
            conn = DBUtil.getConnertion();
            String sql = "select dname,loc from dept where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,deptno);
            rs = ps.executeQuery();
            if (rs.next()) {
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");
                out.print("                部门编号
");
                out.print("                部门名称
");
                out.print("                部门位置
");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }

        out.print("			
");
        out.print("		");
        out.print("	");
        out.print("");
    }
}

第二步：点击修改按钮之后，再通过另一个service完成数据的真正修改操作。最后跳转到list页面

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeptModifyServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        String deptno = request.getParameter("deptno");
        String dname = request.getParameter("dname");
        String loc = request.getParameter("loc");
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = DBUtil.getConnertion();
            String sql = "update dept set dname = ?, loc = ? where deptno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, dname);
            ps.setString(2, loc);
            ps.setString(3, deptno);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, ps, null);
        }

        if (count == 1) {
            // 更新成功
            // 跳转到部门列表页面（部门列表页面是通过Java程序动态生成的，所以还需要再次执行另一个Servlet）
            //request.getRequestDispatcher("/dept/list").forward(request, response);

            response.sendRedirect(request.getContextPath() + "/dept/list");
        }else{
            // 更新失败
            //request.getRequestDispatcher("/error.html").forward(request, response);
            response.sendRedirect(request.getContextPath() + "/error.html");
        }
    }
}

5、增加操作

不知何种问题，无法打开新增部门超链接跳转的页面，404报错

主要实现思想：与修改类似，不再赘述。



	
		
		新增部门
	
	
		新增部门
		
		
			部门编号

			部门名称

			部门位置

package com.bjpowernode.oa.web.action;

import com.bjpowernode.oa.utils.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeptSaveServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        Connection conn =null;
        PreparedStatement ps = null;
        String deptno = request.getParameter("deptno");
        String dename = request.getParameter("dename");
        String loc = request.getParameter("loc");
        int count = 0;
        try {
            conn = DBUtil.getConnertion();
            String sql ="insert into dept(deptname,dename,loc) value(?,?,?)";
            conn.prepareStatement(sql);
            ps.setString(1,deptno);
            ps.setString(2,dename);
            ps.setString(3,loc);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
        if (count ==1) {
            request.getRequestDispatcher("/dept/list").forward(request,response);
        }else{
            request.getRequestDispatcher("/error.html").forward(request,response);
        }
    }
}

6、DBUtil工具类

手写了DBUtil工具类，封装了一部分的jdbc连接数据库操作，大大减少了代码量

package com.bjpowernode.oa.utils;

import java.sql.*;
import java.util.ResourceBundle;

public class DBUtil {
    private static ResourceBundle bundle = ResourceBundle.getBundle("resources.jdbc");
    private static String driver = bundle.getString("driver");
    private static String url = bundle.getString("url");
    private static String user = bundle.getString("user");
    private static String password = bundle.getString("password");
    static {
        //注册驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnertion() throws SQLException {
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    //释放资源
    public static void close(Connection conn, Statement ps, ResultSet rs){
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

7、配置文件

路径配置文件



    
    
        modify
        com.bjpowernode.oa.web.action.DeptModifyServlet
    
    
        modify
        /dept/modify
    

    
    
        List
        com.bjpowernode.oa.web.action.DeptListServlet
    
    
        List
        /dept/list
    

    
    
        Detail
        com.bjpowernode.oa.web.action.DeptDetailServlet
    
    
        Detail
        /dept/detail
    

    
    
        Del
        com.bjpowernode.oa.web.action.DeptDelServlet
    
    
        Del
        /dept/delete
    

    
    
        Edit
        com.bjpowernode.oa.web.action.DeptEditServlet
    
    
        Edit
        /dept/edit
    

    
    
        save
        com.bjpowernode.oa.web.action.DeptSaveServlet
    
    
        save
        /dept/save

资源配置文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
user=root
password=123456

8、IDEA整体目录结构

9、遇到的问题

已解决
配置文件绑定错误

配置文件class类路径错误

配置文件忘记写路径

js当中路径中，变量在字符串中的格式错误

sql语句错误，忘记加条件，导致数据显示错误

post用doPost接收，不要用doGet

未解决
WEB-INF下的lib添加mysql 驱动jar包之后，一定还需要在TomCat的lib下也添加mysql驱动jar包吗，为什么有的不在TomCat的lib下添加mysql驱动jar包也能访问到数据库的数据，有的必须添加jar包才能访问到数据。

404错误，未找出，导致增加操作的页面无法打开

【JavaWeb】Servlet系列——使用纯Servlet做一个单表的CRUD操作（实际操作实现篇）

7、配置文件

资源配置文件
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/bjpowernode user=root password=123456

8、IDEA整体目录结构

9、遇到的问题

已解决
配置文件绑定错误

配置文件class类路径错误

配置文件忘记写路径

js当中路径中，变量在字符串中的格式错误

sql语句错误，忘记加条件，导致数据显示错误

post用doPost接收，不要用doGet

Java相关栏目本月热门文章

【JavaWeb】Servlet系列——使用纯Servlet做一个单表的CRUD操作（实际操作实现篇）

7、配置文件

资源配置文件 driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/bjpowernode user=root password=123456

8、IDEA整体目录结构

9、遇到的问题

已解决 配置文件绑定错误 配置文件class类路径错误 配置文件忘记写路径 js当中路径中，变量在字符串中的格式错误 sql语句错误，忘记加条件，导致数据显示错误 post用doPost接收，不要用doGet

Java相关栏目本月热门文章

资源配置文件
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/bjpowernode user=root password=123456

已解决
配置文件绑定错误

配置文件class类路径错误

配置文件忘记写路径

js当中路径中，变量在字符串中的格式错误

sql语句错误，忘记加条件，导致数据显示错误

post用doPost接收，不要用doGet