《ACCP7.0S2机试答案.docx》由会员分享，可在线阅读，更多相关《ACCP7.0S2机试答案.docx（16页珍藏版）》请在三一办公上搜索。
1、ACCP7.0S2机试营业网点查询首先针对题目所示创建数据库BranchesMgrUSEBranchesMgrGOCREATETABLEdbo.Branches(idintIDENTITY(1,1)NOTNULL,bnamenvarchar(50)NOTNULL,cityArealdintNOTNULL,addressnvarchar(100)NOTNULL,telephonenvarchar(50)NOTNULL,)ALTERTABLEdbo.BranchesWITHCHECKADDFOREIGNKEY(cityAreald)REFERENCESdbo.CityArea(id)GOCREAT
2、ETABLEdbo.CityArea(idintIDENTITY(1,1)NOTNULL,cnamenvarchar(50)NOTNULL,)其次步：向表中添加至少3条数据表1:BranchesidbnamecityArealdaddresstelephoneD海淀XX路支行1海淀区XX路128876543212海淀XX路支行1海淀区XX路XX.876512343东城支行2东城区XX路12号876524314西城支行3西城区XX路38号87652143表2：CityAreaidCname1 海淀2 7东城3 3西城第三步创建Web项目BranchesMgr第四步：依次创建包名为COm.bran
3、ches,entity,com.branches,dao,com.Branches,biz,com.Branches,action第五步：创建实体类(在COm.branches,entity中)packagecom.branches,entity；publicclassBranchesprivateintid；privateStringname；privateCitycity；privateStringaddress；privateStringtelephone；publicBranches()super()；)publicBranches(intid,Stringname,Citycity,
4、Stringaddress,Stringtelephone)super()；this,id=id；this,name=name；this,city=city；this,address=address；this,telephone=telephone；)publicintgetld()returnid；)publicvoidsetld(intid)this,id=id；)publicStringgetName()returnname；)publicvoidsetName(Stringname)this,name=name；)publicCitygetCity()returncity；)publi
5、cvoidsetCity(Citycity)this,city=city；)publicStringgetAddress()returnaddress；)publicvoidsetAddress(Stringaddress)this,address=address；)publicStringgetTelephone()returntelephone；)publicvoidSetTelephone(Stringtelephone)this.telephone=telephone；packagecom.branches,entity；publicclassCityprivateintid；priv
6、ateStringname；publicCity()super()；)publicCity(intid,Stringname)super()；this,id=id；this,name=name；)publicintgetld()returnid；)publicvoidsetld(intid)this,id=id；)publicStringgetName()returnname；)publicvoidsetName(Stringname)this,name=name；第六步：添加jdbc驱动，创建dao基类用于连接数据库，并创建接口用于实现增删改等功能packagecom.branches,da
7、o；importjava.sql.Connection；importjava.sql.DriverManager;importjava.sql.PreparedStatement；importjava.sql.ResultSet;publicclassBaseDaoprotectedConnectionconn=null；protectedResultSetrs=null；protectedPreparedStatementps=null；获得数据库连接publicvoidgetConnection()throwsExceptionClass,forName(,com.microsoft.sq
8、lserver.jdbc.SQLServerDriver,)；conn=DriverManager.getConnection(njdbc：sqlserver：/localhost：1433；DataBaseName=CityDB,wsaw,w123n);)publicResultSetexecuteQuery(Stringsql,Objectparam)throwsExceptionthis.getConnection()；ps=conn.prepareStatement(sql)；if(param!=null)for(inti=0；iparam,length；i+)ps.SetObject
9、(i+l,parami)；)rs=ps.executeQuery()；returnrs；)publicintexecuteUpdate(Stringsql,Objectparam)throwsException(this.getConnection()；ps=conn.prepareStatement(sql)；if(param!=null)for(inti=0；iparam,length；i+)ps.SetObject(i+l,parami)；)introws=ps.executeUpdateO；this.CloseReSourceO；returnrows；)释放资源publicvoidCl
10、oseReSourceOthrowsExceptionif(ps!=null)ps.closeO；if(rs!=null)rs.close()；if(conn!=null)conn,closeO；packagecom.branches,dao；importjava.util.List；importcom.branches,entity.Branches；publicinterfaceBranchesDao1.istgetBranches()throwsException；BranchesgetBranche(intid)throwsException；booleanUpdBranches(Br
11、anchesbranches)throwsException；booleandelBranches(intid)throwsException；)packagecom.branches,dao；importjava.util.List；importcom.branches,entity.City；publicinterfaceCityDao1.istgetCitys()throwsException；)第七步：编写接口实现类packagecom.branches,dao.impl；importjava.util.ArrayList;importjava.util.List；importcom.
12、branches,dao.BaseDao；importcom.branches,dao.BranchesDao;importcom.branches,entity.Branches；importcom.branches,entity.City；publicclassBrandchesDaoImplextendsBaseDaoimplementsBranchesDaopublicListgetBranches()throwsExceptionStringsql=,selectc.idascicl,c.nameascname,b.idasbid,b.nameasbname,b.address,b.
13、telephonefromBranchesbinnerjoincityareaconb.CityAreaId=C.idr,;super.executeQuery(sql,null)；1.istlist=newArrayListO；while(rs.next()Branchesbra=newBranches()；bra.setld(rs.getlnt(HbidH)；bra.setName(rs.getString(Mbnamer,)；bra.setAddress(rs.getString(,addressn)；bra.SetTelephone(rs.getString(,telephone,f)
14、；bra.setCity(newCity(rs.getlnt(r,cidM),rs.getString(,cname,)；list,add(bra)；)super.CloseReSourceO;returnlist；)publicbooleanUpdBranches(Branchesbranches)throwsExceptionStringsql=wupdatebranchessetname=?,cityareaid=?,address=?,telephone=?whereid=?”；Objectparam=branches.getName(),branches.getCity().getl
15、d(),branches.getAddress(),branches.getTelephone(),branches,getld()；returnsuper.executeUpdate(sql,param)0；)publicbooleandelBranches(intid)throwsExceptionStringsql=,deletefrombrancheswhereid=?”；Object口param=id；returnsuper.executeUpdate(sql,param)0；)publicBranchesgetBranche(intid)throwsExceptionStrings
16、ql=,selectc.idascid,c.nameascname,b.idasbid,b.nameasbname,b.address,b.telephonefromBranchesbinnerjoincityareaconb.cityreaId=c.idwhereb.id=?”；Object口param=id；super.executeQuery(sql,param)；Branchesbra=null;while(rs.next()bra=newBranches()；bra.setld(rs.getlnt(HbidM)；bra.setName(rs.getString(Mbnamer,)；b
17、ra.setAddress(rs.getString(,addressn)；bra.SetTelephone(rs.getString(,telephone,f)；bra.setCity(newCity(rs.getlnt(,cidr,),rs.getString(,cnamer,)；)super.CloseReSourceO；returnbra；packagecom.branches,dao.impl；importjava.util.ArrayList;importjava.util.List；importcom.branches.dao.BaseDao；importcom.branches
18、.dao.CityDao;importcom.branches,entity.City；publicclassCityDaoImplextendsBaseDaoimplementsCityDaopublicListgetCitys()throwsExceptionStringsql=wselect*fromCityArean;super.executeQuery(sql,null)；1.istlist=newArrayList()；while(rs.next()list,add(newCity(rs.getlnt(,idn),rs.getString(HnameM)；)super.CloseR
19、eSourceO；returnlist；)第八步：在业务层依次调用方法packagecom.branches,biz；importjava.util.List；importcom.branches,dao.BranchesDao;importcom.branches.dao.impl.BranclchesDaoImpl；importcom.branches,entity.Branches；publicclassBranchesManagerBranchesDaodao=newBrandchesDaoImpl()；publicListgetBranches()throwsExceptionret
20、urndao.getBranches()；)publicbooleanmodifyBranches(Branchesbran)throwsExceptionreturndao.UpdBranches(bran)；publicbooleanremoveBranches(intid)throwsExceptionreturndao.delBranches(id)；publicBranchesgetBranche(intid)throwsException(returndao.getBranche(id)；)packagecom.branches,biz；importjava.util.List；i
21、mportcom.branches.dao.CityDao;importcom.branches.dao.impl.CityDaoImpl;importcom.branches,entity.City；publicclassCityManagerCityDaodao=newCityDaoImpl()；publicListgetCitys()throwsExceptionreturndao.getCitys()；)第九步：创建SerVIet,实现题目所要求的功能packagecom.branches,action；importjava.io.IOException;importjava.io.P
22、rintWriter;importjavax.servlet.ServletException;importjavax.servlet.Servlet；importjavax.servlet.ServletRequest;importjavax.servlet.ServletResponse;importcom.branches.biz.BranchesManager；publicclassBranchesActionextendsServlet/* ThedoGetmethodoftheservlet,* Thismethodiscalledwhenaformhasitstagvalueme
23、thodequalstoget.* paramrequesttherequestsendbytheclienttotheserver* paramresponsetheresponsesendbytheservertotheclient* throwsServletExceptionifanerroroccurred* throwsIOExceptionifanerroroccurred* /publicvoiddoGet(ServletRequestrequest,ServletResponseresponse)throwsServletException,IOExceptionreques
24、t.SetCharacterEncoding(,UTF-8r,)；response.SetCharacterEncoding(,UTF-8m)；response.SetContentType(,texthtml,)；try(BranchesManagerbm=newBranchesManager()；request.SetAttribute(,listr,bm.getBranches()；request.getRequestDispatcher(,index,jspr,).forward(request,response)；catch(Exceptione)e.PrintStackTrace(
25、)；response.SendReclirect(nerr.jspr,)；/* ThedoPostmethodoftheservlet,* Thismethodiscalledwhenaformhasitstagvaluemethodequalstopost.* ParanIrequesttherequestsendbytheclienttotheserver* paramresponsetheresponsesendbytheservertotheclient* throwsServletExceptionifanerroroccurred* throwsIOExceptionifanerr
26、oroccurred* /publicvoiddoPost(ServletRequestrequest,ServletResponseresponse)throwsServletException,IOExceptionresponse.SetContentType(,texthtml,)；PrintWriterout=response.getWriter()；out.println(nr,)；out.printin(,n)；out.printin(,Servletr)；out.printin(r,n)；out.print(l,Thisis)；out.print(this.getClass()
27、；out.println(r,usingthePOSTmethod1)；out.println(nn)；out.println(ww)；out.flush()；out.close()；)packagecom.branches,action；importjava.io.IOException;importjavax.servlet.ServletException;importjavax.servlet.Servlet；importjavax.servlet.ServletRequest;importjavax.servlet.ServletResponse;importcom.branches
28、.biz.BranchesManager；importcom.branches.biz.CityManager;importcom.branches,entity.Branches；importcom.branches,entity.City；publicclassModifyBranclchesActionextendsServlet/* ThedoGetmethodoftheservlet,* Thismethodiscalledwhenaformhasitstagvaluemethodequalstoget.* ParanIrequesttherequestsendbytheclient
29、totheserver* paramresponsetheresponsesendbytheservertotheclient* throwsServletExceptionifanerroroccurred* throwsIOExceptionifanerroroccurred* /publicvoiddoGet(ServletRequestrequest,ServletResponseresponse)throwsServletException,IOExceptionrequest.SetCharacterEncoding(,UTF-8h)；response.SetCharacterEn
30、coding(,UTF-8,)；response.SetContentType(ntexthtml,)；try(intid=Integer.parselnt(request.getParameter(,idr,)；BranchesManagerbm=newBranchesManager()；request.SetAttribute(nbrachesn,bm.getBranche(id)；request.SetAttribute(,1istn,newCityManager().getCitys()；request.getRequestDispatcher(modify,jspr,).forwar
31、d(request,response)；catch(Exceptione)e.PrintStackTrace()；response.SendRedirect(,err.jsp)；/* ThedoPostmethodoftheservlet,* Thismethodiscalledwhenaformhasitstagvaluemethodequalstopost.* paramrequesttherequestsendbytheclienttotheserver* paramresponsetheresponsesendbytheservertotheclient* throwsServletE
32、xceptionifanerroroccurred* throwsIOExceptionifanerroroccurred* /publicvoiddoPost(ServletRequestrequest,ServletResponseresponse)throwsServletException,IOExceptionrequest.SetCharacterEncoding(r,UTF-8,)；response.SetCharacterEncoding(,UTF-8r)；response.SetContentType(,texthtml,)；try(Branchesbran=newBranc
33、hes()；bran,setld(lnteger.parselnt(request.getParameter(,bidH)；bran.setName(request.getParameter(,bname,)；bran.SetAddress(request.getParameter(,baddressn)；bran.SetTelephone(request.getParameter(,btelephonen)；bran.setCity(newCity(Integer,parselnt(request.getParameter(nselCityr,),”)；BranchesManagerbm=n
34、ewBranchesManager()；bm.modifyBranches(bran)；response.SendRedirect(,BranchesAction,)；catch(Exceptione)e.PrintStackTrace()；response.SendRedirect(,err.jspr,)；packagecom.branches,action；importjava.io.IOException;importjava.io.PrintWriter;importjavax.servlet.ServletException;importjavax.servlet.Servlet；i
35、mportjavax.servlet.ServletRequest;importjavax.servlet.ServletResponse;importcom.branches.biz.BranchesManager；publicclassRemoveBranchesActionextendsServlet/*ThedoGetmethodoftheservlet,*Thismethodiscalledwhenaformhasitstagvaluemethodequalstoget.* paramrequesttherequestsendbytheclienttotheserver* param
36、responsetheresponsesendbytheservertotheclient* throwsServletExceptionifanerroroccurred* throwsIOExceptionifanerroroccurred* /publicvoiddoGet(ServletRequestrequest,ServletResponseresponse)throwsServletException,IOExceptionrequest.SetCharacterEncoding(,UTF-8r,)；response.SetCharaCterEnCoding(UTF-8”)；re
37、sponse.SetContentType(,texthtml,)；try(BranchesManagerbm=newBranchesManager()；intid=Integer.parselnt(request,getParameter(,id,)；bm.removeBranches(id)；response.SendRedirect(nBranchesAction,)；catch(Exceptione)e.PrintStackTrace()；response.SendRedirect(,err.jspr,)；/* ThedoPostmethodoftheservlet,* Thismet
38、hodiscalledwhenaformhasitstagvaluemethodequalstopost.* paramrequesttherequestsendbytheclienttotheserver* paramresponsetheresponsesendbytheservertotheclient* throwsServletExceptionifanerroroccurred* throwsIOExceptionifanerroroccurred* /publicvoiddoPost(ServletRequestrequest,ServletResponseresponse)th
39、rowsServletException,IOExceptionresponse.SetContentType(,texthtml,)；PrintWriterout=response.getWriter()；out.printIn(wn)；out.println(nw)；out.println(,AServletn)；out.printin(,n)；out.print(l,Thisis)；out.print(this.getClass()；out.printl(n,usingthePOSTmethod)；out.println(r,n)；out.printin(wn)；out.flush()；
40、out.close()；第十步创建jsp页面basehref=nnMyJSP,index,jsp,startingpage!*font:14px微软雅黑；tableborder-collapse：collapse；width：500px;margin：Opxauto；)tabletdborder：Ipxsolid#0094ff;padding：4px；)$(function()$(ntr:oddr,).css(background-COlOrCCC)；);网点查询网点名称所在城区网点地址练习电话操作$b.name$b.city,name$b.address$b.telephone删除修改%Stringpath=request.getContextPath()
