web数据库实验代码.doc_咨信网zixin.com.cn

资源描述

计算机科学与技术系实验报告专业名称 14-软件工程课程名称计算机组成与结构姓名第一帅陆同组人员实验日期 2015-05-21 Web数据库编程一、实验目的：编写Java代码，熟悉并掌握JDBC的使用，包括Connection、Statement和ResultSet等对象的使用。二、实验内容：在MyEclipse环境下编写Java代码，运用JDBC技术完成相应的功能，调试运行程序。三、实验要求： 1. 熟悉并掌握JDBC的配置和常用对象； 2. 运用JDBC技术完成规定功能； 3. 写出实验报告。四、实验学时：2学时五、实验步骤： 1．进入MyEclipse环境，新建一个Web Project； 2. 设计一个数据库，新建出版社表，出版社表字段包括出版社编码（主键），出版社名称。 3. 设计对出版社信息的显示、增加、编辑和删除功能，要求采用Statement对象发送SQL语句； 4. 调试运行程序。六、选作实验 1. 设计对出版社信息的条件查询功能首先得有sqljdbc.jar驱动用以连接SQL Server数据库实验代码： xiancha.jsp <%@ page language="java" contentType="text/html; charset=GBK" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'bookAddProc.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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  <script> function btnDelete(id){ if(confirm("确认删除该条记录吗?")) location.href="/hehehehhehehehe/b/delete.jsp?id="+id; } </script> </head> <body> <a href="/hehehehhehehehe/b/tian.jsp">添加</a> <% ResultSet rs=null; Connection conn=null; Statement stmt= null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=bookshop","root","1234"); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM books");%> <table border=3> <tr> <td>序号</td> <td>书号</td> <td>书名</td> <td>作者</td> <td>出版社名称</td> <td>价格</td> <td>类别</td> <td>操作</td> </tr> <% while(rs.next()) { %> <tr> <td><%=rs.getString("bookid") %></td> <td><%=rs.getString("booknumber") %></td> <td><%=rs.getString("bookname") %></td> <td><%=rs.getString("author") %></td> <td><%=rs.getString("publish") %></td> <td><%=rs.getString("price") %></td> <td><%=rs.getString("type") %></td> <td><a href="/hehehehhehehehe/b/edit.jsp?bookid=<%=rs.getString("bookid") %>&booknumber=<%=rs.getString("booknumber") %> &bookname=<%=rs.getString("bookname") %> &author=<%=rs.getString("author") %> &publish=<%=rs.getString("publish") %> &price=<%=rs.getString("price") %> &type=<%=rs.getString("type") %>">编辑</a>&nbsp <a href="/hehehehhehehehe/b/delete.jsp?id=<%=rs.getString("bookid") %>">删除</a></td> </tr> <% } rs.close(); stmt.close(); conn.close(); %> </table> </center> </body> </html> 添加 tian.jsp <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html><head><base href="<%=basePath%>"> <title>书籍添加</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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> <form action="/hehehehhehehehe/b/tianxian.jsp" method="post"> <table><tr><td>序号</td><td><input type="text" name="bookId"/></td></tr> <tr><td>书号</td><td><input type="text" name="bookNumber"/></td></tr> <tr><td>书名</td><td><input type="text" name="bookName"/></td></tr> <tr><td>作者</td><td><input type="text" name="author"/></td></tr> <tr><td>出版社</td><td><select name="chu_ban_she_shi"> <option value="xuan_ze_shi">------请选择------</option> <option value="1">清华大学出版社</option> <option value="2">高等学府出版社</option> <option value="3">安徽出版社</option> </select></td></tr> <tr><td>价格</td><td><input type="text" name="price"/></td></tr> <tr><td>类别</td><td><input type="text" name="type"/></td></tr> <tr><td colspan="2"><input type="submit" value="保存"/>  <input type="reset" value="重置"/></td></tr></table></form></body></html> 添加后执行功能：把添加的信息导入数据库 tianxian.jsp <%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="UTF-8"%> <%@ page import="java.sql.*" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'tianxian.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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> <% request.setCharacterEncoding("utf-8"); String id = request.getParameter("bookId"); String num = request.getParameter("bookNumber"); String name= request.getParameter("bookName"); String author = request.getParameter("author"); String pub = request.getParameter("chu_ban_she_shi"); String price = request.getParameter("price"); String type= request.getParameter("type"); Connection conn = null; PreparedStatement pstm = null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url="jdbc:sqlserver://localhost:1433;databasename=bookshop"; conn = DriverManager.getConnection(url,"root","1234"); String sql = "insert into books(bookid,booknumber,bookname,author,publish,price,type)values(?,?,?,?,?,?,?)"; pstm = conn.prepareStatement(sql); pstm.setString(1,id); pstm.setString(2,num); pstm.setString(3,name); pstm.setString(4,author); pstm.setString(5,pub); pstm.setString(6,price); pstm.setString(7,type); pstm.executeUpdate(); if(pstm!=null) pstm.close(); if(conn!=null) conn.close(); response.sendRedirect("/hehehehhehehehe/b/xiancha.jsp"); %> </body> </html> 编辑页面 edit.jsp <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'edit.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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> <form action="/hehehehhehehehe/b/edit1.jsp" method="post"> <table> <%request.setCharacterEncoding("utf-8"); %> <tr> <td>序号</td> <td><input type='text' name='bookid' value='<%=request.getParameter("bookid") %>'></td> </tr> <tr> <td>书号</td> <td><input type='text' name='booknumber' value='<%=request.getParameter("booknumber") %>'></td> </tr> <tr> <td>书名</td> <td><input type='text' name='bookname' value='<%=request.getParameter("bookname") %>'></td> </tr> <tr> <td>作者</td> <td><input type='text' name='author' value='<%=request.getParameter("author") %>'></td> </tr> <tr> <td>出版社名称</td> <td><input type='text' name='publish' value='<%=request.getParameter("publish") %>'></td> </tr> <tr> <td>价格</td> <td><input type='text' name='price' value='<%=request.getParameter("price") %>'></td> </tr> <tr> <td>类别</td> <td><input type='text' name='type' value='<%=request.getParameter("type") %>'></td> </tr> <tr> <td><input type='submit' name='sb' value='提交' ></td> </tr> </table> </form> </body> </html> 执行编辑功能：把编辑的数据导入数据库 edit1.jsp <%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'edit1.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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> <% request.setCharacterEncoding("utf-8"); String id = request.getParameter("bookid"); String num = request.getParameter("booknumber"); String name= request.getParameter("bookname"); String pub = request.getParameter("publish"); String author = request.getParameter("author"); String price = request.getParameter("price"); String type= request.getParameter("type"); Connection conn = null; PreparedStatement pstm = null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url="jdbc:sqlserver://localhost:1433;databasename=bookshop"; conn = DriverManager.getConnection(url,"root","1234"); String sql = "update books set bookid=?,booknumber=?,bookname=?,author=?,publish=?,price=?,type=? where bookid=?"; pstm = conn.prepareStatement(sql); pstm.setString(1,id); pstm.setString(2,num); pstm.setString(3,name); pstm.setString(4,author); pstm.setString(5,pub); pstm.setString(6,price); pstm.setString(7,type); pstm.setString(8,id); pstm.executeUpdate(); %> <h2>恭喜，修改成功！！！</h2><a href='/hehehehhehehehe/b/xiancha.jsp'>返回</a> </body> </html> 删除并执行功能： delete.jsp <%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <base href="<%=basePath%>"> <title>My JSP 'delete.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="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page">  </head> <body> <% String id = request.getParameter("id"); Connection conn = null; PreparedStatement pstm = null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url="jdbc:sqlserver://localhost:1433;databasename=bookshop"; conn = DriverManager.getConnection(url,"root","1234"); String sql = "delete from books where bookid=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1,id); ps.execute(); %> <h2>恭喜，刪除成功！！！</h2><a href='/hehehehhehehehe/b/xiancha.jsp'>返回</a> </body> </html>

展开阅读全文