web軟件技術(shù)實驗五

1、web軟件技術(shù)實 驗 報 告（ 9 ）學(xué)號： 姓名：李智斌 班級： 計123 成績：實驗名稱：JDBC訪問數(shù)據(jù)庫實驗地點：信息樓215所使用的工具軟件及環(huán)境：tomcat ，MySQL，navicat，JCreator一、實驗?zāi)康模?. 掌握使用傳統(tǒng)的方法訪問數(shù)據(jù)庫；2. 掌握使用數(shù)據(jù)源的方法訪問數(shù)據(jù)庫。二、實驗內(nèi)容：（一）使用傳統(tǒng)方法通過JSP頁面訪問數(shù)據(jù)庫【步驟1】創(chuàng)建數(shù)據(jù)庫。假設(shè)在MySQL建立了一個名為bookstore的數(shù)據(jù)庫，在其中建立books表，代碼如下：CREATE TABLE books ( bookid character(5) PRIMARY KEY, -書號 titl

2、e text, -書名author character varying(20), -作者 publisher character varying (40), -出版社 price real -價格);向books表中插入幾條記錄，代碼如下：INSERT INTO books VALUES (204,Head First Servlets & JSP, Bryan Basham, 中國電力出版社,98.00);INSERT INTO books VALUES (201, Servlets 與JSP 核心教程, Hall Marty,清華大學(xué)出版社,45);INSERT INTO books VA

3、LUES (202, Tomcat與Java Web 開發(fā)技術(shù)祥解, 孫衛(wèi)琴, 機(jī)械工業(yè)出版社,45);INSERT INTO books VALUES (203, JSP 應(yīng)用開發(fā)技術(shù), 柳永坡,人民郵電出版社,52);INSERT INTO books VALUES (205, J2EE 1.4 編程指南, Spielman Sue,電子工業(yè)出版社,68);注意：需要將數(shù)據(jù)庫的JDBC驅(qū)動程序安裝到應(yīng)用程序的WEB-INFlib目錄中?！静襟E2】使用下面JSP頁面displayBooks.jsp訪問books表中的數(shù)據(jù)。 Database Access Test%try Class.for

4、Name(com.mysql.jdbc.Driver );String dburl = jdbc:mysql:/localhost:3306/bookstore ;Connection conn = DriverManager.getConnection(dburl, bookstore, bookstore); Statement stmt = conn.createStatement();String sql = SELECT * FROM books ;ResultSet rs = stmt.executeQuery(sql);out.println();out.println(書號書名

5、作者價格);while (rs.next()out.println(+ rs.getString(1)+ rs.getString(2)+ rs.getString(3)+ rs.getString(5)+);out.println();rs.close();stmt.close();conn.close();catch (Exception e) out.println(e.getMessage();%結(jié)果如下：（二）通過數(shù)據(jù)源訪問數(shù)據(jù)庫注意：需要將數(shù)據(jù)庫的JDBC驅(qū)動程序安裝到Tomcat安裝目錄的lib目錄中,并重新啟動Tomcat服務(wù)器?！静襟E1】建立局部數(shù)據(jù)源在Web應(yīng)用程序中建立一

6、個META-INF目錄，在其中建立一個context.xml文件，內(nèi)容如下：【步驟2】使用下面的JSP頁面displayBooks.jsp訪問數(shù)據(jù)庫 DataSource Test%try Context context = new InitialContext();DataSource ds = (DataSource) context.lookup(java:comp/env/jdbc/bookDS);Connection conn = ds.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stm

7、t.executeQuery(SELECT * FROM books);out.println();out.println(書號書名作者價格);while (rs.next()out.println(+ rs.getString(1)+ rs.getString(2)+ rs.getString(3)+ rs.getString(5)+);out.println();rs.close();stmt.close();conn.close();catch (Exception e) out.println(e.getMessage();%結(jié)果如下：（三）綜合應(yīng)用。本實驗采用MVC設(shè)計模式，通過數(shù)據(jù)

8、源和DAO對象訪問數(shù)據(jù)庫。其中JavaBeans實現(xiàn)模型，訪問數(shù)據(jù)庫，Servlet實現(xiàn)控制器，JSP頁面實現(xiàn)視圖。 模型包括2個JavaBean：BookBean用于存放圖書信息，BookDAO用于訪問數(shù)據(jù)庫。 控制器包括2個Servlet：BookQueryServlet根據(jù)請求參數(shù)查詢圖書信息、BookInsertServlet用來向數(shù)據(jù)庫中插入數(shù)據(jù)。 視圖包括4個JSP頁面：bookQuery.jsp顯示查詢頁面、bookInsert.jsp顯示插入頁面、display.jsp顯示查詢結(jié)果頁面和errorPage.jsp顯示錯誤頁面?！静襟E1】存放圖書信息的JavaBeans代碼Boo

9、kBean.java，它也是一個傳輸對象。package com.beans;import java.io.*;public class BookBean implements Serializable private String bookid = null; private String title = null; private String author = null; private String publisher = null; private float price = 0.0F; public BookBean() public BookBean(String bookId,

10、 String author, String title, String publisher, float price) this.bookid = bookId; this.title = title; this.author = author; this.publisher = publisher;this.price = price; public String getBookid() return this.bookid; public String getTitle() return title; public String getAuthor() return this.autho

11、r; public float getPrice() return price; public String getPublisher () return publisher; public void setBookid(String bookid) this.bookid=bookid; public void setTitle(String title)this.title=title; public void setAuthor(String author) this.author = author; public void setPrice(float price)this.price

12、=price; public void setPublisher (String publisher) this.publisher = publisher;【步驟2】下面的BookDAO是一個簡單的JavaBeans，它實現(xiàn)數(shù)據(jù)庫的訪問。package com.beans;import java.sql.*;import javax.sql.*;import javax.naming.*;import java.util.ArrayList;import com.beans.BookBean;public class BookDAO private static InitialContext

13、 context= null; private DataSource dataSource = null; public BookDAO() try if(context = null) context = new InitialContext(); dataSource = (DataSource)context.lookup(java:comp/env/jdbc/bookDS); catch(NamingException e2) / 根據(jù)書號查詢圖書信息 public BookBean searchBook(String bookid) Connection conn = null; P

14、reparedStatement pstmt = null; ResultSet rst = null; BookBean book = new BookBean(); try conn = dataSource.getConnection(); pstmt = conn.prepareStatement(SELECT * FROM books WHERE bookid=?); pstmt.setString(1,bookid); rst = pstmt.executeQuery(); if(rst.next() book.setBookid(rst.getString(bookid); bo

15、ok.setTitle(rst.getString(title); book.setAuthor(rst.getString(author); book.setPublisher(rst.getString(publisher); book.setPrice(rst.getFloat(price); return book; else return null; catch(SQLException se) return null; finally try conn.close(); catch(SQLException se) / 插入一本圖書記錄 public boolean insertB

16、ook(BookBean book) Connection conn = null; PreparedStatement pstmt = null; try conn = dataSource.getConnection(); pstmt = conn.prepareStatement( INSERT INTO books VALUES(?,?,?,?,?); pstmt.setString(1,book.getBookid(); pstmt.setString(2,book.getTitle(); pstmt.setString(3,book.getAuthor(); pstmt.setSt

17、ring(4,book.getPublisher(); pstmt.setFloat(3,book.getPrice(); pstmt.executeUpdate(); pstmt.close(); return true; catch(SQLException se) return false; finally try conn.close(); catch(SQLException se) 【步驟3】下面的JSP頁面bookQuery.jsp實現(xiàn)根據(jù)書號查詢圖書信息 Book Query請輸入一個書號:【步驟4】下面的JSP頁面bookInsert.jsp實現(xiàn)向數(shù)據(jù)庫中插入數(shù)據(jù) Book

18、Insert請輸入圖書信息： 書號 書名 作者 出版社 單價 【步驟5】顯示查詢結(jié)果的JSP頁面display.jsp： 書號： 書名： 作者： 出版社： 價格：【步驟6】錯誤頁面errorPage.jsp代碼如下： 對不起，您查的圖書不存在！【步驟7】下面的Servlet實現(xiàn)從請求參數(shù)獲得書號，然后從數(shù)據(jù)庫中查找該書，最后根據(jù)查詢結(jié)果將請求轉(zhuǎn)發(fā)到顯示頁面(display.jsp)或錯誤頁面(errorPage.jsp)。package com.control;import java.io.*;import java.sql.*;import javax.servlet.*;import ja

19、vax.servlet.http.*;import com.beans.BookBean;import com.beans.BookDAO;public class BookQueryServlet extends HttpServlet public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException String bookid = request.getParameter(bookid); BookDAO bookdao = new

20、BookDAO(); BookBean book = bookdao.searchBook(bookid); if(book!=null) request.getSession().setAttribute(book, book); RequestDispatcher view = request.getRequestDispatcher(/display.jsp); view.forward(request, response); else RequestDispatcher view = request.getRequestDispatcher(/errorPage.jsp); view.

21、forward(request, response); 【步驟8】下面的Servlet實現(xiàn)向數(shù)據(jù)庫插入數(shù)據(jù)，并將控制請求的轉(zhuǎn)發(fā)到bookInsert.jsp頁面。package com.control;import java.io.*;import java.sql.*;import javax.servlet.*;import javax.servlet.http.*;import com.beans.BookBean;import com.beans.BookDAO;public class BookInsertServlet extends HttpServlet public void

22、 doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException request.setCharacterEncoding(gb2312); String message = null; BookBean book = new BookBean( request.getParameter(bookid),request.getParameter(title), request.getParameter(author),request.getParameter(publisher), Float.parseFloat(request.getParameter(price) ); BookDAO bookdao = new BookDAO(); boolean success = bookdao.insert