程序中监听数据库变化的各种方法与实践

最新推荐文章于 2024-07-28 02:56:14 发布

置顶

yanghui555

最新推荐文章于 2024-07-28 02:56:14 发布

阅读量9k

点赞数 8

分类专栏：技术方案

本文链接：https://blog.csdn.net/yanghui555/article/details/115356763

版权

本文探讨了如何在程序中监听数据库变化，避免定时任务扫描的低效。介绍了Oracle使用UTL_HTTP包发送HTTP请求、执行Java代码，SQL Server通过XMLHttp发送请求，以及MySQL利用Canal监听数据变化的方法。通过这些技术，可以实现实时捕获数据库变更，提高性能。

摘要由CSDN通过智能技术生成

本文内容

Oracle 的 UTL_HTTP发送Http请求，
Oracle 中执行Java代码
SQLServer XMLHttp 数据库发送Http请求
Mysql 数据监听-Canal Slave伪装

一、程序如何监听数据库变化

在程序监听数据库中数据变化做出及时响应的需求，很难找到一种可靠并且可行的成熟方案。很多情况，不惜牺牲性能，启动定时任务不停的扫描数据库来获取数据变更，这样既牺牲性能，而且无法做到及时监听数据变化。本文将针对如何在程序中及时监听数据库变化且不牺牲性能的前提下实现这样的需求。
本文将给出数据库主动发送Http请求通知应用程序；数据库主动执行Java代码；以及Mysql Salve伪装实时获取数据变化。
看完这篇文章，从此放弃Quartz、轮询、定时任务等方案！
关系型数据库的触发器可以再数据库insert、delete、update事件触发，此时再触发器中由数据库主动发送Http请求，而程序中只需要提供Http API 接口即可实现对数据库数据变更即时捕获，再也不用搞定时任务不断的扫表，不断的查询数据库，性能高、无延迟。

二、数据库与技术方案

在这里插入图片描述

数据库HTTP

已经查明Oracle、SQL Server 支持 Http调用，MySQL5.1-5.5 需要在Linux环境下安装插件mysql-udf-http （暂不支持Windows）

三、本机Http服务SpringBoot代码

@RestController
public class TestController {
   

    @GetMapping("/getUser/{id}")
    public String getUser(@PathVariable("id") String id){
   
        System.out.println("获取参数id="+id);
        return "ok";
    }

    @PostMapping("/getUserPost/{id}")
    public String getUserPost(@PathVariable("id") String id, HttpServletRequest request) {
   
        String name = request.getParameter("name");
        String age = request.getParameter("age");
        System.out.println("Post 获取参数id="+id+",name="+name+",age="+age);
        return "ok";
    }

}

# SpringBoot 配置文件
  
server.port=80
server.servlet.context-path=/

启动本机服务

Oracle UTL_HTTP的使用方法

这里需要说明本地实验的工具是DbVisual、Oracle版本是11g 11.2.0.1 ，分别使用到SYS用户和SCOTT用户。

查看Oracle版本

在这里插入图片描述

1.授权用户可执行UTL_HTTP 包

已SYS用户登录Oracle数据服务，为实验用户SCOTT授权对UTL_HTTP包的执行权限，需要使用命令：

GRANT  EXECUTE ON "UTL_HTTP" TO "SCOTT";
commit;

2.检查创建新的acl访问控制文件

-- 查询ACL控制文件
SELECT * FROM resource_view WHERE any_path like '/sys/acls/%.xml';
-- 创建控制文件：DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
-- 删除控制文件：DBMS_NETWORK_ACL_ADMIN.DROP_ACL( acl => 'www.xml');

在这里插入图片描述

这里需要使用到两条命令，分别是创建ACL文件和删除ACL文件。DBMS_NETWORK_ACL_ADMIN.CREATE_ACL 和DBMS_NETWORK_ACL_ADMIN.DROP_ACL。注意对于本文给出的命令执行需要在PL/SQL 块中执行，就是需要在BEGIN END 中执行，需要说明一点，本文使用的工具是DbVisual 所以执行代码块使用了符号“–/”和 “ /”包裹代码块，这只是DbVisual工具的特性要求，其他工具请忽视。

3.创建acl访问控制文件并为SCOTT用户赋予权限

--/
        BEGIN
                -- 创建文件
                dbms_network_acl_admin.create_acl(
                acl         => 'data_exchange_server_acl.xml',   -- 新文件名
                DESCRIPTION => 'Normal Access',
                principal   => 'CONNECT',  -- 赋予角色 CONNECT
                is_grant    => TRUE,
                PRIVILEGE   => 'connect',
                start_date  => NULL,
                end_date    => NULL);
               
        commit;
        END;
/


--/
        BEGIN
                -- 赋予权限
                dbms_network_acl_admin.add_privilege(
                acl        => 'data_exchange_server_acl.xml',
                principal  => 'SCOTT',-- 赋予权限的大写用户名
                is_grant   => TRUE,
                privilege  => 'connect',
                start_date => null,
                end_date   => null);
                commit;
        END;
/

4.配置ACL访问域名、IP、端口

可以使用命令查询DBA_NETWORK_ACL_PRIVILEGES ,对访问控制列表的管理可以借助DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL和DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL来完成增加和删除。

select * from dba_network_acl_privileges;

在这里插入图片描述

-- 增加访问控制： DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',host => '*.qq.com');
-- 删除访问控制： DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(host => 'www.qq.com');

--/
        BEGIN
                dbms_network_acl_admin.assign_acl(
                acl        => 'data_exchange_server_acl.xml',
                -- 如果使用本机测试的话，不要写localhost 或 127.0.0.1 
                -- 一定写IP地址
                -- 域名,ip地址，ip地址和域名允许使用通配符
                -- 通配符可以是 192.168.1.*  或 *.baidu.com
                host       => '192.168.1.1', 
                lower_port => 80, -- 指定端口号
                upper_port => NULL);
                commit;
        END;
/

-- 查看配置的信息
select * from SYS.DBA_NETWORK_ACLS;

至此，我们已经完成对SCOTT用户使用UTL_HTTP包的前置准备工作，下面切换用户SCOTT登录Oracle服务。

5.创建函数或存储过程访问HTTP

SCOTT用户登录Oracle服务，创建函数完成对HTTP 的GET请求。此例中是最基本的GET请求，需要说明，虽然在设置ACL访问控制列表时可以对域名、I P设置通配符，但是经过实际测试 *.com * 是不支持url传参的例如：“www.test.com?a=1&b=2”这种形式的。但是像Rest方式的 “www.test.com/a”、“www.test.com/a/b/c” 是支持的

对HTTP请求返回的的解析，注意毕竟是数据库，不要幻想对HTTP支持有多么强大，对接口返回JSON、XML格式的相应需要自己想办法解析。所以业务设计中针对数据库请求的HTTP接口无论从请求参数还是返回数据都应该尽量简单。

HTTPS如何请求，这个ORACLE 是支持的，需要先设置SSL 证书。可以参考：

https://oracle-base.com/articles/misc/configure-tcpip-with-ssl-and-tls-for-database-connections

https://docs.oracle.com/cd/E11882_01/network.112/e40393/asoappf.htm#ASOAG9835

GET 请求示例代码如下：

CREATE OR REPLACE FUNCTION "SCOTT"."HTTP_GET"(url varchar2)

RETURN CLOB
AS
BEGIN
          DECLARE
                  -- 声明HttpRequest
                  request UTL_HTTP.REQ;
                  -- 声明HttpResponse
                  response UTL_HTTP.RESP;
                  v_readline CLOB;
                  v_html CLOB;
          BEGIN
                    v_html := '';
                    BEGIN
                      -- 访问指定的网址，并指定第二个参数为 GET 请求。另外还可以设置第三个参数来决定使用协议 HTTP 1.0还是 HTTP1.1
                      request := UTL_HTTP.BEGIN_REQUEST (url,'GET');
                      -- 常规设置字符集
                      UTL_HTTP.SET_BODY_CHARSET('UTF-8');
                      -- 常规设置Request Head 可根据具体场景决定，这里请求网页。
                      UTL_HTTP.SET_HEADER(request, 'Content-Type', 'text/html;charset=utf-8');
                      -- 开始请求并获取返回
                      response := UTL_HTTP.GET_RESPONSE ( request );
                      
                      -- 逐行读取相应信息
                      LOOP
                        UTL_HTTP.READ_LINE ( response, v_readline, TRUE );
                        v_html := v_html || v_readline;
                      END LOOP;
                      
                      -- 关闭response
                      UTL_HTTP.END_RESPONSE(response);
                      -- 关闭request
                      UTL_HTTP.END_REQUEST(request);
                      
                      -- 异常处理
                      EXCEPTION
                        WHEN UTL_HTTP.END_OF_BODY THEN
                                UTL_HTTP.END_RESPONSE (response);
                        WHEN OTHERS THEN
                                UTL_HTTP.END_RESPONSE(response);
                                UTL_HTTP.END_REQUEST(request);
                    END;
                    -- 返回全部相应
                    return v_html;
          END;
END;

执行函数HTTP_GET()向本机发送请求,后台输入打印日志，并返回“ok” 数据库接收并且解析。

select HTTP_GET('http://192.168.1.1/getUser/1') from dual;

在这里插入图片描述

POST请求示例代码：

CREATE OR REPLACE FUNCTION "SCOTT"."HTTP_POST"
(url IN VARCHAR2)
RETURN CLOB
AS
BEGIN
        DECLARE
        request UTL_HTTP.REQ;
        response UTL_HTTP.RESP;
        v_readline CLOB;
        v_html CLOB;
        v_params varchar(300);
        BEGIN
                v_html :='';

                BEGIN
                        request := UTL_HTTP.BEGIN_REQUEST(url,'POST');
                        UTL_HTTP.SET_BODY_CHARSET('UTF-8');
			UTL_HTTP.SET_HEADER(request, 'Content-Type', 'application/x-www-form-urlencoded;charset=UTF-8');
                        -- 中文参数要转义
			v_params := 'age=20&name=' || utl_url.