微服务网关从零搭建——（七）更改存储方式为oracle
资源准备：
下载开源项目
新建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("未监测到配置信息"); } } #endregion if (file.ReRoutes == null || file.ReRoutes.Count == 0) { return new OkResponse<FileConfiguration>(null); } return new OkResponse<FileConfiguration>(file); } } }
注：需要添加之前的CommonHelper引用或者 单独添加Oracle.ManagedDataAccess.Core nuget包引用
至此搭建完毕 将配置文件部分的内容移动到数据库即可。
参考链接：
更改到sqlserver
