ACCP7.0S2机试答案.docx

ACCP7.0S2机试营业网点查询首先针对题目所示创建数据库BranchesMgrUSE BranchesMgrGOCREATE TABLE dbo.Branches(id int IDENTITY(1,1) NOT NULL,bname nvarchar(50) NOT NULL,cityAreaId int NOT NULL,address nvarchar(100) NOT NULL,telephone nvarchar(50) NOT NULL,)ALTER TABLE dbo.Branches WITH CHECK ADD FOREIGN KEY(cityAreaId)REFERENCES dbo.CityArea (id)GOCREATE TABLE dbo.CityArea(id int IDENTITY(1,1) NOT NULL,cname nvarchar(50) NOT NULL,)第二步：向表中添加至少3条数据表1：Branches表2：CityArea第三步创建Web项目BranchesMgr第四步：依次创建包名为com.branches.entity,com. branches.dao, com. B, com. Branches.action第五步：创建实体类（在com.branches.entity中）package com.branches.entity;public class Branches private int id;private String name;private City city;private String address;private String telephone;public Branches() super();public Branches(int id, String name, City city, String address,String telephone) super();this.id = id; = name;this.city = city;this.address = address;this.telephone = telephone;public int getId() return id;public void setId(int id) this.id = id;public String getName() return name;public void setName(String name) = name;public City getCity() return city;public void setCity(City city) this.city = city;public String getAddress() return address;public void setAddress(String address) this.address = address;public String getTelephone() return telephone;public void setTelephone(String telephone) this.telephone = telephone;package com.branches.entity;public class City private int id;private String name;public City() super();public City(int id, String name) super();this.id = id; = name;public int getId() return id;public void setId(int id) this.id = id;public String getName() return name;public void setName(String name) = name;第六步：添加jdbc驱动，创建dao基类用于连接数据库，并创建接口用于实现增删改等功能package com.branches.dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;public class BaseDao protected Connection conn = null;protected ResultSet rs = null;protected PreparedStatement ps = null;/获取数据库连接public void getConnection() throws Exception Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver);conn = DriverManager.getConnection(jdbc:sqlserver:/localhost:1433;DataBaseName=CityDB,sa,123);public ResultSet executeQuery(String sql,Object param) throws Exception this.getConnection();ps = conn.prepareStatement(sql);if (param != null) for (int i = 0; i param.length; i+) ps.setObject(i+1, parami);rs = ps.executeQuery();return rs;public int executeUpdate(String sql,Object param) throws Exception this.getConnection();ps = conn.prepareStatement(sql);if (param != null) for (int i = 0; i param.length; i+) ps.setObject(i+1, parami);int rows = ps.executeUpdate();this.closeReSource();return rows;/释放资源public void closeReSource() throws Exception if(ps != null) ps.close();if(rs != null) rs.close();if(conn != null) conn.close();package com.branches.dao;import java.util.List;import com.branches.entity.Branches;public interface BranchesDao List getBranches()throws Exception;Branches getBranche(int id)throws Exception;boolean updBranches(Branches branches)throws Exception;boolean delBranches(int id)throws Exception;package com.branches.dao;import java.util.List;import com.branches.entity.City;public interface CityDao List getCitys()throws Exception;第七步：编写接口实现类package com.branches.dao.impl;import java.util.ArrayList;import java.util.List;import com.branches.dao.BaseDao;import com.branches.dao.BranchesDao;import com.branches.entity.Branches;import com.branches.entity.City;public class BrandchesDaoImpl extends BaseDao implements BranchesDao public List getBranches() throws Exception String sql=select c.id as cid, as cname,b.id as bid, as bname,b.address,b.telephone from Branches b inner join cityarea c on b.cityAreaId=c.id;super.executeQuery(sql,null);List list=new ArrayList();while(rs.next()Branches bra=new Branches();bra.setId(rs.getInt(bid);bra.setName(rs.getString(bname);bra.setAddress(rs.getString(address);bra.setTelephone(rs.getString(telephone);bra.setCity(new City(rs.getInt(cid),rs.getString(cname);list.add(bra);super.closeReSource();return list;public boolean updBranches(Branches branches) throws Exception String sql=update branches set name=?,cityareaid=?,address=?,telephone=? where id=?;Object param=branches.getName(),branches.getCity().getId(),branches.getAddress(),branches.getTelephone(),branches.getId();return super.executeUpdate(sql, param)0;public boolean delBranches(int id) throws Exception String sql=delete from branches where id=?; Object param=id; return super.executeUpdate(sql, param)0;public Branches getBranche(int id) throws Exception String sql=select c.id as cid, as cname,b.id as bid, as bname,b.address,b.telephone from Branches b inner join cityarea c on b.cityAreaId=c.id where b.id=?;Object param=id;super.executeQuery(sql,param);Branches bra=null;while(rs.next()bra=new Branches();bra.setId(rs.getInt(bid);bra.setName(rs.getString(bname);bra.setAddress(rs.getString(address);bra.setTelephone(rs.getString(telephone);bra.setCity(new City(rs.getInt(cid),rs.getString(cname);super.closeReSource();return bra;package com.branches.dao.impl;import java.util.ArrayList;import java.util.List;import com.branches.dao.BaseDao;import com.branches.dao.CityDao;import com.branches.entity.City;public class CityDaoImpl extends BaseDao implements CityDao public List getCitys() throws Exception String sql=select * from CityArea;super.executeQuery(sql,null);List list=new ArrayList();while(rs.next()list.add(new City(rs.getInt(id),rs.getString(name);super.closeReSource();return list;第八步：在业务层依次调用方法package ;import java.util.List;import com.branches.dao.BranchesDao;import com.branches.dao.impl.BrandchesDaoImpl;import com.branches.entity.Branches;public class BranchesManager BranchesDao dao=new BrandchesDaoImpl();public List getBranches()throws Exceptionreturn dao.getBranches();public boolean modifyBranches(Branches bran)throws Exceptionreturn dao.updBranches(bran);public boolean removeBranches(int id)throws Exceptionreturn dao.delBranches(id);public Branches getBranche(int id) throws Exceptionreturn dao.getBranche(id);package ;import java.util.List;import com.branches.dao.CityDao;import com.branches.dao.impl.CityDaoImpl;import com.branches.entity.City;public class CityManager CityDao dao=new CityDaoImpl();public List getCitys()throws Exception return dao.getCitys();第九步：创建servlet，实现题目所要求的功能package com.branches.action;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import .BranchesManager;public class BranchesAction extends HttpServlet /* * The doGet method of the servlet. * * This method is called when a form has its tag value method equals to get. * * param request the request send by the client to the server * param response the response send by the server to the client * throws ServletException if an error occurred * throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException request.setCharacterEncoding(UTF-8);response.setCharacterEncoding(UTF-8);response.setContentType(text/html);tryBranchesManager bm=new BranchesManager();request.setAttribute(list, bm.getBranches();request.getRequestDispatcher(index.jsp).forward(request, response);catch(Exception e)e.printStackTrace();response.sendRedirect(err.jsp);/* * The doPost method of the servlet. * * This method is called when a form has its tag value method equals to post. * * param request the request send by the client to the server * param response the response send by the server to the client * throws ServletException if an error occurred * throws IOException if an error occurred */public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException response.setContentType(text/html);PrintWriter out = response.getWriter();out.println();out.println();out.println( A Servlet);out.println( );out.print( This is );out.print(this.getClass();out.println(, using the POST method);out.println( );out.println();out.flush();out.close();package com.branches.action;import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import .BranchesManager;import .CityManager;import com.branches.entity.Branches;import com.branches.entity.City;public class ModifyBrandchesAction extends HttpServlet /* * The doGet method of the servlet. * * This method is called when a form has its tag value method equals to get. * * param request the request send by the client to the server * param response the response send by the server to the client * throws ServletException if an error occurred * throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException request.setCharacterEncoding(UTF-8);response.setCharacterEncoding(UTF-8);response.setContentType(text/html);tryint id=Integer.parseInt(request.getParameter(id);BranchesManager bm=new BranchesManager();request.setAttribute(branches,bm.getBranche(id);request.setAttribute(list, new CityManager().getCitys();request.getRequestDispatcher(modify.jsp).forward(request,response);catch(Exception e)e.printStackTrace();response.sendRedirect(err.jsp);/* * The doPost method of the servlet. * * This method is called when a form has its tag value method equals to post. * * param request the request send by the client to the server * param response the response send by the server to the client * throws ServletException if an error occurred * throws IOException if an error occurred */public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException request.setCharacterEncoding(UTF-8);response.setCharacterEncoding(UTF-8);response.setContentType(text/html);tryBranches bran=new Branches();bran.setId(Integer.parseInt(request.getParameter(bid);bran.setName(request.getParameter(bname);bran.setAddress(request.getParameter(baddress);bran.setTelephone(request.getParameter(btelephone);bran.setCity(new City(Integer.parseInt(request.getParameter(selCity),);BranchesManager bm=new BranchesManager();bm.modifyBranches(bran);response.sendRedirect(BranchesAction);catch(Exception e)e.printStackTrace();response.sendRedirect(err.jsp);package com.branches.action;import java.io.IOException;import java.io.PrintWriter;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import .BranchesManager;public class RemoveBranchesAction extends HttpServlet /* * The doGet method of the servlet. * * This method is called when a form has its tag value method equals to get. * * param request the request send by the client to the server * param response the response send by the server to the client * throws ServletException if an error occurred * throws IOException if an error occurred */public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException request.setCharacterEncoding(UTF-8);response.setCharacterEncoding(UTF-8);response.setContentType(text/html);tryBranchesManager bm=new BranchesManager();int id=Integer.parseInt(request.getParameter(id);bm.removeBranches(id);response.sendRedirect(BranchesAction);catch(Exception e)e.printStackTrace();response.sendRedirect(err.jsp);/* * The doPost method of the servlet. * * This m