實驗8-jdbc數(shù)據(jù)庫訪問.doc

實驗8 JDBC訪問數(shù)據(jù)庫一、實驗目的1. 掌握使用傳統(tǒng)的方法訪問數(shù)據(jù)庫；2. 掌握使用數(shù)據(jù)源的方法訪問數(shù)據(jù)庫。二、實驗原理數(shù)據(jù)庫應用是Web應用開發(fā)的一個重要應用。Web應用程序訪問數(shù)據(jù)庫有兩種方法：傳統(tǒng)的方法和使用JNDI數(shù)據(jù)源的方法。傳統(tǒng)方法訪問數(shù)據(jù)庫的步驟是：加載數(shù)據(jù)庫驅(qū)動程序；建立連接對象；創(chuàng)建語句對象；獲得結(jié)果集；關閉有關連接對象。使用數(shù)據(jù)源訪問數(shù)據(jù)庫的步驟是：配置數(shù)據(jù)源（局部數(shù)據(jù)源或全局數(shù)據(jù)源）；通過JNDI機制查找命名數(shù)據(jù)源；通過數(shù)據(jù)源對象創(chuàng)建連接對象；其他與傳統(tǒng)方法一致。三、實驗內(nèi)容與步驟（一）使用傳統(tǒng)方法通過JSP頁面訪問數(shù)據(jù)庫【步驟1】創(chuàng)建數(shù)據(jù)庫。假設在PostgreSQL建立了一個名為bookstore的數(shù)據(jù)庫，在其中建立books表，代碼如下：CREATE TABLE books ( bookid character(5) PRIMARY KEY, -書號 title varchar2(80), -書名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,清華大學出版社,45);INSERT INTO books VALUES (202, Tomcat與Java Web 開發(fā)技術祥解, 孫衛(wèi)琴, 機械工業(yè)出版社,45);INSERT INTO books VALUES (203, JSP 應用開發(fā)技術, 柳永坡,人民郵電出版社,52);INSERT INTO books VALUES (205, J2EE 1.4 編程指南, Spielman Sue,電子工業(yè)出版社,68);注意：需要將數(shù)據(jù)庫的JDBC驅(qū)動程序安裝到應用程序的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.createStatement();String sql = SELECT * FROM books ;ResultSet rs = stmt.executeQuery(sql);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();%運用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.executeQuery(sql);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();% 圖1 displayBooks.jsp（二）通過數(shù)據(jù)源訪問數(shù)據(jù)庫注意：需要將數(shù)據(jù)庫的JDBC驅(qū)動程序安裝到Tomcat安裝目錄的lib目錄中,并重新啟動Tomcat服務器?！静襟E1】建立局部數(shù)據(jù)源在Web應用程序中建立一個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 = stmt.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();%圖2 displayBooks.jsp（三）綜合應用。本實驗采用MVC設計模式，通過數(shù)據(jù)源和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代碼BookBean.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, 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.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 setAuthor(String author) this. author = author; public void setPrice(float price)this.price=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 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; 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,bookid); 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 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.getBookid(); 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頁面bookQuery.jsp實現(xiàn)根據(jù)書號查詢圖書信息 Book Query請輸入一個書號:圖3 bookQuery.jsp【步驟4】下面的JSP頁面bookInsert.jsp實現(xiàn)向數(shù)據(jù)庫中插入數(shù)據(jù) Book Insert請輸入圖書信息： 書號 書名 作者 出版社 單價 圖4 bookInsert.jsp 圖5 插入成功【步驟5】顯示查詢結(jié)果的JSP頁面display.jsp： 書號： 書名： 作者： 出版社： 價格：圖6 顯示查詢結(jié)果【步驟6】錯誤頁面errorPage.jsp代碼如下： 對不起，您查的圖書不存在！圖7 顯示錯誤頁面【步驟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 javax.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 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.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 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(re