# PreparedStatement

## PreparedStatement

參考資料:JDBC為什麼要使用PreparedStatement而不是Statement(<http://www.importnew.com/5006.html>)

Java 提供了三種API來執行查詢語句

Statement - 通用查詢

PreparedStatement - 參數化查詢

CallableStatement - 存儲過程

## PreparedStatement 是什麼?

import java.sql.PreparedStatement;

使用PreparedStatement，DBMS會對Query 進行預編譯處理，將語句的部分儲存起來，以後雖然輸入的參數不同，一樣可以拿出來用。這樣一來，它比Statement 的查詢速度更快。

### 參數化查詢

```
SELECT * from table where field = ?
```

`?`代表參數的位置，因為可以用不同的參數調用它。

### PreparedStatement 比 Statement 更快

PreparedStatement 擁有更佳的性能優勢，SQL語句會在DBMS進行預編譯，並且將執行計畫緩存起來。後續可以用參數化查詢，而不是字串追加。

```
(X)
String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=" + loanType);

(O)
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);
```

### PreparedStatement 可以防止 SQL注入攻擊

在使用參數化查詢的情況下，DBMS是拿編譯完的語句加上參數去運行。 不會將參數視為SQL Query的一部分。因此就算參數含有破壞性指令，也不會被DBMS所運行。

#### 另外一種防止 SQL注入攻擊 的方式

在組SQL Query的時候，先對使用者傳入的參數，單引號都取代為連續2個單引號，以此去避免SQL 注入。

### 可讀性

比起凌亂的字串追加，利用參數去提升可讀性。

## PreparedStatement的局限性

儘管PreparedStatement非常實用，但是它仍有一定的限制。 1.為了防止SQL注入攻擊，PreparedStatement不允許一個佔位符（？）有多個值，在執行有**IN**子句查詢的時候這個問題變得棘手起來。下面這個SQL查詢使用PreparedStatement就不會返回任何結果

```
SELECT * FROM loan WHERE loan_type IN (?)
preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");
```

## 部分總結

1. PreparedStatement可以寫參數化查詢，比Statement能獲得更好的性能。
2. 對於PreparedStatement來說，數據庫可以使用已經編譯過及定義好的執行計劃，這種預處理語句查詢比普通的查詢運行速度更快。
3. PreparedStatement可以阻止常見的SQL注入式攻擊。
4. PreparedStatement可以寫動態查詢語句
5. PreparedStatement與java.sql.Connection對像是關聯的，一旦你關閉了connection，PreparedStatement也沒法使用了。
6. “?”叫做佔位符。
7. PreparedStatement查詢默認返回FORWARD\_ONLY的ResultSet，你只能往一個方向移動結果集的游標。當然你還可以設定為其他類型的值如：”CONCUR\_READ\_ONLY”。

   8.不支持預編譯SQL查詢的JDBC驅動，在調用connection.prepareStatement(sql)的時候，它不會把SQL查詢語句發送給數據庫做預處理，而是等到執行查詢動作的時候（調用executeQuery()方法時）才把查詢語句發送個數據庫，這種情況和使用Statement是一樣的。

   9.佔位符的索引位置從1開始而不是0，如果填入0會導致*java.sql.SQLException invalid column index*異常。所以如果PreparedStatement有兩個佔位符，那麼第一個參數的索引時1，第二個參數的索引是2。

## JDBC

目前有提供下列這兩種 Template\
JdbcTemplate\
NamedParameterJdbcTemplate\
這兩者都是使用 PreparedStatement 去執行查詢，這些都是防止 SQL Injection的措施，在SQLInjection的處理上沒有區別。

## NamedParameterJdbcTemplate 的 範例

### where .. in&#x20;

```
public List<SmsDto> selectDataBySmsId(String... smsId) {
    String sql = " SELECT * FROM " + SmsDto.TABLE_NAME +
            " WHERE sms_id in (:sms_id) ";
    
    //不吃Array，要先轉換成List
    List<String> list = new ArrayList<>(Arrays.asList(smsId));

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("sms_id", list);

    return this.getNamedParameterJdbcTemplate().query(sql, in, new BeanPropertyRowMapper<>(SmsDto.class));
}
```

### where many column

參考資料:(<https://stackoverflow.com/questions/4164949/how-to-protect-against-sql-injection-when-the-where-clause-is-built-dynamically>)

```
public List<SmsDto> test1(String smsId,String smsName) {
    String sql = " SELECT * FROM " + SmsDto.TABLE_NAME +
            " WHERE coalesce(:sms_id, sms_id) = sms_id " +
            " and coalesce(:sms_name, sms_name) = sms_name ";

    MapSqlParameterSource in = new MapSqlParameterSource();
    in.addValue("sms_id", smsId);
    in.addValue("sms_name", smsName);

    return this.getNamedParameterJdbcTemplate().query(sql, in, new BeanPropertyRowMapper<>(SmsDto.class));
}
```

**注意:**&#x53C3;數必須為`null`才會被 `coalesce()` 跳過，空字串會被吃進去