手机看片青青草,国产乱人妻精品入口,亚洲性图第一页,国产精品久久夜夜,日本中文字幕网站,日韩成人区,国外操逼视频,99热在线这里只有精品

微軟mssql數(shù)據(jù)庫

回顧

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

MSSQL簡介

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

UI界面

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

?創(chuàng)建課程表

?編輯課程數(shù)據(jù)

?課程數(shù)據(jù)列表

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

定義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點(diǎn)擊創(chuàng)建表單之后，后臺會轉(zhuǎn)換成對應(yī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'學(xué)分', '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';

修改表

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

application.properties

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

#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

小結(jié)

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

實(shí)現(xiàn)方式代碼量時間穩(wěn)定性傳統(tǒng)開發(fā)1000行左右2天/人5個bug左右crudapi系統(tǒng)0行1分鐘基本為0

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

crudapi簡介

crudapi是crud+api組合，表示增刪改查接口，是一款零代碼可配置的產(chǎn)品。使用crudapi可以告別枯燥無味的增刪改查代碼，讓您更加專注業(yè)務(wù)，節(jié)約大量成本，從而提高工作效率。 crudapi的目標(biāo)是讓處理數(shù)據(jù)變得更簡單，所有人都可以免費(fèi)使用！無需編程，通過配置自動生成crud增刪改查RESTful API，提供后臺UI管理業(yè)務(wù)數(shù)據(jù)。基于主流的開源框架，擁有自主知識產(chǎn)權(quán)，支持二次開發(fā)。

demo演示

crudapi屬于產(chǎn)品級的零代碼平臺，不同于自動代碼生成器，不需要生成Controller、Service、Repository、Entity等業(yè)務(wù)代碼，程序運(yùn)行起來就可以使用，真正0代碼，可以覆蓋基本的和業(yè)務(wù)無關(guān)的CRUD RESTful API。

官網(wǎng)地址：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

由于網(wǎng)絡(luò)原因，GitHub可能速度慢，改成訪問Gitee即可，代碼同步更新。

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

回顧