首页 > 数据库 >nacos适配SqlServer、Oracle

nacos适配SqlServer、Oracle

时间：2023-08-25 13:58:11浏览次数：164  
标签：group String int 适配 SqlServer Oracle NULL id tenant

　　继上文《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";

}
View Code
　　1.1.ConfigInfoAggrMapperBySqlServer
public class ConfigInfoAggrMapperBySqlServer extends AbstractConfigInfoAggrMapperCommon {

    @Override
    public 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";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }

}
View Code
 　　1.2.ConfigInfoBetaMapperBySqlServer
public class ConfigInfoBetaMapperBySqlServer extends AbstractConfigInfoBetaMapperCommon {

    @Override
    public 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 ";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }

}
View Code
 　　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";

    @Override
    public 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";
    }


    @Override
    public 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";
    }


    @Override
    public 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  ";
    }

    @Override
    public 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 ";
    }


    @Override
    public 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";
    }


    @Override
    public 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";
    }

    @Override
    public 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";
    }

    @Override
    public 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";
    }


    @Override
    public 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";
    }


    @Override
    public 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";
    }

    @Override
    public 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 ";
    }

    @Override
    public 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";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }


}
View Code
　　1.4.ConfigInfoTagMapperBySqlServer
public class ConfigInfoTagMapperBySqlServer extends AbstractConfigInfoTagMapperCommon {

    @Override
    public 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  ";
    }


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }
}
View Code
　　1.5.ConfigTagsRelationMapperBySqlServer
public class ConfigTagsRelationMapperBySqlServer extends AbstractConfigTagsRelationMapperCommon {
    @Override
    public 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";
    }


    @Override
    public 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";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }
}
View Code
　　1.6.GroupCapacityMapperBySqlServer
public class GroupCapacityMapperBySqlServer extends AbstractGroupCapacityMapperCommon {


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }
}
View Code
　　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";
    }


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }
}
View Code
　　1.8.TenantCapacityMapperBySqlServer
public class TenantCapacityMapperBySqlServer extends AbstractTenantCapacityMapperCommon {


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }

}
View Code
　　1.9.TenantInfoMapperBySqlServer
public class TenantInfoMapperBySqlServer extends AbstractMapper implements TenantInfoMapper {

    @Override
    public String getTableName() {
        return TableConstant.TENANT_INFO;
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.SQLSERVER;
    }

}
View Code

 　　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');
View Code

　　3.pom

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.4.0.jre11</version>
        </dependency>
View Code

 二.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";
}
View Code
　　1.1.ConfigInfoAggrMapperByOracle
public class ConfigInfoAggrMapperByOracle extends AbstractConfigInfoAggrMapperCommon {

    @Override
    public 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";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
 　　1.2.ConfigInfoBetaMapperByOracle
public class ConfigInfoBetaMapperByOracle extends AbstractConfigInfoBetaMapperCommon {

    @Override
    public 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 ";
    }
    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
 　　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";
    @Override
    public 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";
    }

    @Override
    public 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";
    }

    @Override
    public 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";
    }

    @Override
    public 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  ";
    }
    @Override
    public 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 ";
    }

    @Override
    public 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 ";
    }
    @Override
    public 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";
    }

    @Override
    public 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";
    }
    @Override
    public 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 ";
    }
    @Override
    public 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 ";
    }


    @Override
    public 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 ";
    }

    @Override
    public 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 ";
    }

    @Override
    public 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 ";
    }
    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }


}
View Code
　　1.4.ConfigInfoTagMapperByOracle
public class ConfigInfoTagMapperByOracle extends AbstractConfigInfoTagMapperCommon {

    @Override
    public 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  ";
    }
    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
　　1.5.ConfigTagsRelationMapperByOracle
public class ConfigTagsRelationMapperByOracle extends AbstractConfigTagsRelationMapperCommon {
    @Override
    public 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 ";
    }


    @Override
    public 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 ";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
　　1.6.GroupCapacityMapperByOracle
public class GroupCapacityMapperByOracle extends AbstractGroupCapacityMapperCommon {


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
　　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 ";
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }
}
View Code
　　1.8.TenantCapacityMapperByOracle
public class TenantCapacityMapperByOracle extends AbstractTenantCapacityMapperCommon {


    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }

}
View Code
　　1.9.TenantInfoMapperByOracle
public class TenantInfoMapperByOracle extends AbstractMapper implements TenantInfoMapper {

    @Override
    public String getTableName() {
        return TableConstant.TENANT_INFO;
    }

    @Override
    public String getDataSource() {
        return DataSourceConstantExt.ORACLE;
    }

}
View Code

 　　2.ddl

create table CONFIG_INFO
(
    ID                 NUMBER(20)         not null
        primary 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_DATAGROUPTENANT
    on CONFIG_INFO (DATA_ID, GROUP_ID, TENANT_ID)
;

create table CONFIG_INFO_AGGR
(
    ID           NUMBER(20)         not null
        primary 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_DATAGROUPTENANTDATUM
    on CONFIG_INFO_AGGR (DATA_ID, GROUP_ID, TENANT_ID, DATUM_ID)
;

create table CONFIG_INFO_BETA
(
    ID                 NUMBER(20)         not null
        primary 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_DATAGROUPTENANT
    on CONFIG_INFO_BETA (DATA_ID, GROUP_ID, TENANT_ID)
;

create table CONFIG_INFO_TAG
(
    ID           NUMBER(20)         not null
        primary 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_DATAGROUPTENANTTAG
    on 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 null
        primary key
)
;

create unique index UK_C_CONFIGIDTAG
    on CONFIG_TAGS_RELATION (ID, TAG_NAME, TAG_TYPE)
;

create index IDX_TENANT_ID
    on CONFIG_TAGS_RELATION (TENANT_ID)
;

create table GROUP_CAPACITY
(
    ID                NUMBER(20) not null
        primary 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_ID
    on GROUP_CAPACITY (GROUP_ID)
;

create table HIS_CONFIG_INFO
(
    ID                 NUMBER             not null,
    NID                NUMBER(20)         not null
        primary 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_CREATE
    on HIS_CONFIG_INFO (GMT_CREATE)
;

create index IDX_GMT_MODIFIED
    on HIS_CONFIG_INFO (GMT_MODIFIED)
;

create index IDX_DID
    on HIS_CONFIG_INFO (DATA_ID)
;

create table TENANT_CAPACITY
(
    ID                NUMBER(20) not null
        primary 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_ID
    on TENANT_CAPACITY (TENANT_ID)
;

create table TENANT_INFO
(
    ID            NUMBER(20)    not null
        primary 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_KPTENANTID
    on TENANT_INFO (KP, TENANT_ID)
;

create table USERS
(
    USERNAME VARCHAR2(50 char)  not null
        primary 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_ROLE
        unique (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_PERMISSION
        unique (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_INFO
    maxvalue 999999999999
;

create sequence SEQ_CONFIG_INFO_AGGR
    maxvalue 999999999999
;

create sequence SEQ_CONFIG_INFO_BETA
    maxvalue 999999999999
;

create sequence SEQ_CONFIG_INFO_TAG
    maxvalue 999999999999
;

create sequence SEQ_CONFIG_TAGS_RELATION
    maxvalue 999999999999
;

create sequence SEQ_CAPACITY_ENTITY
    maxvalue 999999999999
;

create sequence SEQ_HIS_CONFIG_INFO
    maxvalue 999999999999
;

create sequence SEQ_TENANT_INFO
    maxvalue 999999999999
;
View Code

　　3.pom

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

 　　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
View Code

 

标签：group,String,int,适配,SqlServer,Oracle,NULL,id,tenant
From： https://www.cnblogs.com/huangrenhui/p/17654636.html

相关文章

  • Red Hat8.8 安装 Oracle19C，配置开机自动启动，创建数据库，调整内核参数
    1.下载rpm包oracle-database-ee-19c-1.0-1.x86_64.rpm2.上传rpm包上传下载的rpm包到响应目录，例如：/home3.安装依赖上传compat-libcap1-1.10-7.el7.x86_64.rpm和compat-libstdc++-33-3.2.3-72.el7.x86_64.rpm包的相应目录例如：/home安装上传的两个rpm包#安......
  • Oracle触发器
    Oracle触发器1.概念触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件......
  • oracle表分区
    转：http://www.bxcqd.com/news/43471.html【ORACLE表分区】一.表分区策略1.识别大表采用ANALYZETABLE语句进行分析，然后查询数据字典获得相应的数据量。2.大表如何分区可根据月份，季度以及年份等进行分区；3.分区的表空间规划要对每个表空间的大小进行估计二.创建表分区a.创......
  • Dbeaver工具，导出oracle 表注释、字段注释
    --表注释SELECTtable_name,comments,'commentontable'||table_name||'is'''||comments||''';'FROMall_tab_commentsWHEREowner='用户名'ANDtable_nameIN('表名')ORDERBYtable_name;--字段注......
  • 【Oracle RAC Database】Oracle Grid Infrastructure 启动流程与日志
    OS启动OHASD（OracleHighAvailabilityServices）init.ohasd.run被启动，该进程负责启动ohasd.bin守护进程[root@node01~]#ps-ef|grepohasd|grep-vgreproot5151018:59?00:00:00/bin/sh/etc/init.d/init.ohasdrun>/dev/null2>&1</dev/......
  • 4、oracle查看表空间使用情况
    oracle查看表空间使用情况查看表空间大小、使用率、剩余空间selecta.tablespace_name,total,free,total-freeasused,substr(free/total*100,1,5)as"FREE%",substr((total-free)/total*100,1,5)as"USED%"from(selecttablespace_name,sum(bytes)/10......
  • 15、oracle误删数据恢复
    目录oracle误删数据恢复1、delete删除数据2、drop删除表恢复oracle误删数据恢复1、delete删除数据回滚到指定时间点的数据select*fromgscommtypeasoftimestampto_timestamp('2019-08-2823','yyyy-mm-ddhh24');2、drop删除表恢复selectobject_name,origi......
  • Oracle行列操作--合并行与按字段拆分
    1、在实际工作中遇到根据某一字段将多行合并成一行的情况，我们下面以选修课的例子进行说明：--createtablecreatetableXXK(idNUMBER,rymcNVARCHAR2(50),xxkmcNVARCHAR2(50))---inserttestdatainsertintoXXKvalues(1,'小明','编程');insertintoXXKv......
  • Oracle 运维常用命令
    介绍在管理Oracle数据库时，我们经常会使用到各种命令，这些命令或长或短。除了经常使用的命令我们能记得住，像那种偶尔会用一次的，很难靠记忆敲出来。为了避免在使用中再次百度找半天，于是就将这些命令写下来了，方便以后查阅。命令Linux命令命令说明命令备注查看oracle主进程状态ps-ef|gr......
  • 1、oracle监听器服务的开启与关闭
    目录oracle监听器服务的开启与关闭1、查看监听状态2、开启监听3、关闭监听4、重启监听oracle监听器服务的开启与关闭Oracle监听器服务关闭时，不让外部进行数据库连接。1、查看监听状态lsnrctlstatus2、开启监听lsnrctlstart3、关闭监听lsnrctlstop4、重启监听lsnrc......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99