無(wú)需編程，基于甲骨文oracle數(shù)據(jù)庫(kù)零代碼生成CRUD增刪改查RESTful API接口
甲骨文oracle數(shù)據(jù)庫(kù)
回顧
通過(guò)之前一篇文章?多數(shù)據(jù)庫(kù)支持?的介紹，采用抽象工廠設(shè)計(jì)模式，已經(jīng)支持了大象數(shù)據(jù)庫(kù)PostgreSQL。之前通過(guò)字符串拼接生成DDL SQL語(yǔ)句，比較繁瑣。本文開(kāi)始，引入了FreeMarker模版引擎，通過(guò)配置模版實(shí)現(xiàn)創(chuàng)建和修改物理表結(jié)構(gòu)SQL語(yǔ)句，簡(jiǎn)化了大量代碼，提高了效率，并且通過(guò)配置oracle數(shù)據(jù)庫(kù)SQL模版，基于oracle數(shù)據(jù)庫(kù)，零代碼實(shí)現(xiàn)crud增刪改查。
FreeMarker簡(jiǎn)介
FreeMarker是一款模板引擎： 即一種基于模板和要改變的數(shù)據(jù)，并用來(lái)生成輸出文本(HTML網(wǎng)頁(yè)，電子郵件，配置文件，源代碼等)的通用工具。 它不是面向最終用戶的，而是一個(gè)Java類庫(kù)，是一款程序員可以嵌入他們所開(kāi)發(fā)產(chǎn)品的組件。模板編寫(xiě)為FreeMarker Template Language (FTL)。它是簡(jiǎn)單的，專用的語(yǔ)言， 不是像PHP那樣成熟的編程語(yǔ)言。 那就意味著要準(zhǔn)備數(shù)據(jù)在真實(shí)編程語(yǔ)言中來(lái)顯示，比如數(shù)據(jù)庫(kù)查詢和業(yè)務(wù)運(yùn)算，之后模板顯示已經(jīng)準(zhǔn)備好的數(shù)據(jù)。在模板中，你可以專注于如何展現(xiàn)數(shù)據(jù)，而在模板之外可以專注于要展示什么數(shù)據(jù)。
UI界面
通過(guò)產(chǎn)品對(duì)象為例，無(wú)需編程，基于Oracle數(shù)據(jù)庫(kù)，通過(guò)配置零代碼實(shí)現(xiàn)CRUD增刪改查RESTful API接口和管理UI。
?創(chuàng)建產(chǎn)品
?編輯產(chǎn)品數(shù)據(jù)
?產(chǎn)品數(shù)據(jù)列表
?通過(guò)Oracle SQL Developer查詢Oracle數(shù)據(jù)
定義元數(shù)據(jù)對(duì)象模型
元數(shù)據(jù)表ca_meta_table
?元數(shù)據(jù)表ca_meta_table，用于記錄表的基本信息。
#
TableEntity對(duì)象
TableEntity為“元數(shù)據(jù)表”對(duì)象，和ca_meta_table字段對(duì)應(yīng)
public class TableEntity {
private Long id;
private String name;
private String caption;
private String description;
private Timestamp createdDate;
private Timestamp lastModifiedDate;
private String pluralName;
private String tableName;
private EngineEnum engine;
private Boolean createPhysicalTable;
private Boolean reverse;
private Boolean systemable;
private Boolean readOnly;
private List<ColumnEntity> columnEntityList;
private List<IndexEntity> indexEntityList;
}
#
元數(shù)據(jù)列ca_meta_column
?元數(shù)據(jù)列ca_meta_column，用于記錄表字段信息，比如類型，長(zhǎng)度，默認(rèn)值等。
#
ColumnEntity對(duì)象
ColumnEntity為“元數(shù)據(jù)列”對(duì)象，和ca_meta_column字段對(duì)應(yīng)
public class ColumnEntity {
private Long id;
private String name;
private String caption;
private String description;
private Timestamp createdDate;
private Timestamp lastModifiedDate;
private Integer displayOrder;
private DataTypeEnum dataType;
private IndexTypeEnum indexType;
private IndexStorageEnum indexStorage;
private String indexName;
private Integer length;
private Integer precision;
private Integer scale;
private String defaultValue;
private Long seqId;
private Boolean unsigned;
private Boolean autoIncrement;
private Boolean nullable;
private Boolean insertable;
private Boolean updatable;
private Boolean queryable;
private Boolean displayable;
private Boolean systemable;
private Long tableId;
}
#
元數(shù)據(jù)索引ca_meta_index
?元數(shù)據(jù)索引ca_meta_index，用于記錄表聯(lián)合索引信息，比如索引類型，名稱等。
IndexEntity對(duì)象
IndexEntity為“元數(shù)據(jù)索引”對(duì)象，和ca_meta_index字段對(duì)應(yīng)
public class IndexEntity {
private Long id;
private String name;
private String caption;
private String description;
private Timestamp createdDate;
private Timestamp lastModifiedDate;
private IndexTypeEnum indexType;
private IndexStorageEnum indexStorage;
private Long tableId;
private List<IndexLineEntity> indexLineEntityList;
}
元數(shù)據(jù)索引行ca_meta_index_line
?元數(shù)據(jù)索引行ca_meta_index_line，用于記錄表聯(lián)合索引行信息，一個(gè)聯(lián)合索引可以對(duì)應(yīng)多個(gè)聯(lián)合索引行，表示由多個(gè)字段組成。
IndexLineEntity對(duì)象
IndexLineEntity“元數(shù)據(jù)索行”對(duì)象，和ca_meta_index_line字段對(duì)應(yīng)
public class IndexLineEntity {
private Long id;
private Long columnId;
private ColumnEntity columnEntity;
private Long indexId;
}
定義FreeMarker模版
創(chuàng)建表create-table.sql.ftl
CREATE TABLE "${tableName}" (
<#list columnEntityList as columnEntity>
<#if columnEntity.dataType == "BOOL">
"${columnEntity.name}" NUMBER(1)<#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.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity.indexType?? && columnEntity.indexType == "PRIMARY"> PRIMARY KEY</#if><#if columnEntity_has_next>,</#if>
<#elseif columnEntity.dataType == "BIGINT">
"${columnEntity.name}" INT<#if columnEntity.defaultValue??> DEFAULT ${columnEntity.defaultValue}</#if><#if columnEntity.nullable != true> NOT NULL</#if><#if columnEntity.indexType?? && columnEntity.indexType == "PRIMARY"> PRIMARY KEY</#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}" REAL<#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}" CHAR(8)<#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}" DATE<#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.indexType?? && columnEntity.indexType == "PRIMARY"> PRIMARY KEY</#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.indexType?? && columnEntity.indexType == "PRIMARY"> PRIMARY KEY</#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}" LONG<#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}" BLOB<#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}" BLOB<#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.indexType?? && columnEntity.indexType == "PRIMARY"> PRIMARY KEY</#if><#if columnEntity_has_next>,</#if>
</#if>
</#list>
);
<#list columnEntityList as columnEntity>
<#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 == "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>
COMMENT ON TABLE "${tableName}" IS '${caption}';
<#list columnEntityList as columnEntity>
COMMENT ON COLUMN "${tableName}"."${columnEntity.name}" IS '${columnEntity.caption}';
</#list>
#
模版解析SQL
首先保存元數(shù)據(jù)信息，下一步傳遞模版名稱和元數(shù)據(jù)model，動(dòng)態(tài)解析成創(chuàng)建表SQL語(yǔ)句，然后創(chuàng)建物理表，這樣元數(shù)據(jù)和物理表就關(guān)聯(lián)上了。運(yùn)行時(shí)通過(guò)解析元數(shù)據(jù)動(dòng)態(tài)生成insert，select，update，delete等SQL語(yǔ)句，零代碼實(shí)現(xiàn)業(yè)務(wù)數(shù)據(jù)crud功能。
public String processTemplateToString(String database, String templateName, Object dataModel) {
String str = null;
StringWriter stringWriter = new StringWriter();
try {
Configuration config = new Configuration(Configuration.VERSION_2_3_31);
config.setNumberFormat("#");
String templateValue = getTemplate(database, templateName);
if (templateValue == null) {
return str;
}
Template template = new Template(templateName, templateValue, config);
template.process(dataModel, stringWriter);
str = stringWriter.getBuffer().toString().trim();
log.info(str);
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException(ApiErrorCode.DEFAULT_ERROR, e.getMessage());
}
return str;
}
public List<String> toCreateTableSql(TableEntity tableEntity) {
String createTableSql = processTemplateToString("create-table.sql.ftl", tableEntity);
if (createTableSql == null) {
throw new BusinessException(ApiErrorCode.DEFAULT_ERROR, "create-table.sql is empty!");
}
List<String> sqls = new ArrayList<String>();
String[] subSqls = createTableSql.split(";");
for (String t : subSqls) {
String subSql = t.trim();
if (!subSql.isEmpty()) {
sqls.add(t);
}
}
return sqls;
}
public Long create(TableDTO tableDTO) {
TableEntity tableEntity = tableMapper.toEntity(tableDTO);
//TODO
Long tableId = crudService.create(TABLE_TABLE_NAME, tableEntity);
List<String> sqlList = crudService.toCreateTableSql(tableEntity);
for (String sql: sqlList) {
execute(sql);
}
//TODO
return tableId;
}
#
修改表
?包括表結(jié)構(gòu)和索引的修改，刪除等，和創(chuàng)建表原理類似。
application.properties
需要根據(jù)需要配置數(shù)據(jù)庫(kù)連接驅(qū)動(dòng)，無(wú)需重新發(fā)布，就可以切換不同的數(shù)據(jù)庫(kù)。
#oracle spring.datasource.url=jdbc:oracle:thin:@//localhost:1521/XEPDB1 spring.datasource.driverClassName=oracle.jdbc.OracleDriver spring.datasource.username=crudapi spring.datasource.password=crudapi spring.datasource.initialization-mode=always spring.datasource.schema=classpath:schema.sql
#
小結(jié)
本文主要介紹了crudapi支持oracle數(shù)據(jù)庫(kù)實(shí)現(xiàn)原理，并且以產(chǎn)品對(duì)象為例，零代碼實(shí)現(xiàn)了CRUD增刪改查RESTful API，后續(xù)介紹更多的數(shù)據(jù)庫(kù)，比如MSSQL Server，Mongodb等。
實(shí)現(xiàn)方式代碼量時(shí)間穩(wěn)定性傳統(tǒng)開(kāi)發(fā)1000行左右2天/人5個(gè)bug左右crudapi系統(tǒng)0行1分鐘基本為0
綜上所述，利用crudapi系統(tǒng)可以極大地提高工作效率和節(jié)約成本，讓數(shù)據(jù)處理變得更簡(jiǎn)單！
crudapi簡(jiǎn)介
crudapi是crud+api組合，表示增刪改查接口，是一款零代碼可配置的產(chǎn)品。使用crudapi可以告別枯燥無(wú)味的增刪改查代碼，讓您更加專注業(yè)務(wù)，節(jié)約大量成本，從而提高工作效率。 crudapi的目標(biāo)是讓處理數(shù)據(jù)變得更簡(jiǎn)單，所有人都可以免費(fèi)使用！ 無(wú)需編程，通過(guò)配置自動(dòng)生成crud增刪改查RESTful API，提供后臺(tái)UI管理業(yè)務(wù)數(shù)據(jù)?；谥髁鞯拈_(kāi)源框架，擁有自主知識(shí)產(chǎn)權(quán)，支持二次開(kāi)發(fā)。
demo演示
crudapi屬于產(chǎn)品級(jí)的零代碼平臺(tái)，不同于自動(dòng)代碼生成器，不需要生成Controller、Service、Repository、Entity等業(yè)務(wù)代碼，程序運(yùn)行起來(lái)就可以使用，真正0代碼，可以覆蓋基本的和業(yè)務(wù)無(wú)關(guān)的CRUD RESTful API。
官網(wǎng)地址：https://crudapi.cn
測(cè)試地址：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可能速度慢，改成訪問(wèn)Gitee即可，代碼同步更新。