长岭建设局网站,免费游戏不用登录的,全国工商信息企业查询官网,wordpress 仿f继上文《nacos适配达梦、瀚高、人大金仓数据库及部分源码探究 》后补充nacos适配SqlServer、Oracle的贴码#xff0c;主要区别是SqlServer、Oracle的分页SQL有点不一样#xff0c;做个记录#xff1b; SqlServer的分页有三种实现方式#xff1a;offset /fetch next、利用ma… 继上文《nacos适配达梦、瀚高、人大金仓数据库及部分源码探究 》后补充nacos适配SqlServer、Oracle的贴码主要区别是SqlServer、Oracle的分页SQL有点不一样做个记录 SqlServer的分页有三种实现方式offset /fetch next、利用max主键、利用row_number关键字 ps具体可参考《sqlServer如何实现分页查询》
一.nacos适配SqlServer 1.插件代码 1.0.DataSourceConstant public class DataSourceConstant {public static final String DM dm;public static final String HIGHGO highgo;public static final String KINGBASE kingbase;public static final String PG postgresql;//增加类型public static final String SQLSERVERsqlserver;}1.1.ConfigInfoAggrMapperBySqlServer public class ConfigInfoAggrMapperBySqlServer extends AbstractConfigInfoAggrMapperCommon {Overridepublic String findConfigInfoAggrByPageFetchRows(int startRow, int pageSize) {return SELECT data_id,group_id,tenant_id,datum_id,app_name,content FROM config_info_aggr WHERE data_id ? AND group_id ? AND tenant_id ? ORDER BY datum_id offset (startRow*pageSize) rows fetch next pageSize rows only;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}}1.2.ConfigInfoBetaMapperBySqlServer public class ConfigInfoBetaMapperBySqlServer extends AbstractConfigInfoBetaMapperCommon {Overridepublic String findAllConfigInfoBetaForDumpAllFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,beta_ips,encrypted_data_key FROM ( SELECT id FROM config_info_beta ORDER BY id offset ( startRow * pageSize ) rows fetch next pageSize rows only ) g, config_info_beta t WHERE g.id t.id ;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}}1.3.ConfigInfoMapperBySqlServer public class ConfigInfoMapperBySqlServer extends AbstractConfigInfoMapperCommon {private static final String DATA_ID dataId;private static final String GROUP group;private static final String APP_NAME appName;private static final String CONTENT content;private static final String TENANT tenant;Overridepublic String findConfigInfoByAppFetchRows(int startRow, int pageSize) {return SELECT id,data_id,group_id,tenant_id,app_name,content FROM config_info WHERE tenant_id LIKE ? AND app_name ? order by id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String getGroupIdList(int startRow, int pageSize) {return SELECT group_id FROM config_info WHERE tenant_id GROUP BY group_id order by group_id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String findAllConfigKey(int startRow, int pageSize) {return SELECT data_id,group_id,app_name FROM ( SELECT id FROM config_info WHERE tenant_id LIKE ? ORDER BY id offset ( startRow * pageSize ) rows fetch next pageSize rows only ) g, config_info t WHERE g.id t.id ;}Overridepublic String findAllConfigInfoBaseFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,content,md5 FROM ( SELECT id FROM config_info ORDER BY id offset ( startRow * pageSize ) rows fetch next pageSize rows only ) g, config_info t WHERE g.id t.id ;}Overridepublic String findAllConfigInfoFragment(int startRow, int pageSize) {return SELECT id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,type,encrypted_data_key FROM config_info WHERE id ? ORDER BY id ASC offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String findChangeConfigFetchRows(MapString, String params, final Timestamp startTime,final Timestamp endTime, int startRow, int pageSize, long lastMaxId) {final String tenant params.get(TENANT);final String dataId params.get(DATA_ID);final String group params.get(GROUP);final String appName params.get(APP_NAME);final String tenantTmp StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;final String sqlFetchRows SELECT id,data_id,group_id,tenant_id,app_name,content,type,md5,gmt_modified FROM config_info WHERE ;String where 11 ;if (!StringUtils.isBlank(dataId)) {where AND data_id LIKE ? ;}if (!StringUtils.isBlank(group)) {where AND group_id LIKE ? ;}if (!StringUtils.isBlank(tenantTmp)) {where AND tenant_id ? ;}if (!StringUtils.isBlank(appName)) {where AND app_name ? ;}if (startTime ! null) {where AND gmt_modified ? ;}if (endTime ! null) {where AND gmt_modified ? ;}return sqlFetchRows where AND id lastMaxId ORDER BY id ASC offset pageSize rows fetch next 1 rows only;}Overridepublic String listGroupKeyMd5ByPageFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,app_name,md5,type,gmt_modified,encrypted_data_key FROM ( SELECT id FROM config_info ORDER BY id offset ( startRow * pageSize ) rows fetch next pageSize rows only ) g, config_info t WHERE g.id t.id;}Overridepublic String findConfigInfoBaseLikeFetchRows(MapString, String params, int startRow, int pageSize) {final String sqlFetchRows SELECT id,data_id,group_id,tenant_id,content FROM config_info WHERE ;String where 11 AND tenant_id ;if (!StringUtils.isBlank(params.get(DATA_ID))) {where AND data_id LIKE ? ;}if (!StringUtils.isBlank(params.get(GROUP))) {where AND group_id LIKE ;}if (!StringUtils.isBlank(params.get(CONTENT))) {where AND content LIKE ? ;}return sqlFetchRows where order by id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String findConfigInfo4PageFetchRows(MapString, String params, int startRow, int pageSize) {final String appName params.get(APP_NAME);final String dataId params.get(DATA_ID);final String group params.get(GROUP);final String sql SELECT id,data_id,group_id,tenant_id,app_name,content,type,encrypted_data_key FROM config_info;StringBuilder where new StringBuilder( WHERE );where.append( tenant_id? );if (StringUtils.isNotBlank(dataId)) {where.append( AND data_id? );}if (StringUtils.isNotBlank(group)) {where.append( AND group_id? );}if (StringUtils.isNotBlank(appName)) {where.append( AND app_name? );}return sql where order by id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String findConfigInfoLike4PageFetchRows(MapString, String params, int startRow, int pageSize) {String dataId params.get(DATA_ID);String group params.get(GROUP);final String appName params.get(APP_NAME);final String content params.get(CONTENT);final String sqlFetchRows SELECT id,data_id,group_id,tenant_id,app_name,content,encrypted_data_key FROM config_info;StringBuilder where new StringBuilder( WHERE );where.append( tenant_id LIKE ? );if (!StringUtils.isBlank(dataId)) {where.append( AND data_id LIKE ? );}if (!StringUtils.isBlank(group)) {where.append( AND group_id LIKE ? );}if (!StringUtils.isBlank(appName)) {where.append( AND app_name ? );}if (!StringUtils.isBlank(content)) {where.append( AND content LIKE ? );}return sqlFetchRows where order by id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String findAllConfigInfoFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,app_name,content,md5 FROM ( SELECT id FROM config_info WHERE tenant_id LIKE ? ORDER BY id offset ( startRow * pageSize ) rows fetch next pageSize rows only ) g, config_info t WHERE g.id t.id ;}Overridepublic String findConfigInfoBaseByGroupFetchRows(int startRow, int pageSize) {return SELECT id,data_id,group_id,content FROM config_info WHERE group_id? AND tenant_id? order by id offset ( startRow * pageSize ) rows fetch next pageSize rows only;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}}1.4.ConfigInfoTagMapperBySqlServer public class ConfigInfoTagMapperBySqlServer extends AbstractConfigInfoTagMapperCommon {Overridepublic String findAllConfigInfoTagForDumpAllFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,tag_id,app_name,content,md5,gmt_modified FROM ( SELECT id FROM config_info_tag ORDER BY id offset (startRow*pageSize) rows fetch next pageSize rows only ) g, config_info_tag t WHERE g.id t.id ;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}
}1.5.ConfigTagsRelationMapperBySqlServer public class ConfigTagsRelationMapperBySqlServer extends AbstractConfigTagsRelationMapperCommon {Overridepublic String findConfigInfo4PageFetchRows(MapString, String params, int tagSize, int startRow, int pageSize) {final String appName params.get(appName);final String dataId params.get(dataId);final String group params.get(group);StringBuilder where new StringBuilder( WHERE );final String sql SELECT a.id,a.data_id,a.group_id,a.tenant_id,a.app_name,a.content FROM config_info a LEFT JOIN config_tags_relation b ON a.idb.id;where.append( a.tenant_id? );if (StringUtils.isNotBlank(dataId)) {where.append( AND a.data_id? );}if (StringUtils.isNotBlank(group)) {where.append( AND a.group_id? );}if (StringUtils.isNotBlank(appName)) {where.append( AND a.app_name? );}where.append( AND b.tag_name IN ();for (int i 0; i tagSize; i) {if (i ! 0) {where.append(, );}where.append(?);}where.append() );return sql where order by id offset (startRow*pageSize) rows fetch next pageSize rows only;}Overridepublic String findConfigInfoLike4PageFetchRows(final MapString, String params, int tagSize, int startRow,int pageSize) {final String appName params.get(appName);final String content params.get(content);final String dataId params.get(dataId);final String group params.get(group);StringBuilder where new StringBuilder( WHERE );final String sqlFetchRows SELECT a.id,a.data_id,a.group_id,a.tenant_id,a.app_name,a.content FROM config_info a LEFT JOIN config_tags_relation b ON a.idb.id ;where.append( a.tenant_id LIKE ? );if (!StringUtils.isBlank(dataId)) {where.append( AND a.data_id LIKE ? );}if (!StringUtils.isBlank(group)) {where.append( AND a.group_id LIKE ? );}if (!StringUtils.isBlank(appName)) {where.append( AND a.app_name ? );}if (!StringUtils.isBlank(content)) {where.append( AND a.content LIKE ? );}where.append( AND b.tag_name IN ();for (int i 0; i tagSize; i) {if (i ! 0) {where.append(, );}where.append(?);}where.append() );return sqlFetchRows where order by id offset (startRow*pageSize) rows fetch next pageSize rows only;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}
}1.6.GroupCapacityMapperBySqlServer public class GroupCapacityMapperBySqlServer extends AbstractGroupCapacityMapperCommon {Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}
}1.7.HistoryConfigInfoMapperBySqlServer public class HistoryConfigInfoMapperBySqlServer extends AbstractHistoryConfigInfoMapperCommon {public String pageFindConfigHistoryFetchRows(int pageNo, int pageSize) {final int offset (pageNo - 1) * pageSize;final int limit pageSize;return SELECT nid,data_id,group_id,tenant_id,app_name,src_ip,src_user,op_type,gmt_create,gmt_modified FROM his_config_info WHERE data_id ? AND group_id ? AND tenant_id ? ORDER BY nid DESC offset offset rows fetch next limit rows only;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}
}1.8.TenantCapacityMapperBySqlServer public class TenantCapacityMapperBySqlServer extends AbstractTenantCapacityMapperCommon {Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}}1.9.TenantInfoMapperBySqlServer public class TenantInfoMapperBySqlServer extends AbstractMapper implements TenantInfoMapper {Overridepublic String getTableName() {return TableConstant.TENANT_INFO;}Overridepublic String getDataSource() {return DataSourceConstantExt.SQLSERVER;}}2.ddl CREATE TABLE config_info (id bigint identity(1,1) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) default ,app_name varchar(128),content text,md5 varchar(32) DEFAULT NULL,gmt_create datetime NOT NULL DEFAULT 2010-05-05 00:00:00,gmt_modified datetime NOT NULL DEFAULT 2010-05-05 00:00:00,src_user varchar(128) DEFAULT NULL,src_ip varchar(20) DEFAULT NULL,c_desc varchar(256) DEFAULT NULL,c_use varchar(64) DEFAULT NULL,effect varchar(64) DEFAULT NULL,type varchar(64) DEFAULT NULL,c_schema text DEFAULT NULL,encrypted_data_key varchar(100) DEFAULT NULL,constraint configinfo_id_key PRIMARY KEY (id),constraint uk_configinfo_datagrouptenant UNIQUE (data_id,group_id,tenant_id));
CREATE INDEX configinfo_dataid_key_idx ON config_info(data_id);
CREATE INDEX configinfo_groupid_key_idx ON config_info(group_id);
CREATE INDEX configinfo_dataid_group_key_idx ON config_info(data_id, group_id); CREATE TABLE his_config_info (id bigint NOT NULL,nid bigint identity(1,1) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) default ,app_name varchar(128),content text,md5 varchar(32) DEFAULT NULL,gmt_create datetime NOT NULL DEFAULT 2010-05-05 00:00:00.000,gmt_modified datetime NOT NULL DEFAULT 2010-05-05 00:00:00.000,src_user varchar(128),src_ip varchar(20) DEFAULT NULL,op_type char(10) DEFAULT NULL,encrypted_data_key varchar(100) DEFAULT NULL,constraint hisconfiginfo_nid_key PRIMARY KEY (nid));CREATE INDEX hisconfiginfo_dataid_key_idx ON his_config_info(data_id);
CREATE INDEX hisconfiginfo_gmt_create_idx ON his_config_info(gmt_create);
CREATE INDEX hisconfiginfo_gmt_modified_idx ON his_config_info(gmt_modified);CREATE TABLE config_info_beta (id bigint identity(1,1) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) default ,app_name varchar(128),content text,beta_ips varchar(1024),md5 varchar(32) DEFAULT NULL,gmt_create datetime NOT NULL DEFAULT 2010-05-05 00:00:00,gmt_modified datetime NOT NULL DEFAULT 2010-05-05 00:00:00,src_user varchar(128),src_ip varchar(20) DEFAULT NULL,encrypted_data_key varchar(100) DEFAULT NULL,constraint configinfobeta_id_key PRIMARY KEY (id),constraint uk_configinfobeta_datagrouptenant UNIQUE (data_id,group_id,tenant_id));CREATE TABLE config_info_tag (id bigint identity(1,1) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) default ,tag_id varchar(128) NOT NULL,app_name varchar(128),content text,md5 varchar(32) DEFAULT NULL,gmt_create datetime NOT NULL DEFAULT 2010-05-05 00:00:00,gmt_modified datetime NOT NULL DEFAULT 2010-05-05 00:00:00,src_user varchar(128),src_ip varchar(20) DEFAULT NULL,constraint configinfotag_id_key PRIMARY KEY (id),constraint uk_configinfotag_datagrouptenanttag UNIQUE (data_id,group_id,tenant_id,tag_id));CREATE TABLE config_info_aggr (id bigint identity(1,1) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) default ,datum_id varchar(255) NOT NULL,app_name varchar(128),content text,gmt_modified datetime NOT NULL DEFAULT 2010-05-05 00:00:00,constraint configinfoaggr_id_key PRIMARY KEY (id),constraint uk_configinfoaggr_datagrouptenantdatum UNIQUE (data_id,group_id,tenant_id,datum_id));CREATE TABLE app_list (id bigint identity(1,1) NOT NULL,app_name varchar(128) NOT NULL,is_dynamic_collect_disabled smallint DEFAULT 0,last_sub_info_collected_time datetime DEFAULT 1970-01-01 08:00:00.0,sub_info_lock_owner varchar(128),sub_info_lock_time datetime DEFAULT 1970-01-01 08:00:00.0,constraint applist_id_key PRIMARY KEY (id),constraint uk_appname UNIQUE (app_name));CREATE TABLE app_configdata_relation_subs (id bigint identity(1,1) NOT NULL,app_name varchar(128) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,gmt_modified datetime DEFAULT 2010-05-05 00:00:00,constraint configdatarelationsubs_id_key PRIMARY KEY (id),constraint uk_app_sub_config_datagroup UNIQUE (app_name, data_id, group_id));CREATE TABLE app_configdata_relation_pubs (id bigint identity(1,1) NOT NULL,app_name varchar(128) NOT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,gmt_modified datetime DEFAULT 2010-05-05 00:00:00,constraint configdatarelationpubs_id_key PRIMARY KEY (id),constraint uk_app_pub_config_datagroup UNIQUE (app_name, data_id, group_id));CREATE TABLE config_tags_relation (id bigint NOT NULL,tag_name varchar(128) NOT NULL,tag_type varchar(64) DEFAULT NULL,data_id varchar(255) NOT NULL,group_id varchar(128) NOT NULL,tenant_id varchar(128) DEFAULT ,nid bigint identity(1,1) NOT NULL,constraint config_tags_id_key PRIMARY KEY (nid),constraint uk_configtagrelation_configidtag UNIQUE (id, tag_name, tag_type));CREATE INDEX config_tags_tenant_id_idx ON config_tags_relation(tenant_id);CREATE TABLE group_capacity (id bigint identity(1,1) NOT NULL,group_id varchar(128) DEFAULT ,quota int DEFAULT 0,usage int DEFAULT 0,max_size int DEFAULT 0,max_aggr_count int DEFAULT 0,max_aggr_size int DEFAULT 0,max_history_count int DEFAULT 0,gmt_create datetime DEFAULT 2010-05-05 00:00:00,gmt_modified datetime DEFAULT 2010-05-05 00:00:00,constraint group_capacity_id_key PRIMARY KEY (id),constraint uk_group_id UNIQUE (group_id));CREATE TABLE tenant_capacity (id bigint identity(1,1) NOT NULL,tenant_id varchar(128) DEFAULT ,quota int DEFAULT 0,usage int DEFAULT 0,max_size int DEFAULT 0,max_aggr_count int DEFAULT 0,max_aggr_size int DEFAULT 0,max_history_count int DEFAULT 0,gmt_create datetime DEFAULT 2010-05-05 00:00:00,gmt_modified datetime DEFAULT 2010-05-05 00:00:00,constraint tenant_capacity_id_key PRIMARY KEY (id),constraint uk_tenant_id UNIQUE (tenant_id));CREATE TABLE tenant_info (id bigint identity(1,1) NOT NULL,kp varchar(128) NOT NULL,tenant_id varchar(128) DEFAULT ,tenant_name varchar(128) DEFAULT ,tenant_desc varchar(256) DEFAULT NULL,create_source varchar(32) DEFAULT NULL,gmt_create bigint NOT NULL,gmt_modified bigint NOT NULL,constraint tenant_info_id_key PRIMARY KEY (id),constraint uk_tenant_info_kptenantid UNIQUE (kp,tenant_id));
CREATE INDEX tenant_info_tenant_id_idx ON tenant_info(tenant_id);CREATE TABLE users (username varchar(50) NOT NULL PRIMARY KEY,password varchar(500) NOT NULL,enabled bit NOT NULL
);CREATE TABLE roles (username varchar(50) NOT NULL,role varchar(50) NOT NULL
);INSERT INTO users (username, password, enabled) VALUES (nacos, $2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu, 1);INSERT INTO roles (username, role) VALUES (nacos, ROLE_ADMIN); 3.pom dependencygroupIdcom.microsoft.sqlserver/groupIdartifactIdmssql-jdbc/artifactIdversion12.4.0.jre11/version/dependency二.nacos适配Oracle 1.插件代码 1.0.DataSourceConstant public class DataSourceConstant {public static final String DM dm;public static final String HIGHGO highgo;public static final String KINGBASE kingbase;public static final String PG postgresql;public static final String SQLSERVERsqlserver;//增加类型public static final String ORACLE oracle;
}1.1.ConfigInfoAggrMapperByOracle public class ConfigInfoAggrMapperByOracle extends AbstractConfigInfoAggrMapperCommon {Overridepublic String findConfigInfoAggrByPageFetchRows(int startRow, int pageSize) {return SELECT * FROM (SELECT data_id,group_id,tenant_id,datum_id,app_name,content, ROWNUM as rnum FROM config_info_aggr WHERE data_id ? AND group_id ? AND tenant_id ? ORDER BY datum_id) WHERE rnum startRow and pageSize rnum;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.2.ConfigInfoBetaMapperByOracle public class ConfigInfoBetaMapperByOracle extends AbstractConfigInfoBetaMapperCommon {Overridepublic String findAllConfigInfoBetaForDumpAllFetchRows(int startRow, int pageSize) {return SELECT * FROM (SELECT t.id,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,beta_ips,encrypted_data_key FROM ( SELECT * FROM (SELECT id, ROWNUM as rnum FROM config_info_beta ORDER BY id) WHERE rnum startRow and pageSize rnum ) g, config_info_beta t WHERE g.id t.id ;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.3.ConfigInfoMapperByOracle public class ConfigInfoMapperByOracle extends AbstractConfigInfoMapperCommon {private static final String DATA_ID dataId;private static final String GROUP group;private static final String APP_NAME appName;private static final String CONTENT content;private static final String TENANT tenant;Overridepublic String findConfigInfoByAppFetchRows(int startRow, int pageSize) {return SELECT * FROM (SELECT id,data_id,group_id,tenant_id,app_name,content, ROWNUM as rnum FROM config_info WHERE tenant_id LIKE ? AND app_name ?) WHERE rnum startRow and pageSize rnum;}Overridepublic String getTenantIdList(int startRow, int pageSize) {return SELECT * FROM (SELECT tenant_id, ROWNUM as rnum FROM config_info WHERE tenant_id ! GROUP BY tenant_id) WHERE rnum startRow and pageSize rnum;}Overridepublic String getGroupIdList(int startRow, int pageSize) {return SELECT * FROM (SELECT group_id, ROWNUM as rnum FROM config_info WHERE tenant_id GROUP BY group_id) WHERE rnum startRow and pageSize rnum;}Overridepublic String findAllConfigKey(int startRow, int pageSize) {return SELECT data_id,group_id,app_name FROM ( SELECT * FROM (SELECT id, ROWNUM as rnum FROM config_info WHERE tenant_id LIKE ? ORDER BY id) WHERE rnum startRow and pageSize rnum ) g, config_info t WHERE g.id t.id ;}Overridepublic String findAllConfigInfoBaseFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,content,md5 FROM (SELECT * FROM ( SELECT id, ROWNUM as rnum FROM config_info ORDER BY id) WHERE rnum startRow and pageSize rnum) g, config_info t WHERE g.id t.id ;}Overridepublic String findAllConfigInfoFragment(int startRow, int pageSize) {return SELECT * FROM (SELECT id,ROWNUM as rnum,data_id,group_id,tenant_id,app_name,content,md5,gmt_modified,type,encrypted_data_key FROM config_info WHERE id ? ORDER BY id ASC) WHERE rnum startRow and pageSize rnum ;}Overridepublic String findChangeConfigFetchRows(MapString, String params, final Timestamp startTime,final Timestamp endTime, int startRow, int pageSize, long lastMaxId) {final String tenant params.get(TENANT);final String dataId params.get(DATA_ID);final String group params.get(GROUP);final String appName params.get(APP_NAME);final String tenantTmp StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;final String sqlFetchRows SELECT * FROM (SELECT id,data_id,group_id,tenant_id,app_name,content,type,md5,gmt_modified , ROWNUM as rnum FROM config_info WHERE ;String where 11 ;if (!StringUtils.isBlank(dataId)) {where AND data_id LIKE ? ;}if (!StringUtils.isBlank(group)) {where AND group_id LIKE ? ;}if (!StringUtils.isBlank(tenantTmp)) {where AND tenant_id ? ;}if (!StringUtils.isBlank(appName)) {where AND app_name ? ;}if (startTime ! null) {where AND gmt_modified ? ;}if (endTime ! null) {where AND gmt_modified ? ;}return sqlFetchRows where AND id lastMaxId ORDER BY id ASC) WHERE rnum 0 and pageSize rnum;}Overridepublic String listGroupKeyMd5ByPageFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,app_name,md5,type,gmt_modified,encrypted_data_key FROM (SELECT * FROM ( SELECT id, ROWNUM as rnum FROM config_info ORDER BY id) WHERE rnum startRow and pageSize rnum ) g, config_info t WHERE g.id t.id;}Overridepublic String findConfigInfoBaseLikeFetchRows(MapString, String params, int startRow, int pageSize) {final String sqlFetchRows SELECT * FROM (SELECT id,data_id,group_id,tenant_id,content, ROWNUM as rnum FROM config_info WHERE ;String where 11 AND tenant_id ;if (!StringUtils.isBlank(params.get(DATA_ID))) {where AND data_id LIKE ? ;}if (!StringUtils.isBlank(params.get(GROUP))) {where AND group_id LIKE ;}if (!StringUtils.isBlank(params.get(CONTENT))) {where AND content LIKE ? ;}return sqlFetchRows where ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String findConfigInfo4PageFetchRows(MapString, String params, int startRow, int pageSize) {final String appName params.get(APP_NAME);final String dataId params.get(DATA_ID);final String group params.get(GROUP);final String sql SELECT * FROM (SELECT id,data_id,group_id,tenant_id,app_name,content,type,encrypted_data_key, ROWNUM as rnum FROM config_info;StringBuilder where new StringBuilder( WHERE );where.append( tenant_id? );if (StringUtils.isNotBlank(dataId)) {where.append( AND data_id? );}if (StringUtils.isNotBlank(group)) {where.append( AND group_id? );}if (StringUtils.isNotBlank(appName)) {where.append( AND app_name? );}return sql where ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String findConfigInfoLike4PageFetchRows(MapString, String params, int startRow, int pageSize) {String dataId params.get(DATA_ID);String group params.get(GROUP);final String appName params.get(APP_NAME);final String content params.get(CONTENT);final String sqlFetchRows SELECT * FROM (SELECT id,data_id,group_id,tenant_id,app_name,content,encrypted_data_key, ROWNUM as rnum FROM config_info;StringBuilder where new StringBuilder( WHERE );where.append( tenant_id LIKE ? );if (!StringUtils.isBlank(dataId)) {where.append( AND data_id LIKE ? );}if (!StringUtils.isBlank(group)) {where.append( AND group_id LIKE ? );}if (!StringUtils.isBlank(appName)) {where.append( AND app_name ? );}if (!StringUtils.isBlank(content)) {where.append( AND content LIKE ? );}return sqlFetchRows where ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String findAllConfigInfoFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,app_name,content,md5 FROM (SELECT * FROM ( SELECT id, ROWNUM as rnum FROM config_info WHERE tenant_id LIKE ? ORDER BY id) WHERE rnum startRow and pageSize rnum) g, config_info t WHERE g.id t.id ;}Overridepublic String findConfigInfoBaseByGroupFetchRows(int startRow, int pageSize) {return SELECT * FROM (SELECT id,data_id,group_id,content, ROWNUM as rnum FROM config_info WHERE group_id? AND tenant_id? ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}}1.4.ConfigInfoTagMapperByOracle public class ConfigInfoTagMapperByOracle extends AbstractConfigInfoTagMapperCommon {Overridepublic String findAllConfigInfoTagForDumpAllFetchRows(int startRow, int pageSize) {return SELECT t.id,data_id,group_id,tenant_id,tag_id,app_name,content,md5,gmt_modified FROM ( SELECT * FROM ( SELECT id, ROWNUM as rnum FROM config_info_tag ORDER BY id) WHERE rnum startRow and pageSize rnum ) g, config_info_tag t WHERE g.id t.id ;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.5.ConfigTagsRelationMapperByOracle public class ConfigTagsRelationMapperByOracle extends AbstractConfigTagsRelationMapperCommon {Overridepublic String findConfigInfo4PageFetchRows(MapString, String params, int tagSize, int startRow, int pageSize) {final String appName params.get(appName);final String dataId params.get(dataId);final String group params.get(group);StringBuilder where new StringBuilder( WHERE );final String sql SELECT * FROM (SELECT a.id,a.data_id,a.group_id,a.tenant_id,a.app_name,a.content, ROWNUM as rnum FROM config_info a LEFT JOIN config_tags_relation b ON a.idb.id;where.append( a.tenant_id? );if (StringUtils.isNotBlank(dataId)) {where.append( AND a.data_id? );}if (StringUtils.isNotBlank(group)) {where.append( AND a.group_id? );}if (StringUtils.isNotBlank(appName)) {where.append( AND a.app_name? );}where.append( AND b.tag_name IN ();for (int i 0; i tagSize; i) {if (i ! 0) {where.append(, );}where.append(?);}where.append() );return sql where ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String findConfigInfoLike4PageFetchRows(final MapString, String params, int tagSize, int startRow,int pageSize) {final String appName params.get(appName);final String content params.get(content);final String dataId params.get(dataId);final String group params.get(group);StringBuilder where new StringBuilder( WHERE );final String sqlFetchRows SELECT * FROM (SELECT a.id,a.data_id,a.group_id,a.tenant_id,a.app_name,a.content, ROWNUM as rnum FROM config_info a LEFT JOIN config_tags_relation b ON a.idb.id ;where.append( a.tenant_id LIKE ? );if (!StringUtils.isBlank(dataId)) {where.append( AND a.data_id LIKE ? );}if (!StringUtils.isBlank(group)) {where.append( AND a.group_id LIKE ? );}if (!StringUtils.isBlank(appName)) {where.append( AND a.app_name ? );}if (!StringUtils.isBlank(content)) {where.append( AND a.content LIKE ? );}where.append( AND b.tag_name IN ();for (int i 0; i tagSize; i) {if (i ! 0) {where.append(, );}where.append(?);}where.append() );return sqlFetchRows where ) WHERE rnum startRow and pageSize rnum ;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.6.GroupCapacityMapperByOracle public class GroupCapacityMapperByOracle extends AbstractGroupCapacityMapperCommon {Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.7.HistoryConfigInfoMapperByOracle public class HistoryConfigInfoMapperByOracle extends AbstractHistoryConfigInfoMapperCommon {public String pageFindConfigHistoryFetchRows(int pageNo, int pageSize) {final int offset (pageNo - 1) * pageSize;final int limit pageSize;return SELECT * FROM (SELECT nid,ROWNUM as rnum,data_id,group_id,tenant_id,app_name,src_ip,src_user,op_type,gmt_create,gmt_modified FROM his_config_info WHERE data_id ? AND group_id ? AND tenant_id ? ORDER BY nid DESC ) where rnum offset and limit rnum ;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}
}1.8.TenantCapacityMapperByOracle public class TenantCapacityMapperByOracle extends AbstractTenantCapacityMapperCommon {Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}}1.9.TenantInfoMapperByOracle public class TenantInfoMapperByOracle extends AbstractMapper implements TenantInfoMapper {Overridepublic String getTableName() {return TableConstant.TENANT_INFO;}Overridepublic String getDataSource() {return DataSourceConstantExt.ORACLE;}}2.ddl create table CONFIG_INFO
(ID NUMBER(20) not nullprimary key,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char),CONTENT CLOB not null,MD5 VARCHAR2(32 char),GMT_CREATE DATE not null,GMT_MODIFIED DATE not null,SRC_USER CLOB,SRC_IP VARCHAR2(20 char),APP_NAME VARCHAR2(128 char) default NULL,TENANT_ID VARCHAR2(128 char) default ,C_DESC VARCHAR2(256 char) default NULL,C_USE VARCHAR2(64 char) default NULL,EFFECT VARCHAR2(64 char) default NULL,TYPE VARCHAR2(64 char) default NULL,C_SCHEMA CLOB,ENCRYPTED_DATA_KEY CLOB null
)
;create unique index UK_CONFIGINFO_DATAGROUPTENANTon CONFIG_INFO (DATA_ID, GROUP_ID, TENANT_ID)
;create table CONFIG_INFO_AGGR
(ID NUMBER(20) not nullprimary key,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char) not null,DATUM_ID VARCHAR2(255 char) not null,CONTENT CLOB not null,GMT_MODIFIED DATE not null,APP_NAME VARCHAR2(128 char) default NULL,TENANT_ID VARCHAR2(128 char) default
)
;create unique index UK_C_DATAGROUPTENANTDATUMon CONFIG_INFO_AGGR (DATA_ID, GROUP_ID, TENANT_ID, DATUM_ID)
;create table CONFIG_INFO_BETA
(ID NUMBER(20) not nullprimary key,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char) not null,APP_NAME VARCHAR2(128 char) default NULL,CONTENT CLOB not null,BETA_IPS VARCHAR2(1024 char) default NULL,MD5 VARCHAR2(32 char) default NULL,GMT_CREATE DATE not null,GMT_MODIFIED DATE not null,SRC_USER CLOB,SRC_IP VARCHAR2(20 char) default NULL,TENANT_ID VARCHAR2(128 char) default ,ENCRYPTED_DATA_KEY CLOB not null
)
;create unique index UK_C_DATAGROUPTENANTon CONFIG_INFO_BETA (DATA_ID, GROUP_ID, TENANT_ID)
;create table CONFIG_INFO_TAG
(ID NUMBER(20) not nullprimary key,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char) not null,TENANT_ID VARCHAR2(128 char) default ,TAG_ID VARCHAR2(128 char) not null,APP_NAME VARCHAR2(128 char) default NULL,CONTENT CLOB not null,MD5 VARCHAR2(32 char) default NULL,GMT_CREATE DATE not null,GMT_MODIFIED DATE not null,SRC_USER CLOB,SRC_IP VARCHAR2(20 char) default NULL
)
;create unique index UK_C_DATAGROUPTENANTTAGon CONFIG_INFO_TAG (DATA_ID, GROUP_ID, TENANT_ID, TAG_ID)
;create table CONFIG_TAGS_RELATION
(ID NUMBER(20) not null,TAG_NAME VARCHAR2(128 char) not null,TAG_TYPE VARCHAR2(64 char) default NULL,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char) not null,TENANT_ID VARCHAR2(128 char) default ,NID NUMBER(20) not nullprimary key
)
;create unique index UK_C_CONFIGIDTAGon CONFIG_TAGS_RELATION (ID, TAG_NAME, TAG_TYPE)
;create index IDX_TENANT_IDon CONFIG_TAGS_RELATION (TENANT_ID)
;create table GROUP_CAPACITY
(ID NUMBER(20) not nullprimary key,GROUP_ID VARCHAR2(128 char) default ,QUOTA NUMBER(10) default 0,USAGE NUMBER(10) default 0,MAX_SIZE NUMBER(10) default 0,MAX_AGGR_COUNT NUMBER(10) default 0,MAX_AGGR_SIZE NUMBER(10) default 0,MAX_HISTORY_COUNT NUMBER(10) default 0,GMT_CREATE DATE not null,GMT_MODIFIED DATE not null
)
;comment on table GROUP_CAPACITY is 集群、各Group容量信息表
;comment on column GROUP_CAPACITY.ID is 主键ID
;comment on column GROUP_CAPACITY.GROUP_ID is Group ID空字符表示整个集群
;comment on column GROUP_CAPACITY.QUOTA is 配额0表示使用默认值
;comment on column GROUP_CAPACITY.USAGE is 使用量
;comment on column GROUP_CAPACITY.MAX_SIZE is 单个配置大小上限单位为字节0表示使用默认值
;comment on column GROUP_CAPACITY.MAX_AGGR_COUNT is 聚合子配置最大个数0表示使用默认值
;comment on column GROUP_CAPACITY.MAX_AGGR_SIZE is 单个聚合数据的子配置大小上限单位为字节0表示使用默认值
;comment on column GROUP_CAPACITY.MAX_HISTORY_COUNT is 最大变更历史数量
;comment on column GROUP_CAPACITY.GMT_CREATE is 创建时间
;comment on column GROUP_CAPACITY.GMT_MODIFIED is 修改时间
;create unique index UK_GROUP_IDon GROUP_CAPACITY (GROUP_ID)
;create table HIS_CONFIG_INFO
(ID NUMBER not null,NID NUMBER(20) not nullprimary key,DATA_ID VARCHAR2(255 char) not null,GROUP_ID VARCHAR2(128 char) not null,APP_NAME VARCHAR2(128 char) default NULL,CONTENT CLOB not null,MD5 VARCHAR2(32 char) default NULL,GMT_CREATE DATE not null,GMT_MODIFIED DATE not null,SRC_USER CLOB,SRC_IP VARCHAR2(20 char) default NULL,OP_TYPE CHAR(10 char) default NULL,TENANT_ID VARCHAR2(128 char) default ,ENCRYPTED_DATA_KEY CLOB null
)
;create index IDX_GMT_CREATEon HIS_CONFIG_INFO (GMT_CREATE)
;create index IDX_GMT_MODIFIEDon HIS_CONFIG_INFO (GMT_MODIFIED)
;create index IDX_DIDon HIS_CONFIG_INFO (DATA_ID)
;create table TENANT_CAPACITY
(ID NUMBER(20) not nullprimary key,TENANT_ID VARCHAR2(128) default ,QUOTA NUMBER(10) default 0,USAGE NUMBER(10) default 0,MAX_SIZE NUMBER(10) default 0,MAX_AGGR_COUNT NUMBER(10) default 0,MAX_AGGR_SIZE NUMBER(10) default 0,MAX_HISTORY_COUNT NUMBER(10) default 0,GMT_CREATE DATE not null,GMT_MODIFIED DATE not null
)
;create unique index UK_TENANT_IDon TENANT_CAPACITY (TENANT_ID)
;create table TENANT_INFO
(ID NUMBER(20) not nullprimary key,KP VARCHAR2(128) not null,TENANT_ID VARCHAR2(128 char) default ,TENANT_NAME VARCHAR2(128 char) default ,TENANT_DESC VARCHAR2(256 char) default NULL,CREATE_SOURCE VARCHAR2(32 char) default NULL,GMT_CREATE NUMBER(20) not null,GMT_MODIFIED NUMBER(20) not null
)
;create unique index UK_TENANT_INFO_KPTENANTIDon TENANT_INFO (KP, TENANT_ID)
;create table USERS
(USERNAME VARCHAR2(50 char) not nullprimary key,PASSWORD VARCHAR2(500 char) not null,ENABLED CHAR not null
)
;create table ROLES
(USERNAME VARCHAR2(50 char) not null,ROLE VARCHAR2(50 char) not null,constraint UK_USERNAME_ROLEunique (USERNAME, ROLE)
)
;create table PERMISSIONS
(ROLE VARCHAR2(50 char) not null,RESOURCES VARCHAR2(512 char) not null,ACTION VARCHAR2(8 char) not null,constraint UK_ROLE_PERMISSIONunique (ROLE, RESOURCES, ACTION)
)
;INSERT INTO users (username, password, enabled) VALUES (nacos, $2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu, 1);INSERT INTO roles (username, role) VALUES (nacos, ROLE_ADMIN);create sequence SEQ_CONFIG_INFOmaxvalue 999999999999
;create sequence SEQ_CONFIG_INFO_AGGRmaxvalue 999999999999
;create sequence SEQ_CONFIG_INFO_BETAmaxvalue 999999999999
;create sequence SEQ_CONFIG_INFO_TAGmaxvalue 999999999999
;create sequence SEQ_CONFIG_TAGS_RELATIONmaxvalue 999999999999
;create sequence SEQ_CAPACITY_ENTITYmaxvalue 999999999999
;create sequence SEQ_HIS_CONFIG_INFOmaxvalue 999999999999
;create sequence SEQ_TENANT_INFOmaxvalue 999999999999
;3.pom dependencygroupIdcom.oracle/groupIdartifactIdojdbc7/artifactIdversion12.1.0.2/version/dependency4.nacos-server的application.properties配置 db.pool.config.driverClassNamexx
db.pool.config.schemaxx
spring.sql.init.platformoracle
db.url.0xx
db.user.0xx
db.password.0xx
db.pool.config.connectionTestQueryselect 1 from dual
