资源准备：
下载开源项目
新建oracle表：
-- ---------------------------- -- Table structure for OcelotGlobalConfiguration -- ----------------------------CREATE TABLE OcelotGlobalConfiguration (Id NUMBER(11) NOT NULL ,GatewayName NVARCHAR2(200) NOT NULL ,RequestIdKey NVARCHAR2(100) ,BaseUrl NVARCHAR2(100) ,DownstreamScheme NVARCHAR2(50) ,ServiceDiscoveryProvider NVARCHAR2(300) ,QoSOptions NVARCHAR2(300) ,LoadBalancerOptions NVARCHAR2(300) ,HttpHandlerOptions NVARCHAR2(300) ,LastUpdateTime DATE ,AddTime DATE NOT NULL ,IsDefault NUMBER(4) NOT NULL ) ; COMMENT ON COLUMN OcelotGlobalConfiguration.Id IS '主键'; COMMENT ON COLUMN OcelotGlobalConfiguration.GatewayName IS '网关名称'; COMMENT ON COLUMN OcelotGlobalConfiguration.AddTime IS '添加时间'; COMMENT ON COLUMN OcelotGlobalConfiguration.IsDefault IS '是否默认';-- ---------------------------- -- Records of OcelotGlobalConfiguration -- ---------------------------- INSERT INTO OcelotGlobalConfiguration VALUES ('1', '第一个网关', 'FirstGateway', '', '', '', '', '', '', TO_DATE('1900-01-01 00:00:00.000', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-10-22 18:06:22.337', 'SYYYY-MM-DD HH24:MI:SS'), '1'); COMMIT;-- ---------------------------- -- Table structure for OcelotReRoutes -- ----------------------------CREATE TABLE OcelotReRoutes (Id NUMBER(11) NOT NULL ,OcelotGlobalConfigurationId NUMBER(11) NOT NULL ,UpstreamPathTemplate NVARCHAR2(150) NOT NULL ,UpstreamHttpMethod NVARCHAR2(50) NOT NULL ,UpstreamHost NVARCHAR2(100) NOT NULL ,DownstreamScheme NVARCHAR2(50) ,DownstreamPathTemplate NVARCHAR2(200) ,DownstreamHostAndPorts NVARCHAR2(500) ,AuthenticationOptions NVARCHAR2(300) ,RequestIdKey NVARCHAR2(100) ,CacheOptions NVARCHAR2(200) ,ServiceName NVARCHAR2(100) ,QoSOptions NVARCHAR2(200) ,LoadBalancerOptions NVARCHAR2(200) ,Key NVARCHAR2(100) ,DelegatingHandlers NVARCHAR2(200) ,Priority NUMBER(11) ,Timeout NUMBER(11) ,IsStatus NUMBER(11) NOT NULL ,AddTime DATE NOT NULL ) ; COMMENT ON COLUMN OcelotReRoutes.Id IS '主键'; COMMENT ON COLUMN OcelotReRoutes.OcelotGlobalConfigurationId IS '全局配置ID'; COMMENT ON COLUMN OcelotReRoutes.AddTime IS '添加时间';-- ---------------------------- -- Records of OcelotReRoutes -- ---------------------------- INSERT INTO OcelotReRoutes VALUES ('1', '1', '/connect/token', '[ "POST","GET" ]', ' ', 'http', '/connect/token', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('2', '1', '/connect/authorize', '[ "POST","GET" ]', ' ', 'http', '/connect/authorize', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('3', '1', '/.well-known/openid-configuration', '[ "POST","GET" ]', ' ', 'http', '/.well-known/openid-configuration', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('4', '1', '/.well-known/openid-configuration/jwks', '[ "POST","GET" ]', ' ', 'http', '/.well-known/openid-configuration', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('5', '1', '/connect/userinfo', '[ "POST","GET" ]', ' ', 'http', '/connect/userinfo', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('6', '1', '/connect/endsession', '[ "POST","GET" ]', ' ', 'http', '/connect/endsession', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('7', '1', '/connect/checksession', '[ "POST","GET" ]', ' ', 'http', '/connect/checksession', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('8', '1', '/connect/revocation', '[ "POST","GET" ]', ' ', 'http', '/connect/revocation', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('9', '1', '/connect/introspect', '[ "POST","GET" ]', ' ', 'http', '/connect/introspect', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('10', '1', '/connect/authorize/callback', '[ "POST","GET" ]', ' ', 'http', '/connect/authorize/callback', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('11', '1', '/connect/endsession/callback', '[ "POST","GET" ]', ' ', 'http', '/connect/endsession/callback', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); INSERT INTO OcelotReRoutes VALUES ('12', '1', '/ss1/{everything}', '[ "POST","GET" ]', ' ', 'http', '/api/{everything}', ' [{"Host": "localhost","Port": "5003" },{"Host": "localhost","Port": "1002" }]', '', '', '', '', '', '', '', '', '0', NULL, '1', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS')); COMMIT;-- ---------------------------- -- Primary Key structure for table OcelotGlobalConfiguration -- ---------------------------- ALTER TABLE OcelotGlobalConfiguration ADD CONSTRAINT PK_OcelotGlobalConfiguration PRIMARY KEY (Id);-- ---------------------------- -- Primary Key structure for table OcelotReRoutes -- ---------------------------- ALTER TABLE OcelotReRoutes ADD CONSTRAINT PK_OcelotReRoutes PRIMARY KEY (Id);
注： 修改UPSTREAMHOST 字段为可为空
开始集成：
1.在网关项目中添加对Ocelot.ConfigAuthLimitCache的引用
2.在网关项目的Startup.cs 中修改ConfigureServices节点 标红部分
services.AddOcelot(Configuration).AddAuthLimitCache(opt=> {opt.DbConnectionStrings = Configuration.GetSection("Setting")["ConfigDBConnction"];}).AddConsul();
3.在配置文件中添加数据库连接 蓝色部分
"Setting": {"Port": "5001","ConfigDBConnction": "Data Source=192.168.xxx.xxx/orcl;User ID=xxx;Password=xxx;"},
4.在Program.cs中移除对原配置文件的依赖 注释部分
public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>WebHost.CreateDefaultBuilder(args).UseStartup<Startup>().UseUrls($"http://*:{StartPort}");//.ConfigureAppConfiguration((hostingContext, builder) =>//{// builder.AddJsonFile("configuration.json", false, true);//});
5.修改Ocelot.ConfigAuthLimitCache项目的数据仓储实现
增加
文件
代码：
using Dapper; using Ocelot.Cache; using Ocelot.ConfigAuthLimitCache.Configuration; using Ocelot.ConfigAuthLimitCache.Extensions; using Ocelot.ConfigAuthLimitCache.Models; using Ocelot.Configuration.File; using Ocelot.Configuration.Repository; using Ocelot.Logging; using Ocelot.Responses; using Oracle.ManagedDataAccess.Client; using System; using System.Collections.Generic; using System.Threading.Tasks;namespace Ocelot.ConfigAuthLimitCache.Repository {/// <summary>/// nontracey/// 2019.04-08/// 实现从oracle数据库中提取配置信息/// </summary>public class OracleFileConfigurationRepository : IFileConfigurationRepository{private readonly IOcelotCache<FileConfiguration> _cache;private readonly IOcelotLogger _logger;private readonly ConfigAuthLimitCacheOptions _option;public OracleFileConfigurationRepository(ConfigAuthLimitCacheOptions option, IOcelotCache<FileConfiguration> cache, IOcelotLoggerFactory loggerFactory){_option = option;_cache = cache;_logger = loggerFactory.CreateLogger<OracleFileConfigurationRepository>();}public Task<Response> Set(FileConfiguration fileConfiguration){_cache.AddAndDelete(_option.CachePrefix + "FileConfiguration", fileConfiguration, TimeSpan.FromSeconds(1800), "");return Task.FromResult((Response)new OkResponse());}/// <summary>/// 提取配置信息/// </summary>/// <returns></returns>public async Task<Response<FileConfiguration>> Get(){var config = _cache.Get(_option.CachePrefix + "FileConfiguration", "");if (config != null){return new OkResponse<FileConfiguration>(config);}#region 提取配置信息var file = new FileConfiguration();string glbsql = "select * from OcelotGlobalConfiguration where IsDefault=1 and rownum=1";//提取全局配置信息using (var connection = new OracleConnection(_option.DbConnectionStrings)){var result = await connection.QueryFirstOrDefaultAsync<OcelotGlobalConfiguration>(glbsql);if (result != null){var glb = new FileGlobalConfiguration();glb.BaseUrl = result.BaseUrl;glb.DownstreamScheme = result.DownstreamScheme;glb.RequestIdKey = result.RequestIdKey;if (!String.IsNullOrEmpty(result.HttpHandlerOptions)){glb.HttpHandlerOptions = result.HttpHandlerOptions.ToObject<FileHttpHandlerOptions>();}if (!String.IsNullOrEmpty(result.LoadBalancerOptions)){glb.LoadBalancerOptions = result.LoadBalancerOptions.ToObject<FileLoadBalancerOptions>();}if (!String.IsNullOrEmpty(result.QoSOptions)){glb.QoSOptions = result.QoSOptions.ToObject<FileQoSOptions>();}if (!String.IsNullOrEmpty(result.ServiceDiscoveryProvider)){glb.ServiceDiscoveryProvider = result.ServiceDiscoveryProvider.ToObject<FileServiceDiscoveryProvider>();}file.GlobalConfiguration = glb;//提取路由信息string routesql = "select * from OcelotReRoutes where OcelotGlobalConfigurationId=:OcelotGlobalConfigurationId and IsStatus=1";var routeresult = (await connection.QueryAsync<OcelotReRoutes>(routesql, new { OcelotGlobalConfigurationId=result.Id })).AsList();if (routeresult != null && routeresult.Count > 0){var reroutelist = new List<FileReRoute>();foreach (var model in routeresult){var m = new FileReRoute();if (!String.IsNullOrEmpty(model.AuthenticationOptions)){m.AuthenticationOptions = model.AuthenticationOptions.ToObject<FileAuthenticationOptions>();}if (!String.IsNullOrEmpty(model.CacheOptions)){m.FileCacheOptions = model.CacheOptions.ToObject<FileCacheOptions>();}if (!String.IsNullOrEmpty(model.DelegatingHandlers)){m.DelegatingHandlers = model.DelegatingHandlers.ToObject<List<string>>();}if (!String.IsNullOrEmpty(model.LoadBalancerOptions)){m.LoadBalancerOptions = model.LoadBalancerOptions.ToObject<FileLoadBalancerOptions>();}if (!String.IsNullOrEmpty(model.QoSOptions)){m.QoSOptions = model.QoSOptions.ToObject<FileQoSOptions>();}if (!String.IsNullOrEmpty(model.DownstreamHostAndPorts)){m.DownstreamHostAndPorts = model.DownstreamHostAndPorts.ToObject<List<FileHostAndPort>>();}//开始赋值m.DownstreamPathTemplate = model.DownstreamPathTemplate;m.DownstreamScheme = model.DownstreamScheme;m.Key = model.Key;m.Priority = model.Priority ?? 0;m.RequestIdKey = model.RequestIdKey;m.ServiceName = model.ServiceName;m.Timeout = model.Timeout ?? 0;m.UpstreamHost = model.UpstreamHost;if (!String.IsNullOrEmpty(model.UpstreamHttpMethod)){m.UpstreamHttpMethod = model.UpstreamHttpMethod.ToObject<List<string>>();}m.UpstreamPathTemplate = model.UpstreamPathTemplate;reroutelist.Add(m);}file.ReRoutes = reroutelist;}}else{throw new Exception("未监测到配置信息");}}#endregionif (file.ReRoutes == null || file.ReRoutes.Count == 0){return new OkResponse<FileConfiguration>(null);}return new OkResponse<FileConfiguration>(file);}} }
注：需要添加之前的CommonHelper引用或者 单独添加Oracle.ManagedDataAccess.Core nuget包引用
至此搭建完毕 将配置文件部分的内容移动到数据库即可。
参考链接：
更改到sqlserver
https://www.cnblogs.com/yilezhu/p/9839863.html
