ACCP70S2機試答案

1、ACCP7.0S2機試營業(yè)網(wǎng)點查詢首先針對題目所示創(chuàng)建數(shù)據(jù)庫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)REFEREN

2、CES dbo.CityArea (id)GOCREATE TABLE dbo.CityArea(id int IDENTITY(1,1) NOT NULL,cname nvarchar(50) NOT NULL,)第二步：向表中添加至少3條數(shù)據(jù)表1：Branches表2：CityArea第三步創(chuàng)建Web項目BranchesMgr第四步：依次創(chuàng)建包名為com.branches.entity,com. branches.dao, com. B, com. Branches.action第五步：創(chuàng)建實體類（在中）package ;public class Branches p

3、rivate 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

4、= 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 voi

5、d setAddress(String address) this.address = address;public String getTelephone() return telephone;public void setTelephone(String telephone) this.telephone = telephone;package ;public class City private int id;private String name;public City() super();public City(int id, String name) super();this.id

6、 = 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驅(qū)動，創(chuàng)建dao基類用于連接數(shù)據(jù)庫，并創(chuàng)建接口用于實現(xiàn)增刪改等功能package ;import ;import ;import ;import ;public class BaseDao protected Connecti

7、on conn = null;protected ResultSet rs = null;protected PreparedStatement ps = null;/獲取數(shù)據(jù)庫連接public void getConnection() throws Exception Class.forName("");conn = DriverManager.getConnection("jdbc:sqlserver:/localhost:1433;DataBaseName=CityDB","sa","123");public

8、 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 Excep

9、tion 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

10、.close();if(conn != null) conn.close();package ;import ;import ;public interface BranchesDao List<Branches> getBranches()throws Exception;Branches getBranche(int id)throws Exception;boolean updBranches(Branches branches)throws Exception;boolean delBranches(int id)throws Exception;package ;impo

11、rt ;import ;public interface CityDao List<City> getCitys()throws Exception;第七步：編寫接口實現(xiàn)類package ;import ;import ;import ;import ;import ;import ;public class BrandchesDaoImpl extends BaseDao implements BranchesDao public List<Branches> getBranches() throws Exception String sql="select

12、 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<Branches> list=new ArrayList<Branches>();while(rs.next()Branches bra=new Branches();bra.setId(rs.getInt("b

13、id");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 u

14、pdBranches(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, para

15、m)>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, a

16、s 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

17、(rs.getString("address");bra.setTelephone(rs.getString("telephone");bra.setCity(new City(rs.getInt("cid"),rs.getString("cname");super.closeReSource();return bra;package ;import ;import ;import ;import ;import ;public class CityDaoImpl extends BaseDao implement

18、s CityDao public List<City> getCitys() throws Exception String sql="select * from CityArea"super.executeQuery(sql,null);List<City> list=new ArrayList<City>();while(rs.next()list.add(new City(rs.getInt("id"),rs.getString("name");super.closeReSource();re

19、turn list;第八步：在業(yè)務(wù)層依次調(diào)用方法package ;import ;import ;import ;import ;public class BranchesManager BranchesDao dao=new BrandchesDaoImpl();public List<Branches> getBranches()throws Exceptionreturn dao.getBranches();public boolean modifyBranches(Branches bran)throws Exceptionreturn da

20、o.updBranches(bran);public boolean removeBranches(int id)throws Exceptionreturn dao.delBranches(id);public Branches getBranche(int id) throws Exceptionreturn dao.getBranche(id);package ;import ;import ;import ;import ;public class CityManager CityDao dao=new CityDaoImpl();public List

21、<City> getCitys()throws Exception return dao.getCitys();第九步：創(chuàng)建servlet，實現(xiàn)題目所要求的功能package ;import ;import ;import ;import ;import ;import ;import ;public class BranchesAction extends HttpServlet /* * The doGet method of the servlet. <br> * * This method is called when a form has its tag va

22、lue 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, HttpServlet

23、Response 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();r

24、equest.getRequestDispatcher("index.jsp").forward(request, response);catch(Exception e)e.printStackTrace();response.sendRedirect("err.jsp");/* * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * param re

25、quest 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 ServletExcep

26、tion, IOException response.setContentType("text/html");PrintWriter out = response.getWriter();out.println("<!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN">");out.println("<HTML>");out.println(" <HEAD><TITLE>A Servlet<

27、;/TITLE></HEAD>");out.println(" <BODY>");out.print(" This is ");out.print(this.getClass();out.println(", using the POST method");out.println(" </BODY>");out.println("</HTML>");out.flush();out.close();package ;import ;i

28、mport ;import ;import ;import ;import ;import ;import ;import ;public class ModifyBrandchesAction extends HttpServlet /* * The doGet method of the servlet. <br> * * 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

29、 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.setCharacterEncodi

30、ng("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.setAttrib

31、ute("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. <br> * * This method is called when a form h

32、as 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 reque

33、st, 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("

34、;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

35、=new BranchesManager();bm.modifyBranches(bran);response.sendRedirect("BranchesAction");catch(Exception e)e.printStackTrace();response.sendRedirect("err.jsp");package ;import ;import ;import ;import ;import ;import ;import ;public class RemoveBranchesAction extends HttpServlet /*

36、* The doGet method of the servlet. <br> * * 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 * t

37、hrows 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");try

38、BranchesManager 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. <br

39、> * * 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 *

40、/public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException response.setContentType("text/html");PrintWriter out = response.getWriter();out.println("<!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN">"

41、);out.println("<HTML>");out.println(" <HEAD><TITLE>A Servlet</TITLE></HEAD>");out.println(" <BODY>");out.print(" This is ");out.print(this.getClass();out.println(", using the POST method");out.println(" </B

42、ODY>");out.println("</HTML>");out.flush();out.close();第十步創(chuàng)建jsp頁面<% page language="java" import="java.util.*" pageEncoding="UTF-8"%><% taglib urijsp/jstl/core" prefix="c" %><%String path = request.getContextPath();Str

43、ing basePath = request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html> <head> <base href="<%=basePath%>"> <title>

44、My JSP 'index.jsp' starting page</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywo

45、rds" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link rel="stylesheet" type="text/css" href="styles.css">-><style type="text/css"> *font:14px 微軟

46、雅黑; table border-collapse: collapse; width:500px; margin:0px auto; table td border:1px solid #0094ff; padding:4px; </style> <script type="text/javascript" src="js/jquery-1.8.3.js"></script> <script type="text/javascript"> $(function() $("tr:

47、odd").css("background-color","#ccc"); ); </script> </head> <body> <table> <caption>網(wǎng)點查詢</caption> <tr> <td>網(wǎng)點名稱</td> <td>所在城區(qū)</td> <td>網(wǎng)點地址</td> <td>練習電話</td> <td>操作</td> &l

48、t;/tr> <c:forEach var="b" items="$list" varStatus="status"> <tr> <td>$</td> <td>$</td> <td>$b.address</td> <td>$b.telephone</td> <td><a href="RemoveBranchesAction?id=$b.id"

49、; onclick="return confirm('你確定要刪除嗎?')">刪除</a> <a href="ModifyBrandchesAction?id=$b.id ">修改</a></td> </tr> </c:forEach> </table> </body></html><% page language="java" import="java.util.*" pageE

50、ncoding="UTF-8"%><% taglib urijsp/jstl/core" prefix="c" %><%String path = request.getContextPath();String basePath = request.getScheme()+":/"+request.getServerName()+":"+request.getServerPort()+path+"/"%><!DOCTYPE HTML PUBLIC &

51、quot;-/W3C/DTD HTML 4.01 Transitional/EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'modify.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-co

52、ntrol" content="no-cache"><meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><!-<link r

53、el="stylesheet" type="text/css" href="styles.css">-><style type="text/css"> *font:14px 微軟雅黑; table border-collapse: collapse; width:500px; margin:0px auto; table td border:1px solid #0094ff; padding:4px; </style> </head> <body> &l

54、t;form action="ModifyBrandchesAction" method="post"> <table> <tr> <td>網(wǎng)點名稱</td> <td><input type="text" name="bname" value="$ "/></td> </tr> <tr> <td>所在城區(qū)</td> <td>

55、; <select name="selCity"> <c:forEach var="c" items="$list"> <option value="$c.id" <c:if test="$c.id=branches.city.id "> selected="selected" </c:if>>$</option> </c:forEach> </select> </td>