10種Java開發(fā)者編寫SQL語句時(shí)常見錯(cuò)誤

Java開發(fā)者對(duì)于面向?qū)ο缶幊趟季S與命令行編程思維的協(xié)調(diào)程度，取決于他們?nèi)缦聨追N能力的水平：

技巧(任何人都可以編寫命令行形式的代碼)
教條(有的人使用“模式 - 模式”的方式，即模式無處不在，并以名字作為標(biāo)識(shí))
情緒狀況(在初期，真正面向?qū)ο笮问降拇a比起命令式代碼會(huì)更加難懂。)

但是，當(dāng)Java開發(fā)人員編寫SQL語句時(shí)，一切都變得不同了。SQL是一種說明式語言，與面向?qū)ο笏枷牒兔钍剿枷霟o關(guān)。在SQL語言中，查詢非常容易表達(dá)。但它也不是那么容易以最佳或最正確地方式編寫出來。開發(fā)人員不僅需要重新思考自己的編程模式，還需要從集合論的角度進(jìn)行深入思考。

以下是Java開發(fā)人員使JDBC或jOOQ編寫SQL語句時(shí)，幾種常見的錯(cuò)誤：

1.忘記了NULL

誤解NULL的含義可能是Java開發(fā)人員編寫SQL最常犯的錯(cuò)誤。這有可能是因?yàn)镹ULL也被稱為UNKNOWN，但也有其他的原因。當(dāng)然如果它只被叫做UNKNOWN，會(huì)更容易理解一些。另一個(gè)原因是，JDBC在獲取數(shù)據(jù)，或綁定變量時(shí)，SQL中的NULL被映射到Java中的null。這可能會(huì)導(dǎo)致人們認(rèn)為類似Java中null==null的情況,SQL中也存在NULL= NULL。

一個(gè)更離奇的誤解NULL的例子是，當(dāng)NULL謂詞用于行值表達(dá)式時(shí)。

另一個(gè)微妙的問題產(chǎn)生與對(duì)NOTIn 反連接中NULL含義的誤解。

解決辦法

不斷的訓(xùn)練自己。要時(shí)刻明確NULL的含義，每次你寫SQL時(shí)，都要考慮：

對(duì)于NULL來說謂詞是否正確?
NULL是否影響該函數(shù)的結(jié)果?
2.在Java內(nèi)存中處理數(shù)據(jù)

一些Java開發(fā)者十分了解SQL特性。偶爾JOIN，零散的UNION，沒什么問題。但如果遇到視窗功能，結(jié)果集分組等情況又怎么樣呢?很多Java開發(fā)人員會(huì)把SQL數(shù)據(jù)加載到內(nèi)存，把數(shù)據(jù)轉(zhuǎn)換成一些適合的集合類型，以十分冗長(zhǎng)的循環(huán)結(jié)構(gòu)在集合上執(zhí)行惱人數(shù)學(xué)運(yùn)算(至少在Java 8改進(jìn)容器之前是這樣的)。

但一些SQL數(shù)據(jù)庫除了支持SQL標(biāo)準(zhǔn)外，還支持先進(jìn)的OLAP特性，執(zhí)行效率更好，且更容易編寫。一個(gè)非標(biāo)準(zhǔn)的例子就是甲骨文的MODEL子句。只是讓數(shù)據(jù)庫進(jìn)行數(shù)據(jù)處理過程，將最終獲取的結(jié)果加載到Java內(nèi)存中。因?yàn)橐恍┓浅Ｂ斆鞯娜艘呀?jīng)優(yōu)化了這些昂貴的產(chǎn)品。所以，事實(shí)上，通過向OLAP數(shù)據(jù)庫上進(jìn)行遷移，您將得到兩個(gè)好處：

簡(jiǎn)潔。它可能使得在SQL中編寫正確代碼會(huì)比在Java中相對(duì)容易
性能。該數(shù)據(jù)庫將可能比你的算法要快。更重要的是，你不必再通過網(wǎng)絡(luò)傳輸數(shù)百萬條記錄。
解決辦法

每次你在Java中實(shí)現(xiàn)以數(shù)據(jù)為中心的算法時(shí)，要試著問問自己：有沒有辦法讓數(shù)據(jù)庫執(zhí)行這些工作，而只把結(jié)果交付給我?

3.盡量使用UNION，而不是UNION ALL

相對(duì)于UNION，UNION ALL需要額外的關(guān)鍵字顯得相形見絀。如果在SQL標(biāo)準(zhǔn)已定義如下支持，那將會(huì)好很多：

UNION(允許重復(fù))
UNION DISTINCT(去掉重復(fù))
一般很少需要去除重復(fù)(有時(shí)去重甚至是錯(cuò)誤的)，而且對(duì)于具有很多列的大結(jié)果集，它往往很慢，因?yàn)檫@兩個(gè)子查詢需要排序，每個(gè)元組都需要與隨后的元組進(jìn)行比較。

需要注意的是，即使SQL標(biāo)準(zhǔn)指定了INTERSECTALL和EXCEPTALL，但幾乎沒有任何數(shù)據(jù)庫實(shí)現(xiàn)這些用處不大的操作。

解決辦法

你每次寫到UNION時(shí)，要考慮下你是否實(shí)際上想寫的是UNIONALL。

4.使用JDBC分頁功能將大量結(jié)果分頁

大多數(shù)數(shù)據(jù)庫都支持通過LIMIT.. OFFSET，TOP .. START AT、OFFSET.. FETCH等子句以某種方式對(duì)結(jié)果進(jìn)行分頁。在沒有對(duì)這些子句的支持下，但仍然有ROWNUM(Oracle)或ROW_NUMBER()OVER()(DB2，SQL Server 2008和更低版本)，這比在內(nèi)存中分頁要快得多。而且這對(duì)于大數(shù)據(jù)集更是明顯。

解決辦法

只要使用那些子句或工具(如jOOQ)，可以為你模擬上述分頁子句。

5.將Java內(nèi)存中實(shí)現(xiàn)連接

從SQL的發(fā)展的初期，一些開發(fā)商在面對(duì)SQL連接時(shí)仍然有一種不安的感覺。一直存在著一種固有的恐懼---JOIN速度緩慢。如果基于成本的優(yōu)化器選擇執(zhí)行嵌套循環(huán)，創(chuàng)建一個(gè)連接表源之前，加載完整表到數(shù)據(jù)庫內(nèi)存，那速度確實(shí)十分緩慢。但很這少發(fā)生。通過適當(dāng)?shù)闹^詞，約束和索引，MERGEJOIN和 HASHJOIN操作是非?？斓?。這與正確的元數(shù)據(jù)相關(guān)(我不用再舉Tom Kyte的例子了)。然而，也有仍然可能有不少Java開發(fā)人要會(huì)從單獨(dú)的查詢中加載兩個(gè)表到map容器中，在java內(nèi)存中以某種方式進(jìn)行連接操作。

解決辦法

如果你從多個(gè)步驟的多個(gè)表中進(jìn)行了SELECT操作，那要慎重考慮一下是否可以在一條語句中表達(dá)你所需要的查詢功能。
6.使用DISTINCT或UNION從一個(gè)笛卡爾積中刪除重復(fù)

冗長(zhǎng)連接的存在，會(huì)導(dǎo)致SQL語句中起作用的關(guān)系顯得十分松散。具體地，如果涉及到多列外鍵關(guān)系，很有可能忘記在JOINON子句上添加謂詞。這可能會(huì)導(dǎo)致重復(fù)的記錄，但也許只在特殊情況下。然后一些開發(fā)者可能會(huì)選擇使用DISTINCT再次刪除這些重復(fù)記錄。這種錯(cuò)誤有三種危害：

可能治標(biāo)不治本。甚至在某些邊緣情況下，標(biāo)都治不了
這在有很多列的大結(jié)果集上會(huì)十分的緩慢。DISTINCT會(huì)執(zhí)行ORDER BY操作來刪除重復(fù)。
這在大型笛卡爾積中也十分的緩慢，因?yàn)檫@樣做仍然會(huì)導(dǎo)致在內(nèi)存中加載大量數(shù)據(jù)。
解決辦法

作為一個(gè)經(jīng)驗(yàn)法則，當(dāng)你得到不想要的重復(fù)結(jié)果時(shí)，應(yīng)該首先檢查你的連接謂詞。因?yàn)橛锌赡苁窃谀硞€(gè)地方存在著一個(gè)不易察覺的笛卡爾積。

7.不使用MERGE語句

嚴(yán)格意義上講，這不是一個(gè)真正的錯(cuò)誤，可能只是對(duì)于功能強(qiáng)大的MERGE語句缺乏足夠的認(rèn)知或存在著某種恐懼而已。有些數(shù)據(jù)庫包括其他形式的UPSERT 語句，如MySQL的ONDUPLICATE KEY UPDATE子句。但MERGE真的十分強(qiáng)大，最重要的是在數(shù)據(jù)庫中，它在很大程度上擴(kuò)展了SQL標(biāo)準(zhǔn)，如SQL Server。

解決辦法

如果你通過鏈接INSERT和UPDATE或鏈接SELECT... FOR UPDATE來實(shí)現(xiàn)UPSERTING，那么你要多想一想。拋開與運(yùn)行條件的風(fēng)險(xiǎn)，你也許可以使用一個(gè)簡(jiǎn)單的MERGE語句來達(dá)到目的。

8.使用了聚合函數(shù)，而不是窗體功能

引入窗函數(shù)之前，使用GROUPBY子句與投影聚合函數(shù)是匯總數(shù)據(jù)的唯一方式。這在大部分情況下都十分有效，如果聚集后的數(shù)據(jù)需要由常規(guī)的數(shù)據(jù)進(jìn)行補(bǔ)充，該分組的查詢可以置于連接子查詢中。

但是，SQL：2003定義了窗口功能，目前很多主流的數(shù)據(jù)庫廠商也紛紛實(shí)現(xiàn)了窗口功能。窗口功能可以聚集結(jié)果集中未被分組的數(shù)據(jù)。事實(shí)上，每個(gè)窗口的功能支持自身獨(dú)立的PARTITIONBY子句，這對(duì)于報(bào)表類應(yīng)用是一個(gè)非常有用的工具。

使用窗口功能將：

導(dǎo)致更多的可讀性SQL(減少子查詢中非專用GROUP BY子句的存在)
提高性能，作為一個(gè)RDBMS很可能更容易優(yōu)化其窗口功能。
解決辦法

當(dāng)你在一個(gè)子查詢寫一個(gè)GROUPBY子句時(shí)，仔細(xì)想想這是否能用一個(gè)窗口函數(shù)來完成。

9.使用內(nèi)存排序法進(jìn)行間接排序

在SQLORDER BY子句支持多種類型的表達(dá)式，包括CASE語句，這對(duì)間接排序非常有用。你應(yīng)該永遠(yuǎn)可能在Java內(nèi)存中對(duì)數(shù)據(jù)進(jìn)行排序，因?yàn)槟阏J(rèn)為：

SQL排序太慢
SQL排序不能做到這一點(diǎn)
解決辦法

如果你在內(nèi)存中對(duì)任何SQL數(shù)據(jù)進(jìn)行排序，請(qǐng)仔細(xì)想想，你是否能把排序遷移至數(shù)據(jù)庫中。這和將分頁遷移至數(shù)據(jù)庫中的原因一樣。

10 一個(gè)接一個(gè)的插入大量的記錄

JDBC包含了批處理，而且你應(yīng)該使用它。面對(duì)成千上萬的記錄，切勿為每一條記錄都創(chuàng)建一個(gè)新的PreparedStatement來進(jìn)行插入操作。如果你要將所有記錄都插入到同一個(gè)表，使用單一的SQL語句和多個(gè)綁定值集合建立一個(gè)批處理的INSERT語句。根據(jù)您的數(shù)據(jù)庫和數(shù)據(jù)庫配置，您可能需要在一定數(shù)量的插入的記錄后進(jìn)行提交，為了保持UNDO日志不過分龐大。

解決辦法

始終批量插入大型數(shù)據(jù)集。

Java開發(fā)者編寫SQL語句時(shí)常見的10種錯(cuò)誤，大家是不是有了大概了解，希望在編寫的過程中一定要特別注意！