# JDBC 小吳 20210423

###### tags: `JDBC`

# 模組1 - JDBC API簡介

## ==JDBC API設計目的==
## 資料庫種類

- ==關聯式資料庫(Relational DataBase Management System)==

1. 以表格方式儲存與呈現資料，再用數學集合論為基礎，將表格之間建立關聯以處理複雜的資料關係
2. 如Oracle Database, MySQL, 微軟SQL Server, IBM的DB2等
<br>

- ==非關聯式資料庫(又稱NoSQL)==

1. 分散式進行資料儲存，可區分三大類：文檔儲存、圖形關係儲存與鍵值儲存(key – value)
2. 如Google的BigTable, MongoDB, Cassandra, Redis等

<br>

---

## 程式與資料庫通訊(1)

- 資料庫本身是一個獨立運行的應用程式，所以我們設計的應用程式得利用 ==網路通訊協定== 對資料庫進行指令交換，以便進行資料的CRUD ==(增刪改查)==


<br>

- 但實作上我們會使用一組專門與資料庫進行通訊協定的類別庫來簡化與資料庫溝通的程式撰寫


<br>

---

## 程式與資料庫通訊(2)

- 問題：
1. 不同廠商資料庫的通訊協定都不一樣，需使用不同類別庫撰寫程式
2. 遇到更換資料庫，程式碼也幾乎跟著要重寫
3. 應用程式被類別庫綁死，跨平台議題考量(?)
<br>

- 解決：


<br>

---

## ==JDBC API與關聯式資料庫==
## 資料庫種類

- Java提供一組++資料庫存取API++，名為JDBC ==(Java DataBase Connectivity)==

##### 備註
JDBC是搭配關聯式資料庫的API

<br>

- JDBC是 ==用於執行SQL的解決方案==，Java應用程式開發人員使用JDBC的標準介面，而資料庫廠商則對介面進行實作。因此應用程式開發人員就不需接觸到底層資料庫的驅動程式

- JDBC API使得應用系統開發者能使用相同的介面名稱與方法來存取資料庫的資料，讓程式設計師能專心於應用系統的開發，無需顧慮到不同廠商所提供的不同資料庫系統


<br>
<br>

---

## ==java.sql套件==
## JDBC API套件

- JDBC API已包含在JDK裡

1. ==java.sql==：JDBC的核心API，用來存取資料庫資料，基本功能

2. ==javax.sql==：JDBC Extension API，支援進階資料庫存取功能
<br>

- JDBC標準API分為兩個部份

1. JDBC應用程式開發者介面 (Application Developer Interface)
2. JDBC驅動程式開發者介面 (Driver Developer Interface)

##### 備註
驅動程式為資料庫廠商實作，一般開發者無須瞭解

<br>

---

## java.sql 示意圖


<br>

---

# 模組2 - JDBC Driver

## ==JDBC Driver作用==
## JDBC Driver


##### 備註
分層架構設計目的就是為了降低相依性

<br>

---

## ==四種JDBC Driver==
## Type 1 Driver

- JDBC-ODBC Bridge Driver：
為JDK安裝即附，將JDBC的運作轉成ODBC的機制來連接資料庫，存取速度與功能均有受限，彈性不足，建議在無其它driver可以使用時才用Type1的driver


##### 備註
JDK 8已將ODBC相關類別庫移除

<br>

---

## Type 2 Driver

- Native API Driver ：
此類型driver會以 ==原生(Native)== 方式呼叫資料庫提供的原生程式庫(通常是C/C++實作)，因為採用原生方式，++故存取速度較Type1快++，==但沒有達到跨平台的目標==，需要在各平台先行安裝資料庫所屬的原生程式庫


<br>

---

## Type 3 Driver

- JDBC-Net Driver：
此類型driver會將JDBC呼叫轉換為特定的網路協定(Protocol)，由中介伺服器或元件跟資料庫進行操作，使用此類型driver的==好處是軟體架構可獲得彈性，但速度會較慢==


<br>

---

## Type 4 Driver

- Native Protocol Driver：
此類型driver通常由資料庫廠商直接提供，會將JDBC呼叫轉換為與資料庫特定的網路協定，driver可完全用Java技術實現，因此==達到跨平台功能，效能也有不錯表現，為業界最常見的driver類型==


<br>

---

## 下載與設定JDBC Driver

- JDBC Driver可以從各資料庫網站下載並取得，基本上都是免費取得，而Type 4 Driver通常都是以JAR檔的形式打包好提供給程式設計師們使用

- 在Eclipse的Java Project裡，需要將JAR引入給專案才能使用，但通常會再建議將下載到的JAR檔放置於我們自行設定的classpath下，這樣就能獨立環境也能正常運作

- Eclipse的Java Project引入JAR檔可參考以下圖文操作說明：

<br>


<br>


<br>


<br>

---

# 模組3 - JDBC Driver註冊

## ==JDBC Driver三種註冊==
## 載入JDBC Driver

- 載入JDBC Driver有 ==(3+1)== 種方式：
1. Class Loader方式 - ++類別載入器++ ==(常用)==
2. register方式 - ++會產生共兩個驅動物件，可能會導致資源釋放的問題++ ==(不要用!!!)==
3. System Property方式 - ++看情況，若是同時要註冊多種驅動就可以用此方法++
<br>

- 採用 ++Class Loader++ 方式 ==產生Driver實體==，並++註冊到DriverManager的驅動程式註冊表單++中
- Class.forName(“com.mysql.jdbc.Driver”);
- ==Class.forName(“com.mysql.cj.jdbc.Driver”); (MySQL 8以後的驅動名稱)==
- Class.forName(“oracle.jdbc.driver.OracleDriver”);
- Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”);
- Class.forName(“com.ibm.db2.jdbc.app.DB2Driver”);

##### 備註
"forName"傳入要載入的類別長名稱(套件+類別名)

<br>

- 採用register方式產生Driver實體，並將自己註冊到Driver Manager驅動程式註冊表單中
- DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
- DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

<br>
<br>

- 採用System Property方式
- System.setProperty(“jdbc.drivers”, “com.mysql.jdbc.Driver”);
- 可以包含數個drivers，彼此之間以冒號(:)隔開
- ex:

<br>

---

## ==Class Loader==
## 類別載入器 (Class Loader)

- Java在執行時，需要某類別才會載入.class檔案，而非在程式啟動就載入所有類別，才能讓系統資源的運用更有效率。而載入.class檔案，就會產生一個java.lang.Class的物件實體來對應該檔案，程式設計師即可利用該Class物件實體而更進一步地取得該類別的相關資訊，此機制就稱為 ==反射或映射(Reflection)==

- 需要某類別的時機：
1. 使用指定類別要產生物件實體時 (static區塊會執行)
2. 使用`Class.forName()`方法
3. 使用ClassLoader的`loadClass()`方法 (static區塊不執行)

- JDK 8以前類別載入器可分為三個層級：
Bootstrap Loader → ExtClassLoader → AppClassLoader ==(對應基礎 java模組28的類別路徑)==

<br>

---

## ==自動載入JDBC Driver (JDBC 4.0)==

- 在JDBC 4.0，也就是對應到Java SE6，增加了簡化資料庫驅動載入

- 不需用再呼叫Class.forName方法載入驅動，但在包裝JDBC驅動程式的JAR檔裡，必須要額外有一個「META-INF/services/java.sql.Driver」的檔案

- 該檔案裡要有註明驅動程式的類別名稱，才能達到簡化載入的操作

##### 備註
須注意Java、JDBC版本和驅動程式版本是否有支援

<br>

---

# 模組4 - 建立與關閉資料庫連線

## ==建立資料庫連線==
## 資料庫位置 (URL)

- 資料庫URL為一字串，用以說明連接某一特定資料庫所需要的資訊，包含資料庫位址(IP)、埠號(Port Number)、名稱等

- 資料庫URL格式： ==jdbc : <Subprotocol> : <Subname>==

- 範例:

“jdbc:mysql://” + serverName + “:3306/” + Schema Name
→ jdbc:mysql://localhost:3306/HR ==?serverTimezone=Asia/Taipei==

MySQL 8.0 以後須在URL後面加上 ==時區設定== 才能正常連接

<br>

  - “jdbc:oracle:thin:@” + serverName + “:1521” + SID
  → jdbc:oracle:thin:@localhost:1521:xe (xe指的是Oracle的Express版)

  - “jdbc:sqlserver://” + serverName + “:1433:databaseName=” + DBName
  → jdbc:sqlserver://localhost:1433:databaseName=HR

<br>

##### 備註
埠號:
1. 只要有網路通訊就一定會使用到
2. 範圍從0~65535(共65536個)
3. 範圍從0~1024不使用，因為已經有相關的協定佔用了

<br>

---

## Connection

- Connection代表資料庫一個連線通路，傳遞一系列的SQL指令給資料庫，並管理這些指令的認可與中止

- ==Connection con = DriverManager.getConnection(String url, String userID, String password);==
1. 以資料庫URL做為引數產生Connection物件來連接資料庫
2. DriverManager在驅動程式註冊表單搜尋driver，一旦driver認得此url，便會依據url的資訊連接資料庫

- ==Connection非常珍貴，使用完畢後務必完成歸還的動作！==

<br>

---

## ==關閉資料庫連線==
## 關閉連線 – close( )

- close( )：歸還資源

1. Connection是極為重要的資源，建議寫在finally{ }裡
2. 範例:

    finally {
     if (con != null) {
      try {
       con.close();
      } catch (SQLException e) {…}
     }
    }

<br>

- ==對con檢查不為null== 是為了避免連線尚未建立前就發生例外，導致例外訊息再堆疊NullPointerException訊息而增加除錯時的複雜度

<br>

---

## ==AutoCloseable介面==
## try-with-resources

- 以往程式設計師需自行關閉資源，Java 7開始try – with – resources可確保物件(資源)最後一定會關閉

- 實作 ==java.lang.AutoCloseable== 與 ==java.io.Closeable== 介面的物件皆可視為資源

- 若將有實作AutoCloseable介面的物件置於try{ }裡，try{ }結束時，Java即會自動將該物件close

- JDK 7裡，Connection介面 extends AutoCloseable介面

- 由於仍有可能使用JDK 7以前的版本，故還是寫在finally{ }較保險

<br>

---

# 模組5 - 靜態SQL指令 – Statement

## ==Statement介面==

- 藉由Connection的方法： ==Statement createStatement( )== 來建立與取得Statement物件

- 藉由++Statement++物件，才能==執行靜態的SQL指令==並與資料庫交談

- 因為是從Connection產生出來，所以在使用完畢時也得記得歸還資源：close()方法

- 請注意資源歸還順序： ==越晚建立，越早歸還==

<br>

---

## ==更新與查詢指令==
## 執行SQL指令

- 執行SQL指令的主要兩個方法
1. ==ResultSet executeQuery(String sql)==：查詢資料庫，傳回ResultSet
- 用於SQL的SELECT指令
2. ==int executeUpdate(String sql)==：更新資料庫，傳回成功更新的筆數
- 用於SQL的INSERT、UPDATE與DELETE指令

- SQL指令在程式碼裡是宣告為字串型別，並在呼叫execute相關方法時做為參數傳入，即可送出給資料庫達到交談目的

<br>

---

# 模組6 - ResultSet介面

## ==ResultSet游標機制==
## ResultSet介面

- 當執行`executeQuery()`方法，該方法即傳回從資料庫查詢的結果資料列
- 例如 SELECT * FROM EMPLOYEE後，可得到表格結構的結果


<br>

---

## ==移動游標操作==
## 移動資料列游標

- 移動資料列游標的相關方法:
1. `next()`、`previous()`、`first()`、`last()`、`beforeFirst()`、`afterLast()`
2. `next()`傳回boolean值
3. true代表游標停留位置有指向一個資料列，false即代表沒有指向資料列

- 測試目前資料列指標位置的方法:
`isFirst()`、`isLast()`、`isBeforeFirst()`、`isAfterLast()`

##### 備註
部份移動游標方法為JDBC 2.0時新增，需在建立Statement時設定！

<br>

---

## ==使用游標取得欄位資料==
## 移動資料列游標

- 取出查詢結果資料欄的方法:
1. 有 `type getType(int columnIndex)` 或 `type getType(String columnName)`
- ==index從1開始==，一般來說使用index較有效率
- 但要小心查詢++欄位的順序關係++ (Select *: 根據當初建立表格所宣告的欄位順序)

2. 其餘參考方法:

`getBoolean()`
`getByte()`
`getShort()`
`getInt()`
`getLong()`
`getFloat()`
`getDouble()`
`getString()`
`getDate()`
`getTime()`
`getTimestamp()`
`getBinaryStream()`

- 其它方法:
`int getRow()`：取得目前的列編號，若傳回0，表示游標不在任一列上，可能在第一列前或最後一列後

<br>

---

# 模組7 - 動態SQL指令 – PreparedStatement
## ==串接組成動態SQL指令==
## SQL指令參數動態改變

- 試想系統操作時，每次要新增的資料或是要查詢資料的條件不會永遠都是相同的，但Statement物件只能讓我們執行靜態(也就是固定不變)的SQL指令。

- 因為++SQL指令在Java裡宣告為String++，所以剛好 ==結合變數與文字串接功能==，就可以達到SQL指令參數可隨著程式執行而動態改變

- 範例：


<br>

---

## ==SQL Injection== (SQL 注入攻擊)

- 若是剛剛使用者輸入userName與userPassward為’ 1 OR ‘1’ = ‘1，會發生什麼事情呢？

- 答案：(NAME = '1' OR '1'='1') and (PASSWORD = '1' OR '1'=‘1’)
-> 結果為true，條件成立

- 以上情況，輸入文字夾帶SQL指令內容，若是未做檢查處理，很有可能就被組合成一個 ==合法且有意義的SQL指令==，讓資料庫做出完全不一樣的任務，造成被有心人士入侵或是破壞

<br>

---

## ==PreparedStatement介面==
## 預先處理的PreparedStatement

- PreparedStatement為++Statement的子介面++，因此也可以執行SQL指令

- 藉由Connection的方法： `PreparedStatement prepareStatement(String sql)`
資料庫可預先編譯SQL指令，執行效能較快，常用於需變數傳遞且重複執行的SQL指令

- 因為是從Connection產生出來，所以在使用完畢時也得記得歸還資源： ==`close()方法`==

<br>

---

# 模組8 - 動態SQL指令運用

## ==PreparedStatment==

- 資料庫會將SQL指令預先編譯，可避免資料庫重複解析同一個SQL指令，執行效能較好，==因為可以動態處理SQL指令，可避免SQL Injection攻擊==

<br>

- 對SQL指令裡，++未知或是需動態改變的參數設定為++ =="?"==，並在每次++執行前++，對 =="?"== ++置入不同的值++，透過 `setType(int idx, Type value)` ++提供參數值++

-> ==? 的索引值也是從1開始==
<br>

- PreparedStatement執行動態SQL指令的兩個方法
1. `ResultSet executeQuery()`
2. `int executeUpdate()`

##### 備註
上述兩個()內沒有參數，因為SQL指令已經預先交給資料庫了

<br>

---

# 模組9 - 預存程序(CallableStatement)

## ==預存程序相關==
## CallableStatement

- ++繼承PreparedStatement介面++，所以也有++動態參數功能++ ==(搭配 “?”)==

- `CallableStatement prepareCall(String sql)`：使用 ==預存程序(stored procedure)==，預存程序已事先內建在資料庫中，通常比預先編譯的效能佳

- 預存程序(stored procedure)在應用程式執行前，已事先編譯好在資料庫裡，因此++預存程序的效能一般比預先編譯的敘述快++

- ==程式設計師只需知道預存程序的名稱與輸入輸出的參數，無需暸解SQL指令==

- 不同的資料庫有不同的預存程序語法；對三階開發人員來說，意義不大

##### 備註
Statement的3種執行方法:
1.executeUpdate()
2.executeQuery()
3.execute(): 執行"未知"的SQL指令，例如-預存程序