動(dòng)手?jǐn)]一個(gè)SQL規(guī)范檢查工具
背景
近幾年公司人員規(guī)?？焖僭鲩L(zhǎng)，超過(guò)半數(shù)開發(fā)人員均為近兩年入職的新員工，開發(fā)技能與經(jīng)驗(yàn)欠缺，之前踩坑的經(jīng)驗(yàn)也未能完全了解，出現(xiàn)了幾起因慢SQL而引發(fā)的生產(chǎn)性能問(wèn)題。
為了更好地指導(dǎo)產(chǎn)品SQL設(shè)計(jì)及開發(fā)，避免不恰當(dāng)?shù)脑O(shè)計(jì)、開發(fā)帶來(lái)問(wèn)題和隱患，同時(shí)為了提升開發(fā)人員對(duì)SQL相關(guān)知識(shí)的掌握程度， 我們組織了技術(shù)專家依據(jù)現(xiàn)狀，整理了一份SQL開發(fā)規(guī)范， 通過(guò)明確的規(guī)則指導(dǎo)編寫合理、高效的SQL語(yǔ)句。
然而，在實(shí)踐過(guò)程中發(fā)現(xiàn)，即使我們做了大量的宣講、培訓(xùn)，但是各項(xiàng)目組反饋還是難以將規(guī)范落地。因?yàn)橐?guī)范有數(shù)十條規(guī)則，完全靠人工的檢查與落實(shí)，難免會(huì)有遺漏，而且增大了代碼review的難度。
基于此，我們動(dòng)手?jǐn)]了一個(gè)SQL規(guī)范檢查工具，用來(lái)自動(dòng)化的檢查出不符合規(guī)范的SQL語(yǔ)句。HTML輸出效果如下圖：
詳細(xì)設(shè)計(jì)
整個(gè)工具分為3部分，3個(gè)模塊相互獨(dú)立，可以有多種實(shí)現(xiàn)：
SQL獲?。?/strong>該部分主要用來(lái)獲取SQL語(yǔ)句，可以有多種實(shí)現(xiàn)方式，比如從項(xiàng)目的mapper.xml中解析獲取，也可以由文本文件中獲取。目前我們實(shí)現(xiàn)了利用maven插件，在mvn:compile編譯期間解析項(xiàng)目代碼中的mapper.xml文件，獲取SQL。這部分不是本次介紹的重點(diǎn)，后面再寫單獨(dú)的文章專門介紹。
SQL檢查：是整個(gè)工具的核心部分。入?yún)⑹谦@取到的SQL語(yǔ)句集合，出參為檢查報(bào)告集合。下文會(huì)詳細(xì)介紹該部分。
報(bào)告渲染：該部分主要用來(lái)渲染檢查報(bào)告，如可將報(bào)告渲染為json文件、通過(guò)freemarker等工具生成HTML、等樣式方便查看，也可以通過(guò)jenkins流水線生成報(bào)告。該部分本次也不做詳細(xì)介紹。
本次只詳細(xì)介紹下SQL檢查模塊的設(shè)計(jì)與實(shí)現(xiàn)。
SQL檢查模塊設(shè)計(jì)
每個(gè)SQL都由查詢項(xiàng)、表名、where條件、join條件、limit條件等特定的幾部分構(gòu)成，以下面這個(gè)SQL語(yǔ)句為例，查詢項(xiàng)為a.*,b.name，表名為a,b，where條件為a.id=b.id。
select a.*,b.name from a,b where a.id=b.idSQL檢查的核心流程簡(jiǎn)單來(lái)說(shuō)，就是入?yún)閱蝹€(gè)SQL語(yǔ)句，輸出為檢查報(bào)告。分為以下幾個(gè)具體步驟：
1.將SQL解析成語(yǔ)法樹，可以從語(yǔ)法樹中獲取SQL的各個(gè)部分，如查詢項(xiàng)、關(guān)聯(lián)表、where條件等。
2.根據(jù)SQL的類型，匹配規(guī)則檢查器。如SELECT、UPDATE、DELETE等分別有不同的檢查器。
3.根據(jù)規(guī)則，檢查語(yǔ)法樹的各個(gè)部分，并生成檢查報(bào)告。如有個(gè)規(guī)則為“必須寫明查詢條件，不能出現(xiàn)select *”，這個(gè)重點(diǎn)檢查查詢語(yǔ)句和子查詢的查詢項(xiàng)部分。
4.將檢查報(bào)告輸出為特定樣式，供開發(fā)人員查看。
根據(jù)以上流程，設(shè)計(jì)幾個(gè)核心的接口和類：
Analyzer，語(yǔ)法分析器，用來(lái)將SQL語(yǔ)句解析成語(yǔ)法樹
/**
* SQL語(yǔ)法分析器
*/
public interface Analyzer {
/**
* @param sql sql語(yǔ)句
* @return sql語(yǔ)法樹
*/
AST analyze(String sql);
}AST，抽象語(yǔ)法樹，用來(lái)獲取SQL的各個(gè)部分
/**
* SQL抽象語(yǔ)法樹
*/
public interface AST {
/**
* 獲取語(yǔ)法樹對(duì)應(yīng)SQL的SQL類型
* @return SQL類型枚舉
*/
SqlTypes getSqlType();
String getSql();
Expression getWhere();
GroupByElement getGroupBy();
List<SelectItem> getSelects();
List<Column> getColumns();
List<Join> getJoins();
Limit getLimit();
List<OrderByElement> getOrderByElement();
}Checker，抽象類，所有規(guī)則檢查器的基類，check()方法用來(lái)遍歷規(guī)則集并檢查
/**
* 規(guī)則檢查器
*/
public abstract class Checker {
/**
* @return 規(guī)則檢查器的名稱
*/
public abstract String getName();
/**
* 規(guī)則集
*/
protected List<CheckRule> rules = new ArrayList<>();
public void registeRule(CheckRule rule){
this.rules.add(rule);
}
/**
* @param tree 抽象語(yǔ)法樹
* @return 規(guī)則檢查報(bào)告
*/
public List<Report> check(AST tree){
List<Report> reports = new ArrayList<>();
for(CheckRule rule : rules){
Report report = rule.match(tree);
if (report != null){
reports.add(report);
}
}
return reports;
}
}CheckRule，具體的檢查規(guī)則，每個(gè)規(guī)則器里有多個(gè)檢查規(guī)則，如select類型的SQL語(yǔ)句會(huì)有多個(gè)檢查規(guī)則
/**
* 具體的檢查規(guī)則
*/
public interface CheckRule {
/**
* @param tree 抽象語(yǔ)法樹
* @return 規(guī)則檢查報(bào)告
*/
Report match(AST tree);
/**
* 規(guī)則作用域，SELECT、DELETE等
* @return
*/
List<SqlTypes> scope();
}Report，檢查報(bào)告，每條規(guī)則檢查后都會(huì)生成一條報(bào)告
/**
* 檢查報(bào)告
*/
public class Report {
private boolean pass; //通過(guò)標(biāo)識(shí)
private String desc; //錯(cuò)誤提示
private String sql;//sql語(yǔ)句
private Level level;//報(bào)告等級(jí)
private String sample;//正例，每個(gè)報(bào)告在輸出時(shí)，除了報(bào)告錯(cuò)誤外，還需展示正例，告訴用戶正確的寫法是什么
public enum Level{
WARNING("wanring"),
ERROR("error"),
INFO("info");
private String value;
Level(String value){
this.value = value;
}
}
}Appender，用于輸出報(bào)告，可以定義不同的實(shí)現(xiàn)類，輸出不同的樣式
/**
* 用于輸出報(bào)告，不同的輸出樣式，定義不同的Appender實(shí)現(xiàn)類
*/
public interface Appender {
void print(List<Report> reports);
}CheckerHolder，用來(lái)注冊(cè)Checker，所有的Checker都必須注冊(cè)在CheckerHolder才能生效
public class CheckerHolder {
private static Map<String,Checker> checkers = new ConcurrentHashMap<>(16);
public static void registeChecker(Checker checker){
checkers.putIfAbsent(checker.getName(),checker);
}
public static void unRegisteChecker(Checker checker){
checkers.remove(checker.getName());
}
public static Map<String,Checker> getCheckers(){
return checkers;
}
}有了以上接口和類，可以編寫主流程的測(cè)試代碼了：
public void test(){
String sql = "select * from test";
//sql語(yǔ)法分析器
Analyzer analyzer = new DefaultAnalyzer();
//注冊(cè)select規(guī)則解析器和規(guī)則
Checker selectChecker = new SelectCheck();
CheckRule writeClearlySelectFieldRule = new WriteClearlySelectFieldRule();
selectChecker.registeRule(writeClearlySelectFieldRule);
CheckerHolder.registeChecker(selectChecker);
//注冊(cè)insert規(guī)則解析器和規(guī)則
Checker insertChecker = new InsertCheck();
CheckRule clearTableRule = new ClearTableRule();
insertChecker.registeRule(clearTableRule);
CheckerHolder.registeChecker(insertChecker);
Appender appender = new DefaultAppender();
//解析成抽象語(yǔ)法樹
AST tree = analyzer.analyze(sql);
//遍歷規(guī)則檢查器，開始檢查
for (Checker checker : CheckerHolder.getCheckers().values()){
//每個(gè)規(guī)則生成一個(gè)報(bào)告
List<Report> reports = checker.check(tree);
//輸出
appender.print(reports);
}
}以上便是整個(gè)SQL檢查模塊的設(shè)計(jì)，每個(gè)接口都有具體的實(shí)現(xiàn)，我們使用JSqlParser作為SQL解析的實(shí)現(xiàn)。代碼如下：
語(yǔ)法樹的實(shí)現(xiàn)JSqlParseAst，因重點(diǎn)檢查SELECT類型的語(yǔ)句，因此其他類型的實(shí)現(xiàn)暫時(shí)為null
public class JSqlParseAst implements AST {
private Statement statement;
private String sql;
public JSqlParseAst(Statement statement, String sql) {
this.statement = statement;
this.sql = sql;
}
@Override
public SqlTypes getSqlType() {
if (statement instanceof Select) {
return SqlTypes.SELECT;
} else if (statement instanceof Update) {
return SqlTypes.UPDATE;
} else if (statement instanceof Delete) {
return SqlTypes.DELETE;
} else if (statement instanceof Insert) {
return SqlTypes.INSERT;
} else if (statement instanceof Replace) {
return SqlTypes.REPLACE;
} else if(statement instanceof GrammarErrStatement){
return SqlTypes.ERROR;
}
else {
return SqlTypes.OTHER;
}
}
@Override
public String getSql() {
return this.sql;
}
@Override
public Expression getWhere() {
switch (this.getSqlType()) {
case SELECT:
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getWhere();
case UPDATE:
Update update = (Update) statement;
return update.getWhere();
case DELETE:
Delete delete = (Delete) statement;
return delete.getWhere();
default:
return null;
}
}
@Override
public GroupByElement getGroupBy() {
switch (this.getSqlType()) {
case SELECT:
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getGroupBy();
default:
return null;
}
}
@Override
public List<SelectItem> getSelects() {
switch (this.getSqlType()) {
case SELECT:
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getSelectItems();
default:
return null;
}
}
@Override
public List<Column> getColumns() {
switch (this.getSqlType()) {
case INSERT:
Insert insert = (Insert) statement;
return insert.getColumns();
default:
return null;
}
}
@Override
public List<Join> getJoins() {
switch (this.getSqlType()) {
case SELECT:
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getJoins();
default:
return null;
}
}
@Override
public Limit getLimit() {
if (SqlTypes.SELECT == getSqlType()) {
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getLimit();
} else {
return null;
}
}
@Override
public List<OrderByElement> getOrderByElement() {
if (SqlTypes.SELECT == getSqlType()) {
Select select = (Select) statement;
return ((PlainSelect) select.getSelectBody()).getOrderByElements();
} else {
return null;
}
}
}解析器的實(shí)現(xiàn)JSqlParseAnalyzer
/**
* SQL語(yǔ)法解析
*/
public class JSqlParseAnalyzer implements Analyzer {
@Override
public AST analyze(String sql) {
JSqlParseAst ast = null;
try {
Statement statement = CCJSqlParserUtil.parse(sql);
ast = new JSqlParseAst(statement, sql);
} catch (Exception e) {
ast = new JSqlParseAst(new GrammarErrStatement(), sql);
}
return ast;
}
}
Checker的實(shí)現(xiàn)比較簡(jiǎn)單，因?yàn)榇蟛糠诌壿嫸家寻诨愔?，子類只需要提供一個(gè)name即可，用來(lái)標(biāo)識(shí)Checker的類型。SelectChecker實(shí)現(xiàn)如下：
public class SelectChecker extends Checker {
@Override
public String getName() {
return "SELECT";
}
}CheckRule的一個(gè)具體實(shí)現(xiàn)WriteClearlySelectFieldRule，檢查SQL中不能出現(xiàn)SELECT *
/**
* 寫明查詢字段，不要使用select *
*/
public class WriteClearlySelectFieldRule implements CheckRule {
@Override
public Report match(AST tree) {
Report report = new Report(tree.getSql());
report.setPass(true);
List<SelectItem> selectItems = tree.getSelects();
//查詢體中是否有*號(hào)
if(checkAsterisk(selectItems)){
report.setDesc("請(qǐng)寫明查詢字段，不要使用select *");
report.setPass(false);
report.setLevel(Report.Level.ERROR);
return report;
}
//join子查詢中是否有*號(hào)，有則報(bào)錯(cuò)
List<Join> joins = tree.getJoins();
if(joins == null || joins.size() <1){
return report;
}
for(Join join : joins){
//如果是子查詢
if(join.getRightItem() instanceof SubSelect){
//獲取子查詢
SelectBody selectBody = ((SubSelect) join.getRightItem()).getSelectBody();
if(selectBody instanceof PlainSelect){
//檢查是否有*號(hào)
if(checkAsterisk(((PlainSelect) selectBody).getSelectItems())){
report.setDesc("請(qǐng)寫明查詢字段，不要使用select *");
report.setPass(false);
report.setLevel(Report.Level.ERROR);
return report;
}
}
}
}
//where子查詢中是否有*號(hào)
Expression where = tree.getWhere();
ExpressionVisitorAdapter adapter = new ExpressionVisitorAdapter();
adapter.setSelectVisitor( new MySelectVisitor(report));
where.accept(adapter);
return report;
}
@Override
public List<SqlTypes> scope() {
return Arrays.asList(SqlTypes.SELECT);
}
}Appender的實(shí)現(xiàn)類，DefaultAppender，默認(rèn)往控制臺(tái)輸出報(bào)告
public class DefaultAppender implements Appender {
@Override
public void print(List<Report> result) {
if (result == null || result.size() < 1){
return;
}
System.out.println("========報(bào)告如下=========");
for (Report report : result){
//不通過(guò)才打印
if (!report.isPass()){
System.out.println(report);
System.out.println();
}
}
}
}以上代碼測(cè)試結(jié)果如下：
Report{pass=false, desc='請(qǐng)寫明查詢字段，不要使用select *', sql='select * from test', level=ERROR, sample='null'}擴(kuò)展性設(shè)計(jì)
因?yàn)橐?guī)則較多，需要多個(gè)人協(xié)作共同完成。在剛剛的示例代碼中，每個(gè)規(guī)則實(shí)現(xiàn)后，都需要注冊(cè)才能生效。
//注冊(cè)select規(guī)則解析器和規(guī)則
Checker selectChecker = new SelectCheck();
CheckRule writeClearlySelectFieldRule = new WriteClearlySelectFieldRule();
selectChecker.registeRule(writeClearlySelectFieldRule);
CheckerHolder.registeChecker(selectChecker);
//注冊(cè)insert規(guī)則解析器和規(guī)則
Checker insertChecker = new InsertCheck();
CheckRule clearTableRule = new ClearTableRule();
insertChecker.registeRule(clearTableRule);
CheckerHolder.registeChecker(insertChecker);當(dāng)規(guī)則很多的時(shí)候，注冊(cè)相關(guān)的代碼就需要重復(fù)寫很多遍，作為一個(gè)“優(yōu)秀”的程序猿，怎么能容忍這樣的事情發(fā)生呢，因此我們采用了java的SPI機(jī)制。具體原理介紹請(qǐng)參照之前的文章“搞懂SPI擴(kuò)展機(jī)制”。
從上述代碼可以看出，有兩類實(shí)現(xiàn)需要注冊(cè)，一類是Checker實(shí)現(xiàn)類，一類是CheckRule實(shí)現(xiàn)類。因此在META-INF/services目錄下，新建兩個(gè)文件，文件名分別為兩個(gè)接口的全路徑，如下：
Checker文件內(nèi)容為：
CheckRule文件內(nèi)容為：
有了這兩個(gè)文件后，還需要使用ServiceLoader將所有實(shí)現(xiàn)類加載，并注冊(cè)在程序中，代碼如下：
/**
* java spi注冊(cè)checker和rule
*/
static {
ServiceLoader<Checker> checkers = ServiceLoader.load(Checker.class);
Iterator<Checker> iteratorChecker = checkers.iterator();
while (iteratorChecker.hasNext()) {
Checker checker = iteratorChecker.next();
CheckerHolder.registeChecker(checker);
}
ServiceLoader<CheckRule> services = ServiceLoader.load(CheckRule.class);
Iterator<CheckRule> iteratorCheckRule = services.iterator();
while (iteratorCheckRule.hasNext()) {
CheckRule rule = iteratorCheckRule.next();
List<SqlTypes> scopes = rule.scope();
for (SqlTypes scope : scopes) {
CheckerHolder.getCheckers().get(scope.toString()).registeRule(rule);
}
}
}以上便是整個(gè)SQL檢查模塊的完整實(shí)現(xiàn)。
總結(jié)
整個(gè)工具由SQL獲取、SQL檢查、報(bào)告渲染三部分構(gòu)成。SQL可從程序的mapper.xml中獲取，也可在應(yīng)用程序運(yùn)行過(guò)程中輸出到文本日志，從文本日志中讀取。SQL檢查可使用JSqlParser實(shí)現(xiàn)，也可使用Antrl、Druid等工具實(shí)現(xiàn)。報(bào)告渲染可根據(jù)需要輸出至HTML、數(shù)據(jù)庫(kù)、PDF等。
目前我們只實(shí)現(xiàn)了從mapper.xml中獲取Sql，使用JsqlParser解析SQL檢查，結(jié)果輸出至控制臺(tái)和Html文件，后續(xù)根據(jù)需要，再編寫其它的實(shí)現(xiàn)。
關(guān)注“程序員順仔和他的朋友們”，帶你了解更多開發(fā)和架構(gòu)知識(shí)。