监听数据库数据变化_监听 数据库表的变化 c++ builder

pgsql:

-- 监听channelA频道
LISTEN channelA;

-- 向channelA广播消息
NOTIFY channelA, 'test-message';

-- 监听channelA频道
PERFORM pg_listen('channelA');

-- 向channelA广播消息
PERFORM pg_notify('channelA', 'test-message');

1.数据库中表添加触发器方法

CREATE OR REPLACE FUNCTION "public"."add_channel"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
declare
begin
	PERFORM pg_notify('channela'::text,row_to_json(NEW)::text);
  return new;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

2. 绑定表与触发器关联

CREATE TRIGGER add_channel AFTER INSERT ON 表名称 FOR EACH ROW EXECUTE FUNCTION 触发器方法名();

3 java 中代码 

@SneakyThrows
public static void init(String[] args) {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://xxxx";
        Connection conn = DriverManager.getConnection(url, "xxx", "xxxx");
        PGConnection pgconn = conn.unwrap(PGConnection.class);
        // 监听channelA频道
        Statement stmt = conn.createStatement();
        stmt.execute("LISTEN channela");
        stmt.close();
        scheduledExecutorService.scheduleAtFixedRate(() -> {
            try {
                List<PGNotification> result = Lists.newArrayList(pgconn.getNotifications());
                result.forEach(item -> {
                    System.out.println(JSON.toJSONString(item));
                });
            } catch (SQLException e) {
            }
        }, 0, 1, TimeUnit.SECONDS);
    }

注意点：channel 在触发器中调用时，大小写需要注意

适用

没有其他中间件，好维护。

可以知晓一个业务数据的前后变化的同时，不与之前的业务有联系，耦合程度降低。

mysql :

前提 要开启bin-log 日志 

<dependency>
   <groupId>com.github.shyiko</groupId>
   <artifactId>mysql-binlog-connector-java</artifactId>
   <version>0.21.0</version>
</dependency>

 /**
     * 连接mysqlBinLog
     */
    public void init() {
        BinaryLogClient client = new BinaryLogClient("127.0.0.1", 3306, "root", "123456");
        client.setServerId(2);
        client.registerEventListener(event -> {
            EventData data = event.getData();
            if (data instanceof TableMapEventData) {
                System.out.println("Table:");
                TableMapEventData tableMapEventData = (TableMapEventData) data;
                System.out.println(tableMapEventData.getTable());
            }
            if (data instanceof UpdateRowsEventData) {
                System.out.println("Update:");
                System.out.println(data);
            } else if (data instanceof WriteRowsEventData) {
                System.out.println("Insert:");
                System.out.println(data);
            } else if (data instanceof DeleteRowsEventData) {
                System.out.println("Delete:");
                System.out.println(data);
            }
        });
        try {
            client.connect();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }