機房使用資料庫注意事項執行 JSP 程式三步驟使用 JDBC 連結 MySQL 資料庫 SQL 語言 JSP 的資料庫基本存取 - 新增、刪除、更新分頁顯示 SQL 查詢

大綱 • 機房使用資料庫注意事項 • 執行JSP程式三步驟 • 使用JDBC連結MySQL資料庫 • SQL語言 • JSP的資料庫基本存取-新增、刪除、更新 • 分頁顯示SQL查詢 • PreparedStatement類別執行SQL指令

機房使用資料庫注意事項 • 步驟1:開啟虛擬xp • 步驟2:開啟resin • 步驟3:用phpmyadmin建立資料庫登入資料庫 http://localhost/phpMyAdmin/ 使用者名稱：root 密碼：root

執行JSP程式三步驟 • Windows作業系統的記事本就可以編輯JSP原始程式碼 • 在Windows XP請執行「開始/所有程式/附屬應用程式/記事本」指令啟動記事本，如下圖所示： STEP1: 存成檔名為1.jsp的檔案  <html> <head> <title> 1.jsp </title> </head> <body> 測試JSP程式的執行......... 1 + 1 = <%= 1+1 %> </body> </html> STEP2: 將1.jsp檔移至C:\resin-3.1.10\ resin-3.1.10\webapps\ROOT STEP3: 輸入網址:http://localhost:8080/1.jsp

使用JDBC連結MySQL資料庫 • 在安裝好MySQL的JDBC驅動程式後，JSP程式就可以使用JDBC建立資料庫連結，然後透過JDBC API執行SQL指令來存取資料庫的記錄資料。 • 4步驟: • 步驟1: JSP程式首先需要載入JDBC驅動程式 • 步驟2: 建立Connection連結物件 • 步驟3:建立JDBC的Statement物件 • 步驟4: 關閉Connection、Statement物件 • http://localhost:8080/ch6/ch6.jsp

An Example-隨堂練習  <%@ page contentType="text/html; charset=MS950" import="java.sql.*"%> <html> <body> <h2>建立JDBC資料庫連結</h2><hr> <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; // 驅動程式參數 String sDriver = "com.mysql.jdbc.Driver"; String sCon = "jdbc:mysql://localhost:3306/school?user=root&password=123456"; try { Class.forName(sDriver); // 載入 JDBC driver dbCon = DriverManager.getConnection(sCon); // 建立資料連結 if ( dbCon != null ) out.print("建立資料來源連結成功! "); stmt = dbCon.createStatement(); // 建立Statement物件 if ( stmt != null ) out.print("建立Statement物件成功! "); stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 }catch(SQLException e) { out.print(e); } %> </body> </html>

SQL語言的基礎 • 「SQL」（Structured Query Language） • 公認的關聯式資料庫建立與資料之標準語言 • SQL指令語法可以用來查詢、新增、刪除和更新資料庫的記錄 • 目前Access、SQL Server、DB2、MySQL、MySQLMaxDB、Oracle和Sybase等資料庫管理系統都是以SQL語言作為標準的資料庫語言。

SQL語言的基礎-3種類 • 資料定義語言（Data Definition Language，DLL） • 定義資料表名稱、屬性名稱、屬性資料型態…等。 • 資料操作語言（Data Manipulation Language，DML） • 資料的查詢(select)、新增(insert)、刪除(delete)和更新(update)指令。 • 資料控制語言（Data Control Language，DCL） • 屬於資料庫安全設定和權限管理的相關指令。

Jsp網頁與sql語言 • 在Jsp網頁中，必須透過sql語言操作資料庫 • 利用statement物件，執行sql敍述，而連結資料庫取得資料 Jsp網頁資料庫 SQL語言 Statement物件

SQL語法說明所使用的符號 • 英文字母大寫：為SQL使用的保留字 • 「|」選擇符號：使用於某幾個語法 • { }大括號：「 { }」通常與「|」一起使用 • 「,」：分隔欄位項目 • 「…」省略符號

SQL查詢指令 – SELECT(語法) • SQL的資料查詢指令只有一個SELECT指令，完整指令語法如下所示： SELECT 欄位1, 欄位2 FROM 資料表 WHERE conditions • 上述SELECT指令的欄位1~2為記錄的欄位，conditions是查詢條件，指令使用口語來說是「從資料表取回符合WHERE子句條件的記錄，顯示欄位1和2」。

WHERE子句查詢條件4類 • 文字查詢條件 • 數字查詢條件 • 日期查詢條件 • 多重查詢條件

SQL查詢指令 – SELECT • 「*」記錄欄位 • 在前述SQL語法的欄位1~2是取回的資料表欄位，我們可以使用"*"符號代表所有欄位，表示取回資料表記錄的所有欄位，如下所示： • 上述指令沒有WHERE子句，所以將資料表內所有的記錄和欄位取回。 SELECT * FROM students

SQL查詢指令 – SELECT 文字查詢條件 • ‘文字欄位’要加上單引號， • 例如：’93001’，如下所示： • SELECT * FROM students WHERE stdno='93001'

SQL查詢指令 – SELECT 文字查詢條件 • 使用【LIKE】查詢資料包含某字串 • LIKE+ 萬元字元 +已知部份條件 • 例如：學號包含’3’的子字串，如下所示： • SELECT * FROM students WHERE stdno LIKE '%3%'

SQL查詢指令 • 建立Statement物件執行SQL指令 • String sSQL="SELECT * FROM students"; • boolean state = stmt.execute(sSQL); • 在使用Statement物件執行SQL指令後，接著可以使用getResultSet()方法取得ResultSet物件 ResultSetrs = stmt.getResultSet(); • 使用getMetaData()方法取得ResultSetMetaData物件 ResultSetMetaDatamd = rs.getMetaData();

An Example (取得資料表的資訊) <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; // 驅動程式參數 String sDriver = "com.mysql.jdbc.Driver"; String sCon = "jdbc:mysql://localhost:3306/school?user=root&password=123456"; ResultSetrs = null; ResultSetMetaDatamd = null; try { Class.forName(sDriver); // 載入 JDBC driver dbCon = DriverManager.getConnection(sCon); // 建立資料連結 if ( dbCon != null ) out.print("建立資料來源連結成功! "); stmt = dbCon.createStatement(); // 建立Statement物件 if ( stmt != null ) out.print("建立Statement物件成功! "); //建立SQL指令 String sSQL = "Select * from student"; boolean state = stmt.execute(sSQL); if(state){ rs = stmt.getResultSet(); md = rs.getMetaData(); int count = md.getColumnCount(); out.print("資料欄位數:" + count); } stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 }catch(SQLException e) { out.print(e);} %>

SQL查詢指令 – SELECT • 數字、日期查詢條件 • 數值或日期欄位可以使用<>、>、<、>=和<=不等於、大於、小於、大於等於和小於等於等運算子建立多樣化的查詢條件。 • 數值欄位不需要額外字元括起， • 例如：總學分大於17，如下所示： • SELECT * FROM students WHERE totalcredit > 17

SQL查詢指令 – SELECT 多重查詢條件 • WHERE子句的查詢條件可以使用AND和OR邏輯運算子連接，其基本語法，如下所示： • AND且運算子：連接的前後條件都必須成立，整個條件才成立。例如：學號包含"3"且姓名有"會"子字串，如下所示： • SELECT * FROM students WHERE stdno LIKE '%3%' AND name LIKE '%會%' • OR或運算子：連接的前後條件只需任何一個成立即可。例如：學號包含"6"或姓名有"會"子字串，如下所示： • SELECT * FROM students WHERE stdno LIKE '%6%' OR name LIKE '%會%'

SQL查詢指令 – SELECT 排序輸出 • SQL的查詢結果可以指定欄位進行由小到大，或由大到小排序，只需在SELECT指令的最後加上ORDER BY子句即可，如下所示： • 上述查詢結果使用totalcredit欄位排序，預設是由小到大的ASC，如果想倒過來由大到小，只需加上DESC，如下所示： • SELECT * FROM students • WHERE totalcredit > 17 ORDER BY totalcreditDESC SELECT * FROM students WHERE totalcredit > 17 ORDER BY totalcredit

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

An Example (新增記錄) String stud_id, name, address, phone, email; stud_id ="P112"; name = "會安"; phone = "0911147258"; address = "111111111"; email = "158@com.tw"; // 建立插入記錄的SQL指令 String sSQL = "INSERT INTO student (stud_id, name, phone, address, email) VALUES ('"+stud_id+"','"+name+"','"+phone+"','"+address+"','"+email+"')"; try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement(); // 執行SQL指令插入記錄 stmt.executeUpdate(sSQL); out.print("成功插入記錄:" + stud_id+ " "); stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 } catch(SQLException e) { out.print(e); }

JSP的資料庫基本存取-刪除記錄 • DELETE指令的語法格式，如下所示： DELETE FROM table WHERE conditions • table是資料表，conditions為刪除記錄的條件口語來說是「將符合conditions條件的記錄刪除掉」 • DELETE指令的注意事項，如下所示： • WHERE子句是DELETE指令的必要元素，如果沒有WHERE子句，資料表內的所有記錄都會被刪除。

An Example (刪除記錄) <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; // 驅動程式參數 String sDriver = "com.mysql.jdbc.Driver"; String sCon = "jdbc:mysql://localhost:3306/school?user=root&password=123456"; String stud_id; stud_id ="P112"; // 建立刪除記錄的SQL指令 String sSQL = "DELETE FROM student WHERE stud_id ='"; sSQL += stud_id + "'"; out.print("刪除記錄的SQL指令:" + sSQL + " "); try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement(); // 執行SQL指令刪除記錄 stmt.executeUpdate(sSQL); out.print("成功刪除記錄:" + stud_id + " "); stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 } catch(SQLException e) { out.print(e); } %>

JSP的資料庫基本存取-更新記錄 • UPDATE更新記錄指令是將資料表內符合條件的記錄，都更新成指定的欄位值，如果更新欄位不只一個，請使用逗號分隔，語法如下所示： • UPDATE指令的注意事項，如下所示： • WHERE子句是必要元素，如果沒有WHERE子句，資料表內所有記錄欄位都會被更新。 • 更新欄位值是數值不用單引號包圍，字元與日期/時間需要單引號包圍。 UPDATE table SET column1 = 'value1' , column2 = 'value2' WHERE conditions

An Example (更新記錄) <%@ page contentType="text/html;" import="java.sql.*"%> <body> <% Connection dbCon = null; // 宣告物件變數 Statement stmt = null; // 驅動程式參數 String sDriver = "com.mysql.jdbc.Driver"; String sCon = "jdbc:mysql://localhost:3306/school?user=root&password=123456"; String stud_id, name, address; stud_id ="P112"; name=“Lin"; address=“Taiwan"; // 建立更新記錄的SQL指令 String sSQL = "UPDATE student SET "; sSQL += "address='" + address + "', "; sSQL += "name='" + name+"'"; sSQL += " WHERE stud_id='" + stud_id + "'"; out.print("更新記錄的SQL指令:" + sSQL + " "); try { // 載入 JDBC driver Class.forName(sDriver); // 建立資料連結和Statement物件 dbCon = DriverManager.getConnection(sCon); stmt = dbCon.createStatement(); stmt.executeUpdate(sSQL); // 執行SQL指令更新記錄 out.print("成功更新記錄:" + stud_id + " "); stmt.close(); // 關閉Statement物件 dbCon.close(); // 關閉連結 } catch(SQLException e) { out.print(e); } %> </body>

機房使用資料庫注意事項執行 JSP 程式三步驟使用 JDBC 連結 MySQL 資料庫 SQL 語言 JSP 的資料庫基本存取 - 新增、刪除、更新分頁顯示 SQL 查詢