# PreparedStatement ## PreparedStatement 參考資料:JDBC為什麼要使用PreparedStatement而不是Statement() 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 ``` public List selectDataBySmsId(String... smsId) { String sql = " SELECT * FROM " + SmsDto.TABLE_NAME + " WHERE sms_id in (:sms_id) "; //不吃Array，要先轉換成List List 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 參考資料:() ``` public List 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)); } ``` **注意:**參數必須為`null`才會被 `coalesce()` 跳過，空字串會被吃進去 --- # Agent Instructions: 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://brianwu.gitbook.io/brian/java/preparedstatement.md?ask= ``` 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.