idea写一个Servlet以实现增删改查 Emp表,连接oracle数据库
时间: 2024-05-18 15:18:43 浏览: 133
好的,我可以帮您实现一个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。
相关推荐
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)