一、弁言

近来SmartSql被正式引入到了NCC,借着这个契机写一个运用教程系列

 

二、SmartSql简介[摘自官方文档]

1. SmartSql是什么?

SmartSql = MyBatis + Cache(Memory | Redis) + R/W Splitting +Dynamic Repository + Diagnostics ......

2. SmartSql的特征

简约、高效、高机能、扩展性、监控、渐进式开辟!

3. 她是怎样事情的?

  SmartSql 自创了 MyBatis 的头脑,运用 XML 来治理 SQL ,并且供应了若干个挑选器标签来消弭代码层面的种种 if/else 的推断分支。

  SmartSql将治理你的 SQL ,并且经由过程挑选标签来保护正本你在代码层面的种种前提推断,使你的代码越发幽美。

4. 为什么挑选SmartSql?

  DotNet 系统下大都是 Linq 系的 ORM,Linq 很好,消弭了开辟人员对 SQL 的依靠。 但却无视了一点,SQL 自身实在不庞杂,并且在庞杂查询场景傍边开辟人员很难经由过程编写Linq来天生优越机能的SQL,置信运用过EF的同砚一定有如许的体验:“我想好了Sql怎样写,然后再来写Linq,完了能够还要再查看一下Linq输出的Sql是什么样的“。这是异常蹩脚的体验。要想对Sql做相对的优化,那末开辟者必需对Sql有相对的控制权。其余Sql自身很简朴,为什么要增添一层翻译器呢?

 

三、最先SmartSql之旅

  知道了SmartSql是什么,那接下来我们最先竖立一个项目从0最先运用SmartSql写一个简朴的CURD接口效劳。

  先上一个项目构造,然后我们逐一剖析他们的作用

 

1. 竖立DB

  这里我用的DB是MSSql,直接贴剧本了。

Create DatabaseSmartSqlSampleGO
UseSmartSqlSampleGO
Create TableT_Article ( Idbigint not null primary key identity(1,1), Titlenvarchar(255) not null, Contentnvarchar(max) null, Authornvarchar(255) null, Statusint not null, CreateTimedatetime not null default getdate(), ModifiedTimedatetime not null default getdate() )

Init剧本

2. SmartSql 基本设置装备摆设

2.1 增加Nuget依靠

  SmartSql的库能够直接在Nuget上找到,但由于.NetCoreMVC的项目如今自带了DI依靠注入的干系,我们只需要直接援用SmartSql.DI.Extension就可以够了。

  项目的依靠性包罗了

  1. AspNetCore基本库

  2. SmartSql.DI.Extension(我们的主角)

  3. Swashbuckle.AspNetCore(轻易我们接口测试)

 

2.2 增加SmartSql设置装备摆设文件

  SmartSql是一个基于Xml设置装备摆设的ORM。这点和Mybatis没有什么分歧。若是你熟习Mybatis,置信你很快就可以顺应SmartSql。若是你之前没打仗过相似的ORM。那请随着这个教程,一步一步相识SmartSql的壮大。

  SmartSqlMapConfig.xml,SmartSql的出发点。

1 <?xml version="1.0" encoding="utf-8"?>
2 <!--
3 //*******************************4 // Create By Noah.Ji5 // Date 2019-05-106 // Github : https://github.com/noahjzc/SmartSqlSample7 //*******************************-->
8 <SmartSqlMapConfigxmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
9 <!--许可运用缓存(今后章节细讲)-->
10 <SettingsIsCacheEnabled="true" />
11 <!--属性、特征设置装备摆设节点,这里只设置装备摆设一个衔接字符串-->
12 <Properties>
13 <PropertyName="ConnectionString"Value="Data Source=localhost;database=SmartSqlSample;uid=sa;pwd=123456" />
14 <PropertyName="ReadOneConnectionString"Value="Data Source=123.123.123.123;database=SmartSqlSample;uid=sa;pwd=123456" />
15 </Properties>
16 <!--数据库设置装备摆设 Start-->
17 <Database>
18 <DbProviderName="SqlServer" />
19 <WriteName="Sample-Write"ConnectionString="${ConnectionString}" />
20 <!--多读节点设置装备摆设-->
21 <!--
22 <Read Name="Sample-Node-1" ConnectionString="${ReadOneConnectionString}" Weight="60"/>23 <Read Name="Sample-Node-2" ConnectionString="Data Source=456.456.456.456;database=SmartSqlSample;uid=sa;pwd=123456" Weight="40"/>24 -->
25 </Database>
26 <!--数据库设置装备摆设 End-->
27 <!--数据Map设置装备摆设 Start-->
28 <SmartSqlMaps>
29 <!--文件夹-->
30 <SmartSqlMapPath="Maps"Type="Directory"></SmartSqlMap>
31
32 <!--文件夹及子集(递归猎取文件夹下一切Map文件)-->
33 <!--<SmartSqlMap Path="Maps" Type="DirectoryWithAllSub"></SmartSqlMap>-->
34
35 <!--单个文件-->
36 <!--<SmartSqlMap Path="Maps/T_Article.xml" Type="File"></SmartSqlMap>-->
37
38 <!--嵌入式资本-->
39 <!--<SmartSqlMap Path="SmartSqlSampleChapterOne.Maps.T_Article.xml, SmartSqlSampleChapterOne" Type="Embedded"></SmartSqlMap>-->
40
41 <!--http资本-->
42 <!--<SmartSqlMap Type="Uri" Path="https://smartsql.net/Maps/T_Article.xml" />-->
43 </SmartSqlMaps>
44 <!--数据Map设置装备摆设 End-->
45 </SmartSqlMapConfig>

2.3 表Map设置装备摆设

2.3.1 Root节点

1 <SmartSqlMapScope="Article"xmlns="http://SmartSql.net/schemas/SmartSqlMap.xsd">
2 ...3 </SmartSqlMap>

这里的关键在于Scope,这个属性是用于定位Map的。

2.3.2 CUD设置装备摆设

<!--新增-->
<StatementId="Insert">INSERT INTO T_Article (Title ,Content ,Author ,Status ,CreateTime ,ModifiedTime ) VALUES (@Title ,@Content ,@Author ,@Status ,@CreateTime ,GetDate() ); SELECT Scope_Identity();</Statement>
<!--删除-->
<StatementId="Delete">DELETE T_Article WHERE Id = @Id</Statement>
<!--更新-->
<StatementId="Update">UPDATE T_Article<Set>ModifiedTime = GetDate()<IsPropertyPrepend=","Property="Title">Title = @Title</IsProperty>
<IsPropertyPrepend=","Property="Content">Content = @Content</IsProperty>
<IsPropertyPrepend=","Property="Author">Author = @Author</IsProperty>
<IsPropertyPrepend=","Property="Status">Status = @Status</IsProperty>
<IsPropertyPrepend=","Property="CreateTime">CreateTime = @CreateTime</IsProperty>
</Set>Where id=@Id</Statement>

CUD设置装备摆设

2.3.3 通用查询节点

<StatementId="QueryParams">
<Where>
<IsGreaterEqualPrepend="And"Property="Id"CompareValue="0">T.Id = @Id</IsGreaterEqual>
<IsNotEmptyPrepend="And"Property="Title">T.Title Like '%'+@Title+'%'</IsNotEmpty>
<IsNotEmptyPrepend="And"Property="Ids">T.Id IN @Ids</IsNotEmpty>
</Where>
</Statement>

通用查询节点

这个Statement节点实在和其余节点没什么区分。SmartSql许可Statement的嵌套。运用划定规矩以下面这段设置装备摆设

<StatementId="Query">SELECT T.* FROM T_Article T<IncludeRefId="QueryParams" />
<SwitchPrepend="Order By"Property="OrderBy">
<Default>T.id Desc</Default>
</Switch>
<IsNotEmptyPrepend="Limit"Property="Taken">@Taken</IsNotEmpty>
</Statement>

Query

在这段Query设置装备摆设中。我们运用了Include标签来引入上面界说好的Id为QueryParams的Statement,如许就做到了查询设置装备摆设的通用性。比方我还能够将QueryParams设置装备摆设到分页和查询效果数的设置装备摆设中。以下:

<!--猎取分页数据-->
<StatementId="QueryByPage">SELECT T.* FROM T_Article As T<IncludeRefId="QueryParams" />
<SwitchPrepend="Order By"Property="OrderBy">
<Default>T.Id Desc</Default>
</Switch>Offset ((@PageIndex-1)*@PageSize) Rows Fetch Next @PageSize Rows Only;</Statement>

<!--猎取纪录数-->
<StatementId="GetRecord">SELECT Count(1) FROM T_Article T<IncludeRefId="QueryParams" />
</Statement>

分页及效果数查询

2.4 Startup

注入SmartSql

//register smartsql
services.AddSmartSql(builder =>{ builder.UseAlias("SmartSqlSampleChapterOne"); //界说实例别号,在多库场景下实用。//.UseXmlConfig(ResourceType.File,"MyConfig.xml");
});

在2.2中我们把基本设置装备摆设文件命名为SmartSqlMapConfig。这个是默许文件名,我们也能够像上面的解释代码一样。自界说设置装备摆设文件的称号。

 

3. 让设置装备摆设事情起来

实在到了这一步一切都顺其自然了。我觉得没有什么能够多讲了。直接上代码了!

1 usingMicrosoft.Extensions.DependencyInjection;2 usingSmartSql;3 usingSmartSqlSampleChapterOne.Entity;4 usingSystem;5 usingSystem.Collections.Generic;6 
7 namespaceSmartSqlSampleChapterOne.DataAccess8 {9 /// <summary>
10 ///
11 /// </summary>
12 public classArticleDataAccess13 {14 private readonlyISqlMapper _sqlMapper;15
16 /// <summary>
17 ///
18 /// </summary>
19 /// <param name="sp"></param>
20 publicArticleDataAccess(IServiceProvider sp)21 {22 _sqlMapper = sp.GetSmartSql("SmartSqlSampleChapterOne").SqlMapper;23 }24
25 /// <summary>
26 ///Insert27 /// </summary>
28 /// <param name="article"></param>
29 /// <returns></returns>
30 public longInsert(T_Article article)31 {32 return _sqlMapper.ExecuteScalar<long>(newRequestContext33 {34 Scope = "Article",35 SqlId = "Insert",36 Request =article37 });38 }39
40 /// <summary>
41 ///Update42 /// </summary>
43 /// <param name="article"></param>
44 /// <returns></returns>
45 public intUpdate(T_Article article)46 {47 return _sqlMapper.Execute(newRequestContext48 {49 Scope = "Article",50 SqlId = "Update",51 Request =article52 });53 }54
55 /// <summary>
56 ///DyUpdate57 /// </summary>
58 /// <param name="updateObj"></param>
59 /// <returns></returns>
60 public int DyUpdate(objectupdateObj)61 {62 return _sqlMapper.Execute(newRequestContext63 {64 Scope = "Article",65 SqlId = "Update",66 Request =updateObj67 });68 }69
70 /// <summary>
71 ///Delete72 /// </summary>
73 /// <param name="id"></param>
74 /// <returns></returns>
75 public int Delete(longid)76 {77 return _sqlMapper.Execute(newRequestContext78 {79 Scope = "Article",80 SqlId = "Delete",81 Request = new { Id =id }82 });83 }84
85 /// <summary>
86 ///GetById87 /// </summary>
88 /// <param name="id"></param>
89 /// <returns></returns>
90 public T_Article GetById(longid)91 {92 return _sqlMapper.QuerySingle<T_Article>(newRequestContext93 {94 Scope = "Article",95 SqlId = "GetEntity",96 Request = new { Id =id }97 });98 }99
100 /// <summary>
101 ///Query102 /// </summary>
103 /// <param name="queryParams"></param>
104 /// <returns></returns>
105 public IEnumerable<T_Article> Query(objectqueryParams)106 {107 return _sqlMapper.Query<T_Article>(newRequestContext108 {109 Scope = "Article",110 SqlId = "Query",111 Request =queryParams112 });113 }114
115 /// <summary>
116 ///GetRecord117 /// </summary>
118 /// <param name="queryParams"></param>
119 /// <returns></returns>
120 public int GetRecord(objectqueryParams)121 {122 return _sqlMapper.ExecuteScalar<int>(newRequestContext123 {124 Scope = "Article",125 SqlId = "GetRecord",126 Request =queryParams127 });128 }129
130 /// <summary>
131 ///IsExist132 /// </summary>
133 /// <param name="queryParams"></param>
134 /// <returns></returns>
135 public bool IsExist(objectqueryParams)136 {137 return _sqlMapper.QuerySingle<bool>(newRequestContext138 {139 Scope = "Article",140 SqlId = "IsExist",141 Request =queryParams142 });143 }144 }145 }

ArticleDataAccess

 

4. 末了一步

4.1 ArticleController

有了DataAccess我们能够轻松的操纵数据库了。末了一步我们竖立一个Controller,对外袒露一些接口吧。

usingMicrosoft.AspNetCore.Mvc;usingSmartSqlSampleChapterOne.DataAccess;usingSmartSqlSampleChapterOne.Entity;usingSystem.Collections.Generic;namespaceSmartSqlSampleChapterOne.Controllers {/// <summary>
///
/// </summary>
[Route("[controller]/[action]")]public classArticleController : Controller {private readonlyArticleDataAccess _articleDataAccess;/// <summary>
///constructor/// </summary>
/// <param name="articleDataAccess"></param>
publicArticleController(ArticleDataAccess articleDataAccess) { _articleDataAccess=articleDataAccess; }/// <summary>
///
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
[HttpPost]publicT_Article Add([FromBody] T_Article article) { article.Id=_articleDataAccess.Insert(article);returnarticle; }/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet]public T_Article Get([FromQuery] longid) {return_articleDataAccess.GetById(id); }/// <summary>
///
/// </summary>
/// <param name="article"></param>
/// <returns></returns>
[HttpPost]public boolUpdate([FromBody] T_Article article) {return _articleDataAccess.Update(article) > 0; }/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <param name="status"></param>
/// <returns></returns>
[HttpPost]public bool UpdateStatus([FromQuery] long id, [FromQuery] intstatus) {return _articleDataAccess.DyUpdate(new{ Id=id, Status=status })> 0; }/// <summary>
///
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet]public bool IsExist([FromQuery] longid) {return _articleDataAccess.IsExist(new{ Id=id }); }/// <summary>
///
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
[HttpGet]public IEnumerable<T_Article> Query([FromQuery] string key = "") {return _articleDataAccess.Query(new{ Title=key }); } } }

ArticleController

4.2 Startup

前面我们已把SmartSql注入到了DI。如今我们再完美一下它,把Mvc和Swagger也注入进去。

1 usingMicrosoft.AspNetCore.Builder;2 usingMicrosoft.AspNetCore.Hosting;3 usingMicrosoft.Extensions.Configuration;4 usingMicrosoft.Extensions.DependencyInjection;5 usingMicrosoft.Extensions.Logging;6 usingSwashbuckle.AspNetCore.Swagger;7 usingSystem;8 usingSystem.IO;9 usingSmartSql.ConfigBuilder;10 usingSmartSqlSampleChapterOne.DataAccess;11 
12 namespaceSmartSqlSampleChapterOne13 {14 public classStartup15 {16 publicStartup(IConfiguration configuration)17 {18 Configuration =configuration;19 }20
21 public IConfiguration Configuration { get; }22 //This method gets called by the runtime. Use this method to add services to the container.23 //For more information on how to configure your application, visithttps://go.microsoft.com/fwlink/?LinkID=398940
24 public voidConfigureServices(IServiceCollection services)25 {26 services.AddMvc();27
28 services.AddLogging(logging =>
29 {30 logging.SetMinimumLevel(LogLevel.Trace);31 logging.AddConsole();32 });33
34 //register smartsql
35 services.AddSmartSql(builder =>
36 {37 builder.UseAlias("SmartSqlSampleChapterOne"); //界说实例别号,在多库场景下实用。38 //.UseXmlConfig(ResourceType.File,"MyConfig.xml");
39 });40
41 //register data access
42 services.AddSingleton<ArticleDataAccess>();43
44 //register swagger
45 services.AddSwaggerGen(c =>
46 {47 c.SwaggerDoc("SmartSqlSampleChapterOne", newInfo48 {49 Title = "SmartSqlSample.ChapterOne",50 Version = "v1",51 Description = "SmartSqlSample.ChapterOne"
52 });53 var filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SmartSqlSampleChapterOne.xml");54 if(File.Exists(filePath)) c.IncludeXmlComments(filePath);55 });56
57 }58
59 //This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
60 public voidConfigure(IApplicationBuilder app, IHostingEnvironment env)61 {62 if(env.IsDevelopment()) app.UseDeveloperExceptionPage();63 app.UseMvc();64
65 app.UseSwagger(c =>{ });66 app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/SmartSqlSampleChapterOne/swagger.json", "SmartSqlSampleChapterOne"); });67 }68 }69 }

Startup

好了!至此项目的大部分元素都做了一个简朴引见。我们来看看终究的运转效果吧。

5. 接口演示

接口预览

增加接口

猎取接口

查询接口

 

6. 结语

本篇文章简朴引见了一下怎样运用SmartSql从无到有,完成一个单表的CURD接口完成。但实在SmartSql是一个异常壮大的ORM,它还有很多特征没有睁开。再接下来的系列文章中。我会逐一为人人引见。

示例代码链接在这里

 

下期预报:运用动态署理完成CURD

 

Last modification:March 25th, 2020 at 09:55 am
如果觉得我的文章对你有帮助,请随意赞赏