SmartSql使用教程（1）——初探，建立一個簡單的CURD介面服務

一、引言

最近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 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()
)

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的起點。

<?xml version="1.0" encoding="utf-8" ?>
<!--
//*******************************
// Create By Noah.Ji
// Date 2019-05-10
// Github : https://github.com/noahjzc/SmartSqlSample
//*******************************-->
<SmartSqlMapConfig xmlns="http://SmartSql.net/schemas/SmartSqlMapConfig.xsd">
  <!-- 允許使用快取（以後章節細講） -->
  <Settings IsCacheEnabled="true" />
  <!-- 屬性、特性配置節點，這裡只配置一個連線字串 -->
  <Properties>
    <Property Name="ConnectionString" Value="Data Source=localhost;database=SmartSqlSample;uid=sa;pwd=123456" />
    <Property Name="ReadOneConnectionString" Value="Data Source=123.123.123.123;database=SmartSqlSample;uid=sa;pwd=123456" />
  </Properties>
  <!-- 資料庫配置 Start -->
  <Database>
    <DbProvider Name="SqlServer" />
    <Write Name="Sample-Write" ConnectionString="${ConnectionString}" />
    <!-- 多讀節點配置 -->
    <!--
    <Read Name="Sample-Node-1" ConnectionString="${ReadOneConnectionString}" Weight="60"/>
    <Read Name="Sample-Node-2" ConnectionString="Data Source=456.456.456.456;database=SmartSqlSample;uid=sa;pwd=123456" Weight="40"/>
    -->
  </Database>
  <!-- 資料庫配置 End -->
  <!-- 資料Map配置 Start -->
  <SmartSqlMaps>
    <!-- 資料夾 -->
    <SmartSqlMap Path="Maps" Type="Directory"></SmartSqlMap>

    <!-- 資料夾及子集（遞迴獲取資料夾下所有Map檔案） -->
    <!--<SmartSqlMap Path="Maps" Type="DirectoryWithAllSub"></SmartSqlMap>-->

    <!-- 單個檔案 -->
    <!--<SmartSqlMap Path="Maps/T_Article.xml" Type="File"></SmartSqlMap>-->

    <!-- 嵌入式資源 -->
    <!--<SmartSqlMap Path="SmartSqlSampleChapterOne.Maps.T_Article.xml, SmartSqlSampleChapterOne" Type="Embedded"></SmartSqlMap>-->

    <!-- http資源 -->
    <!--<SmartSqlMap Type="Uri" Path="https://smartsql.net/Maps/T_Article.xml" />-->
  </SmartSqlMaps>
  <!-- 資料Map配置 End -->
</SmartSqlMapConfig>

2.3 表Map配置

2.3.1 Root節點

<SmartSqlMap Scope="Article" xmlns="http://SmartSql.net/schemas/SmartSqlMap.xsd">
...
</SmartSqlMap>

這裡的關鍵在於Scope，這個屬性是用於定位Map的。

2.3.2 CUD配置

<!--新增-->
<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 id=@Id
</Statement>

2.3.3 通用查詢節點

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

這個Statement節點其實和別的節點沒什麼區別。SmartSql允許Statement的巢狀。使用規則如下面這段配置

<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配置中。我們使用了Include標籤來引入上面定義好的Id為QueryParams的Statement，這樣就做到了查詢配置的通用性。例如我還可以將QueryParams配置到分頁和查詢結果數的配置中。如下：

<!--獲取分頁資料-->
<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. 讓配置工作起來

其實到了這一步一切都順其自然了。我感覺沒有什麼可以多講了。直接上程式碼了！

using Microsoft.Extensions.DependencyInjection;
using SmartSql;
using SmartSqlSampleChapterOne.Entity;
using System;
using System.Collections.Generic;

namespace SmartSqlSampleChapterOne.DataAccess
{
    /// <summary>
    /// 
    /// </summary>
    public class ArticleDataAccess
    {
        private readonly ISqlMapper _sqlMapper;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sp"></param>
        public ArticleDataAccess(IServiceProvider sp)
        {
            _sqlMapper = sp.GetSmartSql("SmartSqlSampleChapterOne").SqlMapper;
        }

        /// <summary>
        /// Insert
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public long Insert(T_Article article)
        {
            return _sqlMapper.ExecuteScalar<long>(new RequestContext
            {
                Scope = "Article",
                SqlId = "Insert",
                Request = article
            });
        }

        /// <summary>
        /// Update
        /// </summary>
        /// <param name="article"></param>
        /// <returns></returns>
        public int Update(T_Article article)
        {
            return _sqlMapper.Execute(new RequestContext
            {
                Scope = "Article",
                SqlId = "Update",
                Request = article
            });
        }

        /// <summary>
        /// DyUpdate
        /// </summary>
        /// <param name="updateObj"></param>
        /// <returns></returns>
        public int DyUpdate(object updateObj)
        {
            return _sqlMapper.Execute(new RequestContext
            {
                Scope = "Article",
                SqlId = "Update",
                Request = updateObj
            });
        }

        /// <summary>
        /// Delete
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(long id)
        {
            return _sqlMapper.Execute(new RequestContext
            {
                Scope = "Article",
                SqlId = "Delete",
                Request = new { Id = id }
            });
        }

        /// <summary>
        /// GetById
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T_Article GetById(long id)
        {
            return _sqlMapper.QuerySingle<T_Article>(new RequestContext
            {
                Scope = "Article",
                SqlId = "GetEntity",
                Request = new { Id = id }
            });
        }

        /// <summary>
        /// Query
        /// </summary>
        /// <param name="queryParams"></param>
        /// <returns></returns>
        public IEnumerable<T_Article> Query(object queryParams)
        {
            return _sqlMapper.Query<T_Article>(new RequestContext
            {
                Scope = "Article",
                SqlId = "Query",
                Request = queryParams
            });
        }

        /// <summary>
        /// GetRecord
        /// </summary>
        /// <param name="queryParams"></param>
        /// <returns></returns>
        public int GetRecord(object queryParams)
        {
            return _sqlMapper.ExecuteScalar<int>(new RequestContext
            {
                Scope = "Article",
                SqlId = "GetRecord",
                Request = queryParams
            });
        }

        /// <summary>
        /// IsExist
        /// </summary>
        /// <param name="queryParams"></param>
        /// <returns></returns>
        public bool IsExist(object queryParams)
        {
            return _sqlMapper.QuerySingle<bool>(new RequestContext
            {
                Scope = "Article",
                SqlId = "IsExist",
                Request = queryParams
            });
        }
    }
}

 

4. 最後一步

4.1 ArticleController

有了DataAccess我們可以輕鬆的運算元據庫了。最後一步我們建立一個Controller，對外暴露一些介面吧。

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
            });
        }
    }
}

4.2 Startup

前面我們已經把SmartSql注入到了DI。現在我們再完善一下它，把Mvc和Swagger也注入進去。

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Swashbuckle.AspNetCore.Swagger;
using System;
using System.IO;
using SmartSql.ConfigBuilder;
using SmartSqlSampleChapterOne.DataAccess;

namespace SmartSqlSampleChapterOne
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }
        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            services.AddLogging(logging =>
            {
                logging.SetMinimumLevel(LogLevel.Trace);
                logging.AddConsole();
            });

            // register smartsql
            services.AddSmartSql(builder =>
            {
                builder.UseAlias("SmartSqlSampleChapterOne");       // 定義例項別名，在多庫場景下適用。
                //.UseXmlConfig(ResourceType.File,"MyConfig.xml");
            });

            // register data access
            services.AddSingleton<ArticleDataAccess>();

            // register swagger
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("SmartSqlSampleChapterOne", new Info
                {
                    Title = "SmartSqlSample.ChapterOne",
                    Version = "v1",
                    Description = "SmartSqlSample.ChapterOne"
                });
                var filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SmartSqlSampleChapterOne.xml");
                if (File.Exists(filePath)) c.IncludeXmlComments(filePath);
            });

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment()) app.UseDeveloperExceptionPage();
            app.UseMvc();

            app.UseSwagger(c => { });
            app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/SmartSqlSampleChapterOne/swagger.json", "SmartSqlSampleChapterOne"); });
        }
    }
}

好了！至此專案的大部分元素都做了一個簡單介紹。我們來看看最終的執行結果吧。

5. 介面演示

介面預覽

新增介面

獲取介面

查詢介面

 

6. 結語

本篇文章簡單介紹了一下如何使用SmartSql從無到有，完成一個單表的CURD介面實現。但其實SmartSql是一個非常強大的ORM，它還有許多特性沒有展開。再接下來的系列文章中。我會一一為大家介紹。

示例程式碼連結在這裡

 

下期預告：使用動態代理實現CURD