大量SQL的解決方案——sdmap

最近看到群裡面經常討論大型應用中 <code>SQL/<code>的管理辦法，有人說用<code>EF/<code>/<code>EFCore/<code>，但很多人不信任它生成<code>SQL/<code>的語句；有人說用<code>Dapper/<code>，但將<code>SQL/<code>寫到代碼中有些人覺得不合適；有人提出用存儲過程，但現在輿論紛紛反對這種做法；有人提出了<code>iBatis.NET/<code>，它可以配置確保高靈活性高性能，也提供動態<code>SQL/<code>的功能，但已經多年沒有維護。

在幾年前，我們某項目中就有總共 <code>4MB/<code>以上的<code>SQL/<code>語句文本，我也注意到產品做大後會，一定出現這個問題，所以我就依照<code>MyBatis/<code>的核心思想，支持可配置、動態<code>SQL/<code>，但去除了臃腫的<code>xml/<code>，自己實現了一套簡單好用的語法，然後開源了出來，名字就叫<code>sdmap/<code>。

在我的介紹頁面上已經指出， <code>sdmap/<code>的如下特性：

• 非常簡單的語法來描述動態 <code>SQL/<code>；

• 使用了 <code>EmitCIL/<code>來確保性能；

• 有 <code>V

  isualStudio/<code>插件支持，實現了代碼高亮、代碼摺疊、快速導航的特性；

• 支持所有主流數據庫，如 <code>MySQL/<code>、<code>SQLServer/<code>、<code>SQLite/<code>等（只要<code>Dapper/<code>能支持）；

• 可以擴展支持非關係型數據庫，如 <code>Neo4j/<code>；

• 單元測試全覆蓋。

語法

如圖：

該語法有如下特點：

• 用 <code>namespace/<code>關鍵字表達名字空間;

• 用 <code>sql/<code>關鍵字表示模板語句;

• 用 <code>#/<code>號的特殊語法可以進行一些判斷，裡面有<code>isEqual<>/<code>、<code>#isNotEmpty<>/<code>等特殊語法;

• 用 <code>#include<>/<code>，可以包含另一個<code>SQL/<code>語句;

• 語句可以嵌套， <code>sql{}/<code>中可以包含另一個<code>sql{}/<code>。

我們可以對比一下 <code>iBatis/<code>/<code>MyBatis/<code>的語法：

<code>i class="chrome-extension-mutihighlight chrome-extension-mutihighlight-style-3">apper/<code>
 
<code>PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"/<code>
<code>"http://mybatis.org/dtd/mybatis-3-mapper.dtd">/<code>

<code>appernamespace="org.apache.ibatis.submitted.rounding.Mapper">/<code>
<code><resultmap>type="org.apache.ibatis.submitted.rounding.User" id="usermap">/<resultmap>/<code>
<code>column="id" property="id"/> 
/<code>
<code><result>column="name" property="name"/>/<result>/<code>
<code><result>column="funkyNumber" property="funkyNumber"/>/<result>/<code>
<code><result>column="roundingMode" property="roundingMode"/>/<result>/<code>
<code>/<code>

<code><select>id="getUser" resultMap="usermap">/<select>/<code>
<code>select * from users/<code>
<code>/<code>
 
<code><insert>id="insert">/<insert>/<code>
<code>insert into users (id, name, funkyNumber, roundingMode) values (/<code>
<code>#{id}, #{name}, #{funkyNumber}, #{roundingMode}/<code>
<code>)/<code>
<code>/<code>

<code><resultmap>type="org.apache.ibatis.submitted.rounding.User" id="usermap2">/<resultmap>/<code>
<code>column="id" property="id"/>/<code>
<code><result>column="name" property="name"/>/<result>/<code> 
<code><result>column="funkyNumber" property="funkyNumber"/>/<result>/<code>
<code><result>column="roundingMode" property="roundingMode"/<result>/<code>
<code>typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>/<code>
<code>/<code>
<code><select>id="getUser2" resultMap="usermap2">/<select>/<code>
<code>select * from users2/<code>
<code>/<code>
<code><insert>id="insert2">/<insert>/<code> 
<code>insert into users2 (id, name, funkyNumber, roundingMode) values (/<code>
<code>#{id}, #{name}, #{funkyNumber}, #{roundingMode, typeHandler=org.apache.ibatis.type.EnumTypeHandler}/<code>
<code>)/<code>
<code>/<code>

<code>apper>/<code>

相比之下，由於 <code>XML/<code>的存在，<code>MyBatis/<code>的語法有更多噪音。

簡單應用

Hello World

其實和 <code>MyBatis/<code>不同，<code>sdmap/<code>設計之初就只考慮好好做一個小巧、精緻、快速的模板引擎。因此<code>sdmap/<code>可以不依賴於任何數據庫，只做字符串解析，最簡單的代碼是：

<code>// 安裝NuGet包：sdmap/<code>
<code>varc =newSdmapCompiler;/<code>
<code>c.AddSourceCode("sql v1 {Hello World}");/<code>
<code>Console.WriteLine(c.Emit("v1", )); // Hello World/<code>

參數傳入

當然有一些前端輸入，這樣就需要第二個參數：

<code>varc =newSdmapCompiler;/<code>
<code>c.AddSourceCode("sql v1 {Hello #prop<name>!}");/<name>/<code> 
<code>Console.WriteLine(c.Emit("v1", new{ Name = "Hero"}));// Hello Hero!/<code>

注意我使用了一個 <code>#prop<>/<code>的語法，這是<code>sdmap/<code>中調用指令的語句，表示將<code>Name/<code>屬性按原樣顯示在此處。

參數判斷

有些語句需要根據前端的不同而不同，比如典型的“動態 <code>SQL/<code>”問題，如果前端傳了參數，則執行過濾，沒有傳則不過濾，這樣的代碼如下：

<code>varc =newSdmapCompiler;/<code>
<code>c.AddSourceCode(@"/<code>
<code>sql v1/<code>
 
<code>{/<code>
<code>SELECT * FROM [Customer]/<code>
<code>WHERE 1=1/<code>
<code>#isNotEmpty<location>/<code>
<code>}");/<code>
<code>Console.WriteLine(c.Emit("v1", new{ Id = 1, Location = "長沙"}));/<code>
<code>Console.WriteLine(c.Emit("v1", new{ Id = 2, Location = ""}));/<code>

輸出結果如下：

<code>SELECT * FROM [Customer] /<code>
<code>WHERE 1=1 /<code>
<code>AND Location = '長沙'/<code> 

<code>SELECT * FROM [Customer] /<code>
<code>WHERE 1=1 /<code>

可見，關鍵的那個 <code>isNotEmpty<>/<code>控制了<code>Location/<code>判斷的語句。

擴展：sdmap.ext

每次使用時，都需要實例化一個 <code>SdmapCompiler/<code>來加載<code>sdmap/<code>語句很麻煩，在項目中，這部分邏輯重用度非常高，因此我寫了一個擴展：<code>sdmap.ext/<code>，定義了<code>ISdmapEmiter/<code>接口，該接口定義如下：

<code>publicinterfaceISdmapEmiter/<code>
<code>{/<code>
<code> 
stringEmit(stringstatementId,objectparameters);/<code>
<code>}/<code>

相當於最簡單的生成器，然後我寫了幾個內置實現，可以直接從文件系統或者程序集嵌入的資源中讀入這些文件：

<code>publicclassEmbeddedResourceSqlEmiter : ISdmapEmiter/<code>
<code>{ /<code>
<code>publicstaticEmbeddedResourceSqlEmiter CreateFrom(Assembly assembly);/<code>
<code>// ... 
/<code>
<code>}/<code>

<code>publicclassMultipleAssemblyEmbeddedResourceSqlEmiter : ISdmapEmiter/<code>
<code>{/<code>
<code>publicstaticMultipleAssemblyEmbeddedResourceSqlEmiter CreateFrom(paramsAssembly[] assemblies);/<code>
<code>// .../<code>
<code>}/<code>

<code>publicclassFileSystemSqlEmiter : ISdmapEmiter/<code>
 
<code>{/<code>
<code>publicstaticFileSystemSqlEmiter FromSqlDirectory(/<code>
<code>stringsqlDirectory,/<code>
<code>boolensureCompiled =false);/<code>

<code>publicstaticFileSystemSqlEmiter FromSqlDirectoryAndWatch(/<code>
<code>stringsqlDirectory,/<code>
<code>boolensureCompiled =false);/<code>
<code> 
// .../<code>
<code>}/<code>

那麼有人會問，數據庫參數化該如何實現呢？

擴展：sdmap.ext.Dapper

答案是 <code>Dapper/<code>，<code>sdmap/<code>訪問數據庫時，依賴<code>Dapper/<code>做參數化。其實很好理解，<code>sdmap/<code>只做數據庫訪問時的<code>SQL/<code>模板引擎前端，<code>Dapper/<code>做後端（當然不一定非要用<code>Dapper/<code>），<code>sdmap/<code>只負責生成<code>SQL/<code>語句。

但隨著大家使用越來越多，我也注意到確實可以寫一些東西，便於大家更好地配合 <code>Dapper/<code>一起使用。因此我寫了另外兩個擴展：<code>sdmap.ext/<code>和<code>sdmap.ext.Dapper/<code>。

其中 <code>sdmap.ext/<code>仍然和數據庫無關，定義了一些<code>.sdmap/<code>文件的讀取和自動加載邏輯；<code>sdmap.ext.D

如圖，用過 <code>Dapper/<code>的朋友知道，<code>Dapper/<code>為<code>IDbConnection/<code>定義了一套擴展方法，這裡我也為<code>IDbConnection/<code>定義了一套一樣的擴展，只要最後加了<code>ByMap/<code>後綴，第二個參數都為<code>sqlMapName/<code>，與其傳入原始的<code>SQL/<code>語句，此處將傳入定義在<code>.sdmap/<code>文件中的配置，如原先使用<code>Dapper/<code>的朋友，代碼可能這樣寫：

<code>vardata = _db.Query<customer>("SELECT * FROM [Customer] Where Id = @Id");/<customer>/<code>

換成 <code>sdmap/<code>後，代碼應該是這樣寫：

<code>vardata = _db.QueryByMap<customer>("Customers.GetById");/<customer>/<code>

然後 <code>sdmap/<code>配置如下：

<code>namespaceCustomers/<code>
 
<code>{/<code>
<code>sql GetById/<code>
<code>{/<code>
<code>SELECT * FROM [Customer] WHERE Id = @Id/<code>
<code>}/<code>
<code>}/<code>

注意， <code>sdmap/<code>使用了<code>Dapper/<code>的參數化方式，只需在<code>SQL/<code>中寫<code>@Id/<code>這樣的語句，即可自動實現參數化，得出結果完全一樣，並且<code>SQL/<code>不存在注入問題，代碼中不包含<code>SQL/<code>語句，語句都寫在配置文件中。

數組參數化

由於 <code>Dapper/<code>的存在，<code>sdmap/<code>相當於也自動支持了數組的參數化，只要像<code>Dapp

<code>namespaceCustomer/<code>
<code>{/<code>
<code>sql GetByIds/<code>
<code>{/<code>
<code>SELECT * FROM [Customer] WHERE Id IN @Ids/<code>
<code>}/<code>
<code>}/<code>

相關鏈接

<code>Github/<code>地址

https://github.com/sdcb/sdmap 我的 <code>Github/<code>首頁還包含了使用<code>sdmap.ext.Dapper/<code>的一步一步使用教程，可以依照

文檔地址

https://github.com/sdcb/sdmap/wiki

所有指令參考鏈接：

https://github.com/sdcb/sdmap/wiki/Common-macros

<code>NuGet/<code>包地址

• https://www.nuget.org/packages/sdmap

• https://www.nuget.org/packages/sdmap.ext

• https://www.nuget.org/packages/sdmap.ext.Dapper

<code>V

isualStudio插件/<code>地址

https://marketplace.visualstudio.com/items?itemName=sdmapvstool.sdmapvstool

<code>VS/<code>插件提供了<code>.sdmap/<code>文件代碼高亮、自動定位、代碼摺疊的功能，可以不裝，但不裝就沒這些體驗。

總結

我寫 <code>sdmap/<code>最初純粹是因為想挑戰自己，它包含了【編譯器前端——<code>ANTLR/<code>】、【編譯器後端——<code>CIL/<code>】、【<code>VisualStudio/<code>插件如何製作】、單元測試、文檔等主題。

但後來隨著這個項目的發展，越來越多的朋友用了起來。用過的都紛紛提出了自己的想法，然後做了許多潤色，解決了不少侷限性，但我從未做過推廣——這是我第一次將這個項目用文字的形式發表出來。希望這個項目能給大家以管理大量 <code>SQL/<code>的啟發。

閱讀更多 心萊科技 的文章

關鍵字: Server Studio 前端