无需编程,基于微软mssql数据库零代码生成CRUD增删改查RESTful API接口
2022/4/13 8:12:31
本文主要是介绍无需编程,基于微软mssql数据库零代码生成CRUD增删改查RESTful API接口,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
无需编程,基于微软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
需要根据需要配置数据库连接驱动,无需重新发布,就可以切换不同的数据库。
#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数据库实现原理,并且以课程对象为例,零代码实现了CRUD增删改查RESTful API,后续介绍更多的数据库,比如Mongodb等。
实现方式 | 代码量 | 时间 | 稳定性 |
---|---|---|---|
传统开发 | 1000行左右 | 2天/人 | 5个bug左右 |
crudapi系统 | 0行 | 1分钟 | 基本为0 |
综上所述,利用crudapi系统可以极大地提高工作效率和节约成本,让数据处理变得更简单!
这篇关于无需编程,基于微软mssql数据库零代码生成CRUD增删改查RESTful API接口的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2022-03-01沐雪多租宝商城源码从.NetCore3.1升级到.Net6的步骤
- 2024-12-06使用Microsoft.Extensions.AI在.NET中生成嵌入向量
- 2024-11-18微软研究:RAG系统的四个层次提升理解与回答能力
- 2024-11-15C#中怎么从PEM格式的证书中提取公钥?-icode9专业技术文章分享
- 2024-11-14云架构设计——如何用diagrams.net绘制专业的AWS架构图?
- 2024-05-08首个适配Visual Studio平台的国产智能编程助手CodeGeeX正式上线!C#程序员必备效率神器!
- 2024-03-30C#设计模式之十六迭代器模式(Iterator Pattern)【行为型】
- 2024-03-29c# datetime tryparse
- 2024-02-21list find index c#
- 2024-01-24convert toint32 c#