需求：实现增加新部门的功能，对应数据库表示Oracle的dept表
一、javaMVC 增
实现：
1、视图层（V）：注册部门 deptAdd.jsp
在注册新部门页面只需输入“部门名称”和“城市”两个字段。部门编号deptno的逻辑是保存新的部门信息的时候在原有的部门编号的基础上去最大值加10，返回值是最新的部门编号。
2、控制层（C）：DeptController.java
deptAdd.jsp中hidden callTp 为 deptAdd，在控制端接收后处理
处理逻辑是先调用service层，获取新的deptno，然后组成完成的DeptBean后重新调用service
elseif(callTp.equals("deptAdd")) {
String dname= request.getParameter("dnameTxt");
String loc= request.getParameter("locTxt");intdeptno =ds.getNextDetpno();
DeptBean dept=newDeptBean();
dept.setDeptno(deptno);
dept.setDname(dname);
dept.setLoc(loc);intaddInt =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）
//获取下一个deptnopublicintgetNextDetpno() {intnextDeptno = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.PRintStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returnnextDeptno;
}
保存DeptBean
//增加一条dept数据publicintdeptAdd(DeptBean dept) {intinsertInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returninsertInt;
}
二、Java MVC 删
1、视图层（V）：注册部门 deptList.jsp
调用控制器时callTp=deptDelete和传输要删除的deptno。
<td><ahref="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td>
2、控制层（C）：DeptController.java
elseif(callTp.equals("deptDelete")) {intdeleteInt = 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
//删除部门一条记录publicintdeptDelete(intdeptno) {intdeleteResulInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returndeleteResulInt;
}
三、Java MVC 改
1、视图层（V）：注册部门 deptList.jsp、deptUpdate.jsp
调用控制器时callTp=deptUpdate和传输要删除的deptno。
<td><ahref="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td>
2、控制层（C）：DeptController.java
调用控制器时callTp=deptUpdate和传输要删除的deptno，利用deptno重新查询dept数据，然后跳转到deptUpdate.jsp页面。
在deptUpdate.jsp页面中修改“部门名称”和“城市”字段后点击保存，往控制器传输callTp=deptSave。
elseif(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);
}elseif(callTp.equals("deptSave")) {
deptBean.setDname(request.getParameter("dnameTxt"));
deptBean.setLoc(request.getParameter("locTxt"));
deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno")));intupdateInt =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查询单条部门信息publicDeptBean deptById(intdeptno) {
DeptBean dept=newDeptBean();
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e) {
e.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returndept;
}
部门信息修改后进行保存。
//更新dept信息publicintdeptSave(DeptBean deptBean) {intupdateResulInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returnupdateResulInt;
}
四、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><metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"><title>部门查询</title><linkhref="/web01//CSS/main.css"rel="stylesheet"type="text/css"/></head><body><%@ include file="top.jsp"%><formaction="/web01/deptController"method="get">部门名称：<inputtype="text"name="dnameTxt">城市：<inputtype="text"name="locTxt"><inputtype="submit"value="Search"><inputtype="hidden"name="callTp"value="deptList"><
br/><table><tr><th>部门编号</th><th>部门名称</th><th>地点</th><th>更新操作</th><th>删除操作</th></tr><c:forEachitems="${requestScope.deptBeanList}"var="dept"><tr><td><c:outvalue="${dept.deptno }"default=" "></c:out></td><td><c:outvalue="${dept.dname }"default=" "></c:out></td><td><c:outvalue="${dept.loc }"default=" "></c:out></td><td><ahref="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td><td><ahref="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td></tr></c:forEach></table><emstyle="color: red"><c:outvalue="${requestScope.updateResultMsg }"></c:out></em><emstyle="color: red"><c:outvalue="${requestScope.deleteResultMsg }"></c:out></em><emstyle="color: red"><c:outvalue="${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 listpublicArrayList<DeptBean>deptList(DeptBean db){
ArrayList<DeptBean> deptList =newArrayList<DeptBean>();
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e) {
e.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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=newDeptBean();
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);
}returndeptList;
}
全部代码如下：
主页面：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><metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"><title>index page</title><linkhref="/web01//css/main.css"rel="stylesheet"type="text/css"/></head><body><%@ include file="/view/top.jsp"%><ul><li>员工管理<ul><li><ahref="/web01/empController">员工查询</a></li><li>注册员工</li></ul></li><li>部门管理<ul><li><ahref="/web01/deptController?callTp=deptList">部门查询</a></li><li><ahref="/web01/view/deptAdd.jsp">注册部门</a></li></ul></li><li>系统管理<ul><li><ahref="/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><metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"><title>部门查询</title><linkhref="/web01//css/main.css"rel="stylesheet"type="text/css"/></head><body><%@ include file="top.jsp"%><formaction="/web01/deptController"method="get">部门名称：<inputtype="text"name="dnameTxt">城市：<inputtype="text"name="locTxt"><inputtype="submit"value="Search"><inputtype="hidden"name="callTp"value="deptList"><
br/><table><tr><th>部门编号</th><th>部门名称</th><th>地点</th><th>更新操作</th><th>删除操作</th></tr><c:forEachitems="${requestScope.deptBeanList}"var="dept"><tr><td><c:outvalue="${dept.deptno }"default=" "></c:out></td><td><c:outvalue="${dept.dname }"default=" "></c:out></td><td><c:outvalue="${dept.loc }"default=" "></c:out></td><td><ahref="/web01/deptController?callTp=deptUpdate&deptno=${dept.deptno }">更改</a></td><td><ahref="/web01/deptController?callTp=deptDelete&deptno=${dept.deptno }">删除</a></td></tr></c:forEach></table><emstyle="color: red"><c:outvalue="${requestScope.updateResultMsg }"></c:out></em><emstyle="color: red"><c:outvalue="${requestScope.deleteResultMsg }"></c:out></em><emstyle="color: red"><c:outvalue="${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><metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"><title>部门查询</title><linkhref="/web01//css/main.css"rel="stylesheet"type="text/css"/></head><body><%@ include file="top.jsp"%><formaction="/web01/deptController"method="get">部门编号：<inputtype="text"disabled="disabled"value="${requestScope.deptBean.deptno}"><
br>部门名称：<inputtype="text"name="dnameTxt"value="${requestScope.deptBean.dname}"><
br>城市：<inputtype="text"name="locTxt"value="${requestScope.deptBean.loc}"><
br><inputtype="submit"value="Save"><inputtype="hidden"name="callTp"value="deptSave"><inputtype="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><metahttp-equiv="Content-Type"content="text/html; charset=UTF-8"><title>部门查询</title><linkhref="/web01//css/main.css"rel="stylesheet"type="text/css"/></head><body><%@ include file="top.jsp"%><formaction="/web01/deptController"method="get">部门名称：<inputtype="text"name="dnameTxt"value=""maxlength="14"><
br>城市：<inputtype="text"name="locTxt"value=""maxlength="13"><
br><inputtype="submit"value="Add"><inputtype="hidden"name="callTp"value="deptAdd"><
br/></form><%@ include file="bottom.jsp"%></body></html>
部门的控制器：DeptController.java
packagecom.test.biz.controller;importjava.io.IOException;importjava.util.ArrayList;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.test.biz.bean.DeptBean;importcom.test.biz.service.DeptService;importcom.test.system.service.RequestInfoService;/*** Servlet implementation class deptController*/@WebServlet("/DeptController")publicclassDeptControllerextendsHttpServlet {privatestaticfinallongserialVersionUID = 1L;/***@seeHttpServlet#HttpServlet()*/publicDeptController() {super();
}/***@seeHttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)*/protectedvoiddoGet(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {
RequestInfoService ris=newRequestInfoService();
ris.saveRequestInfo(request);
DeptService ds=newDeptService();
DeptBean deptBean=newDeptBean();
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);
}elseif(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);
}elseif(callTp.equals("deptSave")) {
deptBean.setDname(request.getParameter("dnameTxt"));
deptBean.setLoc(request.getParameter("locTxt"));
deptBean.setDeptno(Integer.parseInt(request.getParameter("deptno")));intupdateInt =ds.deptSave(deptBean);if(updateInt == 1) {
request.setAttribute("updateResultMsg", "更新成功！");
}else{
request.setAttribute("updateResultMsg", "更新失败！");
}
request.getRequestDispatcher("/view/deptList.jsp").forward(request, response);
}elseif(callTp.equals("deptDelete")) {intdeleteInt = 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);
}elseif(callTp.equals("deptAdd")) {
String dname= request.getParameter("dnameTxt");
String loc= request.getParameter("locTxt");intdeptno =ds.getNextDetpno();
DeptBean dept=newDeptBean();
dept.setDeptno(deptno);
dept.setDname(dname);
dept.setLoc(loc);intaddInt =ds.deptAdd(dept);if(addInt == 1) {
request.setAttribute("addResultMsg", "添加成功！");
}else{
request.setAttribute("addResultMsg", "添加失败！");
}
request.getRequestDispatcher("/view/deptList.jsp").forward(request, response);
}
}/***@seeHttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)*/protectedvoiddoPost(HttpServletRequest request, HttpServletResponse response)throwsServletException, IOException {this.doGet(request, response);
}
}
部门Bean：DeptBean.java
packagecom.test.biz.bean;publicclassDeptBean {privateintdeptno;privateString dname;privateString loc;publicintgetDeptno() {returndeptno;
}publicvoidsetDeptno(intdeptno) {this.deptno =deptno;
}publicString getDname() {returndname;
}publicvoidsetDname(String dname) {this.dname =dname;
}publicString getLoc() {returnloc;
}publicvoidsetLoc(String loc) {this.loc =loc;
}
}
部门的服务层：DeptService.java
packagecom.test.biz.service;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importcom.test.biz.bean.DeptBean;importcom.test.common.dao.BaseDao;publicclassDeptService {privateintidx = 1;privateConnection conn =null;privatePreparedStatement pstmt =null;privateResultSet rs =null;//获取dept listpublicArrayList<DeptBean>deptList(DeptBean db){
ArrayList<DeptBean> deptList =newArrayList<DeptBean>();
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e) {
e.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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=newDeptBean();
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);
}returndeptList;
}//利用deptno查询单条部门信息publicDeptBean deptById(intdeptno) {
DeptBean dept=newDeptBean();
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e) {
e.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returndept;
}//更新dept信息publicintdeptSave(DeptBean deptBean) {intupdateResulInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returnupdateResulInt;
}//删除部门一条记录publicintdeptDelete(intdeptno) {intdeleteResulInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returndeleteResulInt;
}//获取下一个deptnopublicintgetNextDetpno() {intnextDeptno = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returnnextDeptno;
}//增加一条dept数据publicintdeptAdd(DeptBean dept) {intinsertInt = 0;
BaseDao baseDao=newBaseDao();try{
conn=baseDao.dbConnection();
}catch(SQLException e1) {
e1.printStackTrace();
}
StringBuffer sqlBf=newStringBuffer();
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);
}returninsertInt;
}
}
公用BaseDao.java
packagecom.test.common.dao;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjavax.naming.Context;importjavax.naming.InitialContext;importjavax.naming.NamingException;importjavax.sql.DataSource;publicclassBaseDao {
Connection conn=null;publicBaseDao() {
}publicConnection dbConnection()throwsSQLException{try{
Context sourceCtx=newInitialContext();
DataSource ds= (DataSource) sourceCtx.lookup("java:comp/env/jdbc/orcl");
conn=ds.getConnection();
System.out.println("----> Connection Success！");
}catch(NamingException e) {
e.printStackTrace();
}returnconn;
}publicvoiddbDisconnection()throwsSQLException{
conn.close();
System.out.println("----> Connection End！");
}publicvoiddbDisconnection(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();
}
}
}
}