實(shí)驗(yàn)8jdbc數(shù)據(jù)庫(kù)訪問(wèn)_第1頁(yè)
實(shí)驗(yàn)8jdbc數(shù)據(jù)庫(kù)訪問(wèn)_第2頁(yè)
實(shí)驗(yàn)8jdbc數(shù)據(jù)庫(kù)訪問(wèn)_第3頁(yè)
實(shí)驗(yàn)8jdbc數(shù)據(jù)庫(kù)訪問(wèn)_第4頁(yè)
實(shí)驗(yàn)8jdbc數(shù)據(jù)庫(kù)訪問(wèn)_第5頁(yè)
已閱讀5頁(yè)，還剩6頁(yè)未讀， 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明：本文檔由用戶提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

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

2、L建立了一個(gè)名為bookstore的數(shù)據(jù)庫(kù)，在其中建立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 &

3、JSP', 'Bryan Basham', '中國(guó)電力出版社',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 boo

4、ks VALUES ('203', 'JSP 應(yīng)用開發(fā)技術(shù)', '柳永坡','人民郵電出版社',52);INSERT INTO books VALUES ('205', 'J2EE 1.4 編程指南', 'Spielman Sue','電子工業(yè)出版社',68);注意：需要將數(shù)據(jù)庫(kù)的JDBC驅(qū)動(dòng)程序安裝到應(yīng)用程序的WEB-INFlib目錄中?！静襟E2】使用下面JSP頁(yè)面displayBooks.jsp訪問(wèn)books表中的數(shù)據(jù)。<% page contentType

5、="text/html; charset=gb2312" %><% page import="java.sql.*"%><html><head> <title>Database Access Test</title></head><body><%try Class.forName("org.postgresql.Driver");String dburl = "jdbc:postgresql:/localhost:5432/boo

6、kstore"Connection conn = DriverManager.getConnection(dburl, "bookstore", "bookstore"); Statement stmt = conn.createStatement();String sql = "SELECT * FROM books" ;ResultSet rs = stmt.executeQuery(sql);out.println("<table border=1>");out.println(&quo

7、t;<tr><td>書號(hào)</td><td>書名</td><td>作者</td><td>價(jià)格</td></tr>");while (rs.next()out.println("<tr><td>"+ rs.getString(1)+"</td><td>"+ rs.getString(2)+"</td><td>"+ rs.getString(

8、3)+"</td><td>"+ rs.getString(5)+"</td></tr>");out.println("</table>");rs.close();stmt.close();conn.close();catch (Exception e) out.println(e.getMessage();%></body></html>運(yùn)用mysql的代碼如下：<% page contentType="text/html; ch

9、arset=gb2312"%><% page import="java.sql.*"%><html><head><title>Database Access Test</title></head><body><%try Class.forName("com.mysql.jdbc.Driver");String dburl = "jdbc:mysql:/localhost:3306/bookstore"Connection conn

10、 = DriverManager.getConnection(dburl, "root", "");Statement stmt = conn.createStatement();String sql = "SELECT * FROM books"ResultSet rs = stmt.executeQuery(sql);out.println("<table border=1>");out.println("<tr><td>書號(hào)</td><td>

11、;書名</td><td>作者</td><td>價(jià)格</td></tr>");while (rs.next() out.println("<tr><td>" + rs.getString(1) + "</td><td>"+ rs.getString(2) + "</td><td>" + rs.getString(3)+ "</td><td>"

12、 + rs.getString(5) + "</td></tr>");out.println("</table>");rs.close();stmt.close();conn.close(); catch (Exception e) out.println(e.getMessage();%></body></html> 圖1 displayBooks.jsp（二）通過(guò)數(shù)據(jù)源訪問(wèn)數(shù)據(jù)庫(kù)注意：需要將數(shù)據(jù)庫(kù)的JDBC驅(qū)動(dòng)程序安裝到Tomcat安裝目錄的lib目錄中,并重新啟動(dòng)Tomcat服務(wù)器。

13、【步驟1】建立局部數(shù)據(jù)源在Web應(yīng)用程序中建立一個(gè)META-INF目錄，在其中建立一個(gè)context.xml文件，內(nèi)容如下：<?xml version="1.0" encoding="utf-8"?><Context reloadable = "true"><Resource name="jdbc/bookDS" type="javax.sql.DataSource" maxActive="4" maxIdle="2" use

14、rname="bookstore" maxWait="5000" driverClassName="org.postgresql.Driver" password="bookstore" url="jdbc:mysql:/localhost:3306/bookstore"/></Context>【步驟2】使用下面的JSP頁(yè)面displayBooks.jsp訪問(wèn)數(shù)據(jù)庫(kù)<% page contentType="text/html; charset=gb2312&qu

15、ot; %><% page import="java.sql.*, javax.sql.*,javax.naming.*"%><html><head> <title>DataSource Test</title></head><body><%try Context context = new InitialContext();DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/bookDS&qu

16、ot;);Connection conn = ds.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT * FROM books");out.println("<table border=1>");out.println("<tr><td>書號(hào)</td><td>書名</td><td>作者</td><td&

17、gt;價(jià)格</td></tr>");while (rs.next()out.println("<tr><td>"+ rs.getString(1)+"</td><td>"+ rs.getString(2)+"</td><td>"+ rs.getString(3)+"</td><td>"+ rs.getString(5)+"</td></tr>"

18、);out.println("</table>");rs.close();stmt.close();conn.close();catch (Exception e) out.println(e.getMessage();%></body></html>圖2 displayBooks.jsp（三）綜合應(yīng)用。本實(shí)驗(yàn)采用MVC設(shè)計(jì)模式，通過(guò)數(shù)據(jù)源和DAO對(duì)象訪問(wèn)數(shù)據(jù)庫(kù)。其中JavaBeans實(shí)現(xiàn)模型，訪問(wèn)數(shù)據(jù)庫(kù)，Servlet實(shí)現(xiàn)控制器，JSP頁(yè)面實(shí)現(xiàn)視圖。· 模型包括2個(gè)JavaBean：BookBean用于存放圖書信息，Bo

19、okDAO用于訪問(wèn)數(shù)據(jù)庫(kù)。· 控制器包括2個(gè)Servlet：BookQueryServlet根據(jù)請(qǐng)求參數(shù)查詢圖書信息、BookInsertServlet用來(lái)向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)。· 視圖包括4個(gè)JSP頁(yè)面：bookQuery.jsp顯示查詢頁(yè)面、bookInsert.jsp顯示插入頁(yè)面、display.jsp顯示查詢結(jié)果頁(yè)面和errorPage.jsp顯示錯(cuò)誤頁(yè)面。【步驟1】存放圖書信息的JavaBeans代碼BookBean.java，它也是一個(gè)傳輸對(duì)象。package com.beans;import java.io.*;public class BookBean imp

20、lements 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

21、; 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 pub

22、lisher; 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;【

23、步驟2】下面的BookDAO是一個(gè)簡(jiǎn)單的JavaBeans，它實(shí)現(xiàn)數(shù)據(jù)庫(kù)的訪問(wèn)。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(co

24、ntext = null) context = new InitialContext(); dataSource = (DataSource)context.lookup("java:comp/env/jdbc/bookDS"); catch(NamingException e2) / 根據(jù)書號(hào)查詢圖書信息 public BookBean searchBook(String bookid) Connection conn = null; PreparedStatement pstmt = null; ResultSet rst = null; BookBean book =

25、 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")

26、; 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 ins

27、ertBook(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.getAuth

28、or(); 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頁(yè)面bookQuery.jsp實(shí)現(xiàn)根據(jù)書號(hào)查詢圖書信息<% page contentType="text/html; charse

29、t=gb2312" %><html><head> <title>Book Query</title></head><body>請(qǐng)輸入一個(gè)書號(hào):<br><form action="bookquery.do" method = "post"><input type="text" name="bookid"><br><input type="submit" va

30、lue="提交"></form></body></html>圖3 bookQuery.jsp【步驟4】下面的JSP頁(yè)面bookInsert.jsp實(shí)現(xiàn)向數(shù)據(jù)庫(kù)中插入數(shù)據(jù)<% page contentType="text/html; charset=gb2312" %><html><head> <title>Book Insert</title></head><body><h3>請(qǐng)輸入圖書信息：</h3>&l

31、t;% if(request.getAttribute("result")!=null) out.print(request.getAttribute("result");%><form action = "bookinsert.do" method = "post"> <table> <tr><td>書號(hào)</td> <td><input type="text" name="bookid" &

32、gt;</td></tr> <tr><td>書名</td><td><input type="text" name="title"></td></tr> <tr><td>作者</td><td><input type="text" name="author" ></td></tr> <tr><td>出版社&

33、lt;/td><td><input type="text" name="publisher" ></td></tr> <tr><td>單價(jià)</td><td><input type="text" name="price" ></td></tr> <tr><td><input type="submit" value="確定&

34、quot; ></td> <td><input type="reset" value="重置" ></td> </tr></table></form></body></html>圖4 bookInsert.jsp 圖5 插入成功【步驟5】顯示查詢結(jié)果的JSP頁(yè)面display.jsp：<% page contentType="text/html;charset=gb2312"%><jsp:useBean

35、id="book" class="com.beans.BookBean" scope="request"/><html><body> 書號(hào)：<jsp:getProperty name="book" property="bookid"/> 書名：<jsp:getProperty name="book" property="title"/> 作者：<jsp:getProperty name="

36、;book" property="author"/> 出版社：<jsp:getProperty name="book" property="publisher"/> 價(jià)格：<jsp:getProperty name="book" property="price"/></body></html>圖6 顯示查詢結(jié)果【步驟6】錯(cuò)誤頁(yè)面errorPage.jsp代碼如下：<% page contentType="text/h

37、tml;charset=gb2312"%><html><body> 對(duì)不起，您查的圖書不存在！</body></html>圖7 顯示錯(cuò)誤頁(yè)面【步驟7】下面的Servlet實(shí)現(xiàn)從請(qǐng)求參數(shù)獲得書號(hào)，然后從數(shù)據(jù)庫(kù)中查找該書，最后根據(jù)查詢結(jié)果將請(qǐng)求轉(zhuǎn)發(fā)到顯示頁(yè)面(display.jsp)或錯(cuò)誤頁(yè)面(errorPage.jsp)。package com.control;import java.io.*;import java.sql.*;import javax.servlet.*;import javax.servlet.http.*;i

38、mport 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(

39、); 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("

40、/errorPage.jsp"); view.forward(request, response); 【步驟8】下面的Servlet實(shí)現(xiàn)向數(shù)據(jù)庫(kù)插入數(shù)據(jù)，并將控制請(qǐng)求的轉(zhuǎn)發(fā)到bookInsert.jsp頁(yè)面。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 ext

41、ends 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.pa

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽，若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間，僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理，對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請(qǐng)與我們聯(lián)系，我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論