《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().getld(),branches.getAddress(),branches.getTelephone(),branches,getld();returnsuper.executeUpdate(sql,param)0;)publicbooleandelBranches(intid)throwsExceptionStringsql=,deletefrombrancheswhereid=?”;Object口param=id;returnsuper.executeUpd