Java開發者寫SQL時常犯的10個錯誤

有意思的是，在JDBC出現了許多年後的今天，這個錯誤依然出現在部落格、論壇和郵件串列中，即便要記住和理解它是一件很簡單的事。開發者不使用PreparedStatements的原因可能有如下幾個：

• 他們對PreparedStatements不瞭解

• 他們認為使用PreparedStatements太慢了

• 他們認為寫PreparedStatements太費力

來吧，我們來破除上面的謠言。96%的案例中，用PreparedStatement比靜態宣告陳述句更好。為什麼呢？就是下麵這些簡單的原因：

• 使用行內系結值（inlining bind values）可以從源頭上避免糟糕的陳述句引起語法錯誤。

• 使用行內系結值可以避免糟糕的陳述句引起的SQL註入漏洞。

• 當插入更多“複雜的”資料型別（比如時間戳、二進位制資料等等）時，可以避免邊緣現象（edge-cases）。

• 你若保持PreparedStatements的連線開啟狀態而不馬上關閉，只要重新系結新值就可以進行復用。

• 你可以在更多複雜的資料庫裡使用adaptive cursor sharing——自適應遊標共享（Oracle的說法）。這可以幫你在每次新設定系結值時阻止SQL陳述句硬解析。

（譯者註：硬解析的弊端。硬解析即整個SQL陳述句的執行需要完完全全的解析，生成執行計劃。而硬解析，生成執行計劃需要耗用CPU資源，以及SGA資源。在此不得不提的是對庫快取中 閂的使用。閂是鎖的細化，可以理解為是一種輕量級的序列化裝置。當行程申請到閂後，則這些閂用於保護共享記憶體的數在同一時刻不會被兩個以上的行程修改。在 硬解析時，需要申請閂的使用，而閂的數量在有限的情況下需要等待。大量的閂的使用由此造成需要使用閂的行程排隊越頻繁，效能則逾低下）

某些特殊情況下你需要對值進行行內系結，這是為了給基於成本的效能最佳化器提示該查詢將要涉及的資料集。典型的情況是用“常量”判斷：

• DELETED = 1

• STATUS = 42

而不應該用一個“變數”判斷：

• FIRST_NAME LIKE “Jon%”

• AMOUNT > 19.95

要註意的是，現代資料庫已經實現了系結資料窺探（bind-variable peeking）。因此，預設情況下，你也可以為你所有的查詢引數使用系結值。在你寫嵌入的JPQL或嵌入的SQL時，用JPA CriteriaQuery或者jOOQ這類高層次的API可以很容易也很清晰的幫你生成PreparedStatements陳述句並系結值。

更多的背景資料：

• 系結資料窺探（bind-variable peeking）的附加說明：這個主題有一篇有趣的文章，它出自Oracle大師Tanel Poder之手。

• 遊標分享。StackOverflow上一個有趣的問題。

解決方案：

預設情況下，總是使用PreparedStatements來代替靜態宣告陳述句，而永遠不要在你的SQL陳述句嵌入行內系結值。

2、傳回太多列

這個錯誤發生的非常頻繁，它不光會影響你的資料庫執行計劃，也會對你的Java應用造成不好的影響。讓我們先看看對後者的影響：

對Java程式的不良影響:

如 果你為了滿足不同DAO層之間的資料復用而select *或者預設的50個列，這樣將會有大量的資料從資料庫讀入到JDBC結果集中，即使你不從結果集讀取資料，它也被傳遞到了線路上並被JDBC驅動器載入到 了記憶體中。如果你知道你只需要2-3列資料的話，這就造成了嚴重的IO和記憶體的浪費。

這個（問題的嚴重性）都是顯而易見的，要小心……

對資料庫執行計劃的不良影響：

這些影響事實上可能比對Java應用的影響還要嚴重。當複雜的資料庫要針對你的查詢請求計算出最佳執行計劃時，它會進行大量的SQL轉換（SQL transformation ）。還好，請求中的一部分可以被略去，因為它們對SQL連對映或過濾條件起不了什麼作用。我最近寫了一篇部落格來講述這個問題：元資料樣式會對Oracle查詢轉換產生怎樣的影響。

現在，給你展示一個錯誤的例子。想一想有兩個檢視的複雜查詢：

SELECT *

FROM customer_view c

JOIN order_view o

ON c.cust_id = o.cust_id

每個關聯了上述關聯表取用的檢視也可能再次關聯其他表的資料，像 CUSTOMER_ADDRESS、ORDER_HISTORY、ORDER_SETTLEMENT等等。進行select * 對映時，你的資料庫除了把所有連線表都載入進來以外別無選擇，實際上，你唯一感興趣的資料可能只有這些：

SELECT c.first_name, c.last_name, o.amount

FROM customer_view c

JOIN order_view o

ON c.cust_id = o.cust_id

一個好的資料庫會在轉換你的SQL陳述句時自動移除那些不需要的連線，這樣資料庫就只需要較少的IO和記憶體消耗。

解決方案：

永遠不要用select *（這樣的查詢）。也不要在執行不同請求時復用相同的對映。儘量嘗試減少對映到你所真正需要的資料。

需要註意的是，想在物件-關係對映（ORMs）上達成這個標的有些難。

3、把JOIN當做了SELECT的子句

對於效能或SQL陳述句的正確性來說，這不算錯。但是不管如何，SQL開發者應該意識到JOIN子句不是SELECT陳述句的一部分。SQL standard 1992 定義了表取用：

6.3

::=

[ [ AS ]

[ ] ]

| [ AS ]

[ ]

|

7.4

::=

FROM

[ {

}... ]

7.5

::=

|

|

::=

CROSS JOIN

::=

[ NATURAL ] [ ] JOIN

[ ]