中华前沿观察网,欢迎您!

帮助中心 广告联系

网站关键词: 中华前沿观察网

大量SQL的解决方案——sdmap

来源:时间:2020-03-29 10:08:16 阅读:1

大量SQL的解决方案——sdmap

最近看到群里面经常讨论大型应用中 SQL的管理办法,有人说用EF/EFCore,但很多人不信任它生成SQL的语句;有人说用Dapper,但将SQL写到代码中有些人觉得不合适;有人提出用存储过程,但现在舆论纷纷反对这种做法;有人提出了iBatis.NET,它可以配置确保高灵活性高性能,也提供动态SQL的功能,但已经多年没有维护。

在几年前,我们某项目中就有总共 4MB以上的SQL语句文本,我也注意到产品做大后会,一定出现这个问题,所以我就依照MyBatis的核心思想,支持可配置、动态SQL,但去除了臃肿的xml,自己实现了一套简单好用的语法,然后开源了出来,名字就叫sdmap

在我的介绍页面上已经指出, sdmap的如下特性:

  • 非常简单的语法来描述动态 SQL

  • 使用了 EmitCIL来确保性能;

  • VisualStudio插件支持,实现了代码高亮、代码折叠、快速导航的特性;

  • 支持所有主流数据库,如 MySQLSQLServerSQLite等(只要Dapper能支持);

  • 可以扩展支持非关系型数据库,如 Neo4j

  • 单元测试全覆盖。

语法

如图:

该语法有如下特点:

  • namespace关键字表达名字空间;

  • sql关键字表示模板语句;

  • #号的特殊语法可以进行一些判断,里面有isEqual<>#isNotEmpty<>等特殊语法;

  • #include<>,可以包含另一个SQL语句;

  • 语句可以嵌套, sql{}中可以包含另一个sql{}

我们可以对比一下 iBatis/MyBatis的语法:

  1. <!DOCTYPE mapper

  2. PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

  3. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">


  4. <mappernamespace="org.apache.ibatis.submitted.rounding.Mapper">

  5. <resultMaptype="org.apache.ibatis.submitted.rounding.User" id="usermap">

  6. <idcolumn="id" property="id"/>

  7. <resultcolumn="name" property="name"/>

  8. <resultcolumn="funkyNumber" property="funkyNumber"/>

  9. <resultcolumn="roundingMode" property="roundingMode"/>

  10. </resultMap>


  11. <selectid="getUser" resultMap="usermap">

  12. select * from users

  13. </select>

  14. <insertid="insert">

  15. insert into users (id, name, funkyNumber, roundingMode) values (

  16. #{id}, #{name}, #{funkyNumber}, #{roundingMode}

  17. )

  18. </insert>


  19. <resultMaptype="org.apache.ibatis.submitted.rounding.User" id="usermap2">

  20. <idcolumn="id" property="id"/>

  21. <resultcolumn="name" property="name"/>

  22. <resultcolumn="funkyNumber" property="funkyNumber"/>

  23. <resultcolumn="roundingMode" property="roundingMode"

  24. typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>

  25. </resultMap>

  26. <selectid="getUser2" resultMap="usermap2">

  27. select * from users2

  28. </select>

  29. <insertid="insert2">

  30. insert into users2 (id, name, funkyNumber, roundingMode) values (

  31. #{id}, #{name}, #{funkyNumber}, #{roundingMode, typeHandler=org.apache.ibatis.type.EnumTypeHandler}

  32. )

  33. </insert>


  34. </mapper>

相比之下,由于 XML的存在,MyBatis的语法有更多噪音。

简单应用

Hello World

其实和 MyBatis不同,sdmap设计之初就只考虑好好做一个小巧、精致、快速的模板引擎。因此sdmap可以不依赖于任何数据库,只做字符串解析,最简单的代码是:

  1. // 安装NuGet包:sdmap

  2. varc =newSdmapCompiler;

  3. c.AddSourceCode("sql v1 {Hello World}");

  4. Console.WriteLine(c.Emit("v1", )); // Hello World

参数传入

当然有一些前端输入,这样就需要第二个参数:

  1. varc =newSdmapCompiler;

  2. c.AddSourceCode("sql v1 {Hello #prop<Name>!}");

  3. Console.WriteLine(c.Emit("v1", new{ Name = "Hero"}));// Hello Hero!

注意我使用了一个 #prop<>的语法,这是sdmap中调用指令的语句,表示将Name属性按原样显示在此处。

参数判断

有些语句需要根据前端的不同而不同,比如典型的“动态 SQL”问题,如果前端传了参数,则执行过滤,没有传则不过滤,这样的代码如下:

  1. varc =newSdmapCompiler;

  2. c.AddSourceCode(@"

  3. sql v1

  4. {

  5. SELECT * FROM [Customer]

  6. WHERE 1=1

  7. #isNotEmpty<Location, sql {AND Location = '#prop<Location>'}

  8. }");

  9. Console.WriteLine(c.Emit("v1", new{ Id = 1, Location = "长沙"}));

  10. Console.WriteLine(c.Emit("v1", new{ Id = 2, Location = ""}));

输出结果如下:

  1. SELECT * FROM [Customer]

  2. WHERE 1=1

  3. AND Location = '长沙'


  4. SELECT * FROM [Customer]

  5. WHERE 1=1

可见,关键的那个 isNotEmpty<>控制了Location判断的语句。

扩展:sdmap.ext

每次使用时,都需要实例化一个 SdmapCompiler来加载sdmap语句很麻烦,在项目中,这部分逻辑重用度非常高,因此我写了一个扩展:sdmap.ext,定义了ISdmapEmiter接口,该接口定义如下:

  1. publicinterfaceISdmapEmiter

  2. {

  3. stringEmit(stringstatementId,objectparameters);

  4. }

相当于最简单的生成器,然后我写了几个内置实现,可以直接从文件系统或者程序集嵌入的资源中读入这些文件:

  1. publicclassEmbeddedResourceSqlEmiter : ISdmapEmiter

  2. {

  3. publicstaticEmbeddedResourceSqlEmiter CreateFrom(Assembly assembly);

  4. // ...

  5. }


  6. publicclassMultipleAssemblyEmbeddedResourceSqlEmiter : ISdmapEmiter

  7. {

  8. publicstaticMultipleAssemblyEmbeddedResourceSqlEmiter CreateFrom(paramsAssembly[] assemblies);

  9. // ...

  10. }


  11. publicclassFileSystemSqlEmiter : ISdmapEmiter

  12. {

  13. publicstaticFileSystemSqlEmiter FromSqlDirectory(

  14. stringsqlDirectory,

  15. boolensureCompiled =false);


  16. publicstaticFileSystemSqlEmiter FromSqlDirectoryAndWatch(

  17. stringsqlDirectory,

  18. boolensureCompiled =false);

  19. // ...

  20. }

那么有人会问,数据库参数化该如何实现呢?

扩展:sdmap.ext.Dapper

答案是 Dappersdmap访问数据库时,依赖Dapper做参数化。其实很好理解,sdmap只做数据库访问时的SQL模板引擎前端,Dapper做后端(当然不一定非要用Dapper),sdmap只负责生成SQL语句。

但随着大家使用越来越多,我也注意到确实可以写一些东西,便于大家更好地配合 Dapper一起使用。因此我写了另外两个扩展:sdmap.extsdmap.ext.Dapper

其中 sdmap.ext仍然和数据库无关,定义了一些.sdmap文件的读取和自动加载逻辑;sdmap.ext.Dapper依赖于Dapper,定义了一些便利方法:

如图,用过 Dapper的朋友知道,DapperIDbConnection定义了一套扩展方法,这里我也为IDbConnection定义了一套一样的扩展,只要最后加了ByMap后缀,第二个参数都为sqlMapName,与其传入原始的SQL语句,此处将传入定义在.sdmap文件中的配置,如原先使用Dapper的朋友,代码可能这样写:

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

换成 sdmap后,代码应该是这样写:

  1. vardata = _db.QueryByMap<Customer>("Customers.GetById");

然后 sdmap配置如下:

  1. namespaceCustomers

  2. {

  3. sql GetById

  4. {

  5. SELECT * FROM [Customer] WHERE Id = @Id

  6. }

  7. }

注意, sdmap使用了Dapper的参数化方式,只需在SQL中写@Id这样的语句,即可自动实现参数化,得出结果完全一样,并且SQL不存在注入问题,代码中不包含SQL语句,语句都写在配置文件中。

数组参数化

由于 Dapper的存在,sdmap相当于也自动支持了数组的参数化,只要像Dapper那样写IN即可:

  1. namespaceCustomer

  2. {

  3. sql GetByIds

  4. {

  5. SELECT * FROM [Customer] WHERE Id IN @Ids

  6. }

  7. }

相关链接

Github地址

https://github.com/sdcb/sdmap 我的 Github首页还包含了使用sdmap.ext.Dapper的一步一步使用教程,可以依照上面的使用。

文档地址

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

所有指令参考链接:

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

NuGet包地址

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

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

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

VisualStudio插件地址

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

VS插件提供了.sdmap文件代码高亮、自动定位、代码折叠的功能,可以不装,但不装就没这些体验。

总结

我写 sdmap最初纯粹是因为想挑战自己,它包含了【编译器前端——ANTLR】、【编译器后端——CIL】、【VisualStudio插件如何制作】、单元测试、文档等主题。

但后来随着这个项目的发展,越来越多的朋友用了起来。用过的都纷纷提出了自己的想法,然后做了许多润色,解决了不少局限性,但我从未做过推广——这是我第一次将这个项目用文字的形式发表出来。希望这个项目能给大家以管理大量 SQL的启发。

推荐阅读:快门速度怎么调