11-1 JDBC的基礎-說明 昇陽公司提出的資料庫中介軟體（Middleware）稱為「JDBC」（Java Database Connectivity），這是一種開放標準的Java程式介面，可以讓Java程式連接資料庫管理系統， 以Java技術來說，就是實作JDBC驅動程式介面（JDBC Driver Interface）的類別，即JDBC AP即JDBC API。
11-1 JDBC的基礎-圖例
11-1 JDBC的基礎-驅動程式種類 JDBC-ODBC橋接驅動程式（JDBC-ODBC Bridge）：Java程式不是直接連接資料庫管理系統，而是透過微軟ODBC的中介軟體來存取資料庫。 原生API的Java驅動程式（A Native-API Partly Java Driver）：將Java程式的JDBC呼叫轉換成專屬資料庫管理系統的原生API呼叫。 Java網路協定驅動程式（A Net-protocol All Java Driver）：將Java程式的JDBC呼叫轉換成資料庫管理系統專屬的網路協定，再由伺服器轉換成資料庫管理系統的API呼叫。 Java原生通訊協定驅動程式（A Native-protocol All Java Driver）：將Java程式的JDBC呼叫直接轉換成資料庫管理系統原生通訊協定的API呼叫，以便客戶端直接連線資料庫伺服器。
11-2 建立MySQL的資料庫連結 11-2-1 安裝MySQL的JDBC驅動程式 11-2-2 使用JDBC連結MySQL資料庫
11-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-INF\lib」資料夾，以Ch11應用程式為例，其完整路徑為「C:\Inetpub\wwwroot\Ch11\WEB-INF\lib」。
11-2-2 使用JDBC連結MySQL資料庫-說明 在安裝好MySQL的JDBC驅動程式後，JSP程式就可以使用JDBC建立資料庫連結，然後透過JDBC API執行SQL指令來存取資料庫的記錄資料。
11-2-2 使用JDBC連結MySQL資料庫-步驟一 步驟一：載入驅動程式 在JSP程式首先需要載入JDBC驅動程式，如下所示： String sDriver = "com.mysql.jdbc.Driver"; Class.forName(sDriver); 上述程式碼的字串sDriver是驅動程式名稱com.mysql.jdbc.Driver，接著使用Class類別方法forName()方法載入驅動程式。
11-2-2 使用JDBC連結MySQL資料庫-步驟二 步驟二：建立Connection連結物件 在載入JDBC驅動程式後，就可以使用DriverManager類別的getConnection()類別方法建立Connection物件dbCon，如下所示： sCon = "jdbc:mysql://localhost:3306/school?user=root&password=123456"; dbCon=DriverManager.getConnection(sCon);
11-2-2 使用JDBC連結MySQL資料庫-步驟三 步驟三：建立JDBC的Statement物件 Statement物件的目的是執行SQL指令，在建立好Connection物件後，就可以使用createStatement()方法建立Statement物件，如下所示： stmt = dbCon.createStatement();
11-3 建立Access的資料庫連結 11-3-1 JDBC-ODBC資料庫連結的基礎 11-3-2 建立資料庫與系統資料來源 11-3-3 使用JDBC-ODBC連結Access資料庫
11-3-1 JDBC-ODBC資料庫連結的基礎-說明 Access並不支援JDBC驅動程式。所以JSP程式需要使用JDBC的JDBC-ODBC橋接驅動程式，透過JDBC-ODBC驅動程式存取ODBC資料來源的Access資料庫。 「ODBC」（Object Database Connectivity）是微軟開發的中介軟體，提供Windows作業系統的應用程式一種標準的資料庫存取方式，能夠存取位在其它電腦上執行的資料庫系統。
11-3-1 JDBC-ODBC資料庫連結的基礎-圖例
11-3-2 建立資料庫與系統資料來源-建立Access資料庫 Access資料庫是學校資料庫School.mdb，在School.mdb資料庫擁有Students資料表，其欄位說明，如下表所示：
11-3-2 建立資料庫與系統資料來源-新增ODBC系統資料來源 在Access建立好資料庫後，就可以在Windows作業系統建立ODBC系統資料來源，筆者準備在Windows XP作業系統的電腦新增Access資料庫School.mdb名為【school_db】的ODBC系統資料來源，如右圖所示：
11-3-3 使用JDBC-ODBC連結Access資料庫-載入驅動程式 在JSP程式載入的驅動程式是透過ODBC存取資料庫，所以載入JDBC-ODBC驅動程式，如下所示： String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; Class.forName(sDriver); 上述程式碼的字串sDriver是驅動程式名稱sun.jdbc.odbc.JdbcOdbcDriver，接著載入JDBC-ODBC驅動程式。
11-3-3 使用JDBC-ODBC連結Access資料庫-JDBC URL 在載入JDBC-ODBC驅動程式後，就可以使用DriverManager類別的getConnection()類別方法建立Connection物件dbCon，如下所示： String sCon = "jdbc:odbc:school_db"; dbCon = DriverManager.getConnection(sCon);
範例 <!-- JSP程式：Ch11_3_3.jsp --> <%@ page contentType="text/html; charset=MS950" import="java.sql.*"%> <html> <head><title>Ch11_3_3.jsp</title></head> <body> <h2>建立JDBC-ODBC資料庫連結</h2><hr> <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; // 驅動程式參數 String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String sCon = "jdbc:odbc:school_db"; try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); if ( dbCon != null ) out.print("建立資料來源連結成功!<br>"); stmt = dbCon.createStatement(); if ( stmt != null ) out.print("建立Statement物件成功!<br>"); stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 } catch(SQLException e) { out.print(e); %> </body> </html>
11-4 JSP的資料庫基本存取 11-4-1 取得資料表的資訊 11-4-2 顯示資料表的記錄資料 11-4-3 使用JDBC連結MySQL資料庫的中文問題
11-4-1 取得資料表的資訊-步驟四 取得資料表資訊是繼續第11-2-2節的步驟三。 步驟四：使用Statement物件執行SQL指令 String sSQL="SELECT * FROM students"; boolean state = stmt.execute(sSQL);
11-4-1 取得資料表的資訊-步驟五 步驟五：取得ResultSet和ResultSetMetaData物件 在使用Statement物件執行SQL指令後，接著可以使用getResultSet()方法取得ResultSet物件，如下所示： ResultSet rs = stmt.getResultSet(); 上述程式碼在取得ResultSet物件後，再使用getMetaData()方法取得ResultSetMetaData物件，如下所示： ResultSetMetaData md = rs.getMetaData(); 在取得ResultSetMetaData物件後，就可以使用相關方法取得資料表的相關資訊。
11-4-1 取得資料表的資訊-方法 ResultSetMetaData物件的相關方法，如下表所示：
範例 <!-- JSP程式：Ch11_4_1.jsp --> <%@ page contentType="text/html; charset=MS950" import="java.sql.*"%> <html> <head><title>Ch11_4_1.jsp</title></head> <body> <h2>取得資料表的資訊</h2><hr> <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; ResultSet rs = null; ResultSetMetaData md = null; // 驅動程式參數 String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String sCon = "jdbc:odbc:school_db"; try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement(); // 建立SQL指令 String sSQL = "SELECT * FROM students"; boolean state = stmt.execute(sSQL); if ( state ) { rs = stmt.getResultSet(); // 取得ResultSetMetaData物件 md = rs.getMetaData(); int count = md.getColumnCount(); String label, type; int size; out.print("資料表欄位數: " + count + "<br>"); for ( int i = 1; i <=count; i++ ) { label = md.getColumnLabel(i); size = md.getColumnDisplaySize(i); type = md.getColumnTypeName(i); out.print(i + ": 名稱: " + label); out.print(" 尺寸: " + size); out.print(" 類型: " + type + "<br>"); } stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 catch(SQLException e) { out.print(e); %> </body> </html>
11-4-2 顯示資料表的記錄資料-步驟四 在這一節筆者準備執行SQL查詢指令取得資料表的記錄資料，步驟是繼續第11-2-2節的步驟三。 步驟四：使用Statement物件執行SQL指令 在JSP程式執行SQL查詢指令可以取得查詢結果的ResultSet物件，這是使用executeQuery()方法取得ResultSet物件，如下所示： rs = stmt.executeQuery(sSQL); 程式碼取得參數SQL指令sSQL查詢結果的ResultSet物件rs，參數的SQL查詢指令可以取得資料表students的所有記錄。
11-4-2 顯示資料表的記錄資料-步驟五 步驟五：使用迴圈取得ResultSet物件的記錄 在取得查詢結果的ResultSet物件後，可以使用while迴圈配合next()方法來顯示記錄，如下所示： while ( rs.next() ) { out.print(rs.getString("stdno")); out.print(rs.getString("name")); out.print(rs.getString("address")); out.print(rs.getDate("birthday")); out.print(rs.getInt("totalcredit")); }
11-4-2 顯示資料表的記錄資料-步驟五 步驟六：關閉連結的Connection和Statement物件 在處理完資料庫的查詢或操作後，JSP程式需要關閉Connection和Statement物件，使用的都是close()方法，如下所示： stmt.close(); dbCon.close();
範例 <!-- JSP程式：Ch11_4_2.jsp --> <%@ page contentType="text/html; charset=MS950" import="java.sql.*"%> <html> <head><title>Ch11_4_2.jsp</title></head> <body> <h2>顯示資料表的記錄資料</h2><hr> <table border="1"> <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; ResultSet rs = null; // 驅動程式參數 String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String sCon = "jdbc:odbc:school_db"; try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement(); // 建立SQL指令 String sSQL = "SELECT * FROM students"; rs = stmt.executeQuery(sSQL); while ( rs.next() ) { // 使用迴圈顯示記錄資料 out.print("<tr>"); out.print("<td>"+rs.getString("stdno")+"</td>"); out.print("<td>"+rs.getString("name")+"</td>"); out.print("<td>"+rs.getString("address")+"</td>"); out.print("<td>"+rs.getDate("birthday")+"</td>"); out.print("<td>"+rs.getInt("totalcredit")+"</td>"); out.print("</tr>"); } stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 catch(SQLException e) { out.print(e); %> </table> </body> </html>
11-4-3 使用JDBC連結MySQL資料庫的中文問題-編碼 在載入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編碼。
11-4-3 使用JDBC連結MySQL資料庫的中文問題-Unicode字串1 在JSP程式顯示透過JDBC取得MySQL資料庫的中文記錄資料時，因為JDBC驅動程式在轉換中文內碼時，一個中文字會被切割成2個字元，例如：0x4175會切割成0x41和0x75。 所以，JSP程式在顯示中文的記錄資料時，需要先將欄位字串還原成完整中文內碼的Unicode字串，這就是toUnicode()方法的功能。
11-4-3 使用JDBC連結MySQL資料庫的中文問題-Unicode字串2 String toUnicode(String s) { if (s == null || s.length() == 0) { return null; } byte[] buffer = new byte[s.length()]; int i, j; for (i = 0, j = 0; i < s.length(); i++) { if ( s.charAt(i) >= 0x100 ) { char c = (char) s.charAt(i); byte[] buf = (""+c).getBytes(); buffer[j++] = (byte) buf[0]; buffer[j++] = (byte) buf[1]; else buffer[j++] = (byte) s.charAt(i); return new String(buffer,0,j);
11-4-3 使用JDBC連結MySQL資料庫的中文問題-表單資料編碼 指定表單傳送資料的編碼 當SQL指令或欄位值使用表單方式傳遞中文內容給JSP程式時，在JSP程式需要指定傳送的編碼方式，如下所示： request.setCharacterEncoding("MS950"); 上述程式碼使用request物件的setCharacterEncoding()方法指定傳送的編碼方式為MS950，這和JDBC驅動程式的編碼相同，如此JSP程式才能送出正確的SQL指令，將中文欄位內容存入資料庫。
11-5 SQL語言的資料庫查詢 11-5-1 分頁顯示SQL查詢結果
11-5-1 分頁顯示SQL查詢結果-建立Statement物件 因為ResultSet物件的記錄資料需要使用相關方法來移動記錄指標，如此才能分頁顯示查詢結果，所以在使用Connection物件的方法建立Statement物件時，需要設定指標型態和同步等級，如下所示： stmt = dbCon.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
11-5-1 分頁顯示SQL查詢結果-移動記錄指標的方法 ResultSet物件移動記錄指標的相關方法
11-5-1 分頁顯示SQL查詢結果-分頁顯示1 當JSP程式分頁顯示SQL查詢結果時，首先需要計算出ResultSet物件的記錄總數，如下所示： rs.last(); int totalRecords = rs.getRow(); 在取得記錄總數後，即可計算出這些記錄一共可以分成幾頁來顯示，如下所示： totalPages = totalRecords/pageSize; if ( (totalRecords % pageSize) != 0 ) totalPages++;
11-5-1 分頁顯示SQL查詢結果-分頁顯示2 在計算出記錄數和分頁數後，只需使用URL參數傳入顯示的分頁pageNo，就可以移動記錄指標到指定分頁的第1筆記錄，如下所示： rs.absolute((pageNo-1) * pageSize + 1); 在JSP程式顯示分頁記錄是一個do/while迴圈，因為我們已經使用absloute()方法移動記錄指標到分頁的第1筆記錄，所以可以直接取得欄位值，如下所示： int count = 0; do { count++; ……… } while ( rs.next() && count < pageSize );
範例 <!-- JSP程式：Ch11_5.jsp --> <%@ page contentType="text/html; charset=Big5" import="java.sql.*"%> <html> <head><title>Ch11_5.jsp</title></head> <%! // 驅動程式參數 String sDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; String sCon = "jdbc:odbc:school_db"; Connection dbCon = null; Statement stmt = null; %> </head> <body> <center><h2>顯示資料庫的查詢結果</h2><hr> <% int pageNo = 1, pageSize = 3; int totalPages = 1; // 取得表單欄位或URL參數資料 String sql = request.getParameter("Sql"); // 取得顯示的頁碼 if ( request.getParameter("Page") != null ) pageNo = Integer.parseInt(request.getParameter("Page")); try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); // 執行SQL指令 ResultSet rs = stmt.executeQuery(sql); // 計算記錄數
rs.last(); // 最後一筆記錄 int totalRecords = rs.getRow(); // 取得最後一筆的記錄數, 即總記錄數 if ( totalRecords > 0 ) { // 有記錄 totalPages = totalRecords/pageSize; // 計算總頁數 if ( (totalRecords % pageSize) != 0 ) totalPages++; // 不能整除, 加一頁 rs.absolute((pageNo-1) * pageSize + 1); // 移動指標到該頁的第1筆記錄 // 表格標籤 out.print("<p align='center'><table border=1>"); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); // 表格標題列 out.print("<tr>"); for (int i = 0; i < columnCount; i++ ) { out.print("<th>"); out.print(rsmd.getColumnLabel(i+1)+"</th>"); } out.print("</tr>"); // 顯示記錄 int count = 0; do { count++; out.print("<td>"); out.print(rs.getString(i + 1) + "</td>"); } while ( rs.next() && count < pageSize ); out.print("</table></p>"); out.print("<p>記錄數: "+ totalRecords +"</p>"); if ( pageNo > 1 ) // 有上一頁 out.print("| <a href='Ch11_5.jsp?Page="+(pageNo-1)+ "&Sql="+sql+"'>上一頁</a> |"); if ( pageNo < totalPages ) // 有下一頁 out.print("| <a href='Ch11_5.jsp?Page="+(pageNo+1)+ "&Sql="+sql+"'>下一頁</a> |"); out.print(" (" + pageNo + "/" + totalPages + ")"); else // 沒有記錄 out.print("<p>沒有找到記錄!</p>"); // 關閉Statement物件 stmt.close(); // 關閉連結 dbCon.close(); catch(SQLException e) { out.print(e); %></center> </body> </html>
11-8-4 交易處理標籤-範例 JSTL的<sql:transaction>標籤可以將多個<sql:query>和<sql:update>標籤的SQL查詢與操作指令視為一個交易，如下所示： <sql:transaction> <sql:query var="count"> SELECT * …… </sql:query> <sql:update var="count"> INSERT ……….. </sql:update> </sql:transaction>