JSP与JDBC建立网页库课件

1、第11章 JSP與JDBC建立網頁資料庫11-1 JDBC的基礎11-2 建立MySQL的資料庫連結11-3 建立Access的資料庫連結11-4 JSP的資料庫基本存取11-5 SQL語言的資料庫查詢11-6 SQL語言的資料庫操作11-7 JNDI與連結池11-8 JSTL的資料庫存取標籤庫第11章 JSP與JDBC建立網頁資料庫11-1 JDBC的11-1 JDBC的基礎-說明昇陽公司提出的資料庫中介軟體（Middleware）稱為JDBC（Java Database Connectivity），這是一種開放標準的Java程式介面，可以讓Java程式連接資料庫管理系統，以Java技術來說

2、，就是實作JDBC驅動程式介面（JDBC Driver Interface）的類別，即JDBC AP即JDBC API。11-1 JDBC的基礎-說明昇陽公司提出的資料庫中介軟體（11-1 JDBC的基礎-圖例11-1 JDBC的基礎-圖例11-1 JDBC的基礎-驅動程式種類JDBC-ODBC橋接驅動程式（JDBC-ODBC Bridge）：Java程式不是直接連接資料庫管理系統，而是透過微軟ODBC的中介軟體來存取資料庫。原生API的Java驅動程式（A Native-API Partly Java Driver）：將Java程式的JDBC呼叫轉換成專屬資料庫管理系統的原生API呼叫。Ja

3、va網路協定驅動程式（A Net-protocol All Java Driver）：將Java程式的JDBC呼叫轉換成資料庫管理系統專屬的網路協定，再由伺服器轉換成資料庫管理系統的API呼叫。Java原生通訊協定驅動程式（A Native-protocol All Java Driver）：將Java程式的JDBC呼叫直接轉換成資料庫管理系統原生通訊協定的API呼叫，以便客戶端直接連線資料庫伺服器。11-1 JDBC的基礎-驅動程式種類JDBC-ODBC橋接11-2 建立MySQL的資料庫連結11-2-1 安裝MySQL的JDBC驅動程式11-2-2 使用JDBC連結MySQL資料庫11-2

4、 建立MySQL的資料庫連結11-2-1 安裝MyS11-2-1 安裝MySQL的JDBC驅動程式MySQL資料庫系統支援JDBC的Java原生通訊協定驅動程式，稱為MySQL Connector/J，我們可以在MySQL網站免費下載，目前版本是3.0版，在JSP的Web應用程式安裝MySQL Connector/J，其步驟如下所示：1. 使用解壓縮工具從壓縮檔取出JAR檔案：mysql-connector-java-3.0.16-ga-bin.jar。2. 將JAR檔案複製Web應用程式的WEB-INFlib資料夾，以本章的Ch11應用程式為例，其完整路徑為C:InetpubwwwrootC

5、h11WEB-INFlib。11-2-1 安裝MySQL的JDBC驅動程式MySQL資料11-2-2 使用JDBC連結MySQL資料庫-說明在安裝好MySQL的JDBC驅動程式後，JSP程式就可以使用JDBC建立資料庫連結，然後透過JDBC API執行SQL指令來存取資料庫的記錄資料。11-2-2 使用JDBC連結MySQL資料庫-說明在安裝好11-2-2 使用JDBC連結MySQL資料庫-步驟一步驟一：載入驅動程式在JSP程式首先需要載入JDBC驅動程式，如下所示：String sDriver = com.mysql.jdbc.Driver;Class.forName(sDriver);上述

6、程式碼的字串sDriver是驅動程式名稱com.mysql.jdbc.Driver，接著使用Class類別方法forName()方法載入驅動程式。11-2-2 使用JDBC連結MySQL資料庫-步驟一步驟一11-2-2 使用JDBC連結MySQL資料庫-步驟二步驟二：建立Connection連結物件在載入JDBC驅動程式後，就可以使用DriverManager類別的getConnection()類別方法建立Connection物件dbCon，如下所示：sCon = jdbc:mysql:/localhost:3306/school?user=root&password=123456;dbCon

7、=DriverManager.getConnection(sCon);11-2-2 使用JDBC連結MySQL資料庫-步驟二步驟二11-2-2 使用JDBC連結MySQL資料庫-步驟三步驟三：建立JDBC的Statement物件Statement物件的目的是執行SQL指令，在建立好Connection物件後，就可以使用createStatement()方法建立Statement物件，如下所示：stmt = dbCon.createStatement();11-2-2 使用JDBC連結MySQL資料庫-步驟三步驟三11-3 建立Access的資料庫連結11-3-1 JDBC-ODBC資料庫連結的

8、基礎11-3-2 建立資料庫與系統資料來源11-3-3 使用JDBC-ODBC連結Access資料庫11-3 建立Access的資料庫連結11-3-1 JDBC11-3-1 JDBC-ODBC資料庫連結的基礎-說明Access並不支援JDBC驅動程式。所以JSP程式需要使用JDBC的JDBC-ODBC橋接驅動程式，透過JDBC-ODBC驅動程式存取ODBC資料來源的Access資料庫。ODBC（Object Database Connectivity）是微軟開發的中介軟體，提供Windows作業系統的應用程式一種標準的資料庫存取方式，能夠存取位在其它電腦上執行的資料庫系統。11-3-1 JDB

9、C-ODBC資料庫連結的基礎-說明Acc11-3-1 JDBC-ODBC資料庫連結的基礎-圖例11-3-1 JDBC-ODBC資料庫連結的基礎-圖例11-3-2 建立資料庫與系統資料來源-建立Access資料庫Access資料庫是學校資料庫School.mdb，在School.mdb資料庫擁有Students資料表，其欄位說明，如下表所示：11-3-2 建立資料庫與系統資料來源-建立Access資料11-3-2 建立資料庫與系統資料來源-新增ODBC系統資料來源在Access建立好資料庫後，就可以在Windows作業系統建立ODBC系統資料來源，筆者準備在Windows XP作業系統的電腦新增

10、Access資料庫School.mdb名為【school_db】的ODBC系統資料來源，如右圖所示：11-3-2 建立資料庫與系統資料來源-新增ODBC系統資料11-3-3 使用JDBC-ODBC連結Access資料庫-載入驅動程式載入JDBC-ODBC驅動程式在JSP程式載入的驅動程式是透過ODBC存取資料庫，所以載入JDBC-ODBC驅動程式，如下所示：String sDriver = sun.jdbc.odbc.JdbcOdbcDriver;Class.forName(sDriver);上述程式碼的字串sDriver是驅動程式名稱sun.jdbc.odbc.JdbcOdbcDriver，

11、接著載入JDBC-ODBC驅動程式。11-3-3 使用JDBC-ODBC連結Access資料庫-11-3-3 使用JDBC-ODBC連結Access資料庫-JDBC URLJDBC URL位址字串在載入JDBC-ODBC驅動程式後，就可以使用DriverManager類別的getConnection()類別方法建立Connection物件dbCon，如下所示：String sCon = jdbc:odbc:school_db;dbCon = DriverManager.getConnection(sCon);11-3-3 使用JDBC-ODBC連結Access資料庫-11-4 JSP的資料庫基

12、本存取11-4-1 取得資料表的資訊11-4-2 顯示資料表的記錄資料11-4-3 使用JDBC連結MySQL資料庫的中文問題11-4 JSP的資料庫基本存取11-4-1 取得資料表的資11-4-1 取得資料表的資訊-步驟四取得資料表資訊是繼續第11-2-2節的步驟三。步驟四：使用Statement物件執行SQL指令在前面已經說過，建立Statement物件的目的是執行SQL指令，如下所示：String sSQL=SELECT * FROM students;boolean state = stmt.execute(sSQL);11-4-1 取得資料表的資訊-步驟四取得資料表資訊是繼續第11-

13、4-1 取得資料表的資訊-步驟五步驟五：取得ResultSet和ResultSetMetaData物件在使用Statement物件執行SQL指令後，接著可以使用getResultSet()方法取得ResultSet物件，如下所示：ResultSet rs = stmt.getResultSet();上述程式碼在取得ResultSet物件後，再使用getMetaData()方法取得ResultSetMetaData物件，如下所示：ResultSetMetaData md = rs.getMetaData();在取得ResultSetMetaData物件後，就可以使用相關方法取得資料表的相關資訊。

14、11-4-1 取得資料表的資訊-步驟五步驟五：取得Resul11-4-1 取得資料表的資訊-方法ResultSetMetaData物件的相關方法，如下表所示：11-4-1 取得資料表的資訊-方法ResultSetMet11-4-2 顯示資料表的記錄資料-步驟四在這一節筆者準備執行SQL查詢指令取得資料表的記錄資料，步驟是繼續第11-2-2節的步驟三。步驟四：使用Statement物件執行SQL指令在JSP程式執行SQL查詢指令可以取得查詢結果的ResultSet物件，這是使用executeQuery()方法取得ResultSet物件，如下所示：rs = stmt.executeQuery(sS

15、QL);程式碼取得參數SQL指令sSQL查詢結果的ResultSet物件rs，參數的SQL查詢指令可以取得資料表students的所有記錄。11-4-2 顯示資料表的記錄資料-步驟四在這一節筆者準備執11-4-2 顯示資料表的記錄資料-步驟五步驟五：使用迴圈取得ResultSet物件的記錄在取得查詢結果的ResultSet物件後，可以使用while迴圈配合next()方法來顯示記錄，如下所示：while ( rs.next() ) out.print(rs.getString(stdno); out.print(rs.getString(name); out.print(rs.getStrin

16、g(address); out.print(rs.getDate(birthday); out.print(rs.getInt(totalcredit);11-4-2 顯示資料表的記錄資料-步驟五步驟五：使用迴圈取11-4-2 顯示資料表的記錄資料-步驟五步驟六：關閉連結的Connection和Statement物件在處理完資料庫的查詢或操作後，JSP程式需要關閉Connection和Statement物件，使用的都是close()方法，如下所示：stmt.close();dbCon.close();11-4-2 顯示資料表的記錄資料-步驟五步驟六：關閉連結的11-4-3 使用JDBC連結My

17、SQL資料庫的中文問題-編碼指定JDBC驅動程式的編碼在載入JDBC驅動程式時，JSP程式需要指定編碼參數來連結MySQL資料庫，如下所示：String sCon = jdbc:mysql:/localhost:3306/school?user=root& + password=123456&useUnicode=true&characterEncoding=MS950;dbCon = DriverManager.getConnection(sCon);上述JDBC驅動程式設定useUnicode和characterEncoding屬性值為true和MS950，使用Unicode的MS950編

18、碼。11-4-3 使用JDBC連結MySQL資料庫的中文問題-編11-4-3 使用JDBC連結MySQL資料庫的中文問題-Unicode字串1將中文欄位值轉換成Unicode字串在JSP程式顯示透過JDBC取得MySQL資料庫的中文記錄資料時，因為JDBC驅動程式在轉換中文內碼時，一個中文字會被切割成2個字元，例如：0 x4175會切割成0 x41和0 x75。所以，JSP程式在顯示中文的記錄資料時，需要先將欄位字串還原成完整中文內碼的Unicode字串，這就是toUnicode()方法的功能。11-4-3 使用JDBC連結MySQL資料庫的中文問題-U11-4-3 使用JDBC連結MySQL

19、資料庫的中文問題-Unicode字串2String toUnicode(String s) if (s = null | s.length() = 0) return null; byte buffer = new bytes.length(); int i, j; for (i = 0, j = 0; i = 0 x100 ) char c = (char) s.charAt(i); byte buf = (+c).getBytes(); bufferj+ = (byte) buf0; bufferj+ = (byte) buf1; else bufferj+ = (byte) s.char

20、At(i); return new String(buffer,0,j); 11-4-3 使用JDBC連結MySQL資料庫的中文問題-U11-4-3 使用JDBC連結MySQL資料庫的中文問題-表單資料編碼指定表單傳送資料的編碼當SQL指令或欄位值使用表單方式傳遞中文內容給JSP程式時，在JSP程式需要指定傳送的編碼方式，如下所示：request.setCharacterEncoding(MS950);上述程式碼使用request物件的setCharacterEncoding()方法指定傳送的編碼方式為MS950，這和JDBC驅動程式的編碼相同，如此JSP程式才能送出正確的SQL指令，將中文欄

21、位內容存入資料庫。11-4-3 使用JDBC連結MySQL資料庫的中文問題-表11-5 SQL語言的資料庫查詢11-5-1 分頁顯示SQL查詢結果11-5-2 SQL語言的基礎11-5-3 SQL查詢指令 - SELECT11-5 SQL語言的資料庫查詢11-5-1 分頁顯示SQL11-5-1 分頁顯示SQL查詢結果-建立Statement物件建立分頁顯示的Statement物件因為ResultSet物件的記錄資料需要使用相關方法來移動記錄指標，如此才能分頁顯示查詢結果，所以在使用Connection物件的方法建立Statement物件時，需要設定指標型態和同步等級，如下所示：stmt = d

22、bCon.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);11-5-1 分頁顯示SQL查詢結果-建立Statement11-5-1 分頁顯示SQL查詢結果-移動記錄指標的方法ResultSet物件移動記錄指標的相關方法11-5-1 分頁顯示SQL查詢結果-移動記錄指標的方法Re11-5-1 分頁顯示SQL查詢結果-分頁顯示1當JSP程式分頁顯示SQL查詢結果時，首先需要計算出ResultSet物件的記錄總數，如下所示：rs.last();int totalRecords = rs.g

23、etRow();在取得記錄總數後，即可計算出這些記錄一共可以分成幾頁來顯示，如下所示：totalPages = totalRecords/pageSize; if ( (totalRecords % pageSize) != 0 ) totalPages+; 11-5-1 分頁顯示SQL查詢結果-分頁顯示1當JSP程式11-5-1 分頁顯示SQL查詢結果-分頁顯示2在計算出記錄數和分頁數後，只需使用URL參數傳入顯示的分頁pageNo，就可以移動記錄指標到指定分頁的第1筆記錄，如下所示：rs.absolute(pageNo-1) * pageSize + 1); 在JSP程式顯示分頁記錄是一個

24、do/while迴圈，因為我們已經使用absloute()方法移動記錄指標到分頁的第1筆記錄，所以可以直接取得欄位值，如下所示：int count = 0;do count+; while ( rs.next() & count 1711-5-3 SQL查詢指令 SELECT(單一查詢條11-5-3 SQL查詢指令 SELECT(單一查詢條件2)文字的欄位可以使用【LIKE】包含運算子配合%萬用字元，此時查詢的條件子句只需包含的子字串就符合條件，例如：學號包含3的子字串，如下所示：SELECT * FROM students WHERE stdno LIKE %3%數值或日期欄位可以使用、=和

25、 17 ORDER BY totalcredit上述查詢結果使用totalcredit欄位排序，預設是由小到大的ASC，如果想倒過來由大到小，只需加上DESC，如下所示：SELECT * FROM students WHERE totalcredit 17 ORDER BY totalcredit DESC11-5-3 SQL查詢指令 SELECT(排序輸出)11-6 SQL語言的資料庫操作11-6-1 在資料表插入、刪除和更新記錄11-6-2 PreparedStatement類別執行SQL指令11-6 SQL語言的資料庫操作11-6-1 在資料表插入、11-6-1 在資料表插入、刪除和更新

26、記錄-插入記錄SQL語言插入記錄指令INSERT可以新增一筆記錄到資料表，INSERT指令的語法格式，如下所示：INSERT INTO table (column1,column2,.) VALUES (value1, value2 , )INSERT指令的注意事項，如下所示：不論欄位或值的清單，都需要使用逗號分隔。INSERT指令VALUES的值，數值不用單引號包圍，字元與日期/時間需要單引號包圍，Access的日期時間需要#符號。INSERT指令的欄位名稱清單，並不需要和資料表定義的欄位數目或順序相同，只需選擇需要新增資料的欄位，但是括號內的欄位名稱順序需和VALUES值的順序相同。11-

27、6-1 在資料表插入、刪除和更新記錄-插入記錄SQL語11-6-1 在資料表插入、刪除和更新記錄-更新記錄UPDATE更新記錄指令是將資料表內符合條件的記錄，都更新成指定的欄位值，語法如下所示：UPDATE table SET column1 = value1 WHERE conditions如果更新欄位不只一個，請使用逗號分隔，其語法格式，如下所示：UPDATE table SET column1 = value1 , column2 = value2 WHERE conditionsUPDATE指令的注意事項，如下所示：WHERE子句是必要元素，如果沒有WHERE子句，資料表內所有記錄欄位

28、都會被更新。更新欄位值是數值不用單引號包圍，字元與日期/時間需要單引號包圍，Access的日期時間需要#符號。11-6-1 在資料表插入、刪除和更新記錄-更新記錄UPDA11-6-1 在資料表插入、刪除和更新記錄-刪除記錄SQL語言刪除記錄指令DELETE是將資料表內符合條件的記錄通通刪除掉。DELETE指令的語法格式，如下所示：DELETE FROM table WHERE conditions上述指令table是資料表，WHERE子句conditions為刪除記錄的條件，口語來說是將符合conditions條件的記錄刪除掉，DELETE指令的注意事項，如下所示：WHERE子句是DELETE

29、指令的必要元素，如果沒有WHERE子句，資料表內的所有記錄都會被刪除。11-6-1 在資料表插入、刪除和更新記錄-刪除記錄SQL語11-6-2 PreparedStatement類別執行SQL指令-1PreparedStatement類別是Statement類別的子介面，PreparedStatement物件可以預先建立SQL指令字串，並且使用參數指定欄位值，以避免欄位資料型態轉換的錯誤，如下所示：String sSQL=INSERT INTO students + (stdno,name,address,birthday,totalcredit) ;sSQL+=VALUES (?,?,?,#

30、+birthday+#,?);11-6-2 PreparedStatement類別執行SQ11-6-2 PreparedStatement類別執行SQL指令-2接著使用prepareStatement()方法建立PreparedStatement物件pstmt，如下所示：PreparedStatement pstmt = dbCon.prepareStatement(sSQL);PreparedStatement物件pstmt的SQL指令參數需要使用setXXX()方法，這些方法是對應ResultSet類別的getXXX()方法，如下所示：pstmt.setString(1, stdno);p

31、stmt.setString(2, name);pstmt.setString(3, address);pstmt.setInt(4, Integer.parseInt(credit);11-6-2 PreparedStatement類別執行SQ11-7 JNDI與連結池11-7-1 連結池與JNDI的基礎11-7-2 在JSP程式使用JNDI與連結池11-7 JNDI與連結池11-7-1 連結池與JNDI的基11-7-1 連結池與JNDI的基礎-說明在JDBC 2.0版定義資料來源（DataSource）介面，可以讓Java應用程式使用此機制取得資料庫連結，換句話說，在JSP程式並不需要撰寫

32、建立資料庫連結的相關程式碼，而是從資料來源取得資料庫連結，如此，在不修改JSP程式情況下，就可以更改資料來源連結的資料庫。當JSP容器實作JDBC 2.0的資料來源（DataSource）介面，就可以將資料庫連結的管理工作交給JSP容器來處理，而不用JSP程式自行處理。例如：Resin的JSP容器已經實作JDBC的資料來源介面，而且支援連結池（Connection Pool）的功能。11-7-1 連結池與JNDI的基礎-說明在JDBC 2.011-7-1 連結池與JNDI的基礎-連結池連結池（Connection Pool）是在維護多個資料庫連結的集合，以便重複使用這些資料庫連結來降低使用資料

33、庫所造成的系統負擔，因為JSP程式建立資料庫連結是一種十分浪費系統資源的操作，如果重複使用已經開啟的資料庫連結，就可以大幅提昇系統效能。連結池的運作是在一開始就建立一定數量的資料庫連結，如同一個池塘，當JSP程式需要使用資料庫連結時，就從池塘中取出沒用使用的資料庫連結給JSP程式使用，在使用完後即歸還連結池，以便其它JSP程式可以重複使用。11-7-1 連結池與JNDI的基礎-連結池連結池（Conn11-7-1 連結池與JNDI的基礎-JNDIJNDI （Java Naming and Directory Interface）提供Java應用程式所需資源的命名服務（Naming Service

34、），簡單的說，如同Internet的DNS命名服務，使用網域名稱即可找到指定的主機資源。JNDI可以先定義資源的JNDI名稱，在Java應用程式只需使用JNDI名稱即可取得所需的資源。11-7-1 連結池與JNDI的基礎-JNDIJNDI （J11-7-2 在JSP程式使用JNDI與連結池-定義JNDI的資料來源與連結池在web.xml或resin.conf檔案是在或標籤下使用子標籤來定義JNDI的資料來源與連結池，如下所示： jdbc/mysql com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource jdbc:mysql:/l

35、ocalhost:3306/school root 123456 8 20 30s 11-7-2 在JSP程式使用JNDI與連結池-定義JNDI11-7-2 在JSP程式使用JNDI與連結池-取得JNDI定義的資料庫連結在JSP程式在JSP程式取得JNDI定義的資料庫連結前，需要匯入一些套件，如下所示：JSP程式使用InitialContext物件的lookup()方法找尋JNDI名稱，其搜尋路徑為java:comp/env，如下所示：Context env=(Context) new InitialContext().lookup(java:comp/env);DataSource ds =

36、 (DataSource) env.lookup(jdbc/mysql);在找到後，即可取得資料庫連結，如下所示：Connection dbCon = ds.getConnection(); 11-7-2 在JSP程式使用JNDI與連結池-取得JNDI11-8 JSTL的資料庫存取標籤庫11-8-1 指定資料來源標籤11-8-2 資料庫查詢標籤11-8-3 資料庫更新標籤11-8-4 交易處理標籤11-8 JSTL的資料庫存取標籤庫11-8-1 指定資料來11-8 JSTL的資料庫存取標籤庫JSTL資料庫存取標籤庫提供資料庫查詢、更新和交易（Transaction）處理標籤，在JSP程式請先使用taglib指引元素匯入資料庫存取標籤庫的URI，如下所示：11