Java開發者編寫SQL語句時常見的10種錯誤
Java開發者對于面向對象編程思維與命令行編程思維的協調程度，取決于他們如下幾種能力的水平：
- 技巧(任何人都可以編寫命令行形式的代碼)
- 教條(有的人使用“模式 - 模式”的方式，即模式無處不在，并以名字作為標識)
- 情緒狀況(在初期，真正面向對象形式的代碼比起命令式代碼會更加難懂。)
但是，當Java開發人員編寫SQL語句時，一切都變得不同了。SQL是一種說明式語言，與面向對象思想和命令式思想無關。在SQL語言中，查詢非常容易表達。但它也不是那么容易以***或最正確地方式編寫出來。開發人員不僅需要重新思考自己的編程模式，還需要從集合論的角度進行深入思考。
以下是Java開發人員使JDBC或jOOQ編寫SQL語句時，幾種常見的錯誤(排名不分先后)
1.忘記了NULL
誤解NULL的含義可能是Java開發人員編寫SQL最常犯的錯誤。這有可能是因為NULL也被稱為UNKNOWN，但也有其他的原因。當然如果它只被叫做UNKNOWN，會更容易理解一些。另一個原因是，JDBC在獲取數據，或綁定變量時，SQL中的NULL被映射到Java中的null。這可能會導致人們認為類似Java中null==null的情況,SQL中也存在NULL= NULL。
一個更離奇的誤解NULL的例子是，當NULL謂詞用于行值表達式時。
另一個微妙的問題產生與對NOTIn 反連接中NULL含義的誤解。
解決辦法
不斷的訓練自己。要時刻明確NULL的含義，每次你寫SQL時，都要考慮：
- 對于NULL來說謂詞是否正確?
- NULL是否影響該函數的結果?
2.在Java內存中處理數據
一些Java開發者十分了解SQL特性。偶爾JOIN，零散的UNION，沒什么問題。但如果遇到視窗功能，結果集分組等情況又怎么樣呢?很多Java開發人員會把SQL數據加載到內存，把數據轉換成一些適合的集合類型，以十分冗長的循環結構在集合上執行惱人數學運算(至少在Java 8改進容器之前是這樣的)。
但一些SQL數據庫除了支持SQL標準外，還支持先進的OLAP特性，執行效率更好，且更容易編寫。一個非標準的例子就是甲骨文的MODEL子句。只是讓數據庫進行數據處理過程，將最終獲取的結果加載到Java內存中。因為一些非常聰明的人已經優化了這些昂貴的產品。所以，事實上，通過向OLAP數據庫上進行遷移，您將得到兩個好處：
- 簡潔。它可能使得在SQL中編寫正確代碼會比在Java中相對容易
- 性能。該數據庫將可能比你的算法要快。更重要的是，你不必再通過網絡傳輸數百萬條記錄。
解決辦法
每次你在Java中實現以數據為中心的算法時，要試著問問自己：有沒有辦法讓數據庫執行這些工作，而只把結果交付給我?
3.盡量使用UNION，而不是UNION ALL
相對于UNION，UNION ALL需要額外的關鍵字顯得相形見絀。如果在SQL標準已定義如下支持，那將會好很多：
- UNION(允許重復)
- UNION DISTINCT(去掉重復)
一般很少需要去除重復(有時去重甚至是錯誤的)，而且對于具有很多列的大結果集，它往往很慢，因為這兩個子查詢需要排序，每個元組都需要與隨后的元組進行比較。
需要注意的是，即使SQL標準指定了INTERSECTALL和EXCEPTALL，但幾乎沒有任何數據庫實現這些用處不大的操作。
解決辦法
你每次寫到UNION時，要考慮下你是否實際上想寫的是UNIONALL。
4.使用JDBC分頁功能將大量結果分頁
大多數數據庫都支持通過LIMIT.. OFFSET，TOP .. START AT、OFFSET.. FETCH等子句以某種方式對結果進行分頁。在沒有對這些子句的支持下，但仍然有ROWNUM(Oracle)或ROW_NUMBER()OVER()(DB2，SQL Server 2008和更低版本)，這比在內存中分頁要快得多。而且這對于大數據集更是明顯。
解決辦法
只要使用那些子句或工具(如jOOQ)，可以為你模擬上述分頁子句。
5.將Java內存中實現連接
從SQL的發展的初期，一些開發商在面對SQL連接時仍然有一種不安的感覺。一直存在著一種固有的恐懼---JOIN速度緩慢。如果基于成本的優化器選擇執行嵌套循環，創建一個連接表源之前，加載完整表到數據庫內存，那速度確實十分緩慢。但很這少發生。通過適當的謂詞，約束和索引，MERGEJOIN和 HASHJOIN操作是非常快的。這與正確的元數據相關(我不用再舉Tom Kyte的例子了)。然而，也有仍然可能有不少Java開發人要會從單獨的查詢中加載兩個表到map容器中，在java內存中以某種方式進行連接操作。
解決辦法
如果你從多個步驟的多個表中進行了SELECT操作，那要慎重考慮一下是否可以在一條語句中表達你所需要的查詢功能。
#p#
6.使用DISTINCT或UNION從一個笛卡爾積中刪除重復
冗長連接的存在，會導致SQL語句中起作用的關系顯得十分松散。具體地，如果涉及到多列外鍵關系，很有可能忘記在JOINON子句上添加謂詞。這可能會導致重復的記錄，但也許只在特殊情況下。然后一些開發者可能會選擇使用DISTINCT再次刪除這些重復記錄。這種錯誤有三種危害：
- 可能治標不治本。甚至在某些邊緣情況下，標都治不了
- 這在有很多列的大結果集上會十分的緩慢。DISTINCT會執行ORDER BY操作來刪除重復。
- 這在大型笛卡爾積中也十分的緩慢，因為這樣做仍然會導致在內存中加載大量數據。
解決辦法
作為一個經驗法則，當你得到不想要的重復結果時，應該首先檢查你的連接謂詞。因為有可能是在某個地方存在著一個不易察覺的笛卡爾積。
7.不使用MERGE語句
嚴格意義上講，這不是一個真正的錯誤，可能只是對于功能強大的MERGE語句缺乏足夠的認知或存在著某種恐懼而已。有些數據庫包括其他形式的UPSERT 語句，如MySQL的ONDUPLICATE KEY UPDATE子句。但MERGE真的十分強大，最重要的是在數據庫中，它在很大程度上擴展了SQL標準，如SQL Server。
解決辦法
如果你通過鏈接INSERT和UPDATE或鏈接SELECT... FOR UPDATE來實現UPSERTING，那么你要多想一想。拋開與運行條件的風險，你也許可以使用一個簡單的MERGE語句來達到目的。
8.使用了聚合函數，而不是窗體功能
引入窗函數之前，使用GROUPBY子句與投影聚合函數是匯總數據的唯一方式。這在大部分情況下都十分有效，如果聚集后的數據需要由常規的數據進行補充，該分組的查詢可以置于連接子查詢中。
但是，SQL：2003定義了窗口功能，目前很多主流的數據庫廠商也紛紛實現了窗口功能。窗口功能可以聚集結果集中未被分組的數據。事實上，每個窗口的功能支持自身獨立的PARTITIONBY子句，這對于報表類應用是一個非常有用的工具。
使用窗口功能將：
- 導致更多的可讀性SQL(減少子查詢中非專用GROUP BY子句的存在)
- 提高性能，作為一個RDBMS很可能更容易優化其窗口功能。
解決辦法
當你在一個子查詢寫一個GROUPBY子句時，仔細想想這是否能用一個窗口函數來完成。
9.使用內存排序法進行間接排序
在SQLORDER BY子句支持多種類型的表達式，包括CASE語句，這對間接排序非常有用。你應該永遠可能在Java內存中對數據進行排序，因為你認為：
- SQL排序太慢
- SQL排序不能做到這一點
解決辦法
如果你在內存中對任何SQL數據進行排序，請仔細想想，你是否能把排序遷移至數據庫中。這和將分頁遷移至數據庫中的原因一樣。
10 一個接一個的插入大量的記錄
JDBC包含了批處理，而且你應該使用它。面對成千上萬的記錄，切勿為每一條記錄都創建一個新的PreparedStatement來進行插入操作。如果你要將所有記錄都插入到同一個表，使用單一的SQL語句和多個綁定值集合建立一個批處理的INSERT語句。根據您的數據庫和數據庫配置，您可能需要在一定數量的插入的記錄后進行提交，為了保持UNDO日志不過分龐大。
解決辦法
始終批量插入大型數據集。
