ACCP7.0S2机试答案.docx

ACCP7.0S2机试营业网点查询首先针对题目所示创建数据库BranchesMgrUSEBranchesMgrGOCREATETABLEdbo.Branches(idintIDENTITY(1,1)NOTNULL,bnamenvarchar(50)NOTNULL,cityArealdintNOTNULL,addressnvarchar(100)NOTNULL,telephonenvarchar(50)NOTNULL,)ALTERTABLEdbo.BranchesWITHCHECKADDFOREIGNKEY(cityAreald)REFERENCESdbo.CityArea(id)GOCREATETABLEdbo.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.branches,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,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；)publicvoidsetCity(Citycity)this,city=city；)publicStringgetAddress()returnaddress；)publicvoidsetAddress(Stringaddress)this,address=address；)publicStringgetTelephone()returntelephone；)publicvoidSetTelephone(Stringtelephone)this.telephone=telephone；packagecom.branches,entity；publicclassCityprivateintid；privateStringname；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,dao；importjava.sql.Connection；importjava.sql.DriverManager;importjava.sql.PreparedStatement；importjava.sql.ResultSet;publicclassBaseDaoprotectedConnectionconn=null；protectedResultSetrs=null；protectedPreparedStatementps=null；获得数据库连接publicvoidgetConnection()throwsExceptionClass,forName(,com.microsoft.sqlserver.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；i<param,length；i+)ps.SetObject(i+l,parami)；)rs=ps.executeQuery()；returnrs；)publicintexecuteUpdate(Stringsql,Objectparam)throwsException(this.getConnection()；ps=conn.prepareStatement(sql)；if(param!=null)for(inti=0；i<param,length；i+)ps.SetObject(i+l,parami)；)introws=ps.executeUpdateO；this.CloseReSourceO；returnrows；)释放资源publicvoidCloseReSourceOthrowsExceptionif(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.ist<Branches>getBranches()throwsException；BranchesgetBranche(intid)throwsException；booleanUpdBranches(Branchesbranches)throwsException；booleandelBranches(intid)throwsException；)packagecom.branches,dao；importjava.util.List；importcom.branches,entity.City；publicinterfaceCityDao1.ist<City>getCitys()throwsException；)第七步：编写接口实现类packagecom.branches,dao.impl；importjava.util.ArrayList;importjava.util.List；importcom.branches,dao.BaseDao；importcom.branches,dao.BranchesDao;importcom.branches,entity.Branches；importcom.branches,entity.City；publicclassBrandchesDaoImplextendsBaseDaoimplementsBranchesDaopublicList<Branches>getBranches()throwsExceptionStringsql=,selectc.idascicl,c.nameascname,b.idasbid,b.nameasbname,b.address,b.telephonefromBranchesbinnerjoincityareaconb.CityAreaId=C.idr,;super.executeQuery(sql,null)；1.ist<Branches>list=newArrayList<Branches>O；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)；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().getld(),branches.getAddress(),branches.getTelephone(),branches,getld()；returnsuper.executeUpdate(sql,param)>0；)publicbooleandelBranches(intid)throwsExceptionStringsql=,deletefrombrancheswhereid=?”；Object口param=id；returnsuper.executeUpd