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();
}
}