资源准备：
下载开源项目
新建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 ('', '第一个网关', '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'), '');
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 ('', '', '/connect/token', '[ "POST","GET" ]', ' ', 'http', '/connect/token', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/authorize', '[ "POST","GET" ]', ' ', 'http', '/connect/authorize', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/.well-known/openid-configuration', '[ "POST","GET" ]', ' ', 'http', '/.well-known/openid-configuration', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/.well-known/openid-configuration/jwks', '[ "POST","GET" ]', ' ', 'http', '/.well-known/openid-configuration', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/userinfo', '[ "POST","GET" ]', ' ', 'http', '/connect/userinfo', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/endsession', '[ "POST","GET" ]', ' ', 'http', '/connect/endsession', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/checksession', '[ "POST","GET" ]', ' ', 'http', '/connect/checksession', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/revocation', '[ "POST","GET" ]', ' ', 'http', '/connect/revocation', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/introspect', '[ "POST","GET" ]', ' ', 'http', '/connect/introspect', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/authorize/callback', '[ "POST","GET" ]', ' ', 'http', '/connect/authorize/callback', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/connect/endsession/callback', '[ "POST","GET" ]', ' ', 'http', '/connect/endsession/callback', '[{"Host": "localhost","Port": "5003" }]', '', '', '', '', '', '', '', '', '', NULL, '', TO_DATE('2018-10-22', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO OcelotReRoutes VALUES ('', '', '/ss1/{everything}', '[ "POST","GET" ]', ' ', 'http', '/api/{everything}', ' [{"Host": "localhost","Port": "5003" },{"Host": "localhost","Port": "1002" }]', '', '', '', '', '', '', '', '', '', NULL, '', 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": "",
"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(), "");
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 > )
{
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 ?? ;
m.RequestIdKey = model.RequestIdKey;
m.ServiceName = model.ServiceName;
m.Timeout = model.Timeout ?? ;
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("未监测到配置信息");
}
}
#endregion
if (file.ReRoutes == null || file.ReRoutes.Count == )
{
return new OkResponse<FileConfiguration>(null);
}
return new OkResponse<FileConfiguration>(file);
}
}
}
增加oracle仓储
注：需要添加之前的CommonHelper引用或者 单独添加Oracle.ManagedDataAccess.Core nuget包引用
至此搭建完毕 将配置文件部分的内容移动到数据库即可。
参考链接：
更改到sqlserver