无需编程，基于微软mssql数据库零代码生成CRUD增删改查RESTful API接口

回顾

通过之前一篇文章[ 无需编程，基于甲骨文oracle数据库零代码生成CRUD增删改查RESTful API接口 ]()的介绍，引入了FreeMarker模版引擎，通过配置模版实现创立和批改物理表构造SQL语句，并且通过配置oracle数据库SQL模版，基于oracle数据库，零代码实现crud增删改查。本文采纳同样的形式，很容易就能够反对微软SQL Server数据库。

MSSQL简介

SQL Server 是Microsoft 公司推出的关系型数据库管理系统。具备使用方便可伸缩性好与相干软件集成水平低等长处，可从运行Microsoft Windows的电脑和大型多处理器的服务器等多种平台应用。Microsoft SQL Server 是一个全面的数据库平台，应用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储性能，使您能够构建和治理用于业务的高可用和高性能的数据应用程序。

UI界面

通过课程对象为例，无需编程，基于MSSQL数据库，通过配置零代码实现CRUD增删改查RESTful API接口和治理UI。

创立课程表

编辑课程数据

课程数据列表

通过DBeaver数据库工具查问mssql数据

定义FreeMarker模版

创立表create-table.sql.ftl

CREATE TABLE "${tableName}" (<#list columnEntityList as columnEntity>  <#if columnEntity.dataType == "BOOL">    "${columnEntity.name}" BIT<#if columnEntity.defaultValue??> DEFAULT <#if columnEntity.defaultValue == "true">1<#else>0</#if></#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "INT">    "${columnEntity.name}" INT<#if columnEntity.autoIncrement == true> IDENTITY(1, 1)</#if><#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "BIGINT">    "${columnEntity.name}" BIGINT<#if columnEntity.autoIncrement == true> IDENTITY(1, 1)</#if><#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "FLOAT">    "${columnEntity.name}" FLOAT<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "DOUBLE">    "${columnEntity.name}" DOUBLE<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "DECIMAL">    "${columnEntity.name}" DECIMAL<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "DATE">    "${columnEntity.name}" DATE<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "TIME">    "${columnEntity.name}" TIME<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "DATETIME">    "${columnEntity.name}" DATETIME<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "TIMESTAMP">    "${columnEntity.name}" TIMESTAMP<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "CHAR">    "${columnEntity.name}" CHAR(${columnEntity.length})<#if columnEntity.defaultValue??> DEFAULT '${columnEntity.defaultValue}'</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "VARCHAR">    "${columnEntity.name}" VARCHAR(${columnEntity.length})<#if columnEntity.defaultValue??> DEFAULT '${columnEntity.defaultValue}'</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "PASSWORD">    "${columnEntity.name}" VARCHAR(200)<#if columnEntity.defaultValue??> DEFAULT '${columnEntity.defaultValue}'</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "ATTACHMENT">    "${columnEntity.name}" VARCHAR(4000)<#if columnEntity.defaultValue??> DEFAULT '${columnEntity.defaultValue}'</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "TEXT">    "${columnEntity.name}" VARCHAR(4000)<#if columnEntity.defaultValue??> DEFAULT '${columnEntity.defaultValue}'</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "LONGTEXT">    "${columnEntity.name}" TEXT<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "BLOB">    "${columnEntity.name}" BINARY<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#elseif columnEntity.dataType == "LONGBLOB">    "${columnEntity.name}" BINARY<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  <#else>    "${columnEntity.name}" VARCHAR(200)<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity_has_next>,</#if>  </#if></#list>);<#list columnEntityList as columnEntity>  <#if columnEntity.indexType?? && columnEntity.indexType == "PRIMARY">    ALTER TABLE "${tableName}" ADD CONSTRAINT "${columnEntity.indexName}" PRIMARY KEY ("${columnEntity.name}");  </#if>  <#if columnEntity.indexType?? && columnEntity.indexType == "UNIQUE">    ALTER TABLE "${tableName}" ADD CONSTRAINT "${columnEntity.indexName}" UNIQUE("${columnEntity.name}");  </#if>  <#if columnEntity.indexType?? && (columnEntity.indexType == "INDEX" || columnEntity.indexType == "FULLTEXT")>    CREATE INDEX "${columnEntity.indexName}" ON "${tableName}" ("${columnEntity.name}");  </#if></#list><#if indexEntityList??>  <#list indexEntityList as indexEntity>    <#if indexEntity.indexType?? && indexEntity.indexType == "PRIMARY">      ALTER TABLE "${tableName}" ADD CONSTRAINT "${indexEntity.name}" PRIMARY KEY (<#list indexEntity.indexLineEntityList as indexLineEntity>"${indexLineEntity.columnEntity.name}"<#if indexLineEntity_has_next>,</#if></#list>);    </#if>    <#if indexEntity.indexType?? && indexEntity.indexType == "UNIQUE">      ALTER TABLE "${tableName}" ADD CONSTRAINT "${indexEntity.name}" UNIQUE(<#list indexEntity.indexLineEntityList as indexLineEntity>"${indexLineEntity.columnEntity.name}"<#if indexLineEntity_has_next>,</#if></#list>);    </#if>    <#if indexEntity.indexType?? && (indexEntity.indexType == "INDEX" || indexEntity.indexType == "FULLTEXT")>      CREATE INDEX "${indexEntity.name}" ON "${tableName}" (<#list indexEntity.indexLineEntityList as indexLineEntity>"${indexLineEntity.columnEntity.name}"<#if indexLineEntity_has_next>,</#if></#list>);    </#if>  </#list></#if>EXEC sp_addextendedproperty 'MS_Description', N'${caption}', 'SCHEMA', N'dbo','TABLE', N'${tableName}';<#list columnEntityList as columnEntity>  EXEC sp_addextendedproperty 'MS_Description', N'${columnEntity.caption}', 'SCHEMA', N'dbo','TABLE', N'${tableName}', 'COLUMN', N'${columnEntity.name}';</#list>

创立ca_course表

UI点击创立表单之后，后盾会转换成对应的SQL脚本，最终创立物理表。

CREATE TABLE "ca_course" (    "id" BIGINT IDENTITY(1, 1) NOT NULL,    "name" VARCHAR(200) NOT NULL,    "classHour" INT,    "score" FLOAT,    "teacher" VARCHAR(200),    "fullTextBody" VARCHAR(4000),    "createdDate" DATETIME NOT NULL,    "lastModifiedDate" DATETIME);ALTER TABLE "ca_course" ADD CONSTRAINT "primary_key" PRIMARY KEY ("id");CREATE INDEX "ft_fulltext_body" ON "ca_course" ("fullTextBody");EXEC sp_addextendedproperty 'MS_Description', N'课程', 'SCHEMA', N'dbo','TABLE', N'ca_course';EXEC sp_addextendedproperty 'MS_Description', N'编号', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'id';EXEC sp_addextendedproperty 'MS_Description', N'课程名称', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'name';EXEC sp_addextendedproperty 'MS_Description', N'课时', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'classHour';EXEC sp_addextendedproperty 'MS_Description', N'学分', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'score';EXEC sp_addextendedproperty 'MS_Description', N'老师', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'teacher';EXEC sp_addextendedproperty 'MS_Description', N'全文索引', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'fullTextBody';EXEC sp_addextendedproperty 'MS_Description', N'创立工夫', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'createdDate';EXEC sp_addextendedproperty 'MS_Description', N'批改工夫', 'SCHEMA', N'dbo','TABLE', N'ca_course', 'COLUMN', N'lastModifiedDate';

批改表

包含表构造和索引的批改，删除等，和创立表原理相似。

application.properties

须要依据须要配置数据库连贯驱动，无需从新公布，就能够切换不同的数据库。

#mssqlspring.datasource.url=jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=crudapispring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriverspring.datasource.username=saspring.datasource.password=Mssql1433

小结

本文次要介绍了crudapi反对mssql数据库实现原理，并且以课程对象为例，零代码实现了CRUD增删改查RESTful API，后续介绍更多的数据库，比方Mongodb等。

实现形式	代码量	工夫	稳定性
传统开发	1000行左右	2天/人	5个bug左右
crudapi零碎	0行	1分钟	根本为0

综上所述，利用crudapi零碎能够极大地提高工作效率和节约老本，让数据处理变得更简略！

crudapi简介

crudapi是crud+api组合，示意增删改查接口，是一款零代码可配置的产品。应用crudapi能够辞别枯燥无味的增删改查代码，让您更加专一业务，节约大量老本，从而进步工作效率。
crudapi的指标是让解决数据变得更简略，所有人都能够收费应用！
无需编程，通过配置主动生成crud增删改查RESTful API，提供后盾UI治理业务数据。基于支流的开源框架，领有自主知识产权，反对二次开发。

demo演示

crudapi属于产品级的零代码平台，不同于主动代码生成器，不须要生成Controller、Service、Repository、Entity等业务代码，程序运行起来就能够应用，真正0代码，能够笼罩根本的和业务无关的CRUD RESTful API。

官网地址：https://crudapi.cn
测试地址：https://demo.crudapi.cn/crudapi/login

附源码地址

GitHub地址

https://github.com/crudapi/crudapi-admin-web

Gitee地址

https://gitee.com/crudapi/crudapi-admin-web

因为网络起因，GitHub可能速度慢，改成拜访Gitee即可，代码同步更新。