實(shí)驗(yàn)8-jdbc數(shù)據(jù)庫訪問[驕陽教育]

1、實(shí)驗(yàn)8 JDBC訪問數(shù)據(jù)庫一、實(shí)驗(yàn)?zāi)康?. 掌握使用傳統(tǒng)的方法訪問數(shù)據(jù)庫；2. 掌握使用數(shù)據(jù)源的方法訪問數(shù)據(jù)庫。二、實(shí)驗(yàn)原理數(shù)據(jù)庫應(yīng)用是Web應(yīng)用開發(fā)的一個(gè)重要應(yīng)用。Web應(yīng)用程序訪問數(shù)據(jù)庫有兩種方法：傳統(tǒng)的方法和使用JNDI數(shù)據(jù)源的方法。傳統(tǒng)方法訪問數(shù)據(jù)庫的步驟是：加載數(shù)據(jù)庫驅(qū)動(dòng)程序；建立連接對(duì)象；創(chuàng)建語句對(duì)象；獲得結(jié)果集；關(guān)閉有關(guān)連接對(duì)象。使用數(shù)據(jù)源訪問數(shù)據(jù)庫的步驟是：配置數(shù)據(jù)源（局部數(shù)據(jù)源或全局?jǐn)?shù)據(jù)源）；通過JNDI機(jī)制查找命名數(shù)據(jù)源；通過數(shù)據(jù)源對(duì)象創(chuàng)建連接對(duì)象；其他與傳統(tǒng)方法一致。三、實(shí)驗(yàn)內(nèi)容與步驟（一）使用傳統(tǒng)方法通過JSP頁面訪問數(shù)據(jù)庫【步驟1】創(chuàng)建數(shù)據(jù)庫。假設(shè)在PostgreSQ

2、L建立了一個(gè)名為bookstore的數(shù)據(jù)庫，在其中建立books表，代碼如下：CREATE TABLE books ( bookid character(5) PRIMARY KEY, -書號(hào) title varchar2(80), -書名author character varying(20), -作者 publisher character varying (40), -出版社 price real -價(jià)格);向books表中插入幾條記錄，代碼如下：INSERT INTO books VALUES (204,Head First Servlets & JSP, Bryan Basham,

3、中國電力出版社,98.00);INSERT INTO books VALUES (201, Servlets 與JSP 核心教程, Hall Marty,清華大學(xué)出版社,45);INSERT INTO books VALUES (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ù)庫

4、的JDBC驅(qū)動(dòng)程序安裝到應(yīng)用程序的WEB-INFlib目錄中?！静襟E2】使用下面JSP頁面displayBooks.jsp訪問books表中的數(shù)據(jù)。 Database Access Test%try Class.forName(org.postgresql.Driver);String dburl = jdbc:postgresql:/localhost:5432/bookstore;Connection conn = DriverManager.getConnection(dburl, bookstore, bookstore); Statement stmt = conn.createSt

5、atement();String sql = SELECT * FROM books ;ResultSet rs = stmt.executeQuery(sql);out.println();out.println(書號(hào)書名作者價(jià)格);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(

6、e.getMessage();%運(yùn)用mysql的代碼如下：Database Access Test%try Class.forName(com.mysql.jdbc.Driver);String dburl = jdbc:mysql:/localhost:3306/bookstore;Connection conn = DriverManager.getConnection(dburl, root, );Statement stmt = conn.createStatement();String sql = SELECT * FROM books;ResultSet rs = stmt.exe

7、cuteQuery(sql);out.println();out.println(書號(hào)書名作者價(jià)格);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();% 圖1 displayBooks.jsp（二）通過數(shù)據(jù)源訪問數(shù)據(jù)庫注意：需要

8、將數(shù)據(jù)庫的JDBC驅(qū)動(dòng)程序安裝到Tomcat安裝目錄的lib目錄中,并重新啟動(dòng)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 InitialContext();DataSource ds = (DataSource) context.lookup(java:comp/env/jdbc/bookDS);Connection conn = ds.g

9、etConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(SELECT * FROM books);out.println();out.println(書號(hào)書名作者價(jià)格);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

10、 (Exception e) out.println(e.getMessage();%圖2 displayBooks.jsp（三）綜合應(yīng)用。本實(shí)驗(yàn)采用MVC設(shè)計(jì)模式，通過數(shù)據(jù)源和DAO對(duì)象訪問數(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ù)請(qǐng)求參數(shù)查詢圖書信息、BookInsertServlet用來向數(shù)據(jù)庫中插入數(shù)據(jù)。 視圖包括4個(gè)JSP頁面：bookQuery.jsp顯示查詢頁面、

11、bookInsert.jsp顯示插入頁面、display.jsp顯示查詢結(jié)果頁面和errorPage.jsp顯示錯(cuò)誤頁面。【步驟1】存放圖書信息的JavaBeans代碼BookBean.java，它也是一個(gè)傳輸對(duì)象。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 publish

12、er = null; private float price = 0.0F; public BookBean() public BookBean(String 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() return this.boo

13、kid; public String getTitle() return title; public String getAuthor() return this.author; 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 set

14、Author(String author) this. author = author; public void setPrice(float price)this.price=price; public void setPublisher (String publisher) this.publisher = publisher;【步驟2】下面的BookDAO是一個(gè)簡(jiǎn)單的JavaBeans，它實(shí)現(xiàn)數(shù)據(jù)庫的訪問。package com.beans;import java.sql.*;import javax.sql.*;import javax.naming.*;import java.uti

15、l.ArrayList;import com.beans.BookBean;public class BookDAO private static InitialContext 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(NamingExcept

16、ion e2) / 根據(jù)書號(hào)查詢圖書信息 public BookBean searchBook(String bookid) Connection conn = null; PreparedStatement pstmt = null; ResultSet rst = null; BookBean book = new BookBean(); try conn = dataSource.getConnection(); pstmt = conn.prepareStatement(SELECT * FROM books WHERE bookid=?); pstmt.setString(1,boo

17、kid); rst = pstmt.executeQuery(); if(rst.next() book.setBookid(rst.getString(bookid); book.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

18、null; finally try conn.close(); catch(SQLException se) / 插入一本圖書記錄 public boolean insertBook(BookBean book) Connection conn = null; PreparedStatement pstmt = null; try conn = dataSource.getConnection(); pstmt = conn.prepareStatement( INSERT INTO books VALUES(?,?,?,?,?); pstmt.setString(1,book.getBook

19、id(); pstmt.setString(2,book.getTitle(); pstmt.setString(3,book.getAuthor(); pstmt.setString(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頁面

20、bookQuery.jsp實(shí)現(xiàn)根據(jù)書號(hào)查詢圖書信息 Book Query請(qǐng)輸入一個(gè)書號(hào):圖3 bookQuery.jsp【步驟4】下面的JSP頁面bookInsert.jsp實(shí)現(xiàn)向數(shù)據(jù)庫中插入數(shù)據(jù) Book Insert請(qǐng)輸入圖書信息： 書號(hào) 書名 作者 出版社 單價(jià) 圖4 bookInsert.jsp 圖5 插入成功【步驟5】顯示查詢結(jié)果的JSP頁面display.jsp： 書號(hào)： 書名： 作者： 出版社： 價(jià)格：圖6 顯示查詢結(jié)果【步驟6】錯(cuò)誤頁面errorPage.jsp代碼如下： 對(duì)不起，您查的圖書不存在！圖7 顯示錯(cuò)誤頁面【步驟7】下面的Servlet實(shí)現(xiàn)從請(qǐng)求參數(shù)獲得書號(hào)，然后從數(shù)

21、據(jù)庫中查找該書，最后根據(jù)查詢結(jié)果將請(qǐng)求轉(zhuǎn)發(fā)到顯示頁面(display.jsp)或錯(cuò)誤頁面(errorPage.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 BookQueryServlet extends HttpServlet public void doPost(HttpServletReq

22、uest request,HttpServletResponse response) throws ServletException,IOException String bookid = request.getParameter(bookid); BookDAO bookdao = new BookDAO(); BookBean book = bookdao.searchBook(bookid); if(book!=null) request.getSession().setAttribute(book, book); RequestDispatcher view = request.get

23、RequestDispatcher(/display.jsp); view.forward(request, response); else RequestDispatcher view = request.getRequestDispatcher(/errorPage.jsp); view.forward(request, response); 【步驟8】下面的Servlet實(shí)現(xiàn)向數(shù)據(jù)庫插入數(shù)據(jù)，并將控制請(qǐng)求的轉(zhuǎn)發(fā)到bookInsert.jsp頁面。package com.control;import java.io.*;import java.sql.*;import javax.serv

24、let.*;import javax.servlet.http.*;import com.beans.BookBean;import com.beans.BookDAO;public class BookInsertServlet extends HttpServlet public void 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.