nacos2.4.3从mysql迁移到postgresql
背景：项目从mysql改为postgresql数据库，nacos直接修改配置文件application.properties连接失败。
解决方式：下载nacos源码，修改源码支持postgresql，再部署运行。
参考
一、下载源码
1、从Gitee上下载：
2、修改源码
（1）pom文件添加依赖，三个pom文件都需要改(nacos-all、nacos-persistence、nacos-config-plugin)：
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.8</version>
</dependency>
（2）在nacos-plugin模块中仿照mysql修改代码：
①在com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant添加代码：
public static final String POSTGRESQL = "postgresql";
②com.alibaba.nacos.plugin.datasource.enums中添加软件包postgresql和类TrustedPostgresqlFunctionEnum
/*
* Copyright 1999-2018 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.enums.postgresql;
import java.util.HashMap;
import java.util.Map;
/**
* The TrustedSqlFunctionEnum enum class is used to enumerate and manage a list of trusted built-in SQL functions.
* By using this enum, you can verify whether a given SQL function is part of the trusted functions list
* to avoid potential SQL injection risks.
*
* @author blake.qiu
*/
public enum TrustedPostgresqlFunctionEnum {
/**
* NOW().
*/
NOW("NOW()", "NOW()");
private static final Map<String, TrustedPostgresqlFunctionEnum> LOOKUP_MAP = new HashMap<>();
static {
for (TrustedPostgresqlFunctionEnum entry : TrustedPostgresqlFunctionEnum.values()) {
LOOKUP_MAP.put(entry.functionName, entry);
}
}
private final String functionName;
private final String function;
TrustedPostgresqlFunctionEnum(String functionName, String function) {
this.functionName = functionName;
this.function = function;
}
/**
* Get the function name.
*
* @param functionName function name
* @return function
*/
public static String getFunctionByName(String functionName) {
TrustedPostgresqlFunctionEnum entry = LOOKUP_MAP.get(functionName);
if (entry != null) {
return entry.function;
}
throw new IllegalArgumentException(String.format("Invalid function name: %s", functionName));
}
}
③在com.alibaba.nacos.plugin.datasource.impl中添加软件包和类。
AbstractMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.plugin.datasource.enums.postgresql.TrustedPostgresqlFunctionEnum;
import com.alibaba.nacos.plugin.datasource.mapper.AbstractMapper;
/**
* The abstract mysql mapper contains CRUD methods.
*
* @author blake.qiu
**/
public abstract class AbstractMapperByPostgresql extends AbstractMapper {
@Override
public String getFunction(String functionName) {
return TrustedPostgresqlFunctionEnum.getFunctionByName(functionName);
}
}
ConfigInfoAggrMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigInfoAggrMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
import java.util.List;
/**
* The Postgresql implementation of ConfigInfoAggrMapper.
*
* @author hyx
**/
public class ConfigInfoAggrMapperByPostgresql extends AbstractMapperByPostgresql implements ConfigInfoAggrMapper {
@Override
public MapperResult findConfigInfoAggrByPageFetchRows(MapperContext context) {
int startRow = context.getStartRow();
int pageSize = context.getPageSize();
String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
String groupId = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
String tenantId = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
String sql =
"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 + " LIMIT " + pageSize;
List<Object> paramList = CollectionUtils.list(dataId, groupId, tenantId);
return new MapperResult(sql, paramList);
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
ConfigInfoBetaMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigInfoBetaMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
import java.util.ArrayList;
import java.util.List;
/**
* The Postgresql implementation of ConfigInfoBetaMapper.
*
* @author hyx
**/
public class ConfigInfoBetaMapperByPostgresql extends AbstractMapperByPostgresql implements ConfigInfoBetaMapper {
@Override
public MapperResult findAllConfigInfoBetaForDumpAllFetchRows(MapperContext context) {
int startRow = context.getStartRow();
int pageSize = context.getPageSize();
String sql = " 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 + " LIMIT " + pageSize + " )"
+ " g, config_info_beta t WHERE g.id = t.id ";
List<Object> paramList = new ArrayList<>();
paramList.add(startRow);
paramList.add(pageSize);
return new MapperResult(sql, paramList);
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
ConfigInfoMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.ArrayUtils;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.common.utils.NamespaceUtil;
import com.alibaba.nacos.common.utils.StringUtils;
import com.alibaba.nacos.plugin.datasource.constants.ContextConstant;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigInfoMapper;
import com.alibaba.nacos.plugin.datasource.mapper.ext.WhereBuilder;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* The Postgresql implementation of ConfigInfoMapper.
*
* @author hyx
**/
public class ConfigInfoMapperByPostgresql extends AbstractMapperByPostgresql implements ConfigInfoMapper {
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 MapperResult findConfigInfoByAppFetchRows(MapperContext context) {
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String tenantId = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
String sql = "SELECT id,data_id,group_id,tenant_id,app_name,content FROM config_info"
+ " WHERE tenant_id LIKE ? AND app_name= ?" + " OFFSET " + context.getStartRow() + " LIMIT "
+ context.getPageSize();
return new MapperResult(sql, CollectionUtils.list(tenantId, appName));
}
@Override
public MapperResult getTenantIdList(MapperContext context) {
String sql = "SELECT tenant_id FROM config_info WHERE tenant_id != '" + NamespaceUtil.getNamespaceDefaultId()
+ "' GROUP BY tenant_id OFFSET " + context.getStartRow() + " LIMIT " + context.getPageSize();
return new MapperResult(sql, Collections.emptyList());
}
@Override
public MapperResult getGroupIdList(MapperContext context) {
String sql = "SELECT group_id FROM config_info WHERE tenant_id ='" + NamespaceUtil.getNamespaceDefaultId()
+ "' GROUP BY group_id OFFSET " + context.getStartRow() + " LIMIT " + context.getPageSize();
return new MapperResult(sql, Collections.emptyList());
}
@Override
public MapperResult findAllConfigKey(MapperContext context) {
String sql = " SELECT data_id,group_id,app_name FROM ( "
+ " SELECT id FROM config_info WHERE tenant_id LIKE ? ORDER BY id OFFSET " + context.getStartRow() + " LIMIT "
+ context.getPageSize() + " )" + " g, config_info t WHERE g.id = t.id ";
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.TENANT_ID)));
}
@Override
public MapperResult findAllConfigInfoBaseFetchRows(MapperContext context) {
String sql =
"SELECT t.id,data_id,group_id,content,md5" + " FROM ( SELECT id FROM config_info ORDER BY id OFFSET "
+ context.getStartRow() + " LIMIT " + context.getPageSize() + " )"
+ " g, config_info t WHERE g.id = t.id ";
return new MapperResult(sql, Collections.emptyList());
}
@Override
public MapperResult findAllConfigInfoFragment(MapperContext context) {
String contextParameter = context.getContextParameter(ContextConstant.NEED_CONTENT);
boolean needContent = contextParameter != null && Boolean.parseBoolean(contextParameter);
String sql = "SELECT id,data_id,group_id,tenant_id,app_name," + (needContent ? "content," : "")
+ "md5,gmt_modified,type,encrypted_data_key FROM config_info WHERE id > ? ORDER BY id ASC OFFSET "
+ context.getStartRow() + " LIMIT " + context.getPageSize();
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.ID)));
}
@Override
public MapperResult findChangeConfigFetchRows(MapperContext context) {
final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String tenantTmp = StringUtils.isBlank(tenant) ? StringUtils.EMPTY : tenant;
final Timestamp startTime = (Timestamp) context.getWhereParameter(FieldConstant.START_TIME);
final Timestamp endTime = (Timestamp) context.getWhereParameter(FieldConstant.END_TIME);
List<Object> paramList = new ArrayList<>();
final String sqlFetchRows = "SELECT id,data_id,group_id,tenant_id,app_name,type,md5,gmt_modified FROM config_info WHERE ";
String where = " 1=1 ";
if (!StringUtils.isBlank(dataId)) {
where += " AND data_id LIKE ? ";
paramList.add(dataId);
}
if (!StringUtils.isBlank(group)) {
where += " AND group_id LIKE ? ";
paramList.add(group);
}
if (!StringUtils.isBlank(tenantTmp)) {
where += " AND tenant_id = ? ";
paramList.add(tenantTmp);
}
if (!StringUtils.isBlank(appName)) {
where += " AND app_name = ? ";
paramList.add(appName);
}
if (startTime != null) {
where += " AND gmt_modified >=? ";
paramList.add(startTime);
}
if (endTime != null) {
where += " AND gmt_modified <=? ";
paramList.add(endTime);
}
return new MapperResult(
sqlFetchRows + where + " AND id > " + context.getWhereParameter(FieldConstant.LAST_MAX_ID)
+ " ORDER BY id ASC" + " OFFSET " + 0 + " LIMIT " + context.getPageSize(), paramList);
}
@Override
public MapperResult listGroupKeyMd5ByPageFetchRows(MapperContext context) {
String sql = "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 " + context.getStartRow() + " LIMIT "
+ context.getPageSize() + " ) g, config_info t WHERE g.id = t.id";
return new MapperResult(sql, Collections.emptyList());
}
@Override
public MapperResult findConfigInfoBaseLikeFetchRows(MapperContext context) {
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
final String sqlFetchRows = "SELECT id,data_id,group_id,tenant_id,content FROM config_info WHERE ";
String where = " 1=1 AND tenant_id='" + NamespaceUtil.getNamespaceDefaultId() + "' ";
List<Object> paramList = new ArrayList<>();
if (!StringUtils.isBlank(dataId)) {
where += " AND data_id LIKE ? ";
paramList.add(dataId);
}
if (!StringUtils.isBlank(group)) {
where += " AND group_id LIKE ";
paramList.add(group);
}
if (!StringUtils.isBlank(content)) {
where += " AND content LIKE ? ";
paramList.add(content);
}
return new MapperResult(sqlFetchRows + where + " OFFSET " + context.getStartRow() + " LIMIT " + context.getPageSize(),
paramList);
}
@Override
public MapperResult findConfigInfo4PageFetchRows(MapperContext context) {
final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
List<Object> paramList = new ArrayList<>();
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=? ");
paramList.add(tenant);
if (StringUtils.isNotBlank(dataId)) {
where.append(" AND data_id=? ");
paramList.add(dataId);
}
if (StringUtils.isNotBlank(group)) {
where.append(" AND group_id=? ");
paramList.add(group);
}
if (StringUtils.isNotBlank(appName)) {
where.append(" AND app_name=? ");
paramList.add(appName);
}
if (!StringUtils.isBlank(content)) {
where.append(" AND content LIKE ? ");
paramList.add(content);
}
return new MapperResult(sql + where + " OFFSET " + context.getStartRow() + " LIMIT " + context.getPageSize(),
paramList);
}
@Override
public MapperResult findConfigInfoBaseByGroupFetchRows(MapperContext context) {
String sql = "SELECT id,data_id,group_id,content FROM config_info WHERE group_id=? AND tenant_id=?" + " OFFSET "
+ context.getStartRow() + " LIMIT " + context.getPageSize();
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.GROUP_ID),
context.getWhereParameter(FieldConstant.TENANT_ID)));
}
@Override
public MapperResult findConfigInfoLike4PageFetchRows(MapperContext context) {
final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
final String[] types = (String[]) context.getWhereParameter(FieldConstant.TYPE);
WhereBuilder where = new WhereBuilder(
"SELECT id,data_id,group_id,tenant_id,app_name,content,encrypted_data_key,type FROM config_info");
where.like("tenant_id", tenant);
if (StringUtils.isNotBlank(dataId)) {
where.and().like("data_id", dataId);
}
if (StringUtils.isNotBlank(group)) {
where.and().like("group_id", group);
}
if (StringUtils.isNotBlank(appName)) {
where.and().eq("app_name", appName);
}
if (StringUtils.isNotBlank(content)) {
where.and().like("content", content);
}
if (!ArrayUtils.isEmpty(types)) {
where.in("type", types);
}
where.limit(context.getStartRow(), context.getPageSize());
return where.build();
}
@Override
public MapperResult findAllConfigInfoFetchRows(MapperContext context) {
String sql = "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 ? LIMIT ? )"
+ " g, config_info t WHERE g.id = t.id ";
return new MapperResult(sql,
CollectionUtils.list(context.getWhereParameter(FieldConstant.TENANT_ID), context.getStartRow(),
context.getPageSize()));
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
ConfigInfoTagMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigInfoTagMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
import java.util.Collections;
/**
* The Postgresql implementation of ConfigInfoTagMapper.
*
* @author hyx
**/
public class ConfigInfoTagMapperByPostgresql extends AbstractMapperByPostgresql implements ConfigInfoTagMapper {
@Override
public MapperResult findAllConfigInfoTagForDumpAllFetchRows(MapperContext context) {
String sql = " 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 " + context.getStartRow() + " LIMIT "
+ context.getPageSize() + " ) " + "g, config_info_tag t WHERE g.id = t.id ";
return new MapperResult(sql, Collections.emptyList());
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
ConfigTagsRelationMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.ArrayUtils;
import com.alibaba.nacos.common.utils.StringUtils;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.ConfigTagsRelationMapper;
import com.alibaba.nacos.plugin.datasource.mapper.ext.WhereBuilder;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
import java.util.ArrayList;
import java.util.List;
/**
* The Postgresql implementation of ConfigTagsRelationMapper.
*
* @author hyx
**/
public class ConfigTagsRelationMapperByPostgresql extends AbstractMapperByPostgresql implements ConfigTagsRelationMapper {
@Override
public MapperResult findConfigInfo4PageFetchRows(MapperContext context) {
final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
final String[] tagArr = (String[]) context.getWhereParameter(FieldConstant.TAG_ARR);
List<Object> paramList = new ArrayList<>();
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=? ");
paramList.add(tenant);
if (StringUtils.isNotBlank(dataId)) {
where.append(" AND a.data_id=? ");
paramList.add(dataId);
}
if (StringUtils.isNotBlank(group)) {
where.append(" AND a.group_id=? ");
paramList.add(group);
}
if (StringUtils.isNotBlank(appName)) {
where.append(" AND a.app_name=? ");
paramList.add(appName);
}
if (!StringUtils.isBlank(content)) {
where.append(" AND a.content LIKE ? ");
paramList.add(content);
}
where.append(" AND b.tag_name IN (");
for (int i = 0; i < tagArr.length; i++) {
if (i != 0) {
where.append(", ");
}
where.append('?');
paramList.add(tagArr[i]);
}
where.append(") ");
return new MapperResult(sql + where + " OFFSET " + context.getStartRow() + " LIMIT " + context.getPageSize(),
paramList);
}
@Override
public MapperResult findConfigInfoLike4PageFetchRows(MapperContext context) {
final String tenant = (String) context.getWhereParameter(FieldConstant.TENANT_ID);
final String dataId = (String) context.getWhereParameter(FieldConstant.DATA_ID);
final String group = (String) context.getWhereParameter(FieldConstant.GROUP_ID);
final String appName = (String) context.getWhereParameter(FieldConstant.APP_NAME);
final String content = (String) context.getWhereParameter(FieldConstant.CONTENT);
final String[] tagArr = (String[]) context.getWhereParameter(FieldConstant.TAG_ARR);
final String[] types = (String[]) context.getWhereParameter(FieldConstant.TYPE);
WhereBuilder where = new WhereBuilder(
"SELECT a.id,a.data_id,a.group_id,a.tenant_id,a.app_name,a.content,a.type "
+ "FROM config_info a LEFT JOIN config_tags_relation b ON a.id=b.id");
where.like("a.tenant_id", tenant);
if (StringUtils.isNotBlank(dataId)) {
where.and().like("a.data_id", dataId);
}
if (StringUtils.isNotBlank(group)) {
where.and().like("a.group_id", group);
}
if (StringUtils.isNotBlank(appName)) {
where.and().eq("a.app_name", appName);
}
if (StringUtils.isNotBlank(content)) {
where.and().like("a.content", content);
}
if (!ArrayUtils.isEmpty(tagArr)) {
where.and().in("b.tag_name", tagArr);
}
if (!ArrayUtils.isEmpty(types)) {
where.and().in("a.type", types);
}
where.limit(context.getStartRow(), context.getPageSize());
return where.build();
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
GroupCapacityMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.GroupCapacityMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
/**
* The Postgresql implementation of {@link GroupCapacityMapper}.
*
* @author lixiaoshuang
*/
public class GroupCapacityMapperByPostgresql extends AbstractMapperByPostgresql implements GroupCapacityMapper {
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
@Override
public MapperResult selectGroupInfoBySize(MapperContext context) {
String sql = "SELECT id, group_id FROM group_capacity WHERE id > ? LIMIT ?";
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.ID), context.getPageSize()));
}
}
HistoryConfigInfoMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.HistoryConfigInfoMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
/**
* The Postgresql implementation of HistoryConfigInfoMapper.
*
* @author hyx
**/
public class HistoryConfigInfoMapperByPostgresql extends AbstractMapperByPostgresql implements HistoryConfigInfoMapper {
@Override
public MapperResult removeConfigHistory(MapperContext context) {
String sql = "DELETE FROM his_config_info WHERE gmt_modified < ? LIMIT ?";
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.START_TIME),
context.getWhereParameter(FieldConstant.LIMIT_SIZE)));
}
@Override
public MapperResult pageFindConfigHistoryFetchRows(MapperContext context) {
String sql =
"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 "
+ context.getStartRow() + " LIMIT " + context.getPageSize();
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.DATA_ID),
context.getWhereParameter(FieldConstant.GROUP_ID), context.getWhereParameter(FieldConstant.TENANT_ID)));
}
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
TenantCapacityMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.constants.FieldConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.TenantCapacityMapper;
import com.alibaba.nacos.plugin.datasource.model.MapperContext;
import com.alibaba.nacos.plugin.datasource.model.MapperResult;
/**
* The Postgresql implementation of TenantCapacityMapper.
*
* @author hyx
**/
public class TenantCapacityMapperByPostgresql extends AbstractMapperByPostgresql implements TenantCapacityMapper {
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
@Override
public MapperResult getCapacityList4CorrectUsage(MapperContext context) {
String sql = "SELECT id, tenant_id FROM tenant_capacity WHERE id>? LIMIT ?";
return new MapperResult(sql, CollectionUtils.list(context.getWhereParameter(FieldConstant.ID),
context.getWhereParameter(FieldConstant.LIMIT_SIZE)));
}
}
TenantInfoMapperByPostgresql类：
/*
* 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.postgresql;
import com.alibaba.nacos.plugin.datasource.constants.DataSourceConstant;
import com.alibaba.nacos.plugin.datasource.impl.mysql.AbstractMapperByMysql;
import com.alibaba.nacos.plugin.datasource.mapper.TenantInfoMapper;
/**
* The Postgresql implementation of TenantInfoMapper.
*
* @author hyx
**/
public class TenantInfoMapperByPostgresql extends AbstractMapperByPostgresql implements TenantInfoMapper {
@Override
public String getDataSource() {
return DataSourceConstant.POSTGRESQL;
}
}
④在resource文件夹下的com.alibaba.nacos.plugin.datasource.mapper.Mapper添加语句
com.alibaba.nacos.plugin.datasource.impl.postgresql.ConfigInfoAggrMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.ConfigInfoBetaMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.ConfigInfoMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.ConfigInfoTagMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.ConfigTagsRelationMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.HistoryConfigInfoMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.TenantInfoMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.TenantCapacityMapperByPostgresql
com.alibaba.nacos.plugin.datasource.impl.postgresql.GroupCapacityMapperByPostgresql
（3）在nacos-persistence模块
①com.alibaba.nacos.persistence.constants中修改PersistenceConstant类
public static final String POSTGRESQL = "postgresql";
②com.alibaba.nacos.persistence.datasource中修改ExternalDataSourceProperties类
/*
* Copyright 1999-2023 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.persistence.datasource;
import com.alibaba.nacos.common.utils.CollectionUtils;
import com.alibaba.nacos.common.utils.Preconditions;
import com.alibaba.nacos.common.utils.StringUtils;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.boot.context.properties.bind.Bindable;
import org.springframework.boot.context.properties.bind.Binder;
import org.springframework.core.env.Environment;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import static com.alibaba.nacos.common.utils.CollectionUtils.getOrDefault;
/**
* Properties of external DataSource.
*
* @author Nacos
*/
public class ExternalDataSourceProperties {
private static final String JDBC_DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
private static final String TEST_QUERY = "SELECT 1";
private Integer num;
private List<String> url = new ArrayList<>();
private List<String> user = new ArrayList<>();
private List<String> password = new ArrayList<>();
private String jdbcDriverName;
public void setJdbcDriverName(String jdbcDriverName) {
if (StringUtils.isBlank(jdbcDriverName)) {
this.jdbcDriverName = JDBC_DRIVER_NAME;
} else {
this.jdbcDriverName = jdbcDriverName;
}
}
public void setNum(Integer num) {
this.num = num;
}
public void setUrl(List<String> url) {
this.url = url;
}
public void setUser(List<String> user) {
this.user = user;
}
public void setPassword(List<String> password) {
this.password = password;
}
/**
* Build serveral HikariDataSource.
*
* @param environment {@link Environment}
* @param callback Callback function when constructing data source
* @return List of {@link HikariDataSource}
*/
List<HikariDataSource> build(Environment environment, Callback<HikariDataSource> callback) {
List<HikariDataSource> dataSources = new ArrayList<>();
Binder.get(environment).bind("db", Bindable.ofInstance(this));
Preconditions.checkArgument(Objects.nonNull(num), "db.num is null");
Preconditions.checkArgument(CollectionUtils.isNotEmpty(user), "db.user or db.user.[index] is null");
Preconditions.checkArgument(CollectionUtils.isNotEmpty(password), "db.password or db.password.[index] is null");
for (int index = 0; index < num; index++) {
int currentSize = index + 1;
Preconditions.checkArgument(url.size() >= currentSize, "db.url.%s is null", index);
DataSourcePoolProperties poolProperties = DataSourcePoolProperties.build(environment);
if (StringUtils.isEmpty(poolProperties.getDataSource().getDriverClassName())) {
// poolProperties.setDriverClassName(JDBC_DRIVER_NAME);
poolProperties.setDriverClassName(jdbcDriverName);
}
poolProperties.setJdbcUrl(url.get(index).trim());
poolProperties.setUsername(getOrDefault(user, index, user.get(0)).trim());
poolProperties.setPassword(getOrDefault(password, index, password.get(0)).trim());
HikariDataSource ds = poolProperties.getDataSource();
if (StringUtils.isEmpty(ds.getConnectionTestQuery())) {
ds.setConnectionTestQuery(TEST_QUERY);
}
dataSources.add(ds);
callback.accept(ds);
}
Preconditions.checkArgument(CollectionUtils.isNotEmpty(dataSources), "no datasource available");
return dataSources;
}
interface Callback<D> {
/**
* Perform custom logic.
*
* @param datasource dataSource.
*/
void accept(D datasource);
}
}
（4）在nacos-default-auth-plugin模块，
①修改com.alibaba.nacos.plugin.auth.impl.constant中的AuthPageConstant类：
public static final String OFFSET_LIMIT = "OFFSET ? LIMIT ?";
②在com.alibaba.nacos.plugin.auth.impl.persistence.handler.support中添加类PostgresqlPageHandlerAdapter：
package com.alibaba.nacos.plugin.auth.impl.persistence.handler.support;
import com.alibaba.nacos.persistence.constants.PersistenceConstant;
import com.alibaba.nacos.plugin.auth.impl.constant.AuthPageConstant;
import com.alibaba.nacos.plugin.auth.impl.model.OffsetFetchResult;
import com.alibaba.nacos.plugin.auth.impl.persistence.handler.PageHandlerAdapter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* @author w
*/
public class PostgresqlPageHandlerAdapter implements PageHandlerAdapter {
@Override
public boolean supports(String dataSourceType) {
return PersistenceConstant.POSTGRESQL.equals(dataSourceType);
}
@Override
public OffsetFetchResult addOffsetAndFetchNext(String fetchSql, Object[] arg, int pageNo, int pageSize) {
if (!fetchSql.contains(AuthPageConstant.LIMIT)) {
fetchSql += " " + AuthPageConstant.OFFSET_LIMIT;
List<Object> newArgsList = new ArrayList<>(Arrays.asList(arg));
newArgsList.add((pageNo - 1) * pageSize);
newArgsList.add(pageSize);
Object[] newArgs = newArgsList.toArray(new Object[newArgsList.size()]);
return new OffsetFetchResult(fetchSql, newArgs);
}
return new OffsetFetchResult(fetchSql, arg);
}
}
③修改com.alibaba.nacos.plugin.auth.impl.persistence.handler中的类PageHandlerAdapterFactory：
addHandlerAdapter.accept(new PostgresqlPageHandlerAdapter());
3、打包
（1）设置中配置终端Shell路径：
（2）在终端执行：
mvn -Prelease-nacos clean install -Dmaven.test.skip=true -Dcheckstyle.skip=true -Dpmd.skip=true -Drat.skip=true -U
（3）生成的文件在Nacos-2.4.3\distribution\target文件夹
4、运行
（1）修改配置文件
spring.datasource.platform=postgresql
db.num=1
db.url.0=jdbc:postgresql://{{your_ip}}:5432/{{your_database_name}}?characterEncoding=utf8&connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=UTC
db.user.0={{your_user}}
db.password.0={{your_password}}
db.pool.config.driver-class-name=org.postgresql.Driver
（2）在bin文件夹执行语句
startup.cmd -m standalone
（3）访问网页
http://localhost:8848/nacos/index.html
扫一扫
2556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
