Java程式設計師在寫SQL程式時候常犯的10個錯誤

　　Java程式設計師程式設計時需要混合物件導向思維和一般指令式程式設計的方法，能否完美的將兩者結合起來完全得依靠程式設計人員的水準：

• 技能(任何人都能容易學會指令式程式設計)
• 模式(有些人用“模式-模式”,舉個例子,模式可以應用到任何地方，而且都可以歸為某一類模式)
• 心境(首先，要寫個好的物件導向程式是比命令式程式難的多，你得花費一些功夫)

　　但當Java程式設計師寫SQL語句時，一切都不一樣了。SQL是說明性語言而非物件導向或是指令式程式設計語言。在SQL中要寫個查詢語句是很簡單的。但在Java裡類似的語句卻不容易，因為程式設計師不僅要反覆考慮程式設計正規化，而且也要考慮演算法的問題。

　　下面是Java程式設計師在寫SQL時常犯的錯誤(沒有特定的順序)：

　1.忘掉NULL

　　Java程式設計師寫SQL時對NULL的誤解可能是最大的錯誤。也許是因為(並非唯一理由)NULL也稱作UNKNOWN。如果被稱作UNKNOWN，這還好理解些。另一個原因是，當你從資料庫拿東西或是繫結變數時，JDBC將SQL NULL 和Java中的null對應了起來。這樣導致了NULL = NULL(SQL)和null=null(Java)的誤解。

　　對於NULL最大的誤解是當NULL被用作行值表示式完整性約束條件時。

　　另一個誤解出現在對於NULL 在 NOT IN anti-joins的應用中。

　　解決方法：

　　好好的訓練你自己。當你寫SQL時要不停得想到NULL的用法：

• 這個NULL完整性約束條件是正確的？
• NULL是否影響到結果？

　2.在Java記憶體中處理資料

　　很少有Java開發者能將SQL理解的很好.偶爾使用的JOIN,還有古怪的UNION,好吧.但是對於視窗函式呢?還有對集合進行分組呢?許多的Java開發者將SQL資料載入到記憶體中,將這些資料轉換成某些相近的集合型別,然後再那些集合上面使用邊界迴圈控制結構(至少在Java8的集合升級以前)執行令人生厭的數學運算.

　　但是一些SQL資料庫支援先進的(而且是SQL標準支援的!)OLAP特性,這一特性表現更好而且寫起來也更加方便.一個(並不怎麼標準的)例子就是Oracle超棒的MODEL分句.只讓資料庫來做處理然後只把結果帶到Java記憶體中吧.因為畢竟所有非常聰明的傢伙已經對這些昂貴的產品進行了優化.因此實際上,通過將OLAP移到資料庫,你將獲得一下兩項好處:

• 便利性.這比在Java中編寫正確的SQL可能更加的容易.
• 效能表現.資料庫應該比你的演算法處理起來更加快.而且更加重要的是,你不必再去傳遞數百萬條記錄了.

　　完善的方法:

　　每次你使用Java實現一個以資料為中心的演算法時,問問自己:有沒有一種方法可以讓資料庫代替為我做這種麻煩事.

　3. 使用UNION代替UNION ALL

　　太可恥了，和UNION相比UNION ALL還需要額外的關鍵字。如果SQL標準已經規定了支援，那麼可能會更好點。

• UNION（允許重複）
• UNION DISTINCT (去除了重複)

　　移除重複行不僅很少需要（有時甚至是錯的），而且對於帶很多行的大資料集合會相當慢，因為兩個子select需要排序，而且每個元組也需要和它的子序列元組比較。

　　注意即使SQL標準規定了INTERSECT ALL和EXCEPT ALL，很少資料庫會實現這些沒用的集合操作符。

　　處理方法：

　　每次你寫UNION語句時，考慮實際上是否需要UNION ALL語句。

　4.通過JDBC分頁技術給大量的結果進行分頁操作

　　大部分的資料庫都會支援一些分頁命令實現分頁效果，譬如LIMIT..OFFSET,TOP..START AT,OFFSET..FETCH語句等。即使沒有支援這些語句的資料庫，仍有可能對ROWNUM（甲骨文）或者是ROW NUMBER() OVER()過濾（DB2,SQL Server2008等），這些比在記憶體中實現分頁更快速。在處理大量資料中，效果尤其明顯。

　　糾正：

　　僅僅使用這些語句，那麼一個工具（例如JOOQ）就可以模擬這些語句的操作。

　5.在java記憶體中加入資料

　　從SQL的初期開始，當在SQL中使用JOIN語句時，一些開發者仍舊有不安的感覺。這是源自對加入JOIN後會變慢的固有恐懼。假如基於成本的優化選擇去實現巢狀迴圈，在建立一張連線表源前，可能載入所有的表在資料庫記憶體中，這可能是真的。但是這事發生的概率太低了。通過合適的預測，約束和索引，合併連線和雜湊連線的操作都是相當的快。這完全是是關於正確後設資料（在這裡我不能夠引用Tom Kyte的太多）。而且，可能仍然有不少的Java開發人員載入兩張表通過分開查詢到一個對映中，並且在某種程度上把他們加到了記憶體當中。

　　糾正：

　　假如你在各個步驟中有從各種表的查詢操作，好好想想是否可以表達你的查詢操作在單條語句中。

　6.使用DISTINCT和UNION從一個偶然的笛卡爾積中刪除重複

　　由於重量級連線的使用,一個人可以鬆綁對在SQL語句中起作用的所有關係的跟蹤.具體來說,如果多列外來鍵關係被加入進來的話,忘掉加入使用JOIN...ON分句的相關謂語是很有可能的。這可能會導致重複的記錄，但也可能只發生在異常的情況下。一些開發者可能因此選擇使用DISTINCT來移除那些重複的記錄。這在三個方面都證明是錯誤的：

　　它（可能）能夠治標，但並不治本。極端情況下它也可能練治標的能力也沒有。

　　對於含有許多列的大型結果集，它的處理是緩慢的。DISTINCT執行了一個ORDER BY 的操作來移除重複的記錄。

　　對於大型的笛卡爾積，它的處理是緩慢的，仍將會把許多的資料載入到記憶體中。

　　解決方法：

　　作為首要的規則，當你得到了不想要的重複記錄時，常常要重新檢查檢查你的JOIN謂語。很可能有一個微妙的笛卡爾積在那某一個地方。

　7.不去使用MERGE語句

　　這並不是一個真正的錯誤，但可能是由於某些知識的缺失，或者是對於強大的MERGE語句的某種畏懼。一些資料庫知道其他形式的UPSERT語句，例如，MYSQL的 ON DUPLICATE KEY UPDATE 分句。但是MERGE真的是如此強大，在大多數重視擴充套件SQL標準的資料庫，如SQL Server中是很重要的。

　　解決方法：

　　如果你正在通過連結INERT和UPDATE，或者通過連結SELECT...FOR UPDATE進行UPSERT，然後進行INSERT或者UPDATE,請三思。在冒此風險之外，你還可以選擇使用一個簡單的MERGE語句進行表達。

　8. 使用聚合函式代替視窗函式(window functions)

　　在介紹視窗函式之前，在SQL中聚合資料意味著使用GROUP BY語句與聚合函式相對映。在很多情形下都工作得很好，如聚合資料需要濃縮常規資料，那麼就在join子查詢中使用group查詢。

　　但是在SQL：2003中定義了視窗函式，這個在很多主流資料庫都實現了它。視窗函式能夠在結果集上聚合資料，但是卻沒有分組。事實上，每個視窗函式都有自己的、獨立的PARTITION BY語句，這個工具對於顯示報告太TM好了。

　　使用視窗函式：

• 使SQL更易讀（但在子查詢中沒有GROUP BY語句專業）
• 提升效能，像關聯式資料庫管理系統能夠更容易優化視窗函式

　　解決方法：

　　當你在子查詢中使用GROUP BY語句時，請再三考慮是否可以使用視窗函式完成。

　9. 使用記憶體間接排序

　　SQL的ORDER BY語句支援很多型別的表示式，包括CASE語句，對於間接排序十分有用。你可能重來不會在Java記憶體中排序資料，因為你會想：

• SQL排序很慢
• SQL排序辦不到

　　處理方法:

　　如果你在記憶體中排序任何SQL資料，請再三考慮，是否不能在資料庫中排序。這對於資料庫分頁資料十分有用。

　10. 一條一條的插入大量紀錄

　　JDBC ”懂“批處理(batch),你應該不會忘了它。不要使用INSERT語句來一條一條的出入成千上萬的記錄，（因為）每次都會建立一個新的PreparedStatement物件。如果你的所有記錄都插入到同一個表時，那麼就建立一個帶有一條SQL語句以及附帶很多值集合的插入批處理語句。你可能需要在達到一定量的插入記錄後才提交來保證UNDO日誌瘦小，這依賴於你的資料庫和資料庫設定。

　　處理方法：

　　總是使用批處理插入大量資料。

　　原文地址：http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/