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

2、RIMARY KEY,title text,author character vary in g(20), publisher character vary ing (40), price real);向books表中插入幾條記錄，代碼如下：INSERT INTO books V ALUES(204,Head First Servlets & JSP, Bryan Basham,中國電力出版社,98.00);INSERT INTO books V ALUES(201, Servlets 與 JSP 核心教程,Hall Marty,清華大學(xué)出版社,45);INSERT INTO books V

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

4、om.mysql.jdbc.Driver );Stri ng dburl = jdbc:mysql:/localhost:3306/bookstore ;Connection conn = DriverMa nager.getC onnection( dburl, bookstore, bookstore);Stateme nt stmt = conn. createStateme nt();String sql = SELECT * FROM books;ResultSet rs = stmt.executeQuery(sql); out.pri ntl n();out.println( 書

5、號 書名 作者 價(jià)格 ): while (rs. next()out.pri ntl n( + rs.getStri ng(1)+ rs.getStri ng(2) + rs.getStri ng(3)+ rs.getStri ng(5)+);out.pri ntl n( );rs.close();stmt.close();conn. close();catch (Excepti on e) out.pri ntl n( e.getMessage();%結(jié)果如下：書號料名作者價(jià)格204First Servlet & JSPBryan Enwha98201|ServletJSPtl?i!Mart

6、y Hall45202 Tomcat與Jaq Wtb開發(fā)技術(shù)詳解和衛(wèi)琴203JSP憲全學(xué)習(xí)手冊張銀越69205Java EE 5開發(fā)指南Kevin Mukhar49(二)通過數(shù)據(jù)源訪問數(shù)據(jù)庫注意：需要將數(shù)據(jù)庫的JDBC驅(qū)動程序安裝到Tomcat安裝目錄的lib目錄中，并重新啟動Tomcat 服務(wù)器。【步驟1】建立局部數(shù)據(jù)源在 Web應(yīng)用程序中建立一個(gè)META-INF目錄，在其中建立一個(gè)context.xml文件，內(nèi)容如下：【步驟2】使用下面的JSP頁面displayBooks.jsp訪問數(shù)據(jù)庫 DataSource Test%try Context context = new lnitialC

7、ontext();DataSource ds = (DataSource) context.lookup(java:comp/env/jdbc/bookDS); Connection conn = ds.getConnection();Stateme nt stmt = conn. createStateme nt();ResultSet rs = stmt.executeQuery(SELECT * FROM books);out.pri ntl n();out.println( 書號 書名 作者 價(jià)格 ); while (rs. next()out.pri ntl n( + rs.getS

8、tri ng(1)+ rs.getStri ng(2)+ rs.getStri ng(3)+ rs.getStri ng(5)+);out.pri ntl n( );rs.close();stmt.close();conn. close();catch (Excepti on e) out.pri ntl n( e.getMessage();%結(jié)果如下：書號書名柞者ft梧204Head First Servlet & JSPBryan Basham垃201ServletKarty Hall45202frcmcatJava Web開發(fā)技術(shù)詳解45203JSP完全學(xué)習(xí)宇冊I張銀鶴1$9 205Ja

9、va EL 5幵發(fā)指南Kevin lukhar49(三)綜合應(yīng)用。本實(shí)驗(yàn)采用 MVC設(shè)計(jì)模式，通過數(shù)據(jù)源和 DAO對象訪問數(shù)據(jù)庫。其中JavaBeans 實(shí)現(xiàn)模型，訪問數(shù)據(jù)庫，Servlet實(shí)現(xiàn)控制器，JSP頁面實(shí)現(xiàn)視圖。* 模型包括2個(gè)JavaBean： BookBean用于存放圖書信息，BookDAO用于訪問數(shù)據(jù)庫。* 控制器包括2個(gè)Servlet: BookQueryServlet根據(jù)請求參數(shù)查詢圖書信息、 BookInsertServlet 用來向數(shù)據(jù)庫中插入數(shù)據(jù)。* 視圖包括4個(gè)JSP頁面：bookQuery.jsp顯示查詢頁面、bookInsert.jsp顯示插入頁面、displa

10、y.jsp顯示查詢結(jié)果頁面和errorPage.jsp顯示錯(cuò)誤頁面。【步驟1】存放圖書信息的JavaBeans代碼BookBean.java，它也是一個(gè)傳輸對象。package com.bea ns;import java.io.*;public class BookBea n impleme nts Serializableprivate String bookid = nuII;private String title = null;private String author = nuII;private String publisher = null;private float pric

11、e = 0.0F;public BookBea n()public BookBea n( Stri ng bookId, 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() retur n this.bookid; public Stri ng getTitle(

12、) return title; public String getAuthor() return this.author; public float getPrice() return price; public String getPublisher () return publisher; public void setBookid(Stri ng bookid) this.bookid=bookid; public void setTitle(String title)this.title=title; public void setAuthor(Stri ng author) this

13、.author = author; public void setPrice(float price)this.price=price; public void setPublisher (String publisher) this.publisher = publisher;【步驟2】下面的BookDAO是一個(gè)簡單的JavaBeans,它實(shí)現(xiàn)數(shù)據(jù)庫的訪問。package com.bea ns;import java.sql.*;import javax.sql.*;import javax. nami ng.*;import java.util.ArrayList;import com.b

14、ea ns.BookBea n;public class BookDAOprivate static In itialC on text con text= nu II;private DataSource dataSource = nu II;public BookDAO()tryif(con text = n ull)con text = new In itialC on text();dataSource = (DataSource)c on text .lo okup(java:comp/e nv/jdbc/bookDS);catch(Nam in gExcepti on e2)/根據(jù)

15、書號查詢圖書信息public BookBea n searchBook(Stri ng bookid)Connection conn = nu II;PreparedStateme nt pstmt = n ull;ResultSet rst = null;BookBea nbook = new BookBea n();tryconn = dataSource.getC onn ecti on();pstmt = conn .prepareStateme nt(SELECT * FROM books WHERE bookid=?); pstmt.setStri ng(1,bookid);rst

16、 = pstmt.executeQuery();if(rst. next()book.setBookid(rst.getStri ng(bookid);book.setTitle(rst.getStri ng(title); book.setAuthor(rst.getStri ng(author); book.setPublisher(rst.getStri ng(publisher); book.setPrice(rst.getFloat(price); retur n book;elsereturnnu II;catch(SQLExcepti on se)return n ull;fin

17、 allytryconn. close();catch(SQLExcepti on se)/插入一本圖書記錄public boolea n in sertBook(BookBea n book)Conn ecti on conn = n ull;PreparedStateme nt pstmt = n ull;tryconn = dataSource.getC onnection();pstmt = conn. prepareStateme nt(INSERT INTO books V ALUES(?,?,?,?,?);pstmt.setStri ng(1,book.getBookid();p

18、stmt.setStri ng(2,book.getTitle();pstmt.setStri ng(3,book.getAuthor();pstmt.setStri ng(4,book.getPublisher(); pstmt.setFloat(3,book.getPrice();pstmt.executeUpdate();pstmt.close();return true;catch(SQLExcepti on se)return false;fin allytryconn. close();catch(SQLException se) 【步驟3】下面的JSP頁面bookQuery.js

19、p實(shí)現(xiàn)根據(jù)書號查詢圖書信息 Book Query請輸入一個(gè)書號：vform action= bookquery.do method = post【步驟4】下面的JSP頁面book In sert.jsp實(shí)現(xiàn)向數(shù)據(jù)庫中插入數(shù)據(jù) Book In sert請輸入圖書信息： 書號 書名 作者 出版社 單價(jià) 【步驟5】顯示查詢結(jié)果的 JSP頁面display.jsp : 書號： 書名： 作者： 出版社： 價(jià)格：【步驟6】錯(cuò)誤頁面errorPage.jsp代碼如下： 對不起，您查的圖書不存在！【步驟7】下面的Servlet實(shí)現(xiàn)從請求參數(shù)獲得書號，然后從數(shù)據(jù)庫中查找該書，最后根據(jù)查詢 結(jié)果將請求轉(zhuǎn)發(fā)到顯示頁

20、面(display.jsp)或錯(cuò)誤頁面(errorPage.jsp)。package com.c on trol;import java.io.*;import java.sql.*;import javax.servlet.*:import javax.servlet.http.*;import com.bea ns.BookBea n;import com.bea ns.BookDAO;public class BookQueryServlet exte nds HttpServletpublic void doPost(HttpServletRequest request,HttpSer

21、vletResp onse resp on se)throws ServletExcepti on ,IOExcepti onString bookid = request.getParameter(bookid);BookDAO bookdao = new BookDAO();BookBea n book = bookdao.searchBook(bookid);if(book!=null)request.getSession().setAttribute(book, book);RequestDispatcher view = request.getRequestDispatcher(/d

22、isplay.jsp); view.forward(request, resp on se);elseRequestDispatcher view = request.getRequestDispatcher(/errorPage.jsp); view.forward(request, resp on se);【步驟8】下面的Servlet實(shí)現(xiàn)向數(shù)據(jù)庫插入數(shù)據(jù)，并將控制請求的轉(zhuǎn)發(fā)到bookInsert.jsp頁面。package com.c on trol;import java.io.*;import java.sql.*;import javax.servlet.*;import java

23、x.servlet.http.*;import com.bea ns.BookBea n;import com.bea ns.BookDAO;public class Book In sertServlet exte nds HttpServletpublic void doPost(HttpServletRequest request,HttpServletResp onse resp on se)throws ServletExcepti on ,IOExcepti onrequest.setCharacterE ncodi ng(gb2312);String message = n ul

24、l;BookBea n book = new BookBea n( request.getParameter(bookid),request.getParameter(title), request.getParameter(author),request.getParameter(publisher), Float.parseFloat(request.getParameter(price);BookDAO bookdao = new BookDAO();boolea n success = bookdao.i nsertBook(book);if(success)message =成功插入一條記錄。;elsemessage =插入