> For the complete documentation index, see [llms.txt](https://clu.gitbook.io/scrapbook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://clu.gitbook.io/scrapbook/49-statement-vs-preparedstatement.md).

# 4.11 Statement, PreparedStatement and CallableStatement

在JDBC的世界裡, 我們常常會透過Statement/PreparedStatement來對DB進行一些操作, 這篇主要是想記錄一下這兩者之間的差異(CallableStatement我沒用過, 以後有機會再補吧).

## Statement

* 通常用於執行靜態的SQL語句並且獲得SQL產生之結果, 通常會看到以下三種執行SQL語句的方法:
  * executeUpdate(String sql): 執行SQL insert/update/delete語句, 回傳受影響資料的數目或是0
  * executeQuery(String sql): 執行回傳單個ResultSet的SQL語句, 回傳類型就是ResultSet
  * execute(String sql): 執行可以回傳多個結果的SQL語句, 回傳類型是boolean, 若回傳的是更新的數目, 就回傳false, 若是ResultSet, 則回傳true

## PreparedStatement

* 相較於Statement, PreparedStatement是一種pre-compiled語句, 可以使用預留位置("?")替SQL帶入變數值, 講白一點, 就是允許你使用參數化查詢.
* PreparedStatement基本上是java.sql package下的一個介面, 用來執行SQL語句, 常見的使用起手式如下:
  * ```java
    public class PreparedStatementDemo {
        public static void main(String args[]) throws SQLException {
            String queryBillingType = "select distinct billing_type from billing where bank = ?";
            Connection conn = DriverManager.getConnection("mysql:\\localhost:3303", "admin", "admin");
            PreparedStatement preparedStatement = conn.prepareStatement(queryBillingType);

            preparedStatement.setString(1, "Citibank");
            ResultSet result = preparedStatement.executeQuery();

            while(result.next()){
                System.out.println("Billing Type: " + result.getString("billing_type"));
            }       
        }
    }
    ```
* * 透過DriverManager取得connection
  * 透過呼叫connection.preparedStatement(sql)獲得PreparedStatement物件
  * DB會對sql進行pre-compile處理(前提是JDBC要支援), 之後這個PreparedStatement被預先編譯好後, 就可以在未來的查詢中重複利用了, 這在需要多次查詢的時候, 基本上是比Statement物件生成的的查詢速度要來得快的, 以下是一個簡單的範例:
  * 在上面的範例中, 若還是使用PreparedStatement進行同樣的查詢, 儘管參數值不同, 譬如帶入其他銀行的名稱作為參數值, DB還是會去呼叫之前compiler已經compile過的執行語句
* 基本上, 在實際工作場合中, 應該都是使用PreparedStatement的場合多於Statement, 以下是PreparedStatement的一些優勢:
  * **動態參數化查詢**: 可以使用帶參數的sql語句, 通過使用相同的sql搭配不同的參數來做查詢, 總比建立一個不同的語句要來得好. 這個部分有另一個名詞叫做"[同構(isomorphism)](https://zh.wikipedia.org/wiki/同构)", 基本上PreparedStatement也是在解決同構問題.
  * **比Statement快**: 因為PreparedStatement會被pre-compile在DBMS中, 使用時一般來說會比普通的查詢更快, 做的工作也更少(因為DB對這個語句的分析/編譯/最佳化已經在第一次查詢之前就完成了). 這也是為什麼在production環境中, 使用PreparedStatement會比Statement更好的原因之一 --- **不要讓你的DB不開心或是有太多額外的負擔, 不然你遲早會倒大楣**.
  * 另外要注意的一點就是: 為了獲得性能上的優勢, 應該**盡量使用參數化sql而不是使用字串連接的方式**, 以下是兩個範例:
    * SQL1: 用字串連接的方式

      ```java
      String loanType = getLoanType();
      PreparedStatement prestmt = conn.prepareStatement("select banks from billing where billing_type = " + billingType);
      ```
    * SQL2: 參數化sql

      ```java
      PreparedStatement prestmt = conn.prepareStatement("select banks from billing where billing_type = ?");
      prestmt.setString(1, billingType);
      ```
  * **可以防止SQL Injection攻擊**:
    * 以下是一個很老梗的case, 某個網站驗證登入的SQL可能長這副樣子:
    * ```java
      sql = "SELECT * FROM users WHERE name = '" + userName + "' and password = '"+ passWord +"';";
      ```
    * 然後故意填入這些東西:
    * ```java
      userName = "1' OR '1' = '1";
      passWord = "1' OR '1' = '1";
      ```
    * 這樣sql就會變成:
    * ```java
      sql = "SELECT * FROM users WHERE name = '1' OR '1'='1' and password = '1' OR '1'='1';";
      ```
    * 這表示where條件恆為true, 就等於下了這條:
    * ```java
      sql = "SELECT * FROM users";
      ```
    * 這樣就可能可以做到無帳密登入, 或是人家更壞一點, 在裡面綁上"DROP TABLE xxx"之類的東西...
    * 不過, 在使用PreparedStatement的參數化查詢時, 基本上可以避免大多數的SQL Injection. 在使用參數化查詢的情況下, DB不會將參數視為SQL指令的一部分來處理, 而是會於DB對SQL的compile結束後, 才套用參數運作, 故儘管含有破壞性指令, 也不會被DB執行.
    * 另外要補充的是, 前面那個塞入惡意指令的方式(包含單引號字元的那個), 可以透過字元取代來避免攻擊, 譬如:
    * ```java
      sql = "SELECT * FROM users WHERE name = '" + userName + "';";
      ```
    * 傳入的字串可能是:
    * ```java
      userName = " 1' OR 1=1";
      ```
    * 然後我們可以把一個單引號替換成兩個單引號:
    * ```java
      userName = " 1'' OR 1=1";
      ```
    * 結果就會變這樣:
    * ```java
      sql = "SELECT * FROM users WHERE name = '1'' OR 1=1';";
      ```
    * 所以DB會去找name = "1'' OR 1=1"的紀錄, 從而避免了攻擊.
  * 比起散亂的字串連結查詢, PreparedStatement可讀性更高, 也比較安全.
* 雖然這樣看起來, PreparedStatement好像很不錯, 但其還是有一定的限制存在, 例如為了防止SQL Injection, 其不允許一個"?"參數裡包含多個值, 若你的查詢子句裡面使用到了"in(xxx,xxx...)"這種東西的話, 就有點麻煩了, 譬如下面這段SQL就不會回傳結果:
  * ```java
    sql = "SELECT * FROM billing WHERE billing_type IN (?)";
    preparedSatement.setString(1, "'credit card', 'cash', 'apple pay'");
    ```
  * 解法可以參考這篇: [點我](https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives)
* PreparedStatement跟java.sql.Connection是相關聯的, 記得用完PreparedStatement之後再關閉connection, 不然PreparedStatement就不能用了.
* 預留位置"?"的索引位置是從1開始不是從0開始, 你如果寫0會拋出"java.sql.SQLException invalid column index"這種exception.

## CallableStatement

* 主要用於Stored Procedure


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://clu.gitbook.io/scrapbook/49-statement-vs-preparedstatement.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.