需求：实现增加新部门的功能，对应数据库表示Oracle的dept表
一、Java MVC 增
实现：
1、视图层（V）：注册部门 deptAdd.jsp
在注册新部门页面只需输入“部门名称”和“城市”两个字段。部门编号deptno的逻辑是保存新的部门信息的时候在原有的部门编号的基础上去最大值加10，返回值是最新的部门编号。
2、控制层（C）：DeptController.java
deptAdd.jsp中hidden callTp 为 deptAdd，在控制端接收后处理
处理逻辑是先调用service层，获取新的deptno，然后组成完成的DeptBean后重新调用service
else if (callTp.equals("deptAdd")) { String dname = request.getParameter("dnameTxt"); String loc = request.getParameter("locTxt"); int deptno = ds.getNextDetpno(); DeptBean dept = new DeptBean(); dept.setDeptno(deptno); dept.setDname(dname); dept.setLoc(loc); int addInt = ds.deptAdd(dept); if (addInt == 1) { request.setAttribute("addResultMsg", "添加成功！"); } else { request.setAttribute("addResultMsg", "添加失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); }
3、模型层（M）：DeptBean.java、DeptService.java
获取下一个部门编号（已有部门最大编号+10）
// 获取下一个deptno public int getNextDetpno() { int nextDeptno = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT MAX(DEPTNO) + 10 AS DEPTNO \n"); sqlBf.append("FROM DEPT \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; rs = pstmt.executeQuery(); if (rs.next()) { nextDeptno = rs.getInt("DEPTNO"); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return nextDeptno; }
保存DeptBean
// 增加一条dept数据 public int deptAdd(DeptBean dept) { int insertInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("INSERT INTO DEPT(DEPTNO, DNAME, LOC) \n"); sqlBf.append(" VALUES(? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ?) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, dept.getDeptno()); pstmt.setString(idx++, dept.getDname()); pstmt.setString(idx++, dept.getLoc()); insertInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return insertInt; }
二、Java MVC 删
1、视图层（V）：注册部门 deptList.jsp
调用控制器时callTp=deptDelete和传输要删除的deptno。
<td><a href="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td>
2、控制层（C）：DeptController.java
else if (callTp.equals("deptDelete")) { int deleteInt = ds.deptDelete(Integer.parseInt(request.getParameter("deptno"))); if (deleteInt == 1) { request.setAttribute("deleteResultMsg", "删除成功！"); } else { request.setAttribute("deleteResultMsg", "删除失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); }
3、模型层（M）：DeptBean.java、DeptService.java
// 删除部门一条记录 public int deptDelete(int deptno) { int deleteResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("DELETE FROM DEPT \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, deptno); deleteResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return deleteResulInt; }
三、Java MVC 改
1、视图层（V）：注册部门 deptList.jsp、deptUpdate.jsp
调用控制器时callTp=deptUpdate和传输要删除的deptno。
<td><a href="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td>
2、控制层（C）：DeptController.java
调用控制器时callTp=deptUpdate和传输要删除的deptno，利用deptno重新查询dept数据，然后跳转到deptUpdate.jsp页面。
在deptUpdate.jsp页面中修改“部门名称”和“城市”字段后点击保存，往控制器传输callTp=deptSave。
else if (callTp.equals("deptUpdate")) { deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno"))); deptBean = ds.deptById(deptBean.getDeptno()); request.setAttribute("deptBean", deptBean); request.getRequestDispatcher("/view/deptUpdate.jsp").forward(request, response); } else if (callTp.equals("deptSave")) { deptBean.setDname(request.getParameter("dnameTxt")); deptBean.setLoc(request.getParameter("locTxt")); deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno"))); int updateInt = ds.deptSave(deptBean); if (updateInt == 1) { request.setAttribute("updateResultMsg", "更新成功！"); } else { request.setAttribute("updateResultMsg", "更新失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); }
3、模型层（M）：DeptBean.java、DeptService.java
利用deptno查找dept数据，返回给deptUpdate.jsp页面。
// 利用deptno查询单条部门信息 public DeptBean deptById(int deptno) { DeptBean dept = new DeptBean(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e) { e.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT DEPTNO \n"); sqlBf.append(" , DNAME \n"); sqlBf.append(" , LOC \n"); sqlBf.append("FROM DEPT \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, deptno); rs = pstmt.executeQuery(); if (rs.next()) { dept.setDeptno(rs.getInt("DEPTNO")); dept.setDname(rs.getString("DNAME")); dept.setLoc(rs.getString("LOC")); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return dept; }
部门信息修改后进行保存。
// 更新dept信息 public int deptSave(DeptBean deptBean) { int updateResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("UPDATE DEPT SET DNAME = ? \n"); sqlBf.append(" , LOC = ? \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, deptBean.getDname()); pstmt.setString(idx++, deptBean.getLoc()); pstmt.setInt(idx++, deptBean.getDeptno()); updateResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return updateResulInt; }
四、Java MVC 查
1、视图层（V）：注册部门 deptList.jsp
调用控制器时传输callTp=deptList和相应条件值
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>部门查询</title><link href="/web01//css/main.css" rel="stylesheet" type="text/css" /></head><body><%@ include file="top.jsp" %><form action="/web01/deptController" method="get">部门名称：<input type="text" name="dnameTxt">城市：<input type="text" name="locTxt"><input type="submit" value="Search"><input type="hidden" name="callTp" value="deptList"><br/><table> <tr> <th>部门编号</th> <th>部门名称</th> <th>地点</th> <th>更新操作</th> <th>删除操作</th> </tr> <c:forEach items="${requestScope.deptBeanList}" var="dept"> <tr> <td><c:out value="${dept.deptno }" default=" "></c:out></td> <td><c:out value="${dept.dname }" default=" "></c:out></td> <td><c:out value="${dept.loc }" default=" "></c:out></td> <td><a href="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td> <td><a href="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td> </tr> </c:forEach></table><em style="color: red"><c:out value="${requestScope.updateResultMsg }"></c:out></em><em style="color: red"><c:out value="${requestScope.deleteResultMsg }"></c:out></em><em style="color: red"><c:out value="${requestScope.addResultMsg }"></c:out></em></form><%@ include file="bottom.jsp" %></body></html>
2、控制层（C）：DeptController.java
if (callTp.equals("deptList")) { deptBean.setDname(request.getParameter("dnameTxt")); deptBean.setLoc(request.getParameter("locTxt")); ArrayList<DeptBean> deptBeanList = ds.deptList(deptBean); request.setAttribute("deptBeanList", deptBeanList); request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); }
3、模型层（M）：DeptBean.java、DeptService.java
// 获取dept list public ArrayList<DeptBean> deptList(DeptBean db){ ArrayList<DeptBean> deptList = new ArrayList<DeptBean>(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e) { e.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT DEPTNO \n"); sqlBf.append(" , DNAME \n"); sqlBf.append(" , LOC \n"); sqlBf.append("FROM DEPT \n"); sqlBf.append("WHERE DNAME LIKE UPPER(?) || '%' \n"); sqlBf.append("AND LOC LIKE UPPER(?) || '%' \n"); sqlBf.append("ORDER BY DEPTNO \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, db.getDname()); pstmt.setString(idx++, db.getLoc()); rs = pstmt.executeQuery(); while (rs.next()) { DeptBean dept = new DeptBean(); dept.setDeptno(rs.getInt("DEPTNO")); dept.setDname(rs.getString("DNAME")); dept.setLoc(rs.getString("LOC")); deptList.add(dept); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return deptList; }
全部代码如下：
主页面：index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>index page</title><link href="/web01//css/main.css" rel="stylesheet" type="text/css" /></head><body><%@ include file="/view/top.jsp" %><ul> <li>员工管理 <ul> <li><a href="/web01/empController">员工查询</a></li> <li>注册员工</li> </ul> </li> <li>部门管理 <ul> <li><a href="/web01/deptController?callTp=deptList">部门查询</a></li> <li><a href="/web01/view/deptAdd.jsp">注册部门</a></li> </ul> </li> <li>系统管理 <ul> <li><a href="/web01/requestInfoController?callTp=requestInfoPageList&now_page_num=1">访问日志查询</a></li> </ul> </li></ul></body></html>
部门查询：deptList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>部门查询</title><link href="/web01//css/main.css" rel="stylesheet" type="text/css" /></head><body><%@ include file="top.jsp" %><form action="/web01/deptController" method="get">部门名称：<input type="text" name="dnameTxt">城市：<input type="text" name="locTxt"><input type="submit" value="Search"><input type="hidden" name="callTp" value="deptList"><br/><table> <tr> <th>部门编号</th> <th>部门名称</th> <th>地点</th> <th>更新操作</th> <th>删除操作</th> </tr> <c:forEach items="${requestScope.deptBeanList}" var="dept"> <tr> <td><c:out value="${dept.deptno }" default=" "></c:out></td> <td><c:out value="${dept.dname }" default=" "></c:out></td> <td><c:out value="${dept.loc }" default=" "></c:out></td> <td><a href="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td> <td><a href="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td> </tr> </c:forEach></table><em style="color: red"><c:out value="${requestScope.updateResultMsg }"></c:out></em><em style="color: red"><c:out value="${requestScope.deleteResultMsg }"></c:out></em><em style="color: red"><c:out value="${requestScope.addResultMsg }"></c:out></em></form><%@ include file="bottom.jsp" %></body></html>
部门更新：deptUpdate.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>部门查询</title><link href="/web01//css/main.css" rel="stylesheet" type="text/css" /></head><body><%@ include file="top.jsp" %><form action="/web01/deptController" method="get">部门编号：<input type="text" disabled="disabled" value="${requestScope.deptBean.deptno}"><br>部门名称：<input type="text" name="dnameTxt" value="${requestScope.deptBean.dname}"><br>城市：<input type="text" name="locTxt" value="${requestScope.deptBean.loc}"><br><input type="submit" value="Save"><input type="hidden" name="callTp" value="deptSave"><input type="hidden" name="deptno" value="${requestScope.deptBean.deptno}""><br/></form><%@ include file="bottom.jsp" %></body></html>
增加部门：deptAdd.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>部门查询</title><link href="/web01//css/main.css" rel="stylesheet" type="text/css" /></head><body><%@ include file="top.jsp" %><form action="/web01/deptController" method="get">部门名称：<input type="text" name="dnameTxt" value="" maxlength="14"><br>城市：<input type="text" name="locTxt" value="" maxlength="13"><br><input type="submit" value="Add"><input type="hidden" name="callTp" value="deptAdd"><br/></form><%@ include file="bottom.jsp" %></body></html>
部门的控制器：DeptController.java
package com.test.biz.controller;import java.io.IOException;import java.util.ArrayList;import javax.servlet.ServletException;import javax.servlet.annotation.WebServlet;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import com.test.biz.bean.DeptBean;import com.test.biz.service.DeptService;import com.test.system.service.RequestInfoService;/** * Servlet implementation class deptController */@WebServlet("/DeptController")public class DeptController extends HttpServlet { private static final long serialVersionUID = 1L; /** * @see HttpServlet#HttpServlet() */ public DeptController() { super(); } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { RequestInfoService ris = new RequestInfoService(); ris.saveRequestInfo(request); DeptService ds = new DeptService(); DeptBean deptBean = new DeptBean(); String callTp = request.getParameter("callTp"); System.out.println("----callTp : "+callTp); if (callTp.equals("deptList")) { deptBean.setDname(request.getParameter("dnameTxt")); deptBean.setLoc(request.getParameter("locTxt")); ArrayList<DeptBean> deptBeanList = ds.deptList(deptBean); request.setAttribute("deptBeanList", deptBeanList); request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); } else if (callTp.equals("deptUpdate")) { deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno"))); deptBean = ds.deptById(deptBean.getDeptno()); request.setAttribute("deptBean", deptBean); request.getRequestDispatcher("/view/deptUpdate.jsp").forward(request, response); } else if (callTp.equals("deptSave")) { deptBean.setDname(request.getParameter("dnameTxt")); deptBean.setLoc(request.getParameter("locTxt")); deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno"))); int updateInt = ds.deptSave(deptBean); if (updateInt == 1) { request.setAttribute("updateResultMsg", "更新成功！"); } else { request.setAttribute("updateResultMsg", "更新失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); } else if (callTp.equals("deptDelete")) { int deleteInt = ds.deptDelete(Integer.parseInt(request.getParameter("deptno"))); if (deleteInt == 1) { request.setAttribute("deleteResultMsg", "删除成功！"); } else { request.setAttribute("deleteResultMsg", "删除失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); } else if (callTp.equals("deptAdd")) { String dname = request.getParameter("dnameTxt"); String loc = request.getParameter("locTxt"); int deptno = ds.getNextDetpno(); DeptBean dept = new DeptBean(); dept.setDeptno(deptno); dept.setDname(dname); dept.setLoc(loc); int addInt = ds.deptAdd(dept); if (addInt == 1) { request.setAttribute("addResultMsg", "添加成功！"); } else { request.setAttribute("addResultMsg", "添加失败！"); } request.getRequestDispatcher("/view/deptList.jsp").forward(request, response); } } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doGet(request, response); }}
部门Bean：DeptBean.java
package com.test.biz.bean;public class DeptBean { private int deptno; private String dname; private String loc; public int getDeptno() { return deptno; } public void setDeptno(int deptno) { this.deptno = deptno; } public String getDname() { return dname; } public void setDname(String dname) { this.dname = dname; } public String getLoc() { return loc; } public void setLoc(String loc) { this.loc = loc; }}
部门的服务层：DeptService.java
package com.test.biz.service;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import com.test.biz.bean.DeptBean;import com.test.common.dao.BaseDao;public class DeptService { private int idx = 1; private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; // 获取dept list public ArrayList<DeptBean> deptList(DeptBean db){ ArrayList<DeptBean> deptList = new ArrayList<DeptBean>(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e) { e.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT DEPTNO \n"); sqlBf.append(" , DNAME \n"); sqlBf.append(" , LOC \n"); sqlBf.append("FROM DEPT \n"); sqlBf.append("WHERE DNAME LIKE UPPER(?) || '%' \n"); sqlBf.append("AND LOC LIKE UPPER(?) || '%' \n"); sqlBf.append("ORDER BY DEPTNO \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, db.getDname()); pstmt.setString(idx++, db.getLoc()); rs = pstmt.executeQuery(); while (rs.next()) { DeptBean dept = new DeptBean(); dept.setDeptno(rs.getInt("DEPTNO")); dept.setDname(rs.getString("DNAME")); dept.setLoc(rs.getString("LOC")); deptList.add(dept); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return deptList; } // 利用deptno查询单条部门信息 public DeptBean deptById(int deptno) { DeptBean dept = new DeptBean(); BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e) { e.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT DEPTNO \n"); sqlBf.append(" , DNAME \n"); sqlBf.append(" , LOC \n"); sqlBf.append("FROM DEPT \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, deptno); rs = pstmt.executeQuery(); if (rs.next()) { dept.setDeptno(rs.getInt("DEPTNO")); dept.setDname(rs.getString("DNAME")); dept.setLoc(rs.getString("LOC")); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return dept; } // 更新dept信息 public int deptSave(DeptBean deptBean) { int updateResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("UPDATE DEPT SET DNAME = ? \n"); sqlBf.append(" , LOC = ? \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setString(idx++, deptBean.getDname()); pstmt.setString(idx++, deptBean.getLoc()); pstmt.setInt(idx++, deptBean.getDeptno()); updateResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return updateResulInt; } // 删除部门一条记录 public int deptDelete(int deptno) { int deleteResulInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("DELETE FROM DEPT \n"); sqlBf.append("WHERE DEPTNO = ? \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, deptno); deleteResulInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(null, pstmt, conn); } return deleteResulInt; } // 获取下一个deptno public int getNextDetpno() { int nextDeptno = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("SELECT MAX(DEPTNO) + 10 AS DEPTNO \n"); sqlBf.append("FROM DEPT \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; rs = pstmt.executeQuery(); if (rs.next()) { nextDeptno = rs.getInt("DEPTNO"); } } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return nextDeptno; } // 增加一条dept数据 public int deptAdd(DeptBean dept) { int insertInt = 0; BaseDao baseDao = new BaseDao(); try { conn = baseDao.dbConnection(); } catch (SQLException e1) { e1.printStackTrace(); } StringBuffer sqlBf = new StringBuffer(); sqlBf.setLength(0); sqlBf.append("INSERT INTO DEPT(DEPTNO, DNAME, LOC) \n"); sqlBf.append(" VALUES(? \n"); sqlBf.append(" , ? \n"); sqlBf.append(" , ?) \n"); try { pstmt = conn.prepareStatement(sqlBf.toString()); idx = 1; pstmt.setInt(idx++, dept.getDeptno()); pstmt.setString(idx++, dept.getDname()); pstmt.setString(idx++, dept.getLoc()); insertInt = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally { baseDao.dbDisconnection(rs, pstmt, conn); } return insertInt; }}
公用BaseDao.java
package com.test.common.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;public class BaseDao { Connection conn = null; public BaseDao() { } public Connection dbConnection() throws SQLException{ try { Context sourceCtx = new InitialContext(); DataSource ds = (DataSource) sourceCtx.lookup("java:comp/env/jdbc/orcl"); conn = ds.getConnection(); System.out.println("----> Connection Success！"); } catch (NamingException e) { e.printStackTrace(); } return conn; } public void dbDisconnection() throws SQLException{ conn.close(); System.out.println("----> Connection End！"); } public void dbDisconnection(ResultSet rs, PreparedStatement pstmt, Connection conn) { if (rs != null) { try { rs.close(); System.out.println("----> ResultSet End！"); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); System.out.println("----> PreparedStatement End！"); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); System.out.println("----> Connection End！"); } catch (SQLException e) { e.printStackTrace(); } } }}