SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

2019年7月2日10:59:03SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳已关闭评论 501

一、弁言

近来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接口效劳。

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

 

1. 竖立DB

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

Create Database SmartSqlSample GO
Use SmartSqlSample GO
Create Table T_Article ( Id bigint not null primary key identity(1,1), Title nvarchar(255) not null, Content nvarchar(max) null, Author nvarchar(255) null, Status int not null, CreateTime datetime not null default getdate(), ModifiedTime datetime 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(轻易我们接口测试)

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

 

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.Ji  5 // Date 2019-05-10  6 // Github : https://github.com/noahjzc/SmartSqlSample  7 //*******************************-->
 8 <SmartSqlMapConfig xmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
 9   <!-- 许可运用缓存(今后章节细讲) -->
10   <Settings IsCacheEnabled="true" />
11   <!-- 属性、特征设置装备摆设节点,这里只设置装备摆设一个衔接字符串 -->
12   <Properties>
13     <Property Name="ConnectionString" Value="Data Source=localhost;database=SmartSqlSample;uid=sa;pwd=123456" />
14     <Property Name="ReadOneConnectionString" Value="Data Source=123.123.123.123;database=SmartSqlSample;uid=sa;pwd=123456" />
15   </Properties>
16   <!-- 数据库设置装备摆设 Start -->
17   <Database>
18     <DbProvider Name="SqlServer" />
19     <Write Name="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     <SmartSqlMap Path="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 <SmartSqlMap Scope="Article" xmlns="http://SmartSql.net/schemas/SmartSqlMap.xsd">
2 ... 3 </SmartSqlMap>

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

2.3.2 CUD设置装备摆设

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

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

CUD设置装备摆设

2.3.3 通用查询节点

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

<Statement Id="QueryParams">
  <Where>
    <IsGreaterEqual Prepend="And" Property="Id" CompareValue="0"> T.Id = @Id </IsGreaterEqual>
    <IsNotEmpty Prepend="And" Property="Title"> T.Title Like '%'[email protected]+'%' </IsNotEmpty>
    <IsNotEmpty Prepend="And" Property="Ids"> T.Id IN @Ids </IsNotEmpty>
  </Where>
</Statement>

通用查询节点

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

<Statement Id="Query"> SELECT T.* FROM T_Article T <Include RefId="QueryParams" />
  <Switch Prepend="Order By" Property="OrderBy">
    <Default> T.id Desc </Default>
  </Switch>
  <IsNotEmpty Prepend="Limit" Property="Taken">@Taken</IsNotEmpty>
</Statement>

Query

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

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

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

分页及效果数查询

2.4 Startup

注入SmartSql

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

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

 

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

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

 1 using Microsoft.Extensions.DependencyInjection;  2 using SmartSql;  3 using SmartSqlSampleChapterOne.Entity;  4 using System;  5 using System.Collections.Generic;  6 
 7 namespace SmartSqlSampleChapterOne.DataAccess  8 {  9     /// <summary>
 10     /// 
 11     /// </summary>
 12     public class ArticleDataAccess  13  {  14         private readonly ISqlMapper _sqlMapper;  15 
 16         /// <summary>
 17         /// 
 18         /// </summary>
 19         /// <param name="sp"></param>
 20         public ArticleDataAccess(IServiceProvider sp)  21  {  22             _sqlMapper = sp.GetSmartSql("SmartSqlSampleChapterOne").SqlMapper;  23  }  24 
 25         /// <summary>
 26         /// Insert  27         /// </summary>
 28         /// <param name="article"></param>
 29         /// <returns></returns>
 30         public long Insert(T_Article article)  31  {  32             return _sqlMapper.ExecuteScalar<long>(new RequestContext  33  {  34                 Scope = "Article",  35                 SqlId = "Insert",  36                 Request = article  37  });  38  }  39 
 40         /// <summary>
 41         /// Update  42         /// </summary>
 43         /// <param name="article"></param>
 44         /// <returns></returns>
 45         public int Update(T_Article article)  46  {  47             return _sqlMapper.Execute(new RequestContext  48  {  49                 Scope = "Article",  50                 SqlId = "Update",  51                 Request = article  52  });  53  }  54 
 55         /// <summary>
 56         /// DyUpdate  57         /// </summary>
 58         /// <param name="updateObj"></param>
 59         /// <returns></returns>
 60         public int DyUpdate(object updateObj)  61  {  62             return _sqlMapper.Execute(new RequestContext  63  {  64                 Scope = "Article",  65                 SqlId = "Update",  66                 Request = updateObj  67  });  68  }  69 
 70         /// <summary>
 71         /// Delete  72         /// </summary>
 73         /// <param name="id"></param>
 74         /// <returns></returns>
 75         public int Delete(long id)  76  {  77             return _sqlMapper.Execute(new RequestContext  78  {  79                 Scope = "Article",  80                 SqlId = "Delete",  81                 Request = new { Id = id }  82  });  83  }  84 
 85         /// <summary>
 86         /// GetById  87         /// </summary>
 88         /// <param name="id"></param>
 89         /// <returns></returns>
 90         public T_Article GetById(long id)  91  {  92             return _sqlMapper.QuerySingle<T_Article>(new RequestContext  93  {  94                 Scope = "Article",  95                 SqlId = "GetEntity",  96                 Request = new { Id = id }  97  });  98  }  99 
100         /// <summary>
101         /// Query 102         /// </summary>
103         /// <param name="queryParams"></param>
104         /// <returns></returns>
105         public IEnumerable<T_Article> Query(object queryParams) 106  { 107             return _sqlMapper.Query<T_Article>(new RequestContext 108  { 109                 Scope = "Article", 110                 SqlId = "Query", 111                 Request = queryParams 112  }); 113  } 114 
115         /// <summary>
116         /// GetRecord 117         /// </summary>
118         /// <param name="queryParams"></param>
119         /// <returns></returns>
120         public int GetRecord(object queryParams) 121  { 122             return _sqlMapper.ExecuteScalar<int>(new RequestContext 123  { 124                 Scope = "Article", 125                 SqlId = "GetRecord", 126                 Request = queryParams 127  }); 128  } 129 
130         /// <summary>
131         /// IsExist 132         /// </summary>
133         /// <param name="queryParams"></param>
134         /// <returns></returns>
135         public bool IsExist(object queryParams) 136  { 137             return _sqlMapper.QuerySingle<bool>(new RequestContext 138  { 139                 Scope = "Article", 140                 SqlId = "IsExist", 141                 Request = queryParams 142  }); 143  } 144  } 145 }

ArticleDataAccess

 

4. 末了一步

4.1 ArticleController

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

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

using Microsoft.AspNetCore.Mvc; using SmartSqlSampleChapterOne.DataAccess; using SmartSqlSampleChapterOne.Entity; using System.Collections.Generic; namespace SmartSqlSampleChapterOne.Controllers { /// <summary>
    /// 
    /// </summary>
    [Route("[controller]/[action]")] public class ArticleController : Controller { private readonly ArticleDataAccess _articleDataAccess; /// <summary>
        /// constructor /// </summary>
        /// <param name="articleDataAccess"></param>
        public ArticleController(ArticleDataAccess articleDataAccess) { _articleDataAccess = articleDataAccess; } /// <summary>
        /// 
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
 [HttpPost] public T_Article Add([FromBody] T_Article article) { article.Id = _articleDataAccess.Insert(article); return article; } /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
 [HttpGet] public T_Article Get([FromQuery] long id) { return _articleDataAccess.GetById(id); } /// <summary>
        /// 
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
 [HttpPost] public bool Update([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] int status) { return _articleDataAccess.DyUpdate(new { Id = id, Status = status }) > 0; } /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
 [HttpGet] public bool IsExist([FromQuery] long id) { 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也注入进去。

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

 1 using Microsoft.AspNetCore.Builder;  2 using Microsoft.AspNetCore.Hosting;  3 using Microsoft.Extensions.Configuration;  4 using Microsoft.Extensions.DependencyInjection;  5 using Microsoft.Extensions.Logging;  6 using Swashbuckle.AspNetCore.Swagger;  7 using System;  8 using System.IO;  9 using SmartSql.ConfigBuilder; 10 using SmartSqlSampleChapterOne.DataAccess; 11 
12 namespace SmartSqlSampleChapterOne 13 { 14     public class Startup 15  { 16         public Startup(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, visit https://go.microsoft.com/fwlink/?LinkID=398940
24         public void ConfigureServices(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", new Info 48  { 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 void Configure(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. 接口演示

接口预览

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

增加接口

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

猎取接口

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

查询接口

SmartSql运用教程(1)——初探,竖立一个简朴的CURD接口效劳

 

6. 结语

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

示例代码链接在这里

 

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

 

avatar