网络推广网站建设软件定制/如何制作网页链接

　　继上文《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 SQLSERVER="sqlserver";}

　　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(Map<String, 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 = " 1=1 ";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(Map<String, String> params, int startRow, int pageSize) {final String sqlFetchRows = "SELECT id,data_id,group_id,tenant_id,content FROM config_info WHERE ";String where = " 1=1 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(Map<String, 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(Map<String, 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(Map<String, 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.id=b.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 Map<String, 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.id=b.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

        <dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>12.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 SQLSERVER="sqlserver";//增加类型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(Map<String, 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 = " 1=1 ";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(Map<String, 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 = " 1=1 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(Map<String, 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(Map<String, 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(Map<String, 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.id=b.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 Map<String, 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.id=b.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

        <dependency><groupId>com.oracle</groupId><artifactId>ojdbc7</artifactId><version>12.1.0.2</version></dependency>

 　　4.nacos-server的application.properties配置

db.pool.config.driverClassName=xx
db.pool.config.schema=xx
spring.sql.init.platform=oracle
db.url.0=xx
db.user.0=xx
db.password.0=xx
db.pool.config.connectionTestQuery=select 1 from dual