nacos2.2.3 适配南大通用数据库Gbase 8S
nacos2.2.3版本适配南大通用数据库Gbase 8s
·
公司目前产品需要适配新的国产数据库，其中使用了nacos，需要替换nacos使用的数据库，nacos2.2.3已经拥有一个数据库适配的插件，nacos持久层是基于spring jdbc 编写，吐槽一下这里没有使用ORM框架，对后续各种数据库适配工作带来一些需要手动处理的问题
1.下载nacos插件
首先下载nacos的数据库适配插件，git地址：
https://github.com/nacos-group/nacos-plugin.git
切换到2.2.3分支(插件版本号需要和nacos的版本号一致，该插件tag为2.2.x)：
查看其下面的数据库适配组件（该版本只有postgresql适配）：
2.添加南大通用插件适配模块
按照postgresql的插件例子添加南大通用数据库插件适配模块：
该模块需要修改的内容如下：
pom需要和postgresql的格式保持一致：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>nacos-datasource-plugin-ext</artifactId>
<groupId>com.alibaba.nacos</groupId>
<version>${revision}</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>nacos-gbasedbt-datasource-plugin-ext</artifactId>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>com.gbase.jdbc.Driver</groupId>
<artifactId>gbase</artifactId>
<version>3.5.0.2</version>
</dependency>
<dependency>
<groupId>com.alibaba.nacos</groupId>
<artifactId>nacos-datasource-plugin-ext-base</artifactId>
<version>${revision}</version>
<scope>compile</scope>
</dependency>
</dependencies>
</project>其中gbase是从南大通用官网下载的驱动jar包，上传到本地maven私服
3.添加适应南大通用的sql
南大通用数据库的初始建表语句等内容需要修改：
sql内容如下：
-- ----------------------------
-- Table structure for config_info
-- ----------------------------
DROP TABLE IF EXISTS config_info;
CREATE TABLE config_info (
id bigserial NOT NULL,
data_id varchar(255) NOT NULL,
group_id varchar(255) ,
content text NOT NULL,
md5 varchar(32) ,
gmt_create timestamp(6) NOT NULL,
gmt_modified timestamp(6) NOT NULL,
src_user text ,
src_ip varchar(20) ,
app_name varchar(128) ,
tenant_id varchar(128) ,
c_desc varchar(256) ,
c_use varchar(64) ,
effect varchar(64) ,
type varchar(64) ,
c_schema text ,
encrypted_data_key text NOT NULL,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN config_info.id IS 'id';
COMMENT ON COLUMN config_info.data_id IS 'data_id';
COMMENT ON COLUMN config_info.content IS 'content';
COMMENT ON COLUMN config_info.md5 IS 'md5';
COMMENT ON COLUMN config_info.gmt_create IS '创建时间';
COMMENT ON COLUMN config_info.gmt_modified IS '修改时间';
COMMENT ON COLUMN config_info.src_user IS 'source user';
COMMENT ON COLUMN config_info.src_ip IS 'source ip';
COMMENT ON COLUMN config_info.tenant_id IS '租户字段';
COMMENT ON COLUMN config_info.encrypted_data_key IS '秘钥';
COMMENT ON TABLE config_info IS 'config_info';
-- ----------------------------
-- Table structure for config_info_aggr
-- ----------------------------
DROP TABLE IF EXISTS config_info_aggr;
CREATE TABLE config_info_aggr (
id bigserial NOT NULL,
data_id varchar(255) NOT NULL,
group_id varchar(255) NOT NULL,
datum_id varchar(255) NOT NULL,
content text NOT NULL,
gmt_modified timestamp(6) NOT NULL,
app_name varchar(128) ,
tenant_id varchar(128) ,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN config_info_aggr.id IS 'id';
COMMENT ON COLUMN config_info_aggr.data_id IS 'data_id';
COMMENT ON COLUMN config_info_aggr.group_id IS 'group_id';
COMMENT ON COLUMN config_info_aggr.datum_id IS 'datum_id';
COMMENT ON COLUMN config_info_aggr.content IS '内容';
COMMENT ON COLUMN config_info_aggr.gmt_modified IS '修改时间';
COMMENT ON COLUMN config_info_aggr.tenant_id IS '租户字段';
COMMENT ON TABLE config_info_aggr IS '增加租户字段';
-- ----------------------------
-- Table structure for config_info_beta
-- ----------------------------
DROP TABLE IF EXISTS config_info_beta;
CREATE TABLE config_info_beta (
id bigserial NOT NULL,
data_id varchar(255) NOT NULL,
group_id varchar(128) NOT NULL,
app_name varchar(128) ,
content text NOT NULL,
beta_ips varchar(1024) ,
md5 varchar(32) ,
gmt_create timestamp(6) NOT NULL,
gmt_modified timestamp(6) NOT NULL,
src_user text ,
src_ip varchar(20) ,
tenant_id varchar(128) ,
encrypted_data_key text NOT NULL,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN config_info_beta.id IS 'id';
COMMENT ON COLUMN config_info_beta.data_id IS 'data_id';
COMMENT ON COLUMN config_info_beta.group_id IS 'group_id';
COMMENT ON COLUMN config_info_beta.app_name IS 'app_name';
COMMENT ON COLUMN config_info_beta.content IS 'content';
COMMENT ON COLUMN config_info_beta.beta_ips IS 'betaIps';
COMMENT ON COLUMN config_info_beta.md5 IS 'md5';
COMMENT ON COLUMN config_info_beta.gmt_create IS '创建时间';
COMMENT ON COLUMN config_info_beta.gmt_modified IS '修改时间';
COMMENT ON COLUMN config_info_beta.src_user IS 'source user';
COMMENT ON COLUMN config_info_beta.src_ip IS 'source ip';
COMMENT ON COLUMN config_info_beta.tenant_id IS '租户字段';
COMMENT ON COLUMN config_info_beta.encrypted_data_key IS '秘钥';
COMMENT ON TABLE config_info_beta IS 'config_info_beta';
-- ----------------------------
-- Table structure for config_info_tag
-- ----------------------------
DROP TABLE IF EXISTS config_info_tag;
CREATE TABLE config_info_tag (
id bigserial NOT NULL,
data_id varchar(255) NOT NULL,
group_id varchar(128) NOT NULL,
tenant_id varchar(128) ,
tag_id varchar(128) NOT NULL,
app_name varchar(128) ,
content text NOT NULL,
md5 varchar(32) ,
gmt_create timestamp(6) NOT NULL,
gmt_modified timestamp(6) NOT NULL,
src_user text ,
src_ip varchar(20) ,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN config_info_tag.id IS 'id';
COMMENT ON COLUMN config_info_tag.data_id IS 'data_id';
COMMENT ON COLUMN config_info_tag.group_id IS 'group_id';
COMMENT ON COLUMN config_info_tag.tenant_id IS 'tenant_id';
COMMENT ON COLUMN config_info_tag.tag_id IS 'tag_id';
COMMENT ON COLUMN config_info_tag.app_name IS 'app_name';
COMMENT ON COLUMN config_info_tag.content IS 'content';
COMMENT ON COLUMN config_info_tag.md5 IS 'md5';
COMMENT ON COLUMN config_info_tag.gmt_create IS '创建时间';
COMMENT ON COLUMN config_info_tag.gmt_modified IS '修改时间';
COMMENT ON COLUMN config_info_tag.src_user IS 'source user';
COMMENT ON COLUMN config_info_tag.src_ip IS 'source ip';
COMMENT ON TABLE config_info_tag IS 'config_info_tag';
-- ----------------------------
-- Table structure for config_tags_relation
-- ----------------------------
DROP TABLE IF EXISTS config_tags_relation;
CREATE TABLE config_tags_relation (
id bigint NOT NULL,
tag_name varchar(128) NOT NULL,
tag_type varchar(64) ,
data_id varchar(255) NOT NULL,
group_id varchar(128) NOT NULL,
tenant_id varchar(128) ,
nid bigserial NOT NULL,
PRIMARY KEY (nid)
)
;
COMMENT ON COLUMN config_tags_relation.id IS 'id';
COMMENT ON COLUMN config_tags_relation.tag_name IS 'tag_name';
COMMENT ON COLUMN config_tags_relation.tag_type IS 'tag_type';
COMMENT ON COLUMN config_tags_relation.data_id IS 'data_id';
COMMENT ON COLUMN config_tags_relation.group_id IS 'group_id';
COMMENT ON COLUMN config_tags_relation.tenant_id IS 'tenant_id';
COMMENT ON TABLE config_tags_relation IS 'config_tag_relation';
-- ----------------------------
-- Table structure for group_capacity
-- ----------------------------
DROP TABLE IF EXISTS group_capacity;
CREATE TABLE group_capacity (
id bigserial NOT NULL,
group_id varchar(128) NOT NULL,
quota integer NOT NULL,
usage integer NOT NULL,
max_size integer NOT NULL,
max_aggr_count integer NOT NULL,
max_aggr_size integer NOT NULL,
max_history_count integer NOT NULL,
gmt_create timestamp(6) NOT NULL,
gmt_modified timestamp(6) NOT NULL,
PRIMARY KEY (id)
)
;
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 '修改时间';
COMMENT ON TABLE group_capacity IS '集群、各Group容量信息表';
-- ----------------------------
-- Table structure for his_config_info
-- ----------------------------
DROP TABLE IF EXISTS his_config_info;
CREATE TABLE his_config_info (
id int8 NOT NULL,
nid bigserial NOT NULL,
data_id varchar(255) NOT NULL,
group_id varchar(128) NOT NULL,
app_name varchar(128) ,
content CLOB NOT NULL,
md5 varchar(32) DEFAULT NULL,
gmt_create DATETIME YEAR TO SECOND NOT NULL DEFAULT CURRENT YEAR TO SECOND,
gmt_modified timestamp(6) NOT NULL,
src_user CLOB ,
src_ip varchar(20) ,
op_type char(10) ,
tenant_id varchar(128) ,
encrypted_data_key CLOB NOT NULL,
PRIMARY KEY (nid)
);
COMMENT ON COLUMN his_config_info.app_name IS 'app_name';
COMMENT ON COLUMN his_config_info.tenant_id IS '租户字段';
COMMENT ON COLUMN his_config_info.encrypted_data_key IS '秘钥';
COMMENT ON TABLE his_config_info IS '多租户改造';
-- ----------------------------
-- Indexes structure for table his_config_info
-- ----------------------------
CREATE INDEX idx_did ON his_config_info ( data_id );
CREATE INDEX idx_gmt_create ON his_config_info ( gmt_create );
CREATE INDEX idx_gmt_modified ON his_config_info ( gmt_modified );
-- ----------------------------
-- Table structure for permissions
-- ----------------------------
DROP TABLE IF EXISTS permissions;
CREATE TABLE permissions (
role varchar(50) NOT NULL,
resource varchar(512) NOT NULL,
action varchar(8) NOT NULL
)
;
-- ----------------------------
-- Table structure for roles
-- ----------------------------
DROP TABLE IF EXISTS roles;
CREATE TABLE roles (
username varchar(50) NOT NULL,
role varchar(50) NOT NULL
)
;
INSERT INTO roles VALUES ('nacos', 'ROLE_ADMIN');
-- ----------------------------
-- Table structure for tenant_capacity
-- ----------------------------
DROP TABLE IF EXISTS tenant_capacity;
CREATE TABLE tenant_capacity (
id bigserial NOT NULL,
tenant_id varchar(128) NOT NULL,
quota integer NOT NULL,
usage integer NOT NULL,
max_size integer NOT NULL,
max_aggr_count integer NOT NULL,
max_aggr_size integer NOT NULL,
max_history_count integer NOT NULL,
gmt_create timestamp(6) NOT NULL,
gmt_modified timestamp(6) NOT NULL,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN tenant_capacity.id IS '主键ID';
COMMENT ON COLUMN tenant_capacity.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN tenant_capacity.quota IS '配额，0表示使用默认值';
COMMENT ON COLUMN tenant_capacity.usage IS '使用量';
COMMENT ON COLUMN tenant_capacity.max_size IS '单个配置大小上限，单位为字节，0表示使用默认值';
COMMENT ON COLUMN tenant_capacity.max_aggr_count IS '聚合子配置最大个数';
COMMENT ON COLUMN tenant_capacity.max_aggr_size IS '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值';
COMMENT ON COLUMN tenant_capacity.max_history_count IS '最大变更历史数量';
COMMENT ON COLUMN tenant_capacity.gmt_create IS '创建时间';
COMMENT ON COLUMN tenant_capacity.gmt_modified IS '修改时间';
COMMENT ON TABLE tenant_capacity IS '租户容量信息表';
-- ----------------------------
-- Table structure for tenant_info
-- ----------------------------
DROP TABLE IF EXISTS tenant_info;
CREATE TABLE tenant_info (
id bigserial NOT NULL,
kp varchar(128) NOT NULL,
tenant_id varchar(128) ,
tenant_name varchar(128) ,
tenant_desc varchar(256) ,
create_source varchar(32) ,
gmt_create int8 NOT NULL,
gmt_modified int8 NOT NULL,
PRIMARY KEY (id)
)
;
COMMENT ON COLUMN tenant_info.id IS 'id';
COMMENT ON COLUMN tenant_info.kp IS 'kp';
COMMENT ON COLUMN tenant_info.tenant_id IS 'tenant_id';
COMMENT ON COLUMN tenant_info.tenant_name IS 'tenant_name';
COMMENT ON COLUMN tenant_info.tenant_desc IS 'tenant_desc';
COMMENT ON COLUMN tenant_info.create_source IS 'create_source';
COMMENT ON COLUMN tenant_info.gmt_create IS '创建时间';
COMMENT ON COLUMN tenant_info.gmt_modified IS '修改时间';
COMMENT ON TABLE tenant_info IS 'tenant_info';
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
username varchar(50) NOT NULL,
password varchar(500) NOT NULL,
enabled smallint NOT NULL
)
;
INSERT INTO users VALUES ('nacos', '$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu', 1);
-- ----------------------------
-- Indexes structure for table config_info
-- ----------------------------
CREATE UNIQUE INDEX uk_configinfo_datagrouptenant ON config_info (data_id,group_id,tenant_id);
-- ----------------------------
-- Indexes structure for table config_info_aggr
-- ----------------------------
CREATE UNIQUE INDEX uk_configinfoaggr_datagrouptenantdatum ON config_info_aggr (data_id,group_id,tenant_id,datum_id);
-- ----------------------------
-- Indexes structure for table config_info_beta
-- ----------------------------
CREATE UNIQUE INDEX uk_configinfobeta_datagrouptenant ON config_info_beta (data_id,group_id,tenant_id);
-- ----------------------------
-- Indexes structure for table config_info_tag
-- ----------------------------
CREATE UNIQUE INDEX uk_configinfotag_datagrouptenanttag ON config_info_tag (data_id,group_id,tenant_id,tag_id);
-- ----------------------------
-- Indexes structure for table config_tags_relation
-- ----------------------------
CREATE INDEX idx_tenant_id ON config_tags_relation (
tenant_id
);
CREATE UNIQUE INDEX uk_configtagrelation_configidtag ON config_tags_relation (
id,
tag_name,
tag_type
);
-- ----------------------------
-- Indexes structure for table group_capacity
-- ----------------------------
CREATE UNIQUE INDEX uk_group_id ON group_capacity (
group_id
);
-- ----------------------------
-- Indexes structure for table permissions
-- ----------------------------
CREATE UNIQUE INDEX uk_role_permission ON permissions (
role,
resource,
action
);
-- ----------------------------
-- Indexes structure for table roles
-- ----------------------------
CREATE UNIQUE INDEX uk_username_role ON roles (
username,
role
);
-- ----------------------------
-- Indexes structure for table tenant_capacity
-- ----------------------------
CREATE UNIQUE INDEX uk_tenant_id ON tenant_capacity (
tenant_id
);
-- ----------------------------
-- Indexes structure for table tenant_info
-- ----------------------------
CREATE UNIQUE INDEX uk_tenant_info_kptenantid ON tenant_info (
kp,
tenant_id
);
4.编辑南大通用插件适配模块
添加完模块之后，需要添加相应内容，按照Postgresql的格式，添加相应的实现类：
主要修改内容：
在datasource-plugin-ext-base的常量中添加南大通用数据库名称
大部分需要实现的Mapper类的覆盖方法只需要覆盖getDataSource() :
package com.alibaba.nacos.plugin.datasource.impl.gbasedbt;
import com.alibaba.nacos.plugin.datasource.constants.DatabaseTypeConstant;
import com.alibaba.nacos.plugin.datasource.impl.base.BaseConfigInfoAggrMapper;
/**
* The postgresql implementation of ConfigInfoAggrMapper.
*
* @author Long Yu
**/
public class ConfigInfoAggrMapperByGbasedbt extends BaseConfigInfoAggrMapper {
@Override
public String getDataSource() {
return DatabaseTypeConstant.GBASEDBT;
}
}
GbasedbtDatabaseDialect.java的主要内容(分页的语法和postgresql有细微区别，需要修改)：
/*
* Copyright 1999-2022 Alibaba Group Holding Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.nacos.plugin.datasource.dialect;
import com.alibaba.nacos.plugin.datasource.constants.DatabaseTypeConstant;
/**
* PostgreSQL database dialect.
*/
public class GbasedbtDatabaseDialect extends AbstractDatabaseDialect {
@Override
public String getType() {
return DatabaseTypeConstant.GBASEDBT;
}
@Override
public String getLimitTopSqlWithMark(String sql) {
return sql + " LIMIT ? ";
}
@Override
public String getLimitPageSqlWithMark(String sql) {
return sql + " LIMIT ? OFFSET ? ";
}
@Override
public String getLimitPageSql(String sql, int pageNo, int pageSize) {
// return sql + " OFFSET " + getPagePrevNum(pageNo, pageSize) + " LIMIT " + pageSize;
return sql + " LIMIT " + pageSize + " OFFSET " + getPagePrevNum(pageNo, pageSize);
}
@Override
public String getLimitPageSqlWithOffset(String sql, int startOffset, int pageSize){
return sql + " LIMIT " + pageSize + " OFFSET " + startOffset;
}
}
由于南大通用gbasedbt的数据库驱动jar包中包含了HikariCP的代码，导致Hikari的版本冲突，在运行过程中会出现NoSuchFieldError,需要将gbasedbt中的关于HikariCP代码删除掉：
通过JDK自带的jar打包解包工具，
jar -xvf gbasedbt-3.5.0.2.jar
进入com目录中删除zaxxer目录
将解压缩之后的目录拷贝到一个单独的目录 /jar 中
再次使用jar工具进行打包：
jar cvf gbase-3.5.0.3.jar -C jar/ .
将打包之后的jar包上传到maven私服
<dependency>
<groupId>com.gbase.jdbc.Driver</groupId>
<artifactId>gbase</artifactId>
<version>3.5.0.2</version>
</dependency>
项目中主要Mapper实现都基本在base plugin中实现了,只有部分特殊地方需要手动修改
项目产品主要用到nacos中的配置中心导入导出，在线编辑功能，所以主要只是涉及到了config_info， his_config_info表，需要修改两个表对应的Mapper：
ConfigInfoMapperByGbasedbt.java (这里有部分接口需要覆盖，不然分页查询会出错,这是之前发现的错误，由于后续发现的hikari冲突解决了，不知道这里还有没有问题，保险起见还是直接修改,这里也是由于nacos很多地方直接使用异常来做逻辑分支判断，导致底层特殊的异常上层没办法处理)
package com.alibaba.nacos.plugin.datasource.impl.gbasedbt;
import com.alibaba.nacos.common.utils.StringUtils;
import com.alibaba.nacos.plugin.datasource.constants.DatabaseTypeConstant;
import com.alibaba.nacos.plugin.datasource.impl.base.BaseConfigInfoMapper;
import java.util.List;
import java.util.Map;
public class ConfigInfoMapperByGbasedbt extends BaseConfigInfoMapper {
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 getDataSource() {
return DatabaseTypeConstant.GBASEDBT;
}
@Override
public String select(List<String> columns, List<String> where) {
StringBuilder sql = new StringBuilder();
String method = "SELECT * ";
sql.append(method);
sql.append("FROM ");
sql.append(getTableName());
sql.append(" ");
if (where.size() == 0) {
return sql.toString();
}
sql.append("WHERE ");
for (int i = 0; i < where.size(); i++) {
sql.append(where.get(i)).append(" = ").append("?");
if (i != where.size() - 1) {
sql.append(" AND ");
}
}
return sql.toString();
}
@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 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 getLimitPageSqlWithOffset(sqlFetchRows + where, startRow, pageSize);
}
@Override
public String findConfigInfosByIds(int idSize) {
StringBuilder sql = new StringBuilder(
"SELECT * FROM config_info WHERE ");
sql.append("id IN (");
for (int i = 0; i < idSize; i++) {
if (i != 0) {
sql.append(", ");
}
sql.append('?');
}
sql.append(") ");
return sql.toString();
}
}
HistoryConfigInfoMapperByGbasedbt.java
/*
* Copyright 1999-2022 Alibaba Group Holding Ltd.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.alibaba.nacos.plugin.datasource.impl.gbasedbt;
import com.alibaba.nacos.plugin.datasource.constants.DatabaseTypeConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.HistoryConfigInfoMapperByMySql;
import com.alibaba.nacos.plugin.datasource.util.DateUtil;
import java.util.List;
/**
* The postgresql implementation of HistoryConfigInfoMapper.
*
* @author Long Yu
**/
public class HistoryConfigInfoMapperByGbasedbt extends HistoryConfigInfoMapperByMySql {
@Override
public String removeConfigHistory() {
String sql = "DELETE FROM his_config_info WHERE gmt_modified < ? and 0 < ? ";
return sql;
}
@Override
public String getDataSource() {
return DatabaseTypeConstant.GBASEDBT;
}
@Override
public String insert(List<String> columns) {
StringBuilder sql = new StringBuilder();
String method = "INSERT INTO ";
sql.append(method);
sql.append(getTableName());
int size = columns.size();
String gmtCreateName = "gmt_create";
boolean gmtCreateExist = false;
sql.append("(");
for (int i = 0; i < size; i++) {
sql.append(columns.get(i));
if (gmtCreateName.equalsIgnoreCase(columns.get(i))) {
gmtCreateExist = true;
}
if (i != columns.size() - 1) {
sql.append(", ");
}
}
if (!gmtCreateExist) {
sql.append(", " + gmtCreateName);
}
sql.append(") ");
sql.append("VALUES");
sql.append("(");
for (int i = 0; i < size; i++) {
sql.append("?");
if (i != columns.size() - 1) {
sql.append(",");
}
}
if (!gmtCreateExist) {
sql.append(", '" + DateUtil.getCurrentTimeStr() + "'");
}
sql.append(")");
return sql.toString();
}
}
5.编辑南大通用插件SPI配置
nacos 插件需要使用spi加载相关的实现类，插件中需要指定对应要加载的类
com.alibaba.nacos.plugin.datasource.mapper.Mapper ：
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.ConfigInfoAggrMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.ConfigInfoBetaMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.ConfigInfoMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.ConfigInfoTagMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.ConfigTagsRelationMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.HistoryConfigInfoMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.TenantInfoMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.TenantCapacityMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.impl.gbasedbt.GroupCapacityMapperByGbasedbt
com.alibaba.nacos.plugin.datasource.dialect.DatabaseDialect ：
com.alibaba.nacos.plugin.datasource.dialect.GbasedbtDatabaseDialect
6.编辑南大通用插件 sql-error-codes.xml
南大通用数据库返回的部分SQLException异常错误码和正常的错误码和其他数据库不一致，最影响nacos配置功能的是唯一键冲突对应的错误码，由于nacos代码中很多持久化修改中使用DuplicatekeyException做逻辑分支判断，需要将底层对应的唯一键约束异常转换为DuplicateKeyException,默认Spring jdbc 的错误码映射中没有南大通用数据库相关错误码映射，需要手动添加修改：
内容如下：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "https://www.springframework.org/dtd/spring-beans-2.0.dtd">
<!--
- Default SQL error codes for well-known databases.
- Can be overridden by definitions in a "sql-error-codes.xml" file
- in the root of the class path.
-
- If the Database Product Name contains characters that are invalid
- to use in the id attribute (like a space) then we need to add a property
- named "databaseProductName"/"databaseProductNames" that holds this value.
- If this property is present, then it will be used instead of the id for
- looking up the error codes based on the current database.
-->
<beans>
<bean id="DB2" name="Db2" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName">
<value>DB2*</value>
</property>
<property name="badSqlGrammarCodes">
<value>-007,-029,-097,-104,-109,-115,-128,-199,-204,-206,-301,-408,-441,-491</value>
</property>
<property name="duplicateKeyCodes">
<value>-803</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>-407,-530,-531,-532,-543,-544,-545,-603,-667</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>-904,-971</value>
</property>
<property name="transientDataAccessResourceCodes">
<value>-1035,-1218,-30080,-30081</value>
</property>
<property name="deadlockLoserCodes">
<value>-911,-913</value>
</property>
</bean>
<bean id="Derby" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName">
<value>Apache Derby</value>
</property>
<property name="useSqlStateForTranslation">
<value>true</value>
</property>
<property name="badSqlGrammarCodes">
<value>42802,42821,42X01,42X02,42X03,42X04,42X05,42X06,42X07,42X08</value>
</property>
<property name="duplicateKeyCodes">
<value>23505</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>22001,22005,23502,23503,23513,X0Y32</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>04501,08004,42Y07</value>
</property>
<property name="cannotAcquireLockCodes">
<value>40XL1</value>
</property>
<property name="deadlockLoserCodes">
<value>40001</value>
</property>
</bean>
<bean id="H2" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="badSqlGrammarCodes">
<value>42000,42001,42101,42102,42111,42112,42121,42122,42132</value>
</property>
<property name="duplicateKeyCodes">
<value>23001,23505</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>22001,22003,22012,22018,22025,23000,23002,23003,23502,23503,23506,23507,23513</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>90046,90100,90117,90121,90126</value>
</property>
<property name="cannotAcquireLockCodes">
<value>50200</value>
</property>
</bean>
<!-- https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/a78d3275191014b41bae7c4a46d835/content.htm -->
<bean id="HDB" name="Hana" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductNames">
<list>
<value>SAP HANA</value>
<value>SAP DB</value>
</list>
</property>
<property name="badSqlGrammarCodes">
<value>
257,259,260,261,262,263,264,267,268,269,270,271,272,273,275,276,277,278,
278,279,280,281,282,283,284,285,286,288,289,290,294,295,296,297,299,308,309,
313,315,316,318,319,320,321,322,323,324,328,329,330,333,335,336,337,338,340,
343,350,351,352,362,368
</value>
</property>
<property name="permissionDeniedCodes">
<value>10,258</value>
</property>
<property name="duplicateKeyCodes">
<value>301</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>461,462</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>-813,-709,-708,1024,1025,1026,1027,1029,1030,1031</value>
</property>
<property name="invalidResultSetAccessCodes">
<value>-11210,582,587,588,594</value>
</property>
<property name="cannotAcquireLockCodes">
<value>131</value>
</property>
<property name="cannotSerializeTransactionCodes">
<value>138,143</value>
</property>
<property name="deadlockLoserCodes">
<value>133</value>
</property>
</bean>
<bean id="HSQL" name="Hsql" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName">
<value>HSQL Database Engine</value>
</property>
<property name="badSqlGrammarCodes">
<value>-22,-28</value>
</property>
<property name="duplicateKeyCodes">
<value>-104</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>-9</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>-80</value>
</property>
</bean>
<bean id="Informix" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName">
<value>Informix Dynamic Server</value>
</property>
<property name="badSqlGrammarCodes">
<value>-201,-217,-696</value>
</property>
<property name="duplicateKeyCodes">
<value>-239,-268,-6017</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>-692,-11030</value>
</property>
</bean>
<bean id="MS-SQL" name="SqlServer" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName">
<value>Microsoft SQL Server</value>
</property>
<property name="badSqlGrammarCodes">
<value>156,170,207,208,209</value>
</property>
<property name="permissionDeniedCodes">
<value>229</value>
</property>
<property name="duplicateKeyCodes">
<value>2601,2627</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>544,8114,8115</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>4060</value>
</property>
<property name="cannotAcquireLockCodes">
<value>1222</value>
</property>
<property name="deadlockLoserCodes">
<value>1205</value>
</property>
</bean>
<bean id="MySQL" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductNames">
<list>
<value>MySQL</value>
<value>MariaDB</value>
</list>
</property>
<property name="badSqlGrammarCodes">
<value>1054,1064,1146</value>
</property>
<property name="duplicateKeyCodes">
<value>1062</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>630,839,840,893,1169,1215,1216,1217,1364,1451,1452,1557</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>1</value>
</property>
<property name="cannotAcquireLockCodes">
<value>1205,3572</value>
</property>
<property name="deadlockLoserCodes">
<value>1213</value>
</property>
</bean>
<bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="badSqlGrammarCodes">
<value>900,903,904,917,936,942,17006,6550</value>
</property>
<property name="invalidResultSetAccessCodes">
<value>17003</value>
</property>
<property name="duplicateKeyCodes">
<value>1</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>1400,1722,2291,2292</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>17002,17447</value>
</property>
<property name="cannotAcquireLockCodes">
<value>54,30006</value>
</property>
<property name="cannotSerializeTransactionCodes">
<value>8177</value>
</property>
<property name="deadlockLoserCodes">
<value>60</value>
</property>
</bean>
<bean id="PostgreSQL" name="Postgres" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="useSqlStateForTranslation">
<value>true</value>
</property>
<property name="badSqlGrammarCodes">
<value>03000,42000,42601,42602,42622,42804,42P01</value>
</property>
<property name="duplicateKeyCodes">
<value>21000,23505</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>23000,23502,23503,23514</value>
</property>
<property name="dataAccessResourceFailureCodes">
<value>53000,53100,53200,53300</value>
</property>
<property name="cannotAcquireLockCodes">
<value>55P03</value>
</property>
<property name="cannotSerializeTransactionCodes">
<value>40001</value>
</property>
<property name="deadlockLoserCodes">
<value>40P01</value>
</property>
</bean>
<bean id="Sybase" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductNames">
<list>
<value>Sybase SQL Server</value>
<value>Adaptive Server Enterprise</value>
<value>ASE</value> <!-- name as returned by jTDS driver -->
<value>SQL Server</value>
<value>sql server</value> <!-- name as returned by jTDS driver -->
</list>
</property>
<property name="badSqlGrammarCodes">
<value>101,102,103,104,105,106,107,108,109,110,111,112,113,116,120,121,123,207,208,213,257,512</value>
</property>
<property name="duplicateKeyCodes">
<value>2601,2615,2626</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>233,511,515,530,546,547,2615,2714</value>
</property>
<property name="transientDataAccessResourceCodes">
<value>921,1105</value>
</property>
<property name="cannotAcquireLockCodes">
<value>12205</value>
</property>
<property name="deadlockLoserCodes">
<value>1205</value>
</property>
</bean>
<bean id="GBase" name="GBase8s" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductNames">
<list>
<value>GBase 8s Server</value>
<value>GBase 8v Server</value>
</list>
</property>
<property name="useSqlStateForTranslation">
<value>true</value>
</property>
<property name="duplicateKeyCodes">
<value>23000</value>
</property>
</bean>
</beans>
6.插件打包并部署使用
打包之后将其放到对应的目录下：
修改其中的配置文件：
修改其中数据库配置：
启动
页面功能查看测试基本正常
数据库是今天社会发展不可缺少的重要技术，它可以把大量的信息进行有序的存储和管理，为企业的数据处理提供了强大的保障。
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)