無需編程，基于微軟mssql數(shù)據庫零代碼生成CRUD增刪改查RESTful API接口

回顧

通過之前一篇文章 無需編程，基于甲骨文oracle數(shù)據庫零代碼生成CRUD增刪改查RESTful API接口 的介紹，引入了FreeMarker模版引擎，通過配置模版實現(xiàn)創(chuàng)建和修改物理表結構SQL語句，并且通過配置oracle數(shù)據庫SQL模版，基于oracle數(shù)據庫，零代碼實現(xiàn)crud增刪改查。本文采用同樣的方式，很容易就可以支持微軟SQL Server數(shù)據庫。

MSSQL簡介

SQL Server 是Microsoft 公司推出的關系型數(shù)據庫管理系統(tǒng)。具有使用方便可伸縮性好與相關軟件集成程度高等優(yōu)點，可從運行Microsoft Windows的電腦和大型多處理器的服務器等多種平臺使用。Microsoft SQL Server 是一個全面的數(shù)據庫平臺，使用集成的商業(yè)智能 (BI)工具提供了企業(yè)級的數(shù)據管理。Microsoft SQL Server 數(shù)據庫引擎為關系型數(shù)據和結構化數(shù)據提供了更安全可靠的存儲功能，使您可以構建和管理用于業(yè)務的高可用和高性能的數(shù)據應用程序。

UI界面

通過課程對象為例，無需編程，基于MSSQL數(shù)據庫，通過配置零代碼實現(xiàn)CRUD增刪改查RESTful API接口和管理UI。

創(chuàng)建課程表

courseMeta.png

編輯課程數(shù)據

courseData.png

課程數(shù)據列表

courseList.png

通過DBeaver數(shù)據庫工具查詢mssql數(shù)據

DBeaver.png

定義FreeMarker模版

創(chuàng)建表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>

創(chuàng)建ca_course表

UI點擊創(chuàng)建表單之后，后臺會轉換成對應的SQL腳本，最終創(chuàng)建物理表。

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'創(chuàng)建時間', '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';

修改表

freemarker.png

包括表結構和索引的修改，刪除等，和創(chuàng)建表原理類似。

application.properties

需要根據需要配置數(shù)據庫連接驅動，無需重新發(fā)布，就可以切換不同的數(shù)據庫。

#mssql
spring.datasource.url=jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=crudapi
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=sa
spring.datasource.password=Mssql1433

小結

本文主要介紹了crudapi支持mssql數(shù)據庫實現(xiàn)原理，并且以課程對象為例，零代碼實現(xiàn)了CRUD增刪改查RESTful API，后續(xù)介紹更多的數(shù)據庫，比如Mongodb等。

綜上所述，利用crudapi系統(tǒng)可以極大地提高工作效率和節(jié)約成本，讓數(shù)據處理變得更簡單！