idea写一个Servlet以实现增删改查 Emp表，连接oracle数据库

好的，我可以帮您实现一个Servlet来实现Emp表的增删改查操作并连接到Oracle数据库。

首先，您需要在您的项目中添加Oracle数据库的JDBC驱动程序，并将其添加到Web应用程序的类路径中。您可以在以下链接中下载Oracle的JDBC驱动程序：https://www.oracle.com/database/technologies/jdbcdriver-ucp-downloads.html

接下来，您需要创建一个Emp类，该类包含Emp表中的所有字段：

public class Emp {
    private int empno;
    private String ename;
    private String job;
    private int mgr;
    private Date hiredate;
    private double sal;
    private double comm;
    private int deptno;

    // getters and setters
}

然后，您需要创建一个EmpDAO类，该类包含所有与Emp表相关的数据库操作方法：

public class EmpDAO {
    private Connection conn;

    public EmpDAO(Connection conn) {
        this.conn = conn;
    }

    public List<Emp> getAllEmps() throws SQLException {
        List<Emp> emps = new ArrayList<>();
        String sql = "SELECT * FROM emp";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Emp emp = new Emp();
                    emp.setEmpno(rs.getInt("empno"));
                    emp.setEname(rs.getString("ename"));
                    emp.setJob(rs.getString("job"));
                    emp.setMgr(rs.getInt("mgr"));
                    emp.setHiredate(rs.getDate("hiredate"));
                    emp.setSal(rs.getDouble("sal"));
                    emp.setComm(rs.getDouble("comm"));
                    emp.setDeptno(rs.getInt("deptno"));
                    emps.add(emp);
                }
            }
        }
        return emps;
    }

    public Emp getEmpById(int empno) throws SQLException {
        String sql = "SELECT * FROM emp WHERE empno = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, empno);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    Emp emp = new Emp();
                    emp.setEmpno(rs.getInt("empno"));
                    emp.setEname(rs.getString("ename"));
                    emp.setJob(rs.getString("job"));
                    emp.setMgr(rs.getInt("mgr"));
                    emp.setHiredate(rs.getDate("hiredate"));
                    emp.setSal(rs.getDouble("sal"));
                    emp.setComm(rs.getDouble("comm"));
                    emp.setDeptno(rs.getInt("deptno"));
                    return emp;
                } else {
                    return null;
                }
            }
        }
    }

    public void addEmp(Emp emp) throws SQLException {
        String sql = "INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, emp.getEmpno());
            stmt.setString(2, emp.getEname());
            stmt.setString(3, emp.getJob());
            stmt.setInt(4, emp.getMgr());
            stmt.setDate(5, emp.getHiredate());
            stmt.setDouble(6, emp.getSal());
            stmt.setDouble(7, emp.getComm());
            stmt.setInt(8, emp.getDeptno());
            stmt.executeUpdate();
        }
    }

    public void updateEmp(Emp emp) throws SQLException {
        String sql = "UPDATE emp SET ename = ?, job = ?, mgr = ?, hiredate = ?, sal = ?, comm = ?, deptno = ? WHERE empno = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, emp.getEname());
            stmt.setString(2, emp.getJob());
            stmt.setInt(3, emp.getMgr());
            stmt.setDate(4, emp.getHiredate());
            stmt.setDouble(5, emp.getSal());
            stmt.setDouble(6, emp.getComm());
            stmt.setInt(7, emp.getDeptno());
            stmt.setInt(8, emp.getEmpno());
            stmt.executeUpdate();
        }
    }

    public void deleteEmp(int empno) throws SQLException {
        String sql = "DELETE FROM emp WHERE empno = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, empno);
            stmt.executeUpdate();
        }
    }
}

最后，您需要创建一个EmpServlet类，该类处理所有与客户端的HTTP请求，并调用EmpDAO类中的方法来执行数据库操作：

public class EmpServlet extends HttpServlet {
    private EmpDAO dao;

    @Override
    public void init() throws ServletException {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
            String username = "your_username";
            String password = "your_password";
            Connection conn = DriverManager.getConnection(url, username, password);
            dao = new EmpDAO(conn);
        } catch (ClassNotFoundException | SQLException e) {
            throw new ServletException(e);
        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        if (action == null) {
            List<Emp> emps = dao.getAllEmps();
            request.setAttribute("emps", emps);
            request.getRequestDispatcher("/WEB-INF/views/empList.jsp").forward(request, response);
        } else if (action.equals("add")) {
            request.getRequestDispatcher("/WEB-INF/views/addEmp.jsp").forward(request, response);
        } else if (action.equals("edit")) {
            int empno = Integer.parseInt(request.getParameter("empno"));
            Emp emp = dao.getEmpById(empno);
            request.setAttribute("emp", emp);
            request.getRequestDispatcher("/WEB-INF/views/editEmp.jsp").forward(request, response);
        } else if (action.equals("delete")) {
            int empno = Integer.parseInt(request.getParameter("empno"));
            dao.deleteEmp(empno);
            response.sendRedirect(request.getContextPath() + "/emp");
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        if (action.equals("add")) {
            Emp emp = new Emp();
            emp.setEmpno(Integer.parseInt(request.getParameter("empno")));
            emp.setEname(request.getParameter("ename"));
            emp.setJob(request.getParameter("job"));
            emp.setMgr(Integer.parseInt(request.getParameter("mgr")));
            emp.setHiredate(Date.valueOf(request.getParameter("hiredate")));
            emp.setSal(Double.parseDouble(request.getParameter("sal")));
            emp.setComm(Double.parseDouble(request.getParameter("comm")));
            emp.setDeptno(Integer.parseInt(request.getParameter("deptno")));
            dao.addEmp(emp);
            response.sendRedirect(request.getContextPath() + "/emp");
        } else if (action.equals("edit")) {
            Emp emp = new Emp();
            emp.setEmpno(Integer.parseInt(request.getParameter("empno")));
            emp.setEname(request.getParameter("ename"));
            emp.setJob(request.getParameter("job"));
            emp.setMgr(Integer.parseInt(request.getParameter("mgr")));
            emp.setHiredate(Date.valueOf(request.getParameter("hiredate")));
            emp.setSal(Double.parseDouble(request.getParameter("sal")));
            emp.setComm(Double.parseDouble(request.getParameter("comm")));
            emp.setDeptno(Integer.parseInt(request.getParameter("deptno")));
            dao.updateEmp(emp);
            response.sendRedirect(request.getContextPath() + "/emp");
        }
    }
}

在这个例子中，EmpServlet类处理HTTP GET和POST请求。当收到HTTP GET请求时，它会根据参数“action”的值来调用不同的方法。如果“action”是null，则它将获取Emp表中的所有记录并将它们发送到JSP视图进行呈现。如果“action”是“add”，则它将发送一个包含Emp表中所有字段的表单，以便用户添加新记录。如果“action”是“edit”，则它将发送一个包含Emp表中所有字段的表单，以便用户编辑现有记录。如果“action”是“delete”，则它将从Emp表中删除指定的记录。当收到HTTP POST请求时，它将根据参数“action”的值来调用不同的方法。如果“action”是“add”，则它将从表单中获取所有字段的值并将它们插入到Emp表中。如果“action”是“edit”，则它将从表单中获取所有字段的值并将它们更新到Emp表中。

最后，您需要编写JSP视图来呈现Emp表中的数据和表单。您可以创建一个名为empList.jsp的JSP页面来呈现Emp表中的所有记录，一个名为addEmp.jsp的JSP页面来添加新记录，以及一个名为editEmp.jsp的JSP页面来编辑现有记录。这些JSP页面应该使用JSTL和EL表达式来呈现数据和动态生成HTML。